Between [ ] And [ ] problem (1 Viewer)

[ajak1316]

Hi Neil/Bob

SQL Cut and paste:

SELECT test1.date, test1.days, [Date]+[Days] AS [Latest re test], *
FROM test1
WHERE ((([Date]+[Days]) Between [First date] And [Last date]))
ORDER BY [Date]+[Days];

Hope this helps.

ajak

 

[Jon K]

Access is a little confused when evaluating the criteria. Try helping it by declaring the data type of the two parameters.

PARAMETERS [First date] DateTime, [Last date] DateTime;
SELECT test1.date, test1.days, [Date]+[Days] AS [Latest re test], *
FROM test1
WHERE ((([Date]+[Days]) Between [First date] And [Last date]))
ORDER BY [Date]+[Days];


Note:
Date is a function name. It's better not to use it as a field name.
.

 

[ajak1316]

Hi Jon

Thank you very much for that. Very new to Access and completely uninitiated in SQL I'm afraid. What adjustments do I make to my query to try this?

Regards


ajak

 

[raskew]

Look at Post #22. Jon just showed you verbatim (see the bolded line).

Bob

 

[ajak1316]

Hi Jon

I figured out how to change the SQL thanks. Your right, that solves the problem but I can't see that it has made any difference to my Query. I guess this must be a weakness in Access and I may have to start familiarising myself with SQL, if I ever get the time.

Thanks to all contributors for your efforts. I do appreciate it.


ajak

 

[Brianwarnock]

Hi Jon

Your right, that solves the problem but I can't see that it has made any difference to my Query.
ajak

non working to working is one helluva difference, and an important one.
I still prefer to use forms to drive queries, there are examples on the forum , Jon K has a major contribution in the reference section.

Brian