update multiple fields

[slimjen]

All,
Using access 2003; Im trying to update a field based on another field. i.e.
using an update query.

 
If [Shipper State]= ME, VT, NH, CT, N,J, MD, DC,VA, NC,TN SC, MA, DE, RI [Shipper Country] = NORTHEAST
ELSEIF [Shipper State] = OK, AR,TX,LA,MS,AL, GA, FL [Shipper Country]=SOUTHEAST
ELSEIF [Reciever State] =, VT, NH, CT, N,J, MD, DC,VA, NC,TN SC, MA, DE, RI [Reciever Country] = NORTHEAST
ELSEIF [Reciever State]= OK, AR,TX,LA,MS,AL, GA, FL [Shipper Country]=SOUTHEAST
ENDELSE

I have not done a if or case statement in a long while. Can anyone guide me based on my criteria above. Should I put this in a module to call before my append query? Thanks

[alansidman]

Here is the syntax for Case Select. Looks like your perfect for this scenario.

http://www.techonthenet.com/access/f...anced/case.php

[slimjen]

ok. I don't remember how to use this but I'll give it a shot.

Public Function
Dim
Select Case[?]
Case [Shipper State]”ME”
[Shipper Country]=NORTH
Case [Shipper State]”VT”
[Shipper Country]=NORTH
Case [Shipper State]”SC”
[Shipper Country]=NORTH
End Select

Still fuzzy. Not sure if this is going in the correct direction, I assume I have to put this in a module to call. I really need help please starting this off.
Thanks

[alansidman]

You can put it in an after update event or even on an onclick event for a button. The question mark should be populated with the field name you are testing.

Public Function
Dim
Select Case[Shipper State]
Case ”ME”
[Shipper Country]="NORTH"
Case ”VT”
[Shipper Country]="NORTH"
Case ”SC”
[Shipper Country]="NORTH"
End Select

Alternatively, you could do it this way also

Public Function
Dim
Select Case[Shipper State]
Case ”ME”, "VT", "SC"
[Shipper Country] = "NORTH"
Case "FL", LA", "AL"
[Shipper Country] = "SOUTH"
End Select

[slimjen]

ok. Havent done this in a while but I'm having problems with my Case statement. I put it in a function called update because that's what Im doing in a table. When I try to compile it; the Function line turns red pointing out the fields. I don't know if this works because I cant get past the comile error.

Option Compare Database
Function Update([Shipper State]As String,[Shipper Company]As String, [Shipper Country]As String, [Recipient Company]As String, [Recipient Country] As String)
On Error GoTo Err_Update
Select Case [Shipper State]
Case "ME", "VT", "NH", "NY", "CT", "NJ", "MD", "DC", "VA", "NC", "TN", "SC", "MA", "DE", "RI"
[Shipper Country] = "US NORTHEAST REGION"
Case "AR", "TX", "LA", "MS", "AL", "GA", "FL"
[Shipper Country] = "US SOUTH REGION"
Case "ND", "SD", "NE", "KS", "MN", "IA", "MO", "WI", "IL", "IN", "OH", "MI", "KY", "WV", "PA"
[Shipper Country] = "US CENTRAL REGION"
Case "WA", "OR", "CA", "ID", "NV", "MT", "WY", "UT", "CO", "AZ", "NM", "AK", "HI"
[Shipper Country] = "US WEST REGION"
End Select
Select Case [Recipient State]
    Case "ME", "VT", "NH", "NY", "CT", "NJ", "MD", "DC", "VA", "NC", "TN", "SC", "MA", "DE", "RI"
    [Recipient Country] = "US NORTHEAST REGION"
    Case "AR", "TX", "LA", "MS", "AL", "GA", "FL"
    [Recipient Country] = "US SOUTH REGION"
    Case "ND", "SD", "NE", "KS", "MN", "IA", "MO", "WI", "IL", "IN", "OH", "MI", "KY", "WV", "PA"
    [Recipient Country] = "US CENTRAL REGION"
    Case "WA", "OR", "CA", "ID", "NV", "MT", "WY", "UT", "CO", "AZ", "NM", "AK", "HI"
    [Recipient Country] = "US WEST REGION"
End Select
Select Case [Recipient Company]
    Case "Atlas*" Or "NORTH AMERICAN SERVICE CENTER"
    [Recipient Company] = "ATLAS COPCO COMPRESSORS"
End Select
Select Case [Shipper Company]
    Case "Atlas*" Or "NORTH AMERICAN SERVICE CENTER"
    [Shipper Company] = "ATLAS COPCO COMPRESSORS"
End Select
Exit_Update:
    Exit Function
 
Err_Update:
        MsgBox Err.Number & " - " & Err.Description
        Resume Exit_Update
        Exit Function
End Function

Please help.
Thanks