Results 1 to 6 of 6
  1. #1
    NanciW is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2018
    Posts
    4

    Date Query Using Like "*"

    Hi all, its been a while since I've used Access and can't remember how to write a query to pull up dates that allows user input for the date. I tried Like "*"&[Enter date]&"*" but it doesn't return records even though I know there are dates in this particular field. Any help would be appreciated.

  2. #2
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    Not sure why you're wrapping it in wild card characters. If you just want the users to enter one date value use [Enter Date] as your criteria. If you want to allow a range, use Between [Enter Start Date] And [Enter End Date], or something similar.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    BETWEEN [Beginning Date] AND [End Date] if 2 dates, [Enter Date] if only 1.
    not Like
    The main problem with this approach is that the prompts will accept anything (such as "dog"). If you're OK with that, or are using code to validate, then np.
    But BETWEEN isn't usually suitable for dates that contain time.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    NanciW,

    Here is a link to info on Dates with Access by Philipp Stiefel.

    The LIKE operator you tried is used with strings/text
    The delimiter for Date/Time literals in Access/Jet-SQL is the hash sign (#).
    The BETWEEN StartDate AND EndDate is the common way to find dates in a range, as Beetle and Micron have advised.

    Also, Between includes the end points.

    Good luck with your project.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    [QUOTE=orange;394553]
    Also, Between includes the end points.
    but if BETWEEN 04/01/2018 AND 04/07/2018 it doesn't include 04/07/2018 01:30:30 AM for example, right?

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    To add to the comments, date and time are stored as decimal numbers. What you see as a date (e.g. 12/4/2018) is just the way it is formatted. The date is before the decimal point and the time after the decimal point and expressed as the time in seconds divided by 86400, the number of seconds in a day - for example 12 april 2018 23:29 is 43202.985462963, yesterday was 43201 and tomorrow 43203. And don't forget, just because you cannot see the time part, doesn't mean it doesn't exist. Many newbies will use the now() function to populate a field which is formatted as short date when they should be using the date function which does not use the time element.

    so if you use between or >= ...<=, and your dates are stored with a time element and you want to return records between 11th April 2018 and 12th April 2018, the # characters tell sql to change whatever the string is between them into a number e.g.

    between 43201 and 43202

    Note this will not capture a record with a date time of 12 april 2018 23:29 because its time is 43202.985462963 which is outside the range selected.

    Similarly it will not be returned if looking for records with a date of 12 april 2018

    Ironically, using Like [Enter Date] & "*" (i.e. used as a query parameter in this way the value is returned as a date datatype) will return the record because 43202.985462963 is like 43202* (Like is a text comparator, but will also work with numbers). However if the parameter is obtained from a form, that will be text and you'll need to use Like "#" & Forms!myform!datefield & "#*". So it may be the next paragraph which provides a solution to your problem

    Finally, dates in sql need to be expressed unambiguously e.g. 11th April 2018, 11 Apr 2018, etc. or in the US shortdate format of mm/dd/yyyy. The problem is that a date of say 31/12/2018 is unambiguous to sql because there are not 31 months in the year and it is clever enough to swap the month and day values round. However a date of 11/4/2018 is ambiguous, in the UK it is 11th April, in the US it is the 4th November - so SQL will assume 4th November.

    To get round this, if you do not naturally use the US format in your system settings you need to use the format function to convert your date to the US format e.g.

    "#" & format([Enter Date],"mm/dd/yyyy") & "#"

    I agree with the other comments, don't use Like for dates or numbers - just because it works does not make it a good idea.

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

Similar Threads

  1. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  2. Replies: 3
    Last Post: 02-16-2015, 01:04 PM
  3. Replies: 3
    Last Post: 12-06-2014, 03:59 AM
  4. Replies: 2
    Last Post: 11-26-2012, 11:12 PM
  5. Replies: 2
    Last Post: 09-29-2012, 11:22 PM

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