DSUM stops for some records

[matt beamish]

I have a time recording database that has been working fine for several months. A DSUM and IIf functions have now stopped working, and I think it must be to do with the month changing to 2 digits....

My DSUM relies on the conversion using functions, of any given Access date into what I have called "txtmonthyear" so 1/1/2008 would become 1 2008.
Now we have got to 10 2008 the DSUM has stopped calculating.

My DSUM function is this

=DSum("Monthly_Carry_Forward","Q_Monthly_Carry_Forward","txtMonthYear<Forms!F_Daily_overall_Time_Sheet!txtMonthYear")

Q_Monthly_Carry_Forward" is

SELECT Q_Monthly_Balance.txtMonthYear, IIf([Monthly_Balance]>7.4,7.4,[Monthly_Balance]) AS Monthly_Carry_Forward, Q_Monthly_Balance.StaffInit
FROM Q_Monthly_Balance
WHERE (((Q_Monthly_Balance.StaffInit)="mgb"));

I have tried changing my txt month year functions to include an _ rather than a space (so 10 2008 becomes 10_2008) but this hasnt brought the DSUM function back.

Any ideas appreciated. Maybe I am on the wrong track totally????


Thanks

Matt

 

[georgedwilkinson]

Well, you're comparing a text to a text, not a date to a date. You'd have to expect this type of behavior.

Though I could spend a couple of hours to figure this out (dates are my weak spot), I'll let someone else help you now that we have the ball rolling.

 

[matt beamish]

Thanks for response George - I wait with baited breath....
I thought that comparing text to text would be rock solid as there ain't no date calcs going on behind the scenes, so silly me!

 

[matt beamish]

I now see the error of my asking Access to evaluate text fields. So as not to start from scratch I'd like to persevere with my method (as it was working and I was quite pleased with it), but convert my dates in a different way.

I am trying to group a range of dates by their months, and then do various calcs on hours worked within those months.

I can use the month and year functions to return integers.
I can then combine these integers to create a six figure integer e.g. 200809 and 200810. I hope that Access will be able to evaluate these and recognise that 200809<200810.

Does anyone know how I force the Month function to two digits for every month, and so return 01 for January rather than 1?

Thanks

Matt

 

[Rabbie]

Try something like

format(month(Datetoconvert),"00")

 

[matt beamish]

Thanks Rabbie - yes that works fine.

I subsequently found that by applying a format to the individual dates in the queries of "yyyymm" (in the properties box of the field in the query) this has worked OK (although I need to be consistent in applying the formatting to the field, or the date will revert back to the full date). So I can now sort, group and evaluate dates in their months and years.

Your post has taught me how to apply a Format command in the field description of a query which is really useful, as formatting applied in properties boxes is easy to miss, so this is much better.

Sorry for the delay in reply.

Matt