Criteria to search a month

[LizzleJ]

Hi

I have the date on my table as 01/02/2006, there are others like this, i'm wondering how i can search for the whole month, something like **/02/2006. i have tried that way and didn;t work.

Any ideas??

Thanks

 

[wazz]

- add your date field twice to a query (don't show the second one)
- in the second one: Month(YourDateField)
- to find a specific month, put a number (1-12) in the critera

 

[LizzleJ]

Hi

thanks for the info, i see im stil ldoing something wrong as no records are found, i have attached a screenshot to the post.

where do i put the (1) criteria as i tried both and no record are found when they are in the table. i also tried this without that extra booking type field.

Thanks

 

[wazz]

- remove the colon after Month, as above. access will probably change it to -> Expr: Month([FullDate])
- put the month-criteria in the 2nd month-column, in the criteria row, just to the left of "Occasional". use a number to represent the month, i.e. 4 (for april).
- i didn't realize you are doing a totals-query. try removing the first date field. if that doesn't do it (it might not), fool around with it a bit more and let me (us) know how it goes. there are different ways to do this. i hope this gives you the fix you need.

 

[raskew]

Hi -

Copy/paste this to the criteria cell of your date field then, when prompted, enter the mm/yyyy you desire, e.g. 06/2006.

Between DateValue([enter mm/yyyy]) And DateAdd("m",1,DateValue([enter mm/yyyy])-1)

The DateValue() function, when fed only a month and year returns the first day of the specified month. Adding one month to that, using the DateAdd() function and then subtracing one day returns the last day of the specified month.

HTH - Bob

 

[LizzleJ]

Ahh thanks to both Wazz and raskew, i wasn't going to use raskew's way as i didn't understand the +1 and -1. Then i read again i now understand.

Thanks