Dlookup help using unbound form field

[Accessunknown]

Hi,

I'm new to the forums. I would like your help regarding dlookup or if you could guide me in the right direction.

I have a table that consist of pricing for different types of products:

Table name: Costs

ID TYPE EastNew EastRenew WestNew WestRenew
1 500 4100 1000 4400 900
2 501 4100 1000 4400 900
3 600 3400 900 4600 1200
4 700 3700 1300 4900 3300


I have a form that consist of many fields that the user needs to manually input data. 3 fields on that form are the criteria i need to determine which pricing to use back on the table. The first field, which name is LTYPE, is a combobox that the user needs to choose as its type (i.e 500,501,600,700). The second field, which name is EastCoast, is a checkbox (Yes/No) that tells me if it's East Coast (East = yes and West = No). The third field, which name is NewRenewal, is a combobox that the user chooses NEW or Renewal).

Example 1, if the user chooses type 501, checkbox is YES for East, Product is NEW. Then the pricing is $4,100

Example 2, if the user chooses type 700, checkbox is unchecked for NO (which is West), Product is Renewal. Then pricing is $3,300


I would like the unbound field to auto determine the pricing based on the 3 criterias chosen. I read around the forums that Dlookup would be the function for this, however, i tried many times but it did not work. Your help is appreciated. Thank you

 

[CJ_London]

Your table design is not correct - there is a solution but because for the design, it is much more complex.

The correct design would be

Costs
ID autonumber
CostType long
EastWest yesno
NewRenew yesno
CostValue currency

in which case your dlookup would be

dlookup("CostValue","Costs", "CostType=" & me.LType & " AND EastWest=" & me.EastCoast & " AND NewRenew=" & me.newrenewal & "='new'")

but with your table design you would need nested iif statements to work out which column you wanted. I don't have the time to do this right now but just try to write it out yourself to understand the complexity

The other option could be to use the choose function

choose(somevalue between 1 and 4, EastNew, EastRenew, WestNew, WestRenew)

The somevalue would be calculated by comparison. Not tested but something like this e.g.

(abs(me.EastCoast=true)+1)+(abs(me.newrenewal='new')+1)*2

 

[sumox]

YA THE BEST WAY IS to call the Textbox value depending on which u want to
auto-fill the valued

then process it with nested IF
and then finally u wud see the result

I Suggest just write for one/two table field only, if success
then write complete If Else Script

u can AWF search for autofill on base of unbound textbox

 

[Accessunknown]

Thanks so much for your response.

I decided to change the design of my table to what you suggested. So now, it looks like


COSTS
ID CostsType EastWest NewRenewExtMod CostValue
1 500 YES NEW 4100
2 500 YES Renewal 1000
3 500 NO NEW 4400
4 500 NO Renewal 900
5 600 YES NEW 3400

I left the NewRenew as a combobox text because there's actually two more options that i had not listed in my previous post. I only need pricing for NEW and RENEWAL since EXT and MODS do not have pricings.

On the form, if a user were to choose Type 500, leave EastWest unchecked (meaning west), choose NEW, i would like dlookup to help autofill the unbound box $4400 as the cost value.

How would i translate that to dlookup syntax? i've been trying to do it using your example below but i get #Error.

Your table design is not correct - there is a solution but because for the design, it is much more complex.

The correct design would be

Costs
ID autonumber
CostType long
EastWest yesno
NewRenew yesno
CostValue currency

in which case your dlookup would be

dlookup("CostValue","Costs", "CostType=" & me.LType & " AND EastWest=" & me.EastCoast & " AND NewRenew=" & me.newrenewal & "='new'")

but with your table design you would need nested iif statements to work out which column you wanted. I don't have the time to do this right now but just try to write it out yourself to understand the complexity

The other option could be to use the choose function

choose(somevalue between 1 and 4, EastNew, EastRenew, WestNew, WestRenew)

The somevalue would be calculated by comparison. Not tested but something like this e.g.

(abs(me.EastCoast=true)+1)+(abs(me.newrenewal='new')+1)*2

 

[CJ_London]

I'm having to guess at what you are calling things and what datatype they are but basically you need to change the names to what you are actually using. If you want me to do it for you, please answer the following questions

1. In your table is EastWest text or a yes/no field?
2. You previously referred to a control on your form as EastCoast - is it now EastWest?
3. You previously called the combo NewRenewal, now you seem to be calling it NewReNew - which is it?
4. Is your control to select the costtype still called LTYPE?
5. please post the dlookup as you have written it

 

[Accessunknown]

1. In your table is EastWest text or a yes/no field? It is a yes/no checkbox
2. You previously referred to a control on your form as EastCoast - is it now EastWest? It is called EastCoast both on the form and the costs table. (sorry i made a mistake on previous post)
3. You previously called the combo NewRenewal, now you seem to be calling it NewReNew - which is it? It is NewRenewalExtMod both on the form and costs table
4. Is your control to select the costtype still called LTYPE? Yes, it's still LTYPE
5. please post the dlookup as you have written it

=dlookup("CostValue","Costs", "CostsType=" & me.LType & " AND EastWest=" & me.EastCoast & " AND NewRenewalExtMod=" & me.NewRenewalExtMod)

I'm having to guess at what you are calling things and what datatype they are but basically you need to change the names to what you are actually using. If you want me to do it for you, please answer the following questions

1. In your table is EastWest text or a yes/no field?
2. You previously referred to a control on your form as EastCoast - is it now EastWest?
3. You previously called the combo NewRenewal, now you seem to be calling it NewReNew - which is it?
4. Is your control to select the costtype still called LTYPE?
5. please post the dlookup as you have written it

 

[CJ_London]

OK try the following - there are three changes highlighted in red. One is correcting a field name and the other two are because you have a text rather than a numeric value in the criteria

=dlookup("CostValue","Costs", "CostsType=" & me.LType & " AND East[COLOR=red]Coast[/COLOR]=" & me.EastCoast & " AND NewRenewalExtMod=[COLOR=red]'[/COLOR]" & me.NewRenewalExtMod[COLOR=red] & "'"[/COLOR])