Date Calculation

[ckeezer]

Here is the scenerio:
I have two tables:
tblDate: Contains on Months (i.e.Jan, Feb, Mar, etc)
tblActivities: Contains 4 fields Name, date, activity and hours

I have greated a form that allows me to select what month I want to see. It has a simple combo box in it that point back to tblDate.date. I setup a macro that is doing a OpenReport, there Where Clause is as follows:
[Forms]![frmMnthSelect]![Combo0] Like [tblVolunteers]![Date]

Of course this is not working, and I am at a loss.

I have a Query that is setup that is pulling all records for the current month, but I need to be able to select individual months, because I often have to pull reports for past and future projects.

Can some one start me out in the right direction......

Thanks,
Chuck

 

[Pat Hartman]

The Like operator is ONLY used with text fields that are incomplete and concatenated with wildcard characters to indicate how the missing columns should be selected. It is NEVER used with dates or other numeric fields.

When working with date fields, you need to use the appropriate date functions such as Year() and Month() to extract parts of a date.

BTW, you can't select on just month if your table contains data from multiple years. You also need to include year in the selection criteria.

 

[ckeezer]

Ok, How can I pull the records for multiple years then? Will I need to setup a Month and Year selection? Also, since I obviously am not doing to well with the code, can you get me going. All I need is a hint or two I do not want anyone to write the code for me. Thanks

Chuck

 

[Pat Hartman]

Do you mean you want the same month from multiple years?

Where Month(YourDate) = Forms!yourformname!yourcombo; - the combo needs to show a text field but be bound to a numeric field for this to work.

 

[ckeezer]

Tried but failed

Here is what I have in the Where Clause now:
Month([tblVolunteers]![Date])=[Forms]![frmMnthSelect]![Date1]

Date1 is an Unbound Text box and the forms controll source is TblVolunteers, my main tbl. I keep getting error:

Enter Parameter Value:​

Forms!frmMnthSelect!Date1​

When I put in a number for the month (i.e; 9 for Sep, 8 for Aug) Once I fill in the error with a vaild number it will pull my report with the correct records.... Any idea what I am doing wrong.

Chuck

 

[phoenicks7]

I'm not sure exactly what you're trying to accomplish..... but, I have a project in the works now, where we enter Operator statistics on a weekly basis (Week Beginning Monday). However, our QA department only does a Monthly Analysis of the voice records.

So... from the Weekly Events Table, we need to assign the Monthly QA score by comparing the dates.

[QA]![Date] = January, 2005 (February, 2005... March, 2005.. etc)

[Events Table]![Week] = 09/05/05 (09/12/05... 09/19/05... etc, etc)

So... when I pull the data into a report / form for whatever reason.... I do a simple comparison

SELECT ---/ /---- WHERE (Month([Events Table]![Week]) = Month([QA]![Date])) AND (Year([Events Table]![Week]) = Year([QA]![Date]))

So, when the report lists the four (or five) weeks for the month of September... the QA score for the month of September gets included with the data requested.

I don't know if this helps clarify your situation or not, and there might be an easier way to do the same thing. But this was a good fix for my situation.