Results 1 to 12 of 12
  1. #1
    Larryg is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2015
    Posts
    50

    VBA SQL Error 13 Type Mismatch

    Hello all;



    I have tried every combination I could think of, but I'm still stuck. Can anyone see any issues with the following code? I also tried placing # in several different places as tests, but still the same error.

    strSQL2 = "SELECT * " & _
    "FROM tbl_WorkOrderTickets_Converted " & _
    "WHERE (((tbl_WorkOrderTickets_Converted.dDateTicketAssig ned) = (" & [Forms]![frm_AM_LoadOtherTickets].[tb_dDate] & "" * "" & ")" & _
    "AND IsNull([dDateTicketCompleted]);"

    dDateTicketAssigned is formatted as General date
    tb_dDate is formatted as short date on form
    dDateTicketCompleted is formatted as General date

    In short, the intent is to return a dataset based on date, from the users input on the form as the variable.

    Thanks...

  2. #2
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    How about changing the first line of the WHERE clause to this:
    Code:
    WHERE (tbl_WorkOrderTickets_Converted.[dDateTicketAssig ned] = #" & [Forms]![frm_AM_LoadOtherTickets].[tb_dDate] & "# " & _
    Access usually uses # to surround the dates.
    And, if you have any spaces in your field names (like "dDateTicketAssig ned"), you need to enclose them in square brackets.

  3. #3
    Larryg is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2015
    Posts
    50
    Quote Originally Posted by JoeM View Post
    How about changing the first line of the WHERE clause to this:
    Code:
    WHERE (tbl_WorkOrderTickets_Converted.[dDateTicketAssig ned] = #" & [Forms]![frm_AM_LoadOtherTickets].[tb_dDate] & "# " & _
    Access usually uses # to surround the dates.
    And, if you have any spaces in your field names (like "dDateTicketAssig ned"), you need to enclose them in square brackets.
    Thank JoeD for the prompt response.

    The code you supplied provided no error this time, however it returned no records. Its been my experience that when a short date is entered as criteria, against a General Date format, I need to add the & "" * "" in order to return all the dates regardless of the times involved as are stored in the General Date format. When I add in the "" * "" i.e."= #" & [Forms]![frm_AM_LoadOtherTickets].[tb_dDate] & "" * """# " & _" (no beginning and ending "), I get the error again.

    Any thoughts"

  4. #4
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    The way that Access stores dates and times is that dates are a whole number, specifically the number of days since 1/1/1900, and time is just the fractional component of one day.
    So, we can ignore the time component of any date using the INT function to drop the fraction, i.e.
    Code:
    WHERE (INT(tbl_WorkOrderTickets_Converted.[dDateTicketAssig ned]) = #" & [Forms]![frm_AM_LoadOtherTickets].[tb_dDate] & "# " & _

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    the text view of your date from your form needs to be in US format of mm/dd/yyyy. The # characters tells SQL that the value between the two # characters should be treated as a date. If that date is say 15/12/2016, then SQL know that this is the format dd/mm/yyyy because there are not 15 months in the year. But if the date is 10/12/2016, it will be interpreted as 12th October 2016.

    so use the format function to set the date in the right format.

    WHERE (tbl_WorkOrderTickets_Converted.[dDateTicketAssig ned] = #" & format([Forms]![frm_AM_LoadOtherTickets].[tb_dDate],"mm/dd/yyyy") & "# " & _

    The * will not work with dates because dates are stored as a special form of decimal number, not text - they are just viewed in a format you are familiar with.

    short dates/general dates are just views of the same data

    if your dDateTicketAssigned has a time element included (e.g. populated with the now() function rather than the date() function) then try

    WHERE (datevalue(tbl_WorkOrderTickets_Converted.[dDateTicketAssigned]) = #" & format([Forms]![frm_AM_LoadOtherTickets].[tb_dDate],"mm/dd/yyyy") & "# " & _

  6. #6
    Larryg is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2015
    Posts
    50
    Quote Originally Posted by Ajax View Post
    the text view of your date from your form needs to be in US format of mm/dd/yyyy. The # characters tells SQL that the value between the two # characters should be treated as a date. If that date is say 15/12/2016, then SQL know that this is the format dd/mm/yyyy because there are not 15 months in the year. But if the date is 10/12/2016, it will be interpreted as 12th October 2016.

    so use the format function to set the date in the right format.

    WHERE (tbl_WorkOrderTickets_Converted.[dDateTicketAssig ned] = #" & format([Forms]![frm_AM_LoadOtherTickets].[tb_dDate],"mm/dd/yyyy") & "# " & _

    The * will not work with dates because dates are stored as a special form of decimal number, not text - they are just viewed in a format you are familiar with.

    short dates/general dates are just views of the same data

    if your dDateTicketAssigned has a time element included (e.g. populated with the now() function rather than the date() function) then try

    WHERE (datevalue(tbl_WorkOrderTickets_Converted.[dDateTicketAssigned]) = #" & format([Forms]![frm_AM_LoadOtherTickets].[tb_dDate],"mm/dd/yyyy") & "# " & _


    Thanks Ajax...

    Your suggestions produced the following errors:

    Run-time error 3075 - Syntax error in query expression.

    This QBE SQL works as intended:

    SELECT tbl_WorkorderTickets_Converted.*, tbl_WorkorderTickets_Converted.dDateTicketAssigned , tbl_WorkorderTickets_Converted.dDateTicketComplete d
    FROM tbl_WorkorderTickets_Converted
    WHERE (((tbl_WorkorderTickets_Converted.dDateTicketAssig ned) Like "*" & [forms]![frm_AM_LoadOtherTickets]![tb_dDate] & "*") AND ((tbl_WorkorderTickets_Converted.dDateTicketComple ted) Is Null));

    When I add "Like" to your suggestions, that returns the same 3075 error, but says "missing operator".

  7. #7
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Your suggestions produced the following errors:

    Run-time error 3075 - Syntax error in query expression.
    it is possibly because I only quoted part of the code copied from one of the other posts, so without seeing what you actually used in your full query, I'm guessing it is to do with brackets

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    the only reason your *...* works is purely luck at the moment

    today is represented by the number 42702 - tomorrow will be 42703 and yesterday was 42701

    time is represented as number of seconds so far today as a fraction of 86400 (the number of seconds in a day)

    for demonstration purposes 12 noon will be 0.5, 1:33pm will be 0.564 - and 10:14:55 am will be 0.42702

    If the time now is 10:14:55 am today, the value stored will be 42702.42702, tomorrow for the same time will be 42703.42702, etc

    So if you are searching for something with todays date using *...*, and you have a record which has a time of 10:14:55 that will also be returned regardless of what the date actually is.

    In actual fact the fraction is stored to 10 decimal places, so you will also get records back if the record has a value of

    42703.0042702345
    42703.0427023453
    42703.1242702345
    42703.5934542702
    etc

  9. #9
    Larryg is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2015
    Posts
    50
    Quote Originally Posted by Ajax View Post
    the only reason your *...* works is purely luck at the moment

    today is represented by the number 42702 - tomorrow will be 42703 and yesterday was 42701

    time is represented as number of seconds so far today as a fraction of 86400 (the number of seconds in a day)

    for demonstration purposes 12 noon will be 0.5, 1:33pm will be 0.564 - and 10:14:55 am will be 0.42702

    If the time now is 10:14:55 am today, the value stored will be 42702.42702, tomorrow for the same time will be 42703.42702, etc

    So if you are searching for something with todays date using *...*, and you have a record which has a time of 10:14:55 that will also be returned regardless of what the date actually is.

    In actual fact the fraction is stored to 10 decimal places, so you will also get records back if the record has a value of

    42703.0042702345
    42703.0427023453
    42703.1242702345
    42703.5934542702
    etc
    Ajax... actually any day I enter into the input form returns the correct data (using the QBE SQL code). I have tried several different days, and the query returns the correct data each time. But when I turn the QBE SQL into VBA SQL, it fails.

  10. #10
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    I don't think I have anything I can add. If by QBE you mean the Access query builder then use debug.print strSQL2 and compare the output it generates in the immediate window with the sql generated by the the query builder. As I said before, looks like you are missing a couple of brackets.

  11. #11
    Larryg is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2015
    Posts
    50
    Quote Originally Posted by Ajax View Post
    I don't think I have anything I can add. If by QBE you mean the Access query builder then use debug.print strSQL2 and compare the output it generates in the immediate window with the sql generated by the the query builder. As I said before, looks like you are missing a couple of brackets.
    Ajax, thanks. Can you help me understand how if the (VBA) strSQL2 won't run from behind a button, how could it run from the intermediate window? When I typed in debug.Print strSQL2 in the intermediate window, nothing happens. In fact when I type in debug.Print strSQL1, a known working VBA query, nothing happens there either. So, there is really noting to compare if I can't get it to run. I know the QBE (yes, Access query builder) query works, so that tells me the VBA one should also.

    Regarding the perhaps missing brackets... I have tried so many combinations that I'm not sure there are any combinations left to try. I suspect your right, its either bracketing, or parens, or quotes, etc... but I think I need another set of eyes, because mine are just not seeing it. Hopefully someone will jump in here. Thanks for your replies, I appreciate it.

  12. #12
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    how could it run from the intermediate window?
    it doesn't - see this link

    http://stackoverflow.com/questions/2...g-print-log-to

    having printed to the immediate window, you compare it with the sql code you have generated in the query grid. e.g.

    in the immediate window you might have something like

    select * from mytable where ((a)=b

    and in the sql view of the query grid you might have

    select * from mytable where ((a)=b)

    you can see where the missing bracket is

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

Similar Threads

  1. Type 13 (Type Mismatch) error
    By sdel_nevo in forum Programming
    Replies: 5
    Last Post: 01-22-2016, 10:01 AM
  2. Replies: 7
    Last Post: 07-24-2013, 02:01 PM
  3. type mismatch error 13
    By Compufreak in forum Access
    Replies: 3
    Last Post: 08-10-2012, 03:48 AM
  4. Replies: 1
    Last Post: 05-11-2012, 10:59 AM
  5. type mismatch error?
    By hlcarlin in forum Programming
    Replies: 1
    Last Post: 07-07-2011, 08:30 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