Problem with Switch function

[mo]

The following SQL is give me a 'Missing operator' error. I know the problem
is with the Switch function but I'm not sure why.

The Switch function works if I have only one parameter:

strSQL = strSQL & "Switch([hospcode]='G','Royal Galmorgan') "
strSQL = strSQL & "FROM tbl_registration0003 "

but will not work for more than one parameter as part of the function.

Any help much appreciated.

SQL statement:

strSQL = "SELECT surname, forename, sampbarcode, snameChld, fnameChld,
ChlDoB, ChlSex, TrialGrpBak, NegPosVal, TrialGroup, "
strSQL = strSQL & "DateDiff('yyyy', [ChlDob], Now())+
Int(Format(Now(),'mmdd') < Format([ChlDob],'mmdd')) AS ChlAge, "
strSQL = strSQL & "IIf(IsNull([Chlsex]),'Not
Entered',IIf([ChlSex]=1,'Male','Female')) AS Sex, "
strSQL = strSQL & "Switch([hospcode]='G','Royal Galmorgan',
[hospcode]='L,'Llandough',[hospcode]='N','Neville Hall', "
strSQL = strSQL & "[hospcode]='P','Princess of Wales',[hospcode]='R','Royal
Gwent', "
strSQL = strSQL & "[hospcode]='S','Singleton',[hospcode]='U','UHW') AS Hosp
"
strSQL = strSQL & "FROM tbl_registration0003 "
strSQL = strSQL & "WHERE TrialGrpBak = 0 "
strSQL = strSQL & "AND NegPosVal = 1 "

 

[Van T. Dinh]

You left out a single-quote after the 1st letter L in:

[hospcode]='L,'Llandough'

OTOH, it is probably more efficient to use a "look-up" Table and incorporate
it into your Query using an Inner Join rather than the Switch function.

 

[mo]

Doh!!! Thanks for that. Couldn't see the wood for the trees as they say.

Thanks also for the suggestion about the INNER JOIN.

Mo