Try this:

Me.txtPrice = Nz(DLookup("[Price]", "tblPrices", strCriteria),"Not Found")

It could be that the combination of three are yielding NO prices and you're getting a null value in the txtPrice field. In using the Nz function, if there is no price for the given criteria, you'll see "Not Found" in the textbox.



Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

ok I tried that and it says not found in the text box

so it should be an error on the lookup or structure of the tables.

Greg. I am sorry I found out the mistake.

All fixed and working.

But now I have a problem. I want to use an option group. Instead of one of the combo boxes.

How can I put that in the strCriteria?

If you changed one of the choices from a combobox to an option group (e.g. grpType for type), does that mean you're changing its related field in the table to be of type Byte (i.e. =1 or =2) or are you keeping it as text and leaving it as a String type?

If you're changing the field in the table, then if Type were to get the option group then it would look something like this:

Private Sub cmdGetPrice_Click()

    Dim strCriteria As String

    strCriteria = "[PriceLevel] = '" & Me.cboPriceLevel & "'"
    strCriteria = strCriteria & " And [Thickness] = '" & Me.cboThickness & "'"
    strCriteria = strCriteria & " And [Type] = " & Me.grpType

    Me.txtPrice = Nz(DLookup("[Price]", "tblPrices", strCriteria), "Not Found")

End Sub

Where grpType would get a 1 or 2 or 3 or whichever radio button you pressed. Your table would have a field called bytType where it would hold a 1 or 2 or 3, etc.

If Type was still a string in the table, then you'd have to do more manipulation.

Private Sub cmdGetPrice_Click()

    Dim strCriteria As String

    strCriteria = "[PriceLevel] = '" & Me.cboPriceLevel & "'"
    strCriteria = strCriteria & " And [Thickness] = '" & Me.cboThickness & "'"

    Select Case grpType
        Case 1
            strCriteria = strCriteria & " And [Type] = 'X'"
        Case 2
            strCriteria = strCriteria & " And [Type] = 'Y'"
        Case 3
            strCriteria = strCriteria & " And [Type] = 'Z'"
    End Select

    Me.txtPrice = Nz(DLookup("[Price]", "tblPrices", strCriteria), "Not Found")

End Sub

Replace X, Y, and Z to actual string values when the Type = 1, 2, or 3, respectively.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Greg, everything have being usefull so far. I have being using the same procedure to get other component prices and added up. Thanks

But now I will like to try something else that will save me a lot of time.

The thing is:

1 option group: PriceLevel
2 combo boxes: Thickness and Type

The tblGlassPrices has these columns:
ID - Thickness - Type - RGlassPrice - WGlassPrice

RGlassPrice - stands for the Retail price
WGlassPrice - stands for the Wholesale price

The idea is when I choose Retail in the option group it will look for the price under the RGlassPrice column.

Please help me thanks.

greg,

I already found out how. Thanks

Greg,

I am almost done with my program, but I need some minor improvements.

I need to have a checkbox that when value = true, it will show a value from a field in a table.

Here's the thing.

I have a table called tblPackingPrice with 2 columns: IndividualPacking (yes/no) and PackingPrice (number).

I need that when a check box called chkPacking is True a text box called txtPackingPrice shows the value in the field PackingPrice.

That is one thing. The other:

Can I convert a form into a report with a single click. If yes, how can I add a button in the form that when clicked it will create a report with a customized layout that can be printable in order to review the calculations in the form.

Thanks for helping.

Put a checkbox and a textbox on your form and bind them to those two fields you mentioned. On the checkbox's After Update event AND on the form's On Current event put something like this:

Me.txtPackingPrice.Visible = Me.cboIndividualPacking

When the checkbox for individual packing (cboIndividualPacking) is true then the textbox for packing price (txtPackingPrice) will be visible. Otherwise, not.

Yes, use the report wizard and it'll guide you step by step.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

hi,

I will like to know if access has a roundup function like excel that rounds up to the next decimal place.

also it may sound stupid but, how can I show on my form the value in an especific cell (or I dunno how to call it in access terms).