Hi

Can someone please help,I am having trouble creating a query.

I had a problem with two tables, one of them is linked from another database and only contains the first half of our unique number (before the full stop)

The other table contains a unique number in the format 06PG32.150203

With help, the following Expressions were used in a query in the table containing our unique number "Study_Number"

Expr1: InStr(1,[Variables]![Study_Number],".",0)
Expr2: Left([Variables]![Study_Number],[Expr1]-1)

Expr2 now shows only the first half of the Study_Number, but when I use it to try to make a join it keeps coming back with the data type mismatch message. I've gone through all the data and am convinced that it has something to do with the Expr2 field.

Does anyone have any suggestions how to fix this.

Thanks

I think INSTR() of mysql takes only 2 arguments...

INSTR(str,substr)
    Returns the position of the first occurrence of substring substr in string str.
example:-

SELECT INSTR('foobarbar', 'bar'); --will return
-> 4

check your expr1. I am sure about 'where clause' using 'instr function', they worked properly (for me).

Regards
Milind

mmmmm im confused. This is the SQL area, one would think you are refering to an SQL function, are you?. I am not aware of an SQL inStr function, however:

InStr is a VBScript function (yes it does take 4 argument however two of them are optional:

InStr([start, ]string1, string2[, compare])

Additionaly, I believe Left is a function in both languages:

LEFT (character_expression , integer_expression)
Returns the left part of a character string with the specified number of characters.

BTW if you want the SQL equivalent of inStr you may want to use:

SUBSTRING:
Returns part of a character, binary, text, or image expression.

Syntax
SUBSTRING ( expression , start , length )




Wind is your friend
Matt

thanks for all your suggestions,

Finally managed to get it to work, the problem was the data in the StudyNumber field (some didnt include a full stop and some were blank). Not much fun wading through thousands of records, but at least it's working now :)