query criteria

[Humberto]

I have a form from where I get the criteria to a query. two textboxes will be dates (ex. 12/12/01). one for the beggining date of the query and the other one for the end date of the query. I also have more textboxes that also affect the query result, but that is currency. my problem is that when I use the BETWEEN and AND for the date range it only works if the table is sorted by date but the other criteria which will not be sorted will come out incorrect. all help will be appreciated.

 

[jwindon]

I think it would be helpful if you posted your SQL. Goto View, then SQL View. Copy and paste on the forum.

 

[Humberto]

here is what I have in criteria for date
>=[forms]![queryform]![fromdte]<=[forms]![queryform]![todte]

 

[Humberto]

I was also using this in the criteria
between [forms]![queryform]![fromdte] and forms]![queryform]![todte] it only worked on the date field because it was sorted by date, for the other fields it did not work.

 

[Humberto]

SELECT [Cash Disbursment].Date, [Cash Disbursment].PreviousBalance, [Cash Disbursment].MoneyOrder, [Cash Disbursment].CashIn, [Cash Disbursment].TellerDrawer, [Cash Disbursment].CashOut, [Cash Disbursment].ATMIn, [Cash Disbursment].ATMOut, [Cash Disbursment].LooseCoin, [Cash Disbursment].Totals
FROM [Cash Disbursment]
WHERE ((([Cash Disbursment].Date) Between [forms]![query]![dte] And [forms]![query]![todte]))
ORDER BY [Cash Disbursment].Date DESC;

 

[Rich]

What is .Date? It's a reserved word in access. Is that a field/table name?

 

[Hayley Baxter]

I've got a problem similar to this that someone might be able to help me with. I am trying to produce a report based on a query. I want the report to list all the contracts I have that are due to expire within 3mths of the current date. I've looked at the help files for between and and and it does look pretty straighforward, however I have invalid syntax in my statement can anyone see why?

ContractEndDate Date() And DateAdd("m",3,Date())

I have this criteria under my contractend date field. The help files have BetweenDate as the first part of this criteria where I have contractEnd Date. Im assuming this could be my problem. Is this also a recognised word in access?

If so I also tried this

BetweenDate Date() And DateAdd("m",3,Date())

and I get this "You may have entered an operand without an operator" What am I doing wrong?

Many thanks

 

[Rich]

Add the field contractEnd Date to the query grid, in the criteria for that field put
<=DateAdd("m",3,Date())
HTH

 

[Humberto]

.Date is a field if its an Access reserved word I changed it but Im having the same problems. here is the sql..
SELECT [Cash Disbursment].Dte, [Cash Disbursment].PreviousBalance, [Cash Disbursment].MoneyOrder, [Cash Disbursment].CashIn, [Cash Disbursment].TellerDrawer, [Cash Disbursment].CashOut, [Cash Disbursment].ATMIn, [Cash Disbursment].ATMOut, [Cash Disbursment].LooseCoin, [Cash Disbursment].Totals
FROM [Cash Disbursment]
WHERE ((([Cash Disbursment].Dte) Between [forms]![query]![dte] And [forms]![query]![todte]))
ORDER BY [Cash Disbursment].Dte DESC;

 

[Humberto]

Yes, the Dte field defined in the table as a Date/Time data type, the query result for the dte field comes out correct, but the rest of the data comes out incorrect. For the date criteria Im using the BETWEEN and AND, but for the other criteria which is acquired from the same form comes out wrong if I use the BETWEEN and AND for those fields. what can I use instead of that for the rest of the field.

 

[Humberto]

SELECT [Cash Disbursment].Date, [Cash Disbursment].PreviousBalance, [Cash Disbursment].MoneyOrder, [Cash Disbursment].CashIn, [Cash Disbursment].TellerDrawer, [Cash Disbursment].CashOut, [Cash Disbursment].ATMIn, [Cash Disbursment].ATMOut, [Cash Disbursment].LooseCoin, [Cash Disbursment].Totals
FROM [Cash Disbursment]
WHERE ((([Cash Disbursment].Date) Between [forms]![query]![dte] And [forms]![query]![todte]) AND (([Cash Disbursment].PreviousBalance) Like ([Cash Disbursment].[PreviousBalance])<=[forms]![query]![previousbalance]>=[forms]![query]![topreviousbalance]))
ORDER BY [Cash Disbursment].Date DESC;

the code for Previous balance returns nothing, and there are records with that criteria

 

[Humberto]

the SQL that you gave me does not give me anything. what I had gave me the right result for the date field, but what I dont know is what to put in the criteria of the other fields, because if I use between it gives me an error, something like "expression is typed incorrectly or is to complex to be evaluated"