Results 1 to 3 of 3
  1. #1
    hazeleyre23 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    34

    Data type mismatch in criteria expression

    Hi,

    I am recieving the following error message when I try to do a BETWEEN statement in VBA Access.



    'Data type mismatch in criteria expression'

    My statement is :

    Code:
           LStartDate = Me.txtStartDate.Value
           LEndDate = Me.txtEndDate.Value
    
    LSQL = SELECT RDate, Site WHERE RDate BETWEEN '" & LStartDate & "' AND '" & LEndDate & "'
    RDate is a datetime format in SQL and LStartDate and LEndDate are textbox's in access with Shortdate format.

    thanks

  2. #2
    CJ_London is online now VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you are mixing datetypes and text types hence the error message. You are also missing double quotation marks

    to use text created dates to compare with a datetype you use #

    try

    LSQL = "SELECT RDate, Site WHERE RDate BETWEEN #" & LStartDate & "# AND #" & LEndDate & "#"

    note also that dates need to be formatted US style - mm/dd/yyyy when using this style (as opposed to '1st Jan 2016')

    so you may need your code to be

    LSQL = "SELECT RDate, Site WHERE RDate BETWEEN #" & format(LStartDate,"mm/dd/yyyy") & "# AND #" & format(LEndDate,"mm/dd/yyyy") & "#"

    finally, you don't need the LStart/end variables unless required for something else and you don't need to use the me. or .value since these are the default, so your code could be

    LSQL = "SELECT RDate, Site WHERE RDate BETWEEN #" & format(txtStartDate,"mm/dd/yyyy") & "# AND #" & format(txtEndDate,"mm/dd/yyyy") & "#"

  3. #3
    hazeleyre23 is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    34
    Thank you so much! This worked perfectly.

    thank you again

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

Similar Threads

  1. Data type mismatch in criteria expression.
    By pipoconanan45 in forum Reports
    Replies: 1
    Last Post: 09-09-2015, 06:26 AM
  2. Data Type Mismatch in criteria expression
    By gaker10 in forum Queries
    Replies: 3
    Last Post: 03-05-2015, 01:54 PM
  3. Data type mismatch in criteria expression
    By khughes46 in forum Queries
    Replies: 12
    Last Post: 05-01-2014, 07:34 AM
  4. Data Type Mismatch in Criteria Expression
    By dehdahdoh in forum Queries
    Replies: 13
    Last Post: 05-01-2013, 11:41 AM
  5. data type mismatch in criteria expression
    By broecher in forum Access
    Replies: 4
    Last Post: 09-10-2010, 08:29 AM

Tags for this Thread

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