Dynamic IF....

[Maracay]

Hi guys,

I have a table with US states and Canadian province’s names, but because the
data is not always as it should be, I may receive a file with “Ontario†or
“Ont†or “ONâ€, in the program I have an IF statement eg If rs!Province =
“Ontario†or rs!Province = “AB†….. and so on with all the provinces or
States, and is getting to long .
I created and table with all the possible names of provinces and states I
could find, and what I want is to read that table and create the statement IF
with that data, in this way I don’t need to modify the program just add the
new name to the table.

I created this code but I am getting a Type Mismatch error.

I will appreciate any help

Thanks

Dim db As DAO.Database
Dim rsa As DAO.Recordset

Set db = CurrentDb
Set rsa = db.OpenRecordset("tblProvState", dbOpenDynaset)

strCanProv = " "
rsa.MoveFirst

Do Until rsa.EOF
tfCanada = False
If rsa!CountryID = 1 Then ' 1 canada 2 USA
strCanProv = strCanProv & "rs!Province = " & rsa!ProvState & " or
rs!Province = " & rsa!ProvStateAbr
tfCanada = True
End If
rsa.MoveNext

' This is to not to get the “ or “ at the end
If Not rsa.EOF And tfCanada = True Then
strCanProv = strCanProv & " or "
End If

Loop

This is the IF I want to create
If strCanPro then
………………………….
End if

 

[Bob Quintal]

I don't see any answers to your request for help, so I'll try.

See my comments insterted to your code.

Hi guys,

I have a table with US states and Canadian province’s names, but
because the data is not always as it should be, I may receive a
file with “Ontario†or “Ont†or “ONâ€, in the program I
have an IF statement eg If rs!Province = “Ontario†or
rs!Province = “AB†….. and so on with all the provinces or
States, and is getting to long .
I created and table with all the possible names of provinces and
states I could find, and what I want is to read that table and
create the statement IF with that data, in this way I don’t need
to modify the program just add the new name to the table.

I created this code but I am getting a Type Mismatch error.

I will appreciate any help

Thanks

Dim db As DAO.Database
Dim rsa As DAO.Recordset

Set db = CurrentDb
Set rsa = db.OpenRecordset("tblProvState", dbOpenDynaset)

where do you declare strCanProv and tfCanada?

strCanProv = " "

' why a space, you don't need it. just "" is fine.

rsa.MoveFirst

You are automatically on hte first record, because you just opened
the recordset.

Do Until rsa.EOF
tfCanada = False
If rsa!CountryID = 1 Then ' 1 canada 2 USA

This is a dog's breakfast

strCanProv = strCanProv & "rs!Province = " & rsa!ProvState &
" or
rs!Province = " & rsa!ProvStateAbr
tfCanada = True
End If
rsa.MoveNext

' This is to not to get the “ or “ at the end

it's much more efficient to just trim off the last " or "
when finished.

If Not rsa.EOF And tfCanada = True Then
strCanProv = strCanProv & " or "
End If

Loop

I can't decypher your characters. post in plaintext if you need mmore
info about the below statement

This is the IF I want to create
If strCanPro then
………………………….
End if

Here's the code I'd use, if I were to use code and not just a simple
dLookup() function:

Dim rsa As DAO.Recordset
Dim strCanProv as string
Dim strSQL as string

strSQL = "SELECT * from tblProvState WHERE CountryID = 1;"
Set rsa = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

Do Until rsa.EOF
strCanProv = strCanProv _
& """ & rsa!ProvState & """ _
& ","" & rsa!ProvStateAbr & ""","
rsa.MoveNext
Loop
strCanProv = left(strCanProv, len(strCanProv) -1)