DLookUp with date as filter

A

Arvi Laanemets

Hi

I'm trying to lookup a value into unbound text box on unbound form, but
somehow I can't get it to work. My current formula is:
=DLookUp("Type";"Calendar";"CalDay=" & [txtDate])

txtDate is an unbound text box on same form, formatted as "dd\.mm\.yyyy" ,
with default value Date().
Without filter condition the formula works - the value for Type from 1st
record of table Calendar is returned.

Thanks in advance
 
T

Tom Wickerath

Hi Arvi,

I wasn't aware that a semicolon (;) could be used as a valid separator for
the expression, domain and criteria terms of a domain aggregrate function.
Apparently it can, since you stated that the formula works without the
criteria.

Try this instead:

=DLookUp("Type", "Calendar", "CalDay= #" & [txtDate]) & "#")

This wraps the date in # signs, as shown here:

General: DLookup Usage Samples
http://www.mvps.org/access/general/gen0018.htm

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Hi

I'm trying to lookup a value into unbound text box on unbound form, but
somehow I can't get it to work. My current formula is:
=DLookUp("Type";"Calendar";"CalDay=" & [txtDate])

txtDate is an unbound text box on same form, formatted as "dd\.mm\.yyyy" ,
with default value Date().
Without filter condition the formula works - the value for Type from 1st
record of table Calendar is returned.

Thanks in advance
 
A

Arvi Laanemets

Hi Tom

The separator depends on regional settings. (Btw., in VBA I have to use
comma instead, because VBA ignores regional settings).
 
W

Wayne Morgan

Tom is correct, you need to delimit the date with # signs. Also, you may
need to format the date to US format to get it to work properly.

=DLookUp("Type";"Calendar";"CalDay= #" & [txtDate] & "#")
or
=DLookUp("Type";"Calendar";"CalDay= #" & Format([txtDate], "mm/dd/yyyy") &
"#")
 
S

SupportX

Subject: RE: Re: DLookUp with date as filter



-----Original Message-----
Hi Tom

The separator depends on regional settings. (Btw., in VBA I have to use
comma instead, because VBA ignores regional settings).

--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )



Tom Wickerath said:
Hi Arvi,

I wasn't aware that a semicolon (;) could be used as a valid separator for
the expression, domain and criteria terms of a domain aggregrate function.
Apparently it can, since you stated that the formula works without the
criteria.

Try this instead:

=DLookUp("Type", "Calendar", "CalDay= #" & [txtDate]) & "#")

This wraps the date in # signs, as shown here:

General: DLookup Usage Samples
http://www.mvps.org/access/general/gen0018.htm

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

:

Hi

I'm trying to lookup a value into unbound text box on unbound form, but
somehow I can't get it to work. My current formula is:
=DLookUp("Type";"Calendar";"CalDay=" & [txtDate])

txtDate is an unbound text box on same form, formatted as "dd\.mm\.yyyy" ,
with default value Date().
Without filter condition the formula works - the value for Type from 1st
record of table Calendar is returned.

Thanks in advance
Click to expand...
Click to expand...
 
Top