Syntax Help Access 2003

[RobBhat]

I have a simple syntax problem that I want help with.

On an after update field in a form I have the following code

"If Me.Cust_Del_PCode.Value Like "EN9*" Then Me.Delivery_Cost.Value = "55"
If Me.Cust_Del_PCode.Value Like "BD1*" Then Me.Delivery_Cost.Value = "65"


Else: MsgBox (“LOOK UP THE DELIVERY COST”)"

On running the VBA, it does not like the Else: MsgBox ("") syntax.

Please help

Rob

 

[Ranman256]

you cant use LIKE in an IF statement in a form. These are for Queries.
You can use LEFT(me.cust_del_pcode,2)

select case TRUE
     case LEFT(me.cust_del_pcode,2)="EN" 
          'do something
     case LEFT(me.cust_del_pcode,3)="BD1"
        'do something
    case else
       MsgBox (“LOOK UP THE DELIVERY COST”)
end select

 

[RobBhat]

Hello Ranman

The Like statements are working perfectly fine.

The syntax is with the Else: MsgBox line. Can you help me with that?
Rob

 

[RobBhat]

Your select Case may be a better alternative. I will also try that...Thanks

 

[RobBhat]

Hello again...I tried the case statement but its not carrying out the statement I require. I will need to expand on the statement if I can make the basic work. Please note that I have used the post code prefixes "BD11" and "BD1" as will be the case in a real life situation. Here, the first 3 units are the same for both cases.

When the filed = "BD11", it works fine. When its "BD1" it gives a runtime error 94 (Invalid use of Null). On debugging it highlights the line Case Left(Me.Cust_Del_PCode, 4) = "BD11""

"How can I overcome this please?

Select Case True


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

DeliveryCompanyZone1 (Private sub which executes a routine)


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

DeliveryCompanyZone2 (Another Private sub which executes a routine)


Case Else

MsgBox "INPUT THE DELIVERY COST"

End Select

 

[gemma-the-husky]

it's easier if you indent your code, and use code tags when posting

I might also use goto's to make it clearer
I find nested ifs and if..elseifs hard to follow

if left(Me.Cust_Del_PCode, 4) = "bd11" then
    DeliveryCompanyZone1 (Private sub which executes a routine)
    goto nextstep
end if    

if left(Me.Cust_Del_PCode, 3) = "bd1" then
    DeliveryCompanyZone2 (Private sub which executes a routine)
    goto nextstep
end if    

MsgBox "INPUT THE DELIVERY COST"
'you need a way to manage this, here

nextstep:
'carry on

 

[RobBhat]

Thank you Gemma

I will give this a go. Just to clarify "Goto NextsteP2- This is your comment correct?

 

[gemma-the-husky]

I didn't put

goto nextstep2.

I used goto nextstep

nextstep: is a label to indicate the point from which I wish the process to continue to run.

what you are trying to do is this, I think

if test1 succeeds then do process x
   else if test2 succeeds then do process y
       else do process z

nextstep: 'a label to mark this point
carryon with next process

the syntax to do this is quite tricky, as you found.

I find it easier to follow the logic by not using nested ifs, and instead using goto syntax, which is often frowned upon. A case statement might be similar, but you can't use a single case to deal with a series of if ... then

I hope you can see that the following is the same as the original (assuming the nesting IN the original is what I understood it to be).

if test1 succeeds then 
    do process x
    'jump
    goto nextstep
end if

if test2 succeeds then 
     do process y
     'jump
    goto nextstep
end if

do process z
     'jump
    goto nextstep


nextstep:
carryon with next process