If then

[Guest]

Okay, apparently I don't know how to construct IF Then statements. This one
is not giving me the right price structure.

If (52 < Me!Length <= 61) Then
Me.Price = 40.95
ElseIf (44 < Me!Length <= 52) Then
Me.Price = 37.75
ElseIf (34 < Me!Length <= 44) Then
Me!Price = 34.28
ElseIf (27 < Me!Length <= 34) Then
Me!Price = 24.18
ElseIf (14 < Me!Length <= 27) Then
Me!Price = 15.09
ElseIf (7 < Me!Length <= 14) Then
Me!Price = 6.63
ElseIf (0 < Me!Length <= 7) Then
Me!Price = 2.91
Else: Me!Price = "Oversize"
End If

The Current record for Me!Length is being passed through to the SQL, but I
can't get the If Then statements to select the appropriate price for the
Me!Price text box. Please help, if you can.

 

[SusanV]

This is a good time to drop the If..Then and use a SELECT CASE statement
instead - much easier to manage:

This page has an excellent explanation, as well as an example of using this
function with data and conditions similar to yours:

http://visualbasic.about.com/od/learnvbnet/a/blecvbsbs0704.htm

 

[Guest]

Awesome! Thanks
--
Why are you asking me? I dont know what Im doing!

Jaybird

This is a good time to drop the If..Then and use a SELECT CASE statement
instead - much easier to manage:

This page has an excellent explanation, as well as an example of using this
function with data and conditions similar to yours:

http://visualbasic.about.com/od/learnvbnet/a/blecvbsbs0704.htm

 

[SusanV]

Glad to help - those long if...then statement's are a bear <grin>

Awesome! Thanks

 

[Guest]

A related question:
When I add an additional parameter me!Process_Code to my code like this:

Select Case Me!Process_Code
Case Is = "*SLA"
Select Case Me!Length
Case Is <= 7
Me.Price = 2.91
Case Is <= 14
Me.Price = 6.63
Case Is <= 27
Me.Price = 15.09
Case Is <= 34
Me.Price = 24.18
Case Is <= 44
Me.Price = 34.28
Case Is <= 61
Me.Price = 40.95
Case Is > 61
Me.Price = "Oversize"
Case Else
Me.Price = "Need Length"
End Select
Case Else
End Select

The function fails to recognize values such as "7000SLA" after I put the
wildcard criteria in. I need to add pricing for other process codes later,
but I can't even get this one to work. Why does "7000SLA" not equal "*SLA"
in my Select Case Statement? BTW, I've also tried the "if... then" method
with similar results. Either my syntax stinks or the wildcard doesn't work
in this way within the select case statement. Please help.

 

[SusanV]

Unfortunately you can't use a wildcard in a SELECT CASE statement; instead,
use an If...then for the wildcard piece, and then use select case for the
rest:

IF Me!Process_Code LIKE"*SLA" then
Select Case Me!Length
Case Is <= 7
Me.Price = 2.91
Case Is <= 14
Me.Price = 6.63
Case Is <= 27
Me.Price = 15.09
Case Is <= 34
Me.Price = 24.18
Case Is <= 44
Me.Price = 34.28
Case Is <= 61
Me.Price = 40.95
Case Is > 61
Me.Price = "Oversize"
Case Else
Me.Price = "Need Length"
End Select
End if

Here's another page with more info:
http://archive.baarns.com/excel/faq/xd_lang1.asp

Also as a side note, the indentation I added above makes it easier to
read/edit if you need to go back to this piece of code at a later date
;-)

 

[Guest]

Thanks, Susan! I started off with the "if... then" statements, but then ran
into problems. Hopefully, your link will answer my question.
--
Why are you asking me? I dont know what Im doing!

Jaybird

Unfortunately you can't use a wildcard in a SELECT CASE statement; instead,
use an If...then for the wildcard piece, and then use select case for the
rest:

IF Me!Process_Code LIKE"*SLA" then
Select Case Me!Length
Case Is <= 7
Me.Price = 2.91
Case Is <= 14
Me.Price = 6.63
Case Is <= 27
Me.Price = 15.09
Case Is <= 34
Me.Price = 24.18
Case Is <= 44
Me.Price = 34.28
Case Is <= 61
Me.Price = 40.95
Case Is > 61
Me.Price = "Oversize"
Case Else
Me.Price = "Need Length"
End Select
End if

Here's another page with more info:
http://archive.baarns.com/excel/faq/xd_lang1.asp

Also as a side note, the indentation I added above makes it easier to
read/edit if you need to go back to this piece of code at a later date
;-)

 

[Guest]

Ooof! How cruel the Access gods? For their sport alone, are we... I am
brought low. For the sake of a cruddy two letter word ("IS") my code
wouldn't work! This is why I never became a programmer... No aptitude for
it. This was a two day jog down the Access path for what was supposed to be
a simple task. Tsk. Maybe I'll go help the janitor instead of wasting my
time...
--
Why are you asking me? I dont know what Im doing!

Jaybird

Thanks, Susan! I started off with the "if... then" statements, but then ran
into problems. Hopefully, your link will answer my question.

 

[SusanV]

Gah, I should have caught that too...

TGIF, it's been a long week!