SQL Query Syntax

[georgesOne]

Hi All,

I am fighting with a query...
A part of it goes like

sSQL = sSQL & "IIf(" & rst.Fields(k).Name & " <> " & rst.Fields(k - 1).Name & ", " & rst.Fields(k).Name & ", '') AS MMove, "

This works and means: if the value of a certain field(k) <> the value of another field(k-1) then show the value of field(k), otherwise empty string.
The values are "Ship" and "Car", so MMove here displays "Ship"

However I want to show the value of field(k) & " and " & the value of field(k-1), so MMove should display the value:
"Ship and Car".
I tried many things, but either I get the field names, or syntax errors. Any help for the correct SQL?

Thanks a lot, georgesOne

 

[georgesOne]

Sorry for bothering you...
As often: once I post I find the solution:

sSQL = sSQL & "IIf(" & rst.Fields(k).Name & " <> " & rst.Fields(k - 1).Name & ", " & rst.Fields(k-1).Name & " & ' and ' & " & rst.Fields(k).Name & ", '') AS MMove, "

Thanks anyway... always good to know that there are people who will help, georgesOne

 

[PHV]

Something like this ?

sSQL = sSQL & "IIf(" & rst.Fields(k).Name & " <> " & rst.Fields(k - 1).Name & ", " & rst.Fields(k).Name & " & ' and ' & " & rst.Fields(k - 1).Name & ", '') AS MMove, "

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[georgesOne]

Thanks, PHV...

As I said before: always good to know that there are competent people who will help!

Kind regards, georgesOne