When I use the date ranges you last mentioned, I get correct results..
I have attached screen shots.
Also a shot of the DB contents for others to see.
What country are you in ? (relates to your Windows Date settings)
It really is a 'lucky dip' as to what VB and Access and Windows decide to do when they are interpreting your DD/MM/YYYY
Try editing your dates in the DB from 04/04/2005 to 04/Apr/2005
Do that with the other dates, and run the pgm again.
If it then works, then you know you should not trust the system's guess at what ../04/.... means. You should use ../Apr/.... instead.
I am attaching the Original and the one I tweaked.
I have also used .MoveFirst to ensure that the loop through records is picking up all records. (On my runs, it made no difference, but it is a good habit).
Try editing your dates in the DB from 04/04/2005 to 04/Apr/2005
Do that with the other dates, and run the pgm again.
What exactly do you mean by this?
Dates cannot be edited and stored differently in a DB - they are stored in a quadword format that has nothing to do with the display presentation you see.
Most of the confusion I've seen on the forum about dates is directly related to this point. People think they are seeing dates a certain way and then construct weak queries around those assumptions.
I agree with you completely that using an APR instead of 04 format for month is clearly less ambiguous and will probably work for this person...
It's all about coming up with a date formatting concept that works and then using it consistently.
*** Read the sticky in the DB forum about how to get your question answered quickly!! ***
Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".
Normally I wouldn't be tweaking the DB directly like that.
Probably what I should have said is any dates that are being interpreted by code for storage should use Apr instead of 04, so that the correct date is stored. I did mention earlier that Access stores it internally in it's own way, I just forgot to tell myself that, when I quickly said 'try editing your dates'
When you say 'cant work on my PC' what exactly happens (or not) ?
The pgm runs I assume ?
Just when you select the date range you don't see all the records you expect ?
I Looked at the solution you last posted, and noticed that you hadn't really solved the International Date settings problem, You had just made individual variations between using DD/MM/YYYY and MM/DD/YYYY in different places in your pgm.
That was not the way to solve this problem, it is just 'band aiding' it.
I have just run your pgm now, and I am attaching the results.
You haven't said which country you are in, but because we are getting mixed results, I can bet it is not Australia.
So you should either move to Australia, or come up with a solution that is 'fool proof'.
I am also attaching the contents of your DB. If you study them you will see that my run of your pgm returns the three records that the system is forced into thinking are Apr or May.
The other two records can be interpreted as being Dec or Nov, and I guess that is what is happening, in an Australian run of your pgm.
You haven't shown the code that was writing the data to the DB. I am guessing that it is not 'date proof'.
As I said in my first post, I am frightened of Dates, and have invented an alternative approach which uses Longs', which is 'fool proof'
I can tell, that you wil not be open to that approach, so instead you should post all of your code, and really l i s t e n to the other experts.
i m from Malaysia. i get your point. the only thing is i need to change the format before it display on the grid which i havent done it.
when i run your pgm, i put 03/04/2005 to 30/04/2005, i get the result: 04/04/2005 only
for 10/04/2005 to 30/04/2005 the result is 24/05/2005
the result seems totally out of control.
Like I said in my post (#40) - formatting of dates for selection is something that has to be worked out and consistently applied. You will go around in circles if you do not have a firm foundation to work with.
You must come up with a format (yyyy-mm-dd or dd mmm yyyy or whatever works with your regional settings and version of DB) - determine that it does work and then consistently apply it in your program. This R&D portion of development cannot be skipped over.
Once you have a format that works, wrap the logic for "formatting" that into a nice FUNCTION buried in a module somewhere - and use that function everywhere for date translation.
We like to have a function that can translate from "stored" format, "selection" format and "display" format - all with the same function - based on a "output type" parameter.
*** Read the sticky in the DB forum about how to get your question answered quickly!! ***
Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".
Reply With Quote
Originally Posted by RobCrombie
