Case Statement within "INSERT INTO" Query?

[sherlocked]

Greetings Experts:

I am using the below SQL statement to pull a record from one database and insert it into another. I need to be able to update the column POBSort with one of three possible options based on the place of birth that's being displayed on a user subform.

I have done some Googling and found a few suggestions about using the Switch function or writing a module and calling it from within the query but the syntax of those things evade me.

Here is my sql statement:

DoCmd.RunSQL "INSERT INTO tblRecords ( AppNo, AppType, FName, MI, LName, DOB, POB, MailAdd, MailCity, MailState, MailZip, Source, ExecMonth, ExecYear, ExecDate ) " _
           & "IN 'MYURLTOMYDATABASE' " _
           & "SELECT tblAppData.AppNo, tblAppData.AppType, tblAppData.FName, tblAppData.MI, tblAppData.LName, tblAppData.DOB, tblAppData.POB, " _
           & "tblAppData.MailAdd, tblAppData.MailCity, tblAppData.MailState, tblAppData.MailZip, 'DS Referral' AS Expr1, DatePart('m',[ExecDate]) AS Expr2, " _
           & "Format([ExecDate],'yyyy') AS Expr3, tblAppData.ExecDate, " _
           & "FROM tblAppData " _
           & "GROUP BY tblAppData.AppNo, tblAppData.AppType, tblAppData.FName, tblAppData.MI, tblAppData.LName, tblAppData.DOB, tblAppData.POB, " _
           & "tblAppData.MailAdd, tblAppData.MailCity, tblAppData.MailState, tblAppData.MailZip, 'DS Referral', " _
           & "DatePart('m',[ExecDate]), Format([ExecDate],'yyyy'), tblAppData.ExecDate, " _
           & "HAVING (((tblAppData.AppNo)= '" & Form_frmStatusUpdate.frmUpdateCaseData.Form.AppNo & "'))"

Any ideas as to how I can get this query to look at the POB field and apply a CASE statement to update a different field in the destination table based on it's value?

Hope this is as clear as mud. Thanks in advance

 

[Ranman256]

you dont use CASE statments in a query, thats what joining tables is for.
join the lookup table to the main table. This pulls the data wanted.
(or use a Switch statment)

Case statements are for VB code.

 

[sherlocked]

Can you please explain how you would use a SWITCH statement here? I have never used one and wouldn't even know how to begin.

Thank you

 

[moke123]

heres a link to some info on the switch function to get you started...https://www.techonthenet.com/access/functions/advanced/switch.php

 

[sherlocked]

Thanks, this helpful. Will this work if there is more than a one for one option?

To specify, if someone's POB is CA, NY, CT, ND, etc. I want the field to display "USA"

If someone's born in PRI, GUAM, etc it will display something different.

Would SWITCH work in this scenario?

 

[MarkK]

This is an active thread, right? There is no need to double post this. I'll remove the other post.

 

[MarkK]

The most efficient way to affect this outcome is to link to the table that contains this data. Imagine a table like...

tState
StateID
StateAbbrev
StateName
Country

Then maybe you have data like...

Abbrev  Country
CA      USA
NY      USA
CT      USA
ND      USA
PRI     Oth
GUAM    Oth

Then link to that table using the POB, like...

SELECT tState.Country, ...  
FROM tblAppData INNER JOIN tState ON tblAppData.POB = tState.StateAbbrev

See what is going in that partial SQL? The Country field from tState is displayed as a field in the query as linked to by the BOP and StateAbbrev fields. This will be by far that fastest and simplest way to make this work.

Does that make sense?