Re: Date format problem

From: Warren Siu (WarrenSiu_at_discussions.microsoft.com)
Date: 11/22/04

  • Next message: Allen Browne: "Re: Date format problem" 
    Date: Mon, 22 Nov 2004 00:29:09 -0800

    Dear Allen
    By using your advised function, I made two queries for the tables and the
    dates fields are successfully shown as double numbers. However, when the
    matching actually take place the double numbers date are automatically return
    to short date format...and the same happened.

    "Allen Browne" wrote:

    > Okay, Warren.
    >
    > Now ask Access to show you the fields as numbers. Create a query, and enter
    > this as a calculated field into a fresh column in the Field row:
    > CDbl(Nz([date1], #0:00:00#))
    >
    > Do you see whole numbers returned? Fractional numbers? Do they match?
    >
    > This should help you to determine whether the problem is caused by:
    > - wrong century in the date;
    > - time aspects;
    > - incorrect data types (e.g. Text, not Date)
    > - incorrect date formats.
    >
    >
    > BTW, since you are using a date format different to the US, you might find
    > this article helpful:
    > International Date Formats in Access
    > at:
    > http://members.iinet.net.au/~allenbrowne/ser-36.html
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Warren Siu" <WarrenSiu@discussions.microsoft.com> wrote in message
    > news:6824F969-795B-4454-BD24-138934D24132@microsoft.com...
    > > There is no time information on either date fields.
    > > I've got the right result records with clear date, eg 16-Nov-04, however,
    > > whose records with date such as 12-Nov-04 may be missing.
    > >
    > >
    > > "Warren Siu" wrote:
    > >
    > >> I've two table, say table1 and table2, and they have date type field
    > >> [date1]
    > >> and [date2] respectively. They are in Medium Date format. I've tried to
    > >> loop
    > >> through the table1 to find records in table2 by using a combination of
    > >> conditions with matching of [date1] to [date2]. Some expected records
    > >> were
    > >> missed at the result and it seems to be the date format problem. I've
    > >> tried
    > >> to change the date fields in Long Date format, but it didn't work. Does
    > >> anyone tell me how to fix the problem. Thanks
    >
    >
    >

  • Next message: Allen Browne: "Re: Date format problem"

    Relevant Pages

    • Re: Checking for results of SQL statements without saving queries.
      ... > International Date Formats in Access ... > Allen Browne - Microsoft MVP. ... rather than allenbrowne at mvps dot org. ... >> Kind Regards! ...
      (microsoft.public.access.queries)
    • Re: US vs UK date format
      ... International Date Formats in Access ... The article explains that literal dates in VBA and SQL statements are ... rather than allenbrowne at mvps dot org. ...
      (microsoft.public.access.forms)
    • Re: European Date Format
      ... International Date Formats in Access ... Allen Browne - Microsoft MVP. ... Perth, Western Australia. ... "Angus" wrote in message ...
      (microsoft.public.access.forms)
    • Conditional Panel Freezing
      ... I have tables of different formats, and they cover more lines than ... panes regarding which information is in the top line, ... table has its matching top line? ... JFL ...
      (microsoft.public.excel)
    • Re: issue with date ordering
      ... International Date Formats in Access ... The article explains how you must not use your own local date format in a SQL criteria expression. ... Allen Browne - Microsoft MVP. ... after executing this query. ...
      (microsoft.public.access.queries)