parse data and select unique records

[Jeannie]

I have successfully parsed a field in a query, but it produces duplicate
values in the resulting dynaset. When I set the properties of the query to
Unique Values=yes, I get "Invalid procedure or call" error. Here is my
parsed expression:
iif([alloy]<"1",[alloy],Left([alloy],Instr(1,[Alloy]," ")-1)

the <"1" has to be there instead of is null because of poor, mysterious
design by my predecessor!

Thanks

 

[KARL DEWEY]

Post the complete SQL statement.

 

[Marshall Barton]

I have successfully parsed a field in a query, but it produces duplicate
values in the resulting dynaset. When I set the properties of the query to
Unique Values=yes, I get "Invalid procedure or call" error. Here is my
parsed expression:
iif([alloy]<"1",[alloy],Left([alloy],Instr(1,[Alloy]," ")-1)

the <"1" has to be there instead of is null because of poor, mysterious
design by my predecessor!

Strange thing to be doing!?

You will get that error when Alloy does not contain a space.

 

[Lord Kelvan]

you will need another nested iif to handle the space problem

iif([alloy]<"1",[alloy],iif(Instr(1,[Alloy]," "),Left([alloy],Instr(1,
[Alloy]," ")-1),"do something else here")