Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Problem with SQL? (A2k (9.0.4402) SR-1)

    Problem with SQL?

    A2k (9.0.4402) SR-1

    I have four records with the following record create dates:

    11/07/01
    11/08/01
    11/08/01
    11/09/01

    This SQL returns all four records:

    strSQLSELECT s.lngBPermitID INTO tblResultsBP
    FROM qrytblBPermit s
    WHERE s.dtmDateCreate >= #11/07/01#
    AND s.dtmDateCreate <= #11/09/01# ORDER BY strPermitNo

    This SQL returns no records:

    strSQLSELECT s.lngBPermitID INTO tblResultsBP
    FROM qrytblBPermit s
    WHERE s.dtmDateCreate >= #11/08/01#
    AND s.dtmDateCreate <= #11/08/01# ORDER BY strPermitNo

    This SQL returns the 11/07/01 record only:

    strSQLSELECT s.lngBPermitID INTO tblResultsBP
    FROM qrytblBPermit s
    WHERE s.dtmDateCreate >= #11/07/01#
    AND s.dtmDateCreate <= #11/08/01# ORDER BY strPermitNo

    What is wrong with the second and third SQL statement?

    Thanks, John Graves

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Problem with SQL? (A2k (9.0.4402) SR-1)

    Hi John,
    I can't see anything obviously wrong with the SQL so I have a quick question - how were the dates entered? Is it possible that there is a time element in there that is not being displayed? That might account for the lack of records being returned.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with SQL? (A2k (9.0.4402) SR-1)

    Hi Rory

    I'm running the SQL from a query, date field displays as 11/08/01 (I see no time), query is based on table with date field defined as m/d/yyyy 99/99/0000;0;_

    Any other aresa to look at?

    John

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Problem with SQL? (A2k (9.0.4402) SR-1)

    Hi John,
    If you format the field in the query as Long Time does it display 00:00:00?
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with SQL? (A2k (9.0.4402) SR-1)

    Hi Rory

    WOW!

    Another Rookie mistake, I'm updating Record Create and Update fields with Now()

    1. What syntax in a update query can I use to remove the time and start using Date()?

    2. How could I adjust the SQL to handle the date and time and continue using Now()?

    John

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Problem with SQL? (A2k (9.0.4402) SR-1)

    Hi John,
    Been there done that! <img src=/S/grin.gif border=0 alt=grin width=15 height=15> You're not alone....
    You can update the dtmDateCreate field to Format([dtmDateCreate], "dd/mm/yyyy") which should get rid of the time portion. (You can change the date format according to your needs.)
    You could also adjust your query to use something like:
    CreateDate: Format([dtmDateCreate],"dd/mm/yyyy")
    and set your date criteria for this field.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Star Lounger
    Join Date
    Nov 2001
    Location
    Illinois, USA
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with SQL? (A2k (9.0.4402) SR-1)

    Have you tried changing AND to OR?

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Problem with SQL? (A2k (9.0.4402) SR-1)

    Hi,
    That would return all records since every date is either <= or >= any given date.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Star Lounger
    Join Date
    Nov 2001
    Location
    Illinois, USA
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem with SQL? (A2k (9.0.4402) SR-1)

    Oops, sorry. Thanks, Rory.

Posting Permissions

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