Access 2003 VBA Help

[RobBhat]

Hi All

I am desperate for the correct syntax in the following:

If condition1 Then
Statement1
If condition2 Then
Statement2
Else: MsgBox "Do X"

I am getting an error on the Else: MsgBox Line

Can someone PLEASE HELP

Rob

 

[JHB]

See below:

If condition1 Then
     Statement1
ElseIf condition2 Then
     Statement2
Else
  MsgBox "Do X"
End If

If you place the cursor on "If" and press "F1" then the "Help file" open.

 

[RobBhat]

Hello

Tried this. But on running it, it gives me the message:

Compile Error: Else without If AND highlights the Condition 2 line

I dont understand why this is not taking it

 

[RobBhat]

My Code is this:

If (Me.Cust_Del_PCode.Value Like "BD1*" Then DeliveryCompanyZone2



If (Me.Cust_Del_PCode.Value Like "BD11*") Then DeliveryCompanyZone1

Else

MsgBox "Input Delivery cost"



If the Else MsgBox code is not there, it works beautifully. I have tried combinations such as you suggested:

If (Me.Cust_Del_PCode.Value Like "BD1*" Then DeliveryCompanyZone2



ElseIf (Me.Cust_Del_PCode.Value Like "BD11*") Then DeliveryCompanyZone1

Else

MsgBox "Input Delivery cost"

End If

Like I said before, it produces the same error "Else without If"

Can you please help?

 

[JHB]

The below works without error:

If (Me.Cust_Del_PCode.Value Like "BD1*") Then
  MsgBox "DeliveryCompanyZone2"
ElseIf (Me.Cust_Del_PCode.Value Like "BD11*") Then
  MsgBox "DeliveryCompanyZone1"
Else
  MsgBox "Input Delivery cost"
End If

Else post your database, (zip it) + where to find the problem code.

 

[RobBhat]

Thank you for the post

Pls see my comments below. The Private subs are working great without the Else MsgBox "Input Delivery cost"

Can you suggest please?

Thanks again.

Rob

If (Me.Cust_Del_PCode.Value Like "BD1*") Then
MsgBox "DeliveryCompanyZone2" This is not a message box but a private sub
ElseIf (Me.Cust_Del_PCode.Value Like "BD11*") Then
MsgBox "DeliveryCompanyZone1" This is not a message box but a private sub
Else
MsgBox "Input Delivery cost"
End If

 

[PeterF]

You put your sub on the same line as the if ... then, this won't work.
You have to put the sup on the next line as JHB suggested. In your case there is no else , elseif and end if.

 

[RobBhat]

Thank you so much JHB and Peter. The one that works perfectly is this:

If (Me.Cust_Del_PCode.Value Like "BD1*") Then
DeliveryCompanyZone2
If (Me.Cust_Del_PCode.Value Like "BD11*") Then
DeliveryCompanyZone1
Else
MsgBox "Input Delivery cost"
End If
End If

When I had the ElseIf against the second line, it wasnt picking it up.

Again, thank you very much

 

[JHB]

Thank you for the post

Pls see my comments below. The Private subs are working great without the Else MsgBox "Input Delivery cost"

Can you suggest please?

Thanks again.

Rob

Sorry - I didn't create the Sub's so I use the MsgBox to show you how you've to set up the If structure, I thought if was clear for everyone but obviously not.

If (Me.Cust_Del_PCode.Value Like "BD1*") Then
   DeliveryCompanyZone2 
ElseIf (Me.Cust_Del_PCode.Value Like "BD11*") Then
   DeliveryCompanyZone1 
Else   
   MsgBox "Input Delivery cost" 
End If

 

[JHB]

Thank you so much JHB and Peter. The one that works perfectly is this:

If (Me.Cust_Del_PCode.Value Like "BD1*") Then
DeliveryCompanyZone2
If (Me.Cust_Del_PCode.Value Like "BD11*") Then
DeliveryCompanyZone1
Else
MsgBox "Input Delivery cost"
End If
End If

When I had the ElseIf against the second line, it wasnt picking it up.

Again, thank you very much

Your have to change the logic, first check for "BD11*" then "BD1*"

 

[RobBhat]

Hi JHB

It works perfectly. I have tested it. It works only when the "BD1" condition comes before the "BD11". If there is a "BD" condition, then that has to come before the BD1 condition. If the sequence is incorrect, the logic fails. Thats what I am seeing.

I used the following case statement and did not work:

Select Case TRUE

Case Left(Me.Cust_Del_PCode, 3) = "BD1 "


Case Left(Me.Cust_Del_PCode, 4) = "BD11"


Case Else

MsgBox "INPUT THE DELIVERY COST"

End Select

 

[Frothingslosh]

Thank you so much JHB and Peter. The one that works perfectly is this:

If (Me.Cust_Del_PCode.Value Like "BD1*") Then
DeliveryCompanyZone2
If (Me.Cust_Del_PCode.Value Like "BD11*") Then
DeliveryCompanyZone1
Else
MsgBox "Input Delivery cost"
End If
End If

When I had the ElseIf against the second line, it wasnt picking it up.

Again, thank you very much

To elaborate on what JHB is saying, this isn't going to do what you think it does. This is where proper indentation is important:

If (Me.Cust_Del_PCode.Value Like "BD1*") Then
    DeliveryCompanyZone2
    If (Me.Cust_Del_PCode.Value Like "BD11*") Then
        DeliveryCompanyZone1
    Else
        MsgBox "Input Delivery cost"
    End If
End If

Basically, it checks for the value to start with BD1. If it does, it executes DeliveryCompanyZone2, and THEN it checks to see if the value also starts with BD11. If it is, it runs DeliveryCompanyZone1, and if it doesn't start with BD11, pops up the message box 'Input Delivery cost'. So BD11 values will cause both procedures to run, and values that do not start with BD1 will appear to have nothing happen at all.

What you need is this:

If (Me.Cust_Del_PCode.Value Like "BD11*") Then
    Call DeliveryCompanyZone1
ElseIf (Me.Cust_Del_PCode.Value Like "BD1*") Then
    Call DeliveryCompanyZone2
Else
    MsgBox "Input Delivery Cost"
End If

Also, since I saw your other thread, note the use of ElseIf. That's what you use when you want to comparisons in sequence. While you can certainly check for BD11 inside the BD1 section, you would want to move the subroutine call to the inner IF...THEN rather than running it before processing the IF. It's honestly a better idea to just run them like JHB said.

 

[jdraw]

It works perfectly. I have tested it.

Your post started with a request to resolve a syntax issue.
There was a comment re logic, which you have solved.

What exactly is the purpose of the code in business terms?What is the issue/opportunity you are trying to resolve with the code you have posted?
If and Select Case statements are "hows" - vba programming constructs.

 

[RobBhat]

Hi JD

The purpose behind it is this.

We have a form with a customer input and on the basis of that input, the address field is filled in. There is a postcode on the address filed. According to the prefix of the postcode, the delivery cost field has to be populated (set up by the courier companies). The delivery cost for a particular prefix will depend on the following factors - selected in drop down boxes on other fields on the form:

1 Delivery company
2 No of pallets (increments of 0.5 from 0.5 to 8
3 Whether its a next day or economy service

Another expert tried to help me with this using separate table, but had to give up due to the complications (http://www.access-programmers.co.uk/forums/showthread.php?t=289551&goto=newpost).

So I resorted to this where after the code (which you guys helped me with), I go into private subs which guide me to the delivery cost.

 

[RobBhat]

Hi Frothingsloss

Thank you. I will try the code you suggested

If (Me.Cust_Del_PCode.Value Like "BD11*") Then
Call DeliveryCompanyZone1
ElseIf (Me.Cust_Del_PCode.Value Like "BD1*") Then
Call DeliveryCompanyZone2
Else
MsgBox "Input Delivery Cost"
End If

Rob

 

[jdraw]

Rob,
I looked at the related posts between you and Sneuberg. I see lots of how's,(solution parts) but I haven't seen a complete list of requirements. Do you have a clear description of the business involved without talking about forms and dropdown boxes.

Along the lines -- we are a ??? company who does????
Can you put the PostalCodes, Groups and exceptions etc into a business context?

 

[RobBhat]

Hi jdraw

We are a company selling pallets of industrial bags. We have an access database that was set up a few years ago. unfortunately, there was no set field for post codes and users have used different address fields for post codes. When we have an order, we execute it on an access based delivery note form. Some customers have different delivery addresses and we have to select one from a list for these customers. Majority have only 1 delivery address. Once the delivery is made, we invoice the customer using the access database. I hope this helps.

 

[Frothingslosh]

Okay, here's a question for you:

Instead of handling the processing charges entirely via hardcoded functions, have you looked into perhaps creating a ShippingCharges table with just ZIP codes and shipping fees (and an autonumber primary key just in case)? Then your function could simply look for a match, and if no match is found, have the user enter a value. You would need to create an update/entry screen for the charges, too, but you would probably end up saving a lot of hours in the long run.

 

[RobBhat]

Hi Frothingslosh

One of the experts on this site was taking me in that direction. He even very kindly set me up a dummy database. but right at the end, he decided that this would not be workable as there may be too many variables with different freight companies with different combinations of postcode charges, as there will probably be 300 postcode prefixes. The thread is (http://www.access-programmers.co.uk/...1&goto=newpost). But do you mean a table with each post code prefix on the table with one to many relationship with different freight companies with their individual charges inputted? Then, somehow, do a DLookUp function according to the prefix on the customer's delivery address field when its selected on the form to derive the cost according to whether its 'Next day' or 'economy' for x number of pallets selected (There are 17 pallet sizes to be selected). So, each post code will have 16 charges for 'Next day' and 16 charges for 'economy'. ie 32 charges for each delivery company. We use 2 at present and so 64 charges altogether

 

[jdraw]

Is this similar to the business?

Customers Order Pallets of X from your company. The pallets contain various quantities of X and have some weight. Your company hires a Freight Company who ships the pallets(orders) to specified shipping addresses. Freight Companies supply your company with shipping rates for various weights to specific Postal Codes. You Invoice the Customer for Goods and Shipping and then pay the Shipper?

Have you considered identifying ShippingCost based on weight * distance?
You could do a lookup to get miles between 2 addresses to calculate distance.
This would/could be done online when recording the Order. Seems it could be an option to (maintaining) keeping multiple PostalCodes and rates by FreightCompany current.

Just for clarity, Postal codes are area locators --a polygon with some point identified as centre of the polygon. This is an approximation, since an address may be on the west edge of one PostalCode, and the ship to address may be on the east most edge of the Ship to postal code. Shipping from one point to another involves Points. You can calculate distance between Points ( more accurately).