Data Type Mismatch With InStr (1 Viewer)

[Matty]

Hi,

I'm having some problems with a simple query and it's driving me nuts. The invoice numbers in our system have hyphens in them, and I'm trying to find invoice numbers whose part before the hyphen matches a number I enter. Here's my SQL:

SELECT dbo_NIHB_ClaimLog.InvoiceNum, Left([InvoiceNum],InStr([InvoiceNum],"-")-1) AS InvoiceNumLeft
FROM dbo_NIHB_ClaimLog
WHERE (((dbo_NIHB_ClaimLog.InvoiceNum) Is Not Null) AND ((Left([InvoiceNum],InStr([InvoiceNum],"-")-1))=23316));

I'm getting a "Data Type Mismatch In Criteria Expression" error, and I can't figure out why. I've wrapped a Clng() around the Left() function, but that doesn't help either.

Am I missing something here?

 

[FoFa]

Why not just inclose your 23316 in quotes then?

 

[Matty]

I've tried that, but it doesn't work either. I've tried all the combinations I can think of with and without the Clng(), including putting the criteria in quotes.

If I put a 5 instead of the InStr() function, it seems to work. But since the numbers before the hyphen can vary, I can't hard-code a number into there.

 

[mresann]

I've replicated your db and pasted your SQL with absolutely no adjustment. It works perfectly. (In the table field "InvoiceNum", which is a Text field, I put in four records: 23316-3342, 3399-5599880, 2-449855, and 4449550-333). With the WHERE clause, I got the return ("23316"), and when I removed it, I got a return of all four invoice numbers from the left side of the hyphens.

Have you considered creating a new table and testing it on that one? Just put one text field in it and test it like you tested the main table. If this works, there MAY be a corruption issue with your main table.

 

[Matty]

HA! I got it to work, thanks to mresann's suggestion. I use linked SQL Server tables, so to safely mess around with the data in this certain table, I imported it instead of linking it. I re-linked the table and now it works.

I'm just going to have to see what got messed up in the table when I imported it...

Thanks for the help!

 

[jbrumbau]

I found this out the hard way, apparently InStr doesn't work in any SQL statements. You can only use "Like" or "Not Like", and only directly from a field, not a derivation of the field such as UCase$([FieldName]).

Another option, if you absolutely must get a character position, is to reroute something to VBA code in your SQL statement. Such as:
CharPOS(Nz([FieldName],""),"-")
Then declare CharPOS as a function with two string arguments in VBA.