Dlookup in text box 1

[dtutton]

I have a Dlookup expression as follows:

=DLookUp("Price","TyreQuotes","DATNo=" & [Forms]![EquipmentData]![TyreDATNo])

in the Control Source of a text box where "Tyre Quotes" is a query, "Price" and "DATNo" are fields in the Query and "TyreDATNo" is the name of a textbox in the EqupmentData form.

All I get is a flickering "#error" showing in the box. It appears to be in a loop !!

Any help would be appreciated.

David

 

[ghubbell]

Hi David,
Maybe:

=DLookUp("Price","TheTableWhereThePriceFieldIs","DATNo = " & [Forms]![EquipmentData]![TyreDATNo])

Should work going to the table for the price instead. Gord
ghubbell@total.net

 

[jfhewitt]

Try this:
=DLookUp("[Price]","TyreQuotes","[DATNo]=" & TyreDATNo)

Sometimes you have to play around with the syntax...I wish dlookup were a simpler function to use. Good luck.

 

[dtutton]

Gord,

I have to go the query route because the table has multiple prices for the same DATNo (i.e. various quotes) while the query only has one unique value for each DATNo.

If Dlookup could select based on two fields it would be OK

Thanks anyhow

David

 

[dtutton]

Tried =DLookUp("[Price]","TyreQuotes","[DATNo]=" & TyreDATNo) but same flickering error.

Is there any debug method for a field on a form ? or is it real trial and error.

Also any alternate approached of getting the data ?

 

[ghubbell]

David, what's the other criteria that's required if we go to the table? Is it available on your form? I don't think you can get this to go on a query that's grouping stuff with out this problem... Gord
ghubbell@total.net

 

[dtutton]

Gord,

I guess if I were refering to the table I'd want something like:

in table tyrequotes, locate price for DATNo=TyreDATNo and Year>=1999 and Country=USA

where price, year, country and DATNo are all fields in table tyrequotes and TyreDATNo is a value in another table refered to on the form in a label with the same name.

I somehow thought the query route was the soln.

Hope Im not confusing things, I used to program alot in clipper and am a spreadsheet user who is just starting into access - starting being the key word!

Thanks for your help

David

 

[ghubbell]

Hope I'm not confusing things! Is the criteria >=1999 and USA always the same on this form? Gord
ghubbell@total.net

 

[dtutton]

Gord,

Ill keep it simple as Im the sole user - YES

David

 

[ghubbell]

I'm still going here! I think one last question (sorry)...If you looked in the table would this give you a single price or could there be lots of prices based on this criteria? Gord
ghubbell@total.net

 

[dtutton]

Gord,

It would be a single price with:

in table tyrequotes, locate price for DATNo=TyreDATNo and Year>=1999 and Country=USA

David

 

[dtutton]

Gord,

Your comment in the other thread "its in the brackets' was the solution:

=DLookUp("price","TyreQuotes","DATNo='" & [TyreDAT] & "'&quot
refering to the query but the condition value in single quotes.

Thanks for your help

David