Data type mismatch in criteria expression

  • Hi guys,


    I was hoping that you can help me out a bit with an issue I keep having with Excel VBA/Access database.
    I am trying to fetch a certain data with the following statement:


    sQRY = ("SELECT DBobs.[dbObserv] FROM DBobs WHERE DBobs.[dbLine]='" & UFmain.ComboBox2.Value & "'" & " AND DBobs.[dbFAM]= '" & UFmain.ComboBox1.Value & "'" & " AND DBobs.[dbDate]='" & rng50.Value & "'")


    But whenever I run the macro, I keep getting the data type mismatch error.
    The dbObserv is set as date/time, and the rest are short texts. The problem is with my range value (rng50.value). The cell contains the date and time in this format: 25.07.2018 13:54:12 just like in the Access database. I want to compare the 3 datas to retrieve dbObserv's value but I'm missing something with the rng50 value, since the code works if I take that out. Im guessing the format or something is missing.


    Hope it all makes sense. I just started working with Access database through Excel and I'm still learning.


    Any advice would be helpful.

  • Quote

    The dbObserv is set as date/time, and the rest are short texts


    So Bobs.[dbDate] is a column of dates formatted as text?


    If so, then you code is trying to match a date formatted as Date (rng50.Value) with a date formatted as text.


    Try changing

    Code
    1. DBobs.[dbDate]='" & rng50.Value & "'")


    to

    Code
    1. DBobs.[dbDate]='" & CStr(rng50.Value) & "'")

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • I have a similar question
    [ATTACH=JSON]{"data-align":"none","data-linktype":"1","data-linkurl":"http:\/\/yarshopcolor.ru\/","data-size":"full","title":"\u0441\u043a\u0430\u0447\u0430\u0442\u044c \u0434\u0440\u0430\u0439\u0432\u0435\u0440\u0430","data-attachmentid":1218592}[/ATTACH]
    [ATTACH=JSON]{"data-align":"none","data-linktype":"1","data-linkurl":"https:\/\/yandex.ru\/maps\/org\/chestny_kolodets\/98922568988\/?ll=39.874536%2C57.632476&source=wizbiz_new_text_single&z=18","data-size":"full","title":"\u043a\u043e\u043f\u043a\u0430 \u043a\u043e\u043b\u043e\u0434\u0446\u0435\u0432 \u042f\u0440\u043e\u0441\u043b\u0430\u0432\u043b\u044c","data-attachmentid":1218593}[/ATTACH]






    [ATTACH=JSON]{"alt":"Click image for larger version Name:\t!_TOP_5.png Views:\t5 Size:\t80 Bytes ID:\t1218589","data-align":"none","data-attachmentid":"1218589","data-linktype":"1","data-linkurl":"https:\/\/xn----htbbcrhhmmb0am5db8d.xn--p1ai\/","data-size":"full","title":"!_TOP_5.png"}[/ATTACH]
    [ATTACH=JSON]{"alt":"Click image for larger version Name:\t!_TOP_5.png Views:\t5 Size:\t80 Bytes ID:\t1218590","data-align":"none","data-attachmentid":"1218590","data-linktype":"1","data-linkurl":"https:\/\/vk.com\/kopka_kolodecev","data-size":"full","title":"!_TOP_5.png"}[/ATTACH]


    [ATTACH=JSON]{"alt":"Click image for larger version Name:\t!_TOP_5.png Views:\t5 Size:\t80 Bytes ID:\t1218591","data-align":"none","data-attachmentid":"1218591","data-linktype":"1","data-linkurl":"https:\/\/vk.com\/izodru","data-size":"full","title":"seo"}[/ATTACH]

    Images

    • !_TOP_5.png
    • !_TOP_5.png
    • !_TOP_5.png
    • !_TOP_5.png
    • !_TOP_5.png