problem with dlookup

[Guest]

hi

im trying to look up values in a table and if it returns null then it displays a msgbox "No Results
the problem is it constantly returns null and i cant figure out why? here it is

nodata = Dlookup("date", "eventlogs", "[Date]= #" & Forms!Eventlogsmain!Date & "#"
ive checked all of the names on my tables/forms the are correct, ive even trie
nodata = Dlookup("date", "eventlogs", "[Date]= #26/01/2004#") returns null when it should be 26/01/200
i added a watch and it said out of contex
have i got some thing wrong

if some one could give me a kick in the right direction i would be extreemly greatful

thank

joh

 

[Karen]

John,

It may be a problem because Date is a reserved keyword in Access. Rename
the field and try it then. This link given to me recently regarding Access
reserved keywords.

http://support.microsoft.com/support/kb/articles/q286/3/35.asp

Karen

hi,

im trying to look up values in a table and if it returns null then it displays a msgbox "No Results"
the problem is it constantly returns null and i cant figure out why? here it is;


nodata = Dlookup("date", "eventlogs", "[Date]= #" &

Forms!Eventlogsmain!Date & "#")

ive checked all of the names on my tables/forms the are correct, ive even tried
nodata = Dlookup("date", "eventlogs", "[Date]= #26/01/2004#") returns null when it should be 26/01/2004
i added a watch and it said out of context
have i got some thing wrong?

if some one could give me a kick in the right direction i would be extreemly greatfull

thanks

john

 

[Ken Snell]

You must use the US date format in your "WHERE" string in the DLookup
function:

nodata = DLookup("date", "eventlogs", "[Date]= #" &
Format(Forms!Eventlogsmain!Date, "mm/dd/yyyy") & "#")

I also echo Karen's good suggestion about not using Date as the name of a
field or a control.

--
Ken Snell
<MS ACCESS MVP>

hi,

im trying to look up values in a table and if it returns null then it displays a msgbox "No Results"
the problem is it constantly returns null and i cant figure out why? here it is;


nodata = Dlookup("date", "eventlogs", "[Date]= #" &

Forms!Eventlogsmain!Date & "#")

ive checked all of the names on my tables/forms the are correct, ive even tried
nodata = Dlookup("date", "eventlogs", "[Date]= #26/01/2004#") returns null when it should be 26/01/2004
i added a watch and it said out of context
have i got some thing wrong?

if some one could give me a kick in the right direction i would be extreemly greatfull

thanks

john

 

[Steve Schapel]

John,

I would definitely recommend putting []s around 'date'.

Apart from this, all of these expressions *should* work...
Dlookup("[Date]", "eventlogs", "[Date]= #26/01/2004#")
Dlookup("[Date]", "eventlogs", "[Date]= #" & Forms!Eventlogsmain!Date & "#")
Dlookup("[Date]", "eventlogs", "[Date]= Forms!Eventlogsmain!Date")

As long as:
nodata is declared as a Date variable... is it?
*but* in any case, I would not use this as a variable name!!
.... and as long as the entries in the Date field (as mentioned by other
responders in this thread - change this field name!) does not include
any time data. For example, if the data has been entered into this
field using the Now() function, you will not get a match with
26/01/2004. I suspect that this is in fact the problem.

Now, having said all that, I would personally not use this method for
this purpose. If you want to use a domain aggregate function to test for
the existence of records with a certain date in the table, I would
recommend DCount rather than DLookup...

NoDataText = (DCount("*", "eventlogs", "[Date]= #" &
Forms!Eventlogsmain!Date & "#")=0)

 

[Guest]

thanks for your reply's a bit more info for you, ive been designing my db in access2002 (with format set to 2000
it works on my 2002 access but not in 2000, is it just cause access 2002 is a bit more tolerant

ill try thease suggestions and get bac

thanks everyon

joh

 

[Guest]

i forgot to mention that 'nodata' is declared as variable and the data in the [date] field is inputed with and input mask of dd/mm/yyyy and the format is set to that also (same with the form)

ill try using the dcount function.

 

[Ken Snell]

The mask will not store the date data in the format of dd/mm/yyyy...ACCESS
stores dates as Double precision numbers: the integer portion are the
number for full days since 12 midnight on 30 December 1899, and the decimal
portion is the fraction of a 24-hour day represented by the time.

What you see in a textbox does not affect how ACCESS stores and uses the
date info. Therefore, my recommendation for formatting the date value.

There are differences between A2002 and A2000 with respect to some functions
that A2002 can run in queries that A2000 may not, and in how forms can be
handled, but there is nothing in the expression that you've posted that I
would expect should cause any different behavior in the two software
versions.


--
Ken Snell
<MS ACCESS MVP>

i forgot to mention that 'nodata' is declared as variable and the data in

the [date] field is inputed with and input mask of dd/mm/yyyy and the format
is set to that also (same with the form)