Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date query showing wrong dates in output (2003)

    On running a date query l require dates less than 01/11/06 yet it is showing dates with 2007 on the end, see screenshot.
    How can l get it so only dates less than or equal to 01/11/06 are showing?
    As well as that l would like blanks removed.

  2. #2
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date query showing wrong dates in output (2003)

    Set the criteria in the Renewal Date field to <=#01/11/2006# and that should do the trick (it should remove the blanks as well).

  3. #3
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date query showing wrong dates in output (2003)

    Hi,

    I put in &lt;=#01/11/2006# and also l tried #01/11/2006# in the date criteria field and neither worked , i.e blank results.
    What l would like is NULL values not shown and dates before 01/11/06.
    I am having trouble removing NULLS showing dates with 2007 in.

  4. #4
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date query showing wrong dates in output (2003)

    Is the field from your table formatted as Date?

  5. #5
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date query showing wrong dates in output (2003)

    It is date and time field in Excel but text in Access, l cannot change format in Access as field length in table is > 255. Any suggestions?

  6. #6
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date query showing wrong dates in output (2003)

    I cannot change to date format as need to import as all text format , as tried with date format in Excel and did not import correctly

  7. #7
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date query showing wrong dates in output (2003)

    Since it is formatted as text, your criteria will not work. Create a calc: DateValue([YourFieldName]) and in the criteria for this field, enter the <=#01/11/2006#.

  8. #8
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Date query showing wrong dates in output (2003

    In the query view create a new field

    and type

    Newdate: Format([Date],"dd-mm-yy") where Date is the name of the field holding the date as a text string. This will convert the string into a date
    Jerry

Posting Permissions

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