Results 1 to 7 of 7
  1. #1
    Dynamo418 is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Oct 2013
    Posts
    3

    Can't get 'Between' to work for dates!

    Hi all,

    I am trying to do something I thought was simple: only include table items between dates for one of my fields (group by date).

    The dates are in this format (example from table):

    2005-08-24 22:11:00.000000

    I used this SQL line to try and do this:



    HAVING (((TABLE.A)=1) AND ((TABLE.B)=2) AND ((TABLE.C) Between #1/1/2013 12:0:0# And #5/1/2013 12:0:0#))

    However it did not work: Table A and B groups worked fine, but I still get dates from 2005!

    Any ideas?

  2. #2
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Try
    Code:
    Between #1/1/2013 12:00:00# And #5/1/2013 12:00:00#))

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,642
    Also confirm that the data type of the field is Date/Time rather than text.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Yes, my code does assume that .C is actually stored as a date.

    If not, then use something like
    Code:
    DateValue(TABLE.C) Between #1/1/2013 12:00:00# And #5/1/2013 12:00:00#

  5. #5
    Dynamo418 is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Oct 2013
    Posts
    3
    Ah I determined that Table.C is stored as text and not date/time. Unfortunately this is locked and I cannot modify. I tried to use

    DateValue(TABLE.C) Between #1/1/2013 12:00:00# And #5/1/2013 12:00:00#

    but I got the error message: Date type mismatch in criteria expression.Is there another format I can use to create a between statement? It doesn't need to be date format, it just needs to work like a date format if that makes sense!

  6. #6
    Dynamo418 is offline Novice
    Windows 7 64bit Access 2003
    Join Date
    Oct 2013
    Posts
    3
    Figured it out to use (TABLE.C) Between '1/1/2013 12:00:00' And '5/1/2013 12:00:00' seems to work!

  7. #7
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    Yes, but that's doing a text comparison. Try between '1/1/2012' AND '5/1/2013' and you'll see what's wrong.

    Try
    Code:
    DateValue(TABLE.C) Between #1/1/2013# And #5/1/2013#

Please reply to this thread with any new information or opinions.

Similar Threads

  1. List Dates between Start and Finish Dates
    By Buddus in forum Queries
    Replies: 1
    Last Post: 07-26-2013, 01:58 PM
  2. Query input dates for range of dates in two fields
    By kagoodwin13 in forum Queries
    Replies: 3
    Last Post: 02-13-2012, 04:25 PM
  3. Replies: 10
    Last Post: 11-16-2011, 10:58 AM
  4. Mixed dates - show only complete dates?
    By weeblesue in forum Queries
    Replies: 3
    Last Post: 10-27-2010, 02:15 PM
  5. Replies: 2
    Last Post: 04-23-2010, 01:31 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums