Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2010
    Posts
    186

    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?

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by Foskbou View Post
    ...My date field is formated to short date mm/dd/yyyy

    Where
    is this formatting done? You might check the form's or report's Property Pane, as formatting done there will take precedence over formatting done at the table level.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Foskbou View Post
    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
    no thats not right, by definition they do because JET uses a datetime coulumn to store dates or time and or times. if you haven't specified a time element, then ther time should be 00:00:00 or midnight. so run a query which looks at your current data and retuirns any rows which do onot have a time of 00:00:00. that will tell you if your first assumption is actually true.
    Quote Originally Posted by Foskbou View Post
    .....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.
    no check your code, check your default values, check your tables
    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
    Quote Originally Posted by Foskbou View Post
    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
    Agree with Missinglinq its probably goign to come down to where you do your date formatting.

    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 code
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •