Full Version: Dlookup Syntax For String Value
UtterAccess Forums > Microsoft® Access > Access Forms
spooker
Hi,
I have an unbound textbox on a form that I want to default to the standard VAT rate.
The VAT rates are stored in a table named "VAT Rates "and the VAT code for the standard rate is "1" (VAT codes are text).
I'm trying to use the Function DLookup to get the value, but keep getting #Error
In the default value for the textbox I have:
=DLookUp("[Value]","VAT Rates","[VAT Code] = '1'")
This web page says this should work but it doesn't.
I've also tried (amongs others)
=DLookUp("[Value]","VAT Rates","[VAT Code] = & '1'")
But with no luck
Any advice appreciated...
Cheers
Terry
Doug Steele
Syntactically, =DLookUp("[Value]","VAT Rates","[VAT Code] = '1'") is correct. However, I'm wondering whether the problem may be due to your choice of field name. Value is a reserved word: see whether renaming it makes a difference.
For a comprehensive list of names to avoid (as well as a link to a free utility to check your application for compliance), see what Allen Browne has at Problem names and reserved words in Access
arnelgp
Your code is correct. Try inspecting the Table name and the Field name if they are correct.
RAZMaddaz
When all else fails can you try the following:
DLookUp("[Value]","VAT Rates","[VAT Code]=" & 1)
BTW, What version of Access are you using? I have Access 2007 and when I tried using the word "Value" as the Field name, I got the message that Doug mentioned that it is a reserved word, etc.
katy7
Try this :
=DLookUp("[Value]","VAT Rates","[VAT Code]=1")
spooker
Thanks for all your replies...
I'm using Access 2003.
I've tried renaming the field [Value], (I never had any messages about it being a reserved word), but Im still getting an error.
I've also tried the other suggestions with no luck.
I've attached a simple mdb file with just one table and one form. The form has a few unbound text boxes that I have used DLookup with various arguments - but all are giving an error. pullhair.gi
 </div>
</div><div class='postwrapper'>
 <div class='posttopbar'>
  <div class='postname'>Doug Steele</div>
  <div class='postdate'>Jul 13 2011, 07:35 AM</div>
 </div>
 <div class='postcontent'>
  Sorry, I don't do attachments.<br />While it shouldn't matter, see whether putting square brackets around the table name (since it has embedded spaces) makes a difference:<br />=DLookUp("[Field1]","[VAT Rates]","[VAT Code] = '1'&q
 </div>
</div><div class='postwrapper'>
 <div class='posttopbar'>
  <div class='postname'>katy7</div>
  <div class='postdate'>Jul 13 2011, 07:41 AM</div>
 </div>
 <div class='postcontent'>
  I saw the attachment.<br />What Doug said worked :<br />=DLookUp("[VATRate]";"VATRates";"[VATCode] = '1'")
 </div>
</div><div class='postwrapper'>
 <div class='posttopbar'>
  <div class='postname'>katy7</div>
  <div class='postdate'>Jul 13 2011, 07:43 AM</div>
 </div>
 <div class='postcontent'>
  You had two mistakes :<br />on the first field : the "vatrates" has a space between two words and on second field : dlookup search for a field named 'value' not VatRate
 </div>
</div><div class='postwrapper'>
 <div class='posttopbar'>
  <div class='postname'>RAZMaddaz</div>
  <div class='postdate'>Jul 13 2011, 07:47 AM</div>
 </div>
 <div class='postcontent'>
  You also need to place the Dlookup in the Control Source area as oppose to the Default Value area.
 </div>
</div><div class='postwrapper'>
 <div class='posttopbar'>
  <div class='postname'>spooker</div>
  <div class='postdate'>Jul 13 2011, 08:59 AM</div>
 </div>
 <div class='postcontent'>
  One more question....<br />If I renamed the field [Value] to avoid using the reserved word, I'd obviously need to change everything in the application (forms & querys etc...) that refers to this to the new field name.<br />Is there a "Search & Replace" type utility to help with this or is it a case of burning the midnight oil and going through the application and hope I find them all?<br />Cheers
 </div>
</div>
  </div>
  <div class='smalltext'>This is a click here.