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.
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 SeetoOP
@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!
Sorry, only students may add comments.
Click here for more
information on how you can set up an account.
If you are a Visitor, go ahead and post your reply as a
new comment, and we'll move it here for you
once it's approved. Be sure to use the same name and email address.
This thread is now CLOSED. If you wish to comment, start a NEW discussion in
Access Expert 29.
