Data type mismatch in criteria expression

PeterLz · Jul 26th 2018

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.

**KjBox** · Jul 26th 2018

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

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

to

Code

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

Limpa1987 · May 19th 2019

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]

Share