Access Exp 29 lesson 3 - Microsoft Access Expert 29


	Back to Access Expert 29 Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Access Exp 29 lesson 3

Julie Seeto

3 years ago

H, I'm having trouble getting the Orders30Days text box to work on the MainMenuF - I'm following what Rick says:
=DSum("LineTotal","OrdersForStatsQ","OrderDate>=#" & Date()-30 & "#")
but it doesn't work, the text box is blank. I've made sure there are valid orders to find.
However, when I use an actual date in the Control Source
=DSum("LineTotal","OrdersForStatsQ","OrderDate>=#1/2/2023#")
I get results. I want to use Date()-30.
What am I doing wrong? I remember I had this same problem in Lesson 11 but ignored it, now I want to know why. I hope you can help.

	Kevin Robertson @Reply 3 years ago
Check the format of your dates. Ensure the Date() function is returning the same format as OrderDate. Format

	Julie Seeto OP @Reply 3 years ago
The Date() function does return the same format as OrderDate. I used the date picker to enter order dates. I put a temporary text box on the form =Date() and it's the same format as the order date (dd/mm/yyyy) Any other suggestions?

Julie Seeto OP @Reply

3 years ago

Thank you, Kevin.
My regional date format (Australia) is dd/mm/yyyy. My son who is a programmer (though unfamiliar with Access) thankfully helped me find the problem. As it turns out, in #" & Date()-30 & "#" Access was ignoring regional format, assuming instead the format mm/dd/yyyy for all my dates from the 1st to the 12th of the month.
We found a workaround on Google using #" & Format(Date()-30,"mm\/dd\/yyyy") & "#"
Date()-30 now works in my database and I have my sanity back!
Have a great day!

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Expert 29.

Next Unseen

New Feature: Comment Live View