Thread: Union query dates
02-03-11, 18:21 #1Registered User
- Join Date
- Jun 2010
Unanswered: Union query dates
I've been curious about dates displayed in my reports because some of them come up with the time, most don't. My date field is formated to short date mm/dd/yyyy and default value is =date() (today).
My tables don't store the time and my select queries don't store the time.....I think the date is converted to show time in the union query.
I have noticed that the times are on groups of the same day.
Also when entering data anytime the default value is changed or field backed into in the form, it adds the time. I realize that that is something to do with regional setting on the computer, which I am unable to change.
Are there any other tricks I can do to my format, default value, or report to prevent the time from displaying and printing? I just don't have the room for it! LOL
Perhaps lock the date field so that only =Date() is stored, and/or remove the tab stop?
What about write a text box into the report that will clean, or trim the date? That's my excel talking, can that be done?
02-06-11, 12:19 #2Moderator
Provided Answers: 19Hope this helps!
- Join Date
- Jun 2005
- Richmond, Virginia USA
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007
02-06-11, 12:41 #3Jaded Developer
Provided Answers: 59
- Join Date
- Nov 2004
- out on a limb
an easy way to check what is actaully going on is see if there is a time element in your data is to format it for time.
bear in mind the datetime value is so many ticks from a specified point in time (IIRC: 01 Jan 1900) the integer bit is the number fo days since 'der tag' the decimal bit is number of seconds since der tag expressed as 1 / (24 * 60 * 60). so you are dealing with real numbers, probably double precision and thats a whole potetnail minefield for comprisons
what you could do is explicitly cast the date time column to "long" and do your union on that cast value.. that would remove any prospect floating point / double precision variations. or you coudl explcitly format the date to be date only using format
but check your code to make certain that no one is settign a time value
check your data to make certain you have no time values in there
check your tables to make certain you have no default values
if there is time data try and work out if there is a consistent pattern
bear in mind date() isn't the same as now(), I guess you already have as you seem aware, but check whats under the hood. make certain no one has put time values in there. thats the fix Id want to ensure, that there is no time data in there if there shouldn't be. knowing what is in the db as opposed to what you think is there, should be the first step to knowing where your problem lies. the repeat that process with your codeI'd rather be riding on the Tiger 800 or the Norton