1

i get Error 3464 Data type mismatch in criteria expression but i no able left it what is the problem???

Dim strsql As String
strsql = "select * from Shift where DFail Between " & txtVal1 & " and " & txtVal2
Me.RecordSource = strsql
braX
  • 11,506
  • 5
  • 20
  • 33
Hadi
  • 39
  • 1
  • 6
  • What datatype is `DFail` ? – Tim Williams May 12 '20 at 03:36
  • it is of short text – Hadi May 12 '20 at 03:40
  • 1
    if `DFail` is not a numeric field, it's not going to work. String fields cannot be `Between` values. That's a numeric thing. – braX May 12 '20 at 03:58
  • oh yes so i want search between two string what is solution? – Hadi May 12 '20 at 04:06
  • can the string be converted to numeric values? otherwise, it makes no sense. – braX May 12 '20 at 04:07
  • is not other way to search on string rang? – Hadi May 12 '20 at 04:12
  • not with `Between` - if it's a single character, and you want to convert it to its ASCII value first so that it's numeric... you could try that... but you didnt even show us what your variables are, nor the values you have in the `DFail` field, so all anyone can do is guess without more information. – braX May 12 '20 at 04:13
  • ok i wil convert to number type. thanks – Hadi May 12 '20 at 04:36
  • https://stackoverflow.com/questions/26080187/sql-string-comparison-greater-than-and-less-than-operators – Tim Williams May 12 '20 at 04:37
  • BETWEEN AND can be used with strings such as: `WHERE fieldname BETWEEN "A" and "C"`. Your SQL is missing apostrophe delimiters. However, results can be not what you expect. Edit question to show sample data and desired output. – June7 May 12 '20 at 05:34

1 Answers1

1

You miss the single-quotes:

strsql = "select * from Shift where DFail Between '" & txtVal1 & "' and '" & txtVal2 & "'"

Have in mind though, that text is sorted from left to right, like: 1, 12, 2, 20.

Gustav
  • 53,498
  • 7
  • 29
  • 55