Date By Month... Aghhhh!

[shift2076]

Howdy All!

I have a subquery which is supposed to filter overtime hours based on name and date by month. The name is working fine, but it's skipping over the date.

(SELECT SUM([Hours1])FROM[OTHours1] WHERE [OTHours1].[CoveringName1]=[CertificationRoster].[NameLast] AND [OTHours1].[LeaveDate By Month] = [LeaveDate By Month]) AS OTTotals1

I took the filter by name out to test and it tallied up the overtime hours with no regards to month.

The format for date by month is the same in the originating query, OTHours1, and this query (mmmm yyyy). So I don't know why MS Access isn't connecting the dots here.

May the force be with you,
Matt

 

[pbaldy]

The working part has both tables specified, the non-working part doesn't.

 

[shift2076]

I believe you are referring to the [LeaveDate By Month] without a specifying table or query. But that is the [LeaveDate By Month] from THIS query that the originating query is supposed to compare to.

(If that makes any sense)

I tried to add the query title to it ---
[OTHours1].[LeaveDate By Month] = [OTTotals].[LeaveDate By Month]
but that did not work.

Thank you,
Matt

 

[shift2076]

P.S. I also tried -

[OTHours1].[LeaveDate By Month] = Format$([VacationTable].[LeaveDate],'mmmm yyyy')

And got the same results

 

[pbaldy]

I would have expected:

[CertificationRoster].[LeaveDate By Month]

Are you trying to get input from a user?

 

[shift2076]

No, just trying to do some summing and grouping of various values for the "Overtime Tracking" report.

My latest test, I took out the name portion of the subquery, and changed the date part to:
[OTHours1].[LeaveDate By Month] = "March 2007"
And it returned the result that it should have.

This is telling me that you were correct in the fact that, that is where the error is. Now I just need to know what to enter in that spot.

Thanx, Matt

 

[shift2076]

I figured maybe there was a problem with my AND statement, incorrect punctuation or something so I kept the whole statement intact and just changed the date value comparison:

From:
(SELECT SUM([Hours1])FROM[OTHours1] WHERE [OTHours1].[CoveringName1]=[CertificationRoster].[NameLast] AND [OTHours1].[LeaveDate By Month] = [LeaveDate By Month]) AS OTTotals1

To:
(SELECT SUM([Hours1])FROM[OTHours1] WHERE [OTHours1].[CoveringName1]=[CertificationRoster].[NameLast] AND [OTHours1].[LeaveDate By Month] = "March 2007") AS OTTotals1

And it still pulled an accurate tally of the individuals taking vacation in March of this year.

Anyone have an idea?

Thanx,
Matt

 

[pbaldy]

I doubt "[LeaveDate By Month]" by itself will return the correct value, because I'm willing to bet that without a table/query specified, Access will assume the field comes from the current table. In this case, that would be OTHours1, so you're basically comparing the value to itself. Where is that value coming from? Are you certain it returns a value in the expected format of "March 2007"? Can you post a sample db?

 

[shift2076]

Here is the full SQL:

SELECT DISTINCTROW Format$([VacationTable].[LeaveDate],'mmmm yyyy') AS [LeaveDate By Month], CertificationRoster.NameLast, CertificationRoster.NameFirst, CertificationRoster.Crew, (SELECT SUM([Hours1])FROM[OTHours1] WHERE [OTHours1].[CoveringName1]=[CertificationRoster].[NameLast] AND [OTHours1].[LeaveDate By Month] = [LeaveDate By Month]) AS OTTotals1, (SELECT SUM([Hours2])FROM[OTHours2] WHERE [OTHours2].[CoveringName2]=[CertificationRoster].[NameLast] AND [OTHours2].[LeaveDate By Month] = [LeaveDate By Month]) AS OTTotals2, (SELECT SUM([Hours3])FROM[OTHours3] WHERE [OTHours3].[CoveringName3]=[CertificationRoster].[NameLast] AND [OTHours3].[LeaveDate By Month] = [LeaveDate By Month]) AS OTTotals3, [OTTotals1]+[OTTotals2]+[OTTotals3] AS OTTotalHours
FROM CertificationRoster, VacationTable
GROUP BY Format$([VacationTable].[LeaveDate],'mmmm yyyy'), CertificationRoster.NameLast, CertificationRoster.NameFirst, CertificationRoster.Crew, Year([VacationTable].[LeaveDate])*12+DatePart('m',[VacationTable].[LeaveDate])-1;

As you can see on the top, [LeaveDate By Month] is referring to the date grouping of this particular statement. And it is in the same format as the [OTHours1] ( and 2 & 3). I just double checked.

This all seems so simple... I'm stumped though.

Thanx,
Matt

 

[shift2076]

OH, and OTHours1 is a seperate query. This query is OTTotals.

 

[Brianwarnock]

Like Pbaldy I feel a bit uneasy about [LeaveDate By Month] by itself, just to test things i would change As [LeaveDate By Month] to As [LeaveDateByMonth] and of course places where it is referenced and see what happens.

 

[shift2076]

I changed the [LeaveDate By Month] to [LeaveDateByMonth] then to [Date By Month] just for the fun of it, but when I went to the datasheet, it just brought up the 'Enter parameter' dialog box. I made sure that it was changed everywhere in the query.

Matt

 

[shift2076]

What I would like to try is to leave the whole query grouped under the [LeaveDate By Month], but where it's used as a comparison in the subquery, use the actual date field formatted as mmmm yyyy. I'm just not sure of the syntax... punctuation and all.

Matt

 

[shift2076]

I wrote a seperate expression for the date comparison calling it [MonthFilter]. Then changed the three instances of [LeaveDate By Month] to [MonthFilter]. I got the same results as before where it just brought up a dialog box in datasheet view asking for the value of [MonthFilter]

Matt

 

[Brianwarnock]

I'm not totally surprised by the result, you see I don't think that you can use a field defined by an As in the query in which it is defined, in a Where clause, the reason you were not getting errors is that such a field existed, tho' not the one you want, you may have to use another query to create the field and then input that query, but without the means of testing I'm not sure, maybe you could use the original format expression, I don't know.

Brian

 

[shift2076]

Resolved!!!

Well... I finally resolved this issue. It required a complete rewrite. I have Access 2003 at home (only 2000 here at work) and in the 2003 help files I found a reference to writing a union query. This query did all the date combining.

Thank you for your help!
Matt