Help With If and Else If Statement

[Guest]

Here is my code - I am trying to put this in a report so that the value of
the item is based on the field packingtype (each, pack, box, brick) and
depending on the type will determing the price - i then need to take that
value of the item and multiply it by the quanity returned to get a return
value. I have tried this in query mode - does not work - I dont want to have
to create a new table just for calculations but will if i must. Any help is
appericated.

VALUEOFITEM() As Currency
If PACKINGTYPE = "EACH" Then
VALUEOFITEM = KSEACHPRICE
ElseIf PACKINGTYPE = "PACK" Then
VALUEOFITEM = KSPACKPRICE
ElseIf KSPACKPRICE = "BOX" Then
VALUEOFITEM = KSBOXPRICE
ElseIf PACKINGTYPE = "BRICK" Then
GetPriceOfItem = KSBRICKPRICE
Else
End If

 

[Guest]

First, creating a separate table to handle your calculations is both
unneccessary and a bad idea. Tables are not designed to handle calculations.
If yoru DB is set up correctly, you should have a table for your Packing
Types category. This table would have fields like PackingTypeID, PackingType,
PackingPrice, etc.

In your form or report, you can use the DLookup function as the control
source of an unbound control to pull the correct price from the table based
on the value of the PackingType field. It would look something like;

=DLookup ("PackingPrice", "tblPackingTypes", "PackingType = " &
Forms!NameOfYourForm![PackingType])

You can find more info about the DLookup function at this link:

http://msdn2.microsoft.com/en-us/library/Aa172176(office.11).aspx.

HTH

 

[Guest]

Thank you i will check into the dlookup - i did not mention and probably
should have i do have a product master table that has the IdNumber
Description and such - then i have a packingtype table that has only four
options of packing type. It get hairy here because the price table has 5
different sets of prices based on the price level type - so because i can not
get a else if then statement to work for four packaging types and five price
levles for each packaging type i am do a query of each price level separately
- and i need those prices to pull up. I hope this helps explain more.

First, creating a separate table to handle your calculations is both
unneccessary and a bad idea. Tables are not designed to handle calculations.
If yoru DB is set up correctly, you should have a table for your Packing
Types category. This table would have fields like PackingTypeID, PackingType,
PackingPrice, etc.

In your form or report, you can use the DLookup function as the control
source of an unbound control to pull the correct price from the table based
on the value of the PackingType field. It would look something like;

=DLookup ("PackingPrice", "tblPackingTypes", "PackingType = " &
Forms!NameOfYourForm![PackingType])

You can find more info about the DLookup function at this link:

http://msdn2.microsoft.com/en-us/library/Aa172176(office.11).aspx.

HTH

Here is my code - I am trying to put this in a report so that the value of
the item is based on the field packingtype (each, pack, box, brick) and
depending on the type will determing the price - i then need to take that
value of the item and multiply it by the quanity returned to get a return
value. I have tried this in query mode - does not work - I dont want to have
to create a new table just for calculations but will if i must. Any help is
appericated.

VALUEOFITEM() As Currency
If PACKINGTYPE = "EACH" Then
VALUEOFITEM = KSEACHPRICE
ElseIf PACKINGTYPE = "PACK" Then
VALUEOFITEM = KSPACKPRICE
ElseIf KSPACKPRICE = "BOX" Then
VALUEOFITEM = KSBOXPRICE
ElseIf PACKINGTYPE = "BRICK" Then
GetPriceOfItem = KSBRICKPRICE
Else
End If

 

[Guest]

In that case DLookup may not work for you. It is limited in the amount of
criteria it can handle. You may need to use a SQL statement (query) as the
source of your control.

What determines the price level? Is it something the DB user will determine

Thank you i will check into the dlookup - i did not mention and probably
should have i do have a product master table that has the IdNumber
Description and such - then i have a packingtype table that has only four
options of packing type. It get hairy here because the price table has 5
different sets of prices based on the price level type - so because i can not
get a else if then statement to work for four packaging types and five price
levles for each packaging type i am do a query of each price level separately
- and i need those prices to pull up. I hope this helps explain more.

First, creating a separate table to handle your calculations is both
unneccessary and a bad idea. Tables are not designed to handle calculations.
If yoru DB is set up correctly, you should have a table for your Packing
Types category. This table would have fields like PackingTypeID, PackingType,
PackingPrice, etc.

In your form or report, you can use the DLookup function as the control
source of an unbound control to pull the correct price from the table based
on the value of the PackingType field. It would look something like;

=DLookup ("PackingPrice", "tblPackingTypes", "PackingType = " &
Forms!NameOfYourForm![PackingType])

You can find more info about the DLookup function at this link:

http://msdn2.microsoft.com/en-us/library/Aa172176(office.11).aspx.

HTH

Here is my code - I am trying to put this in a report so that the value of
the item is based on the field packingtype (each, pack, box, brick) and
depending on the type will determing the price - i then need to take that
value of the item and multiply it by the quanity returned to get a return
value. I have tried this in query mode - does not work - I dont want to have
to create a new table just for calculations but will if i must. Any help is
appericated.

VALUEOFITEM() As Currency
If PACKINGTYPE = "EACH" Then
VALUEOFITEM = KSEACHPRICE
ElseIf PACKINGTYPE = "PACK" Then
VALUEOFITEM = KSPACKPRICE
ElseIf KSPACKPRICE = "BOX" Then
VALUEOFITEM = KSBOXPRICE
ElseIf PACKINGTYPE = "BRICK" Then
GetPriceOfItem = KSBRICKPRICE
Else
End If

 

[Guest]

The price level is determined by where the product is being returned from or
sent to for example: I have KS for Kansas Prices, WI or Wichita prices, JK
for Jakes Prices, LA for Louisiana Prices, and WK for Western Kansas prices.
When each location is set up there is a field to assign the price level.

I have it in the able but not showing as a field on the report or form - it
is an behind the secens type of criteria that really only I need to see.

Can a DLookup be combinded with an if or else if statement?

In that case DLookup may not work for you. It is limited in the amount of
criteria it can handle. You may need to use a SQL statement (query) as the
source of your control.

What determines the price level? Is it something the DB user will determine

Thank you i will check into the dlookup - i did not mention and probably
should have i do have a product master table that has the IdNumber
Description and such - then i have a packingtype table that has only four
options of packing type. It get hairy here because the price table has 5
different sets of prices based on the price level type - so because i can not
get a else if then statement to work for four packaging types and five price
levles for each packaging type i am do a query of each price level separately
- and i need those prices to pull up. I hope this helps explain more.

First, creating a separate table to handle your calculations is both
unneccessary and a bad idea. Tables are not designed to handle calculations.
If yoru DB is set up correctly, you should have a table for your Packing
Types category. This table would have fields like PackingTypeID, PackingType,
PackingPrice, etc.

In your form or report, you can use the DLookup function as the control
source of an unbound control to pull the correct price from the table based
on the value of the PackingType field. It would look something like;

=DLookup ("PackingPrice", "tblPackingTypes", "PackingType = " &
Forms!NameOfYourForm![PackingType])

You can find more info about the DLookup function at this link:

http://msdn2.microsoft.com/en-us/library/Aa172176(office.11).aspx.

HTH

:

Here is my code - I am trying to put this in a report so that the value of
the item is based on the field packingtype (each, pack, box, brick) and
depending on the type will determing the price - i then need to take that
value of the item and multiply it by the quanity returned to get a return
value. I have tried this in query mode - does not work - I dont want to have
to create a new table just for calculations but will if i must. Any help is
appericated.

VALUEOFITEM() As Currency
If PACKINGTYPE = "EACH" Then
VALUEOFITEM = KSEACHPRICE
ElseIf PACKINGTYPE = "PACK" Then
VALUEOFITEM = KSPACKPRICE
ElseIf KSPACKPRICE = "BOX" Then
VALUEOFITEM = KSBOXPRICE
ElseIf PACKINGTYPE = "BRICK" Then
GetPriceOfItem = KSBRICKPRICE
Else
End If

 

[Guest]

Just because a control is on your form/report doesn't mean your users have to
see it. Set it's visible property to no, then you can use it for references
in your code and your users won't even know it's there.

You mentioned earlier that you couldn't get it to work with a query. If your
tables are related properly, then you should be able to use a query, which,
again, would be the best option. However, it would be difficult to tell you
how to set it up without
seeing your DB.

As to your other question, yes you could use DLookup in an If Then
statement. It sounds like you have a lot of possible conditions though, so if
you're going to use this approach, you might want to use a Select Case
statement also. The code might look something like the following;

If Me.PackingType = "Brick" Then

Select Case Me.PriceLevel

Case "KS"
Me.PackingPrice = DLookup("KSBrickPrice", "tblPrices")

Case "WI"
Me.PackingPrice = DLookup("WIBrickPrice", "tblPrices")

Case "JK"
Me.PackingPrice = DLookup("JKBrickPrice", "tblPrices)

Case "LA"
Me.Packingprice = DLookup("LABrickPrice", "tblPrices")

Case "WK"
Me.PackingPrice = DLookup("WKBrickPrice", "tblPrices")

End Select

Else If Me.PackingType = Pack Then

Select Case Me.PriceLevel

Case "KS"
Me.PackingPrice = DLookup("KSPackPrice", "tblPrices")

Case "WI"
Me.PackingPrice = DLookup("WIPackPrice", "tblPrices")

Case "JK"
Me.PackingPrice = DLookup("JKPackPrice", "tblPrices)

Case "LA"
Me.Packingprice = DLookup("LAPackPrice", "tblPrices")

Case "WK"
Me.PackingPrice = DLookup("WKPackPrice", "tblPrices")

End Select

And so on, and so on. This assumes that you add a PriceLevel control to your
form/report (invisible if you want) and that the control will always have one
of those values in it. Also, remember that each Select Case statement needs
to have an End Select at the end of it.

HTH

The price level is determined by where the product is being returned from or
sent to for example: I have KS for Kansas Prices, WI or Wichita prices, JK
for Jakes Prices, LA for Louisiana Prices, and WK for Western Kansas prices.
When each location is set up there is a field to assign the price level.

I have it in the able but not showing as a field on the report or form - it
is an behind the secens type of criteria that really only I need to see.

Can a DLookup be combinded with an if or else if statement?

In that case DLookup may not work for you. It is limited in the amount of
criteria it can handle. You may need to use a SQL statement (query) as the
source of your control.

What determines the price level? Is it something the DB user will determine

Thank you i will check into the dlookup - i did not mention and probably
should have i do have a product master table that has the IdNumber
Description and such - then i have a packingtype table that has only four
options of packing type. It get hairy here because the price table has 5
different sets of prices based on the price level type - so because i can not
get a else if then statement to work for four packaging types and five price
levles for each packaging type i am do a query of each price level separately
- and i need those prices to pull up. I hope this helps explain more.

:

First, creating a separate table to handle your calculations is both
unneccessary and a bad idea. Tables are not designed to handle calculations.
If yoru DB is set up correctly, you should have a table for your Packing
Types category. This table would have fields like PackingTypeID, PackingType,
PackingPrice, etc.

In your form or report, you can use the DLookup function as the control
source of an unbound control to pull the correct price from the table based
on the value of the PackingType field. It would look something like;

=DLookup ("PackingPrice", "tblPackingTypes", "PackingType = " &
Forms!NameOfYourForm![PackingType])

You can find more info about the DLookup function at this link:

http://msdn2.microsoft.com/en-us/library/Aa172176(office.11).aspx.

HTH

:

Here is my code - I am trying to put this in a report so that the value of
the item is based on the field packingtype (each, pack, box, brick) and
depending on the type will determing the price - i then need to take that
value of the item and multiply it by the quanity returned to get a return
value. I have tried this in query mode - does not work - I dont want to have
to create a new table just for calculations but will if i must. Any help is
appericated.

VALUEOFITEM() As Currency
If PACKINGTYPE = "EACH" Then
VALUEOFITEM = KSEACHPRICE
ElseIf PACKINGTYPE = "PACK" Then
VALUEOFITEM = KSPACKPRICE
ElseIf KSPACKPRICE = "BOX" Then
VALUEOFITEM = KSBOXPRICE
ElseIf PACKINGTYPE = "BRICK" Then
GetPriceOfItem = KSBRICKPRICE
Else
End If