Run time error 3464 Type mismatch in criteria expression

[daisyMay1]

Hello, I am trying to fix a db that was written by someone else. I had to change a key field (GroupNo) in a table from Numeric to Text and am now coming up with data type mismatch. In this part of the code keyNow has been defined as Variant = 0. Error is thrown on the DLookup line.

Public Function doQueryDefSQL(ByRef fromWhere As String, Optional keyNow As Variant = 0) As Boolean
...
If fromWhere <> "Clicked" Then   'Opened
      If Nz(DCount("*", "Accounts", "Location='0'"), 0) = 2 Then
         keyNow = DLookup("GroupNo", "Accounts", "GroupNo<>0")
      Else
         keyNow = getDefault("GroupNo")
      End If
   End If

...


Much thanks!
Shar

[Stewart Ross]

If you have redefined GroupNo as a text field then the Where clause of the DLookup has to be modified slightly. Just as in a SQL Where clause, text values must be enclosed in single quotes:

keyNo = DLookup("GroupNo", "Accounts", "GroupNo<>'0'")

As you mention that your keyNo variable is defined as a variant type you may find that it is set as a text type when returned from the DLookup. This may not matter, as Access will convert types on the fly where needed, but you should at least be aware of the possibility, to avoid further type mismatches occurring in your code.

If you need keyNo to be a numeric value you could change its type to Long, unless the range of the GroupNo field values exceeds what a long integer can represent.

-Stewart

[daisyMay1]

Hi Stewart,
I had already tried that but still received an error. Yes I had found that because keyNow is a Variant that Access will convert based on the value. GroupNo will always be data type TEXT (String) so for instance, whether keyNow value is 0 (varientInteger ) or 2540 (variantDouble or variantLong) or K006 (variantString) or whatever, I think it is erring because it's not an exact data type match, thus the reason for the "mismatch" error. So I'm thinking should I always try to convert keyNow to a String and then it should always match and hopefully will not err? What do you think? If so, what would be the best way to do that?

Again much thanks! I'm in urgent need of some help today as this is holding up a major project.

[daisyMay1]

Oh and I also tried to change the ByRef for keyNow from variant to string but I must have done it wrong because I'm still getting errors.

[Stewart Ross]

To rule out problems with your definition of keyNo simply change its definition from variant to string.

You can check whether DLookup is working as expected using the Immediate window in the VBA editor to execute a print statement for the DLookup itself:

? DLookup("GroupN o", "Accounts", "GroupNo<>' 0'")

This should not give a type mismatch error. If it does, leave out the Where clause and see what happens:

? DLookup("GroupN o", "Accounts")

Without the Where clause this should return the GroupNo from the first row of the Accounts table.

A type mismatch normally arises when an argument of a specific type is not supplied in compatible form - e.g. the where clause structured for a numeric comparison when a text comparison is required, or a null value being supplied when a defined type such as a string or integer has been specified.

Difficult to know what else to advise until you can test the suggestions above.

-Stewart

[daisyMay1]

Okay great. I will try and let you know what happens. Thank you!

[daisyMay1]

Thanks Stewart. The single quotes fixed one issue but now errors are occurring in other parts of the code. :( I'll post new threads for those. Appreciate your help!