How do I do this in SQL

[Guest]

I have a large table of data of about a dozen columns and 1.5 million rows.

I need to concatenate data from two of these columns only if the data meet
certain conditions. Given the number of rows Access seems more appropriate
than Excel. I'm primarily an Excel person, and this is the formula I would
use in Excel:

=IF(LEFT(A1,2)="JS",CONCATENATE(LEFT(A1,2),D1),"")

I'm not conversant enough in SQL to translate that to an Access query. So
let me explain what it does in plain English:
1) IF the left two characters in the first column are "JS",
2) THEN concatenate the left two characters in the first column with the
contents in the fourth column
3) ELSE return an empty string.

Thanks,

Dave

 

[Allen Browne]

Try:
=IIf([A1] Like "JS*", "JS" & [D1], "")

IIf() is the Immediate If.
Ampersand is the concatenation operator.
Using Like instead of Left() avoids calls to VBA.

 

[Guest]

Wow you make it seem so easy, thanks.
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.

Try:
=IIf([A1] Like "JS*", "JS" & [D1], "")

IIf() is the Immediate If.
Ampersand is the concatenation operator.
Using Like instead of Left() avoids calls to VBA.