Hi
Trying to do a dlookup with a date and I can't get it to work with the
criteria.
Hope you can help
=DLookUp("[Week No]","Wages Week No","[Date] =" & [Forms]![Start
Form]![Text171])
Text171 being the date on the form
Date being the coloum in the date
It's always useful, when posting to a newsgroup to include the program
version and state what the specific problem is. "Can't get it to work"
doesn't help much. Do you get the wrong result? no result? an #Error
message? a #Name message?
You have several difficulties here.
1) You have properly surrounded the field name to be looked up with
brackets ([Week No]) but you haven't done the same with the Table name
(Wages Week No). Whenever an object name includes spaces, you'll save
yourself lot's of grief by enclosing the name within brackets. The
easiest solution is to not use spaces within object names.
2) You evidently have a field named "Date".
Date is a reserved word in Access/VBA and should not be used as an
Object/Field name.
Read Microsoft KnowledgeBase articles:
286335 "ACC2002: Reserved Words in Microsoft Access"
209187 "ACC2000: Reserved Words in Microsoft Access"
109312 "ACC: Reserved Words in Microsoft Access"
321266 "ACC2002: Microsoft Jet 4.0 Reserved Words"
3) When using a Date datatype criteria in Access, surround the
criteria date with the date delimiter symbol #.
4) Try it this way (after changing the date field name):
=DLookUp("[Week No]","[Wages Week No]","[DateField] = #" &
[Forms]![Start Form]![Text171] & "#")
If the above DLookUp is being done on the form [Start Form] you can
use the Me keyword to abbreviate the code:
=DLookUp("[Week No]","[Wages Week No]","[DateField] = #" &
Me![Text171] & "#")
