Results 1 to 4 of 4
  1. #1
    ukg1kea is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2022
    Posts
    1

    ODBC Date Range

    I would like to automate the running of several queries based on a date range from either a form or a table.
    I have linked tables and when running the queries using Between #20/07/2022# And #27/07/2022# they work without any issues

    Initially I was using just a form and someone suggested running a Make Table Query to ensure that the data type was a date rather than a string - field detail below
    fromdate: CDate([Forms]![Frm_GetDate]![StartDate])
    todate: CDate([Forms]![Frm_GetDate]![EndDate])

    But when I try to run queries pulling the date range from my table I'm getting the error Data Type Mismatch in criteria expression

    Between (SELECT [tblDateRange].fromdate FROM [tblDaterange]) And (SELECT [tblDateRange].[todate] FROM [tblDateRange])

    I've also tried the expressions below

    Between [tblDateRange]![fromdate] And [tblDateRange]![todate] – asks me to enter parameters
    Between " [tblDateRange]![fromdate]" And "[tblDateRange]![todate]" - Data type mismatch in criteria expression
    "Between [tblDateRange]![fromdate]" And "[tblDateRange]![todate]" - Data type mismatch in criteria expression



    Is what I'm trying to do even possible or is it a case of having to manually enter the date range in each query before I run them?

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    It doesn't work like that I'm afraid, you cannot reference the table!field; you need to use dLookup:

    Between dLookup("fromdate","tblDateRange") And dLookup("todate","tblDateRange")

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Also you dates happen to work because they are unambiguous. The format sql uses is the US format of mm/did/yyyy or the standard of yyyy-mm-dd so if you had a date of 11/7/2022 that will be interpreted as 7th Nov not 11th July (because sql knows there are only 12 months in the year

  4. #4
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    If you drag you report dates table into the query designer without a join you can refer to the fields in the query criteria directly.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

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

Similar Threads

  1. Replies: 4
    Last Post: 11-20-2018, 11:57 PM
  2. Replies: 17
    Last Post: 07-31-2018, 01:23 PM
  3. Replies: 3
    Last Post: 01-26-2016, 01:56 PM
  4. Replies: 4
    Last Post: 04-25-2015, 04:17 PM
  5. Replies: 3
    Last Post: 09-11-2013, 09:49 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