Dates confuzzle me....

[Swift_74d]

Slowly getting a grasp on access, but dates still throw me for a loop. I'm trying to filter a query by a field called "TodaysDate". The query is triggered by a form in which the user inputs two vital parts of the filter; "Year" and "Month". I need the query to show all entrys between 1/1/"year" and "month"/31/"year" but i keep getting a formating error no matter how i try.

I know this should be simple, but i just cant get the formating correct.
If it helps the "TodaysDate" has an imput mask of 99/99/0000;0;_

 

[alansidman]

When using dates in the criteria of a query, the dates need to be surrounded by # signs, ie. Today's date would appear in the criteria as #5/10/2010# using US date convention

Alan

 

[boblarson]

Okay, first up - the input mask is irrelevant here. Anyway, it would be BEST if you could build a form for them to select the year and month but with parameter prompts it becomes a little more of a pain for the user, especially if they mistype something they will have to start over and type everything again.

You might be able to use:

Between DateSerial([Enter Year], 1, 1) And DateSerial([Enter Year], [Enter Month] + 1, 0)

Not all months have 31 days so you need to get the last day of the month and that is what the month plus one and using day zero (0) does in the DateSerial function.

 

[Swift_74d]

Thank you, i kept trying to concatenate the variable into something usable but date serial makes a huge difference.