Select Case question; drawing from multiple tables based on chkbox val 1

[Ovatvvon]

Hello all,

I'm not quite familiar with Select Case in SQL, but know it exists. I'm not sure, though, if it only exists for MS SQL Server alone, or if it can be used for MS Access as well. Does anyone know?

Also, I'm not positive on the format of it. Here are two that I've tried. The first:

SELECT
    Case
        When [tblClients].[clientUseGroupAddress]=1 Then [tblGroups].[groupAddr1] As listClientAddress
        Else [tblClients].[clientAddr1] As listClientAddress
    End
From tblGroups Right Join tblClients on [tblGroups].[groupPK]=[tblClients].[groupFK];

which generates the following error when I try to save it:
Syntax error (missing operator) in query 'Case When [tblClients].[clientUseGroupAddress]=1 Then [tblGroups].[groupAddr1]'.

And the next thing I tried was:

SELECT
    Case ([tblClients].[clientUseGroupAddress]=1) As useGroupAddress
        When useGroupAddress=1 Then ([tblGroups].[groupAddr1]) As listClientAddress
        Else [tblClients].[clientAddr1] As listClientAddress
    End Case
From tblGroups Right Join tblClients on [tblGroups].[groupPK]=[tblClients].[groupFK];

Which generates the following error:
The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect. (and then it highlights the word "When" when I hit the OK button.

Can anyone help?

-Ovatvvon :-Q

 

[PHV]

You may try this (MS-SQL)
SELECT
Case
When [tblClients].[clientUseGroupAddress]=1 Then [tblGroups].[groupAddr1]
Else [tblClients].[clientAddr1]
End As listClientAddress
From tblGroups Right Join tblClients on [tblGroups].[groupPK]=[tblClients].[groupFK];

And the JetSQL syntax:
SELECT
IIf([tblClients].[clientUseGroupAddress]=1,
[tblGroups].[groupAddr1], [tblClients].[clientAddr1]
) As listClientAddress
From tblGroups Right Join tblClients on [tblGroups].[groupPK]=[tblClients].[groupFK];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Ovatvvon]

What a bummer the syntax has to be different for MS SQL and Access.

Thank you ver much for your post PHV. At least now it doesn't generate an error!

One remaining problem, though, is that it only returns the client addresses, but in the case that the group address is to be used, it returns a Null. (I get five records returned, records three and five are null, the rest are filled with client addresses.) Can we rearrange the statement so it pulls the group addresses into those remaining field in the event they are supposed to be used?

-Ovatvvon :-Q

 

[PHV]

You have to check your data:
all tblClients records with clientUseGroupAddress=1 have a valid groupFK pointing to a tblGroups record containing a non null groupAddr1 ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Ovatvvon]

Yep, it all points accurately.

groupPK: groupAddr1

1: 123 Pullberry Lane
2: 125 S.E. Main Street
3: 7401 Metro Blvd.

clientPK:groupFK : useGroupAddress : clientAddr1

1:1 : No : 1347 Highway 55
2:1 : No : 44 Pentington Road
3:2 : Yes : Null
4:2 : No : 1512 125th Ave. NE
5:3 : Yes : Null

So right now, the JetSql pulls exactly what is in the client table, without referencing the "useGroupAddress" checkbox, and then pulling from the tblGroups or tblClients based on that.

I hope I was able to clarify this a little better. Thank you very much for all the help you're providing!!

-Ovatvvon :-Q

 

[PHV]

Seems that useGroupAddress is a Boolean.
Replace this:
[tblClients].[clientUseGroupAddress]=1
By this:
[tblClients].[clientUseGroupAddress]=True

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Ovatvvon]

PERFECT!!!!!!!!!!! Thank you so much!

-Ovatvvon :-Q