Announcement

Collapse
No announcement yet.

Data type mismatch in criteria expression

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.

  • #2
    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) & "'")
    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.

    Comment


    • #3


      I have a similar question
      Click image for larger version

Name:	!_TOP_5.png
Views:	0
Size:	80 Bytes
ID:	1218592
      Click image for larger version

Name:	!_TOP_5.png
Views:	0
Size:	80 Bytes
ID:	1218593





      Click image for larger version  Name:	!_TOP_5.png Views:	5 Size:	80 Bytes ID:	1218589
      Click image for larger version  Name:	!_TOP_5.png Views:	5 Size:	80 Bytes ID:	1218590

      Click image for larger version  Name:	!_TOP_5.png Views:	5 Size:	80 Bytes ID:	1218591

      Comment

      Working...
      X