DATE/MONTH CRITERIA

[MICHELE]

If I have a form so that the user selects a date for the criteria, how can I use that one date to select the whole month?

 

[Hayley Baxter]

You could have a start and end date so you can then enter date1 (first date of month) and end date (last day of month) or alternatively set the criteria to equal month rather than a day.

 

[RV]

Get the month and year of the selected date:

Format(SelectedDate,"yyyym")

and compare this to "select the whole month" (you need to be more specific to get a more specific answer....).

Suc6,

RV

 

[MICHELE]

Specifically I have a form with AcriveX0 and ActiveX12. These are the criteria for my report. One date on each will be chosen (these are the criteria for another report that goes with this one-subform/mainform).
I would like for this new subform's to select the whole month for that ActiveX0 and the whole month for ActiveX12. So it could be the total of the whole month of the selected day on the ActiveX controls.

Please let me know if I should explain it better.

 

[Jack Cowley]

You can add a column to your query like this -

TheMonth:Month([FieldWithDate])

The FieldWithDate is the field in your query that has the date you want. In the Critheria line of the column put code like this -

Month([Forms]![YourFormName]!(FieldWithDate])

You should consider using the Year() function as well if your data contains more than the current years dates.

 

[MICHELE]

Thanks it's working now, especiall;y the year idea. But I was wondering what is an easy way to have it say the month name instead of the number?

 

[Jack Cowley]

Use the MonthName() function....

 

[MICHELE]

I tried that and it kept giving me an error. I put my field name from the table in there, not the activeX field name, and I didn't know what to put in the abbreviate part.
What should I be using?