Visual Basic IF statement…

50 pts.
Tags:
Database
VBA
I am trying to write an IF statement that will show a message box if the postcode of the customer begins with one of about 20 area codes. I can get it working for one code, but as soon as I add ' Or "HS*" ' (minus the apostrophes) it comes up with Type Mismatch. What am I missing / doing wrong? If Forms![OrderProc].DelPostCode Like "BT*" Or "HS*" Then MsgBox "This is being shipped to an overseas area!!!", vbCritical, "*** WARNING ***" End If For simplicity I have only added two of the areacodes, including them all does the same as two anyway. Thanks for any help

Answer Wiki

Thanks. We'll let you know when a new response is added.

You need to add in the Form field each time you use the OR function, as without specifying it, the statement does not know what it is evaluating against, hence the error message. i.e.

If Forms![OrderProc].DelPostCode Like “BT*” Or Forms![OrderProc].DelPostCode Like “HS*” Then….

That will look very untidy if you add in 20 or statements. Possibly a better solution would be to hold the warning postcodes in a table, then read in the field and compare to each of the records in the DB e.g.

dim in_pc
dim warn_pc

in_pc = Forms![OrderProc].DelPostCode

set recordset here
warn_pc = “select pc from warn_pc where pc = instr(in_pc,1,2)”
open recordset etc etc

if the recordset returns something, then send the warning message, if not, then just leave alone…

Let us know if you need help with the recordsets.

Darryn

You could also setup a Select/Case group

dim chkPostCode as string

chkPostCode = subst(Forms![OrderProc].DelPostCode, 1, 2)

Select chkPostCode

Case “BT”, “HS”, ……..
your code to display message box here

It just looks a little cleaner to me.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following