Error message 2

[Ielamrani]

Can anyone tell me why I get the following message:

Data type mismatch in cretiria expression.

The query open for about 10 seconds and then I get the above message. I can't copy the query or export it.


Here is the code behind it:

SELECT DISTINCT Mid([Street],1+InStrRev([Street],' ')) AS FalseStreet, *
FROM dbo_PROV_ADDINFO_HCVW
WHERE (((Mid([Street],1+InStrRev([Street],' '))) In (SELECT Abbrev FROM AddrAbbrev)));

 

[RoyVidar]

First guess - there are occurences of Null in the Street field - see if the following alteration of the expression (both places) work.

[tt]iif(Isnull([street]);"";Mid([Street],1+InStrRev([Street],' ')))[/tt]

Roy-Vidar

 

[Ielamrani]

RoyVidar, you did it. Thanks soooo much.

I made a small change: I replaced ;""; with ,"", Right before the Mid
It did not work with the semicolons but worked with the colon..