Problem with Switch function

  • Thread starter Thread starter mo
  • Start date Start date
M

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 "
 
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.
 
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
 
Back
Top