DLookup returns NULL incorrectly.

[mjhotstone]

Hi there,

In Access 2003, I'm having a problem where DLookup is returning a NULL
value instead of the correct value for a field in a query, while an SQL
statement retrieves the correct value. I wrote this quick SQL
statement to demonstrate the problem:

SELECT DateContractAwardDue,
Nz(DLookUp("DateContractAwardDue","qryCSPDetails","TranNumber='065018'"),"null")
AS DL1,
Nz(DLookUp("Created","qryCSPDetails","TranNumber='065018'"),"null") AS
DL2
FROM qryCSPDetails
WHERE qryCSPDetails.TranNumber='065018';


And here's the result (transposed for ease-of-reading):

DateContractAwardDue 12/12/2005
DL1 null
DL2 11/10/2005 10:33:18

As you can see, the SQL SELECT statement correctly returns the date in
the field, while the first DLookup returns null. The second DLookup,
though, correctly returns the date in a different field in the same
query.

Is there a problem with the DLookup function in 2003, or am I missing
something rather obvious?

Thanks!
-matt

 

[Allen Browne]

There could be several things going on here.

1. What result do you get if you open the Immediate Window (Ctrl+G) and
enter this line:
? DLookUp("DateContractAwardDue","qryCSPDetails","TranNumber='065018'")

2. If that returns Null unexpectedly, open the relevant table in design view
and look at the Data Type for the TranNumber field. If it is Number (not
Text), lose the extra quotes and leading zero, i.e. try:
? DLookUp("DateContractAwardDue","qryCSPDetails","TranNumber = 65018")

I'm guessing that there is a problem with the data types here. DLookup()
does wrongly return Null if the result is actually a zero-length-string, but
I don't believe that's your issue here.

(Note that your Nz() statement has the effect of turning what is presumably
meant to be a date field into a string, because you are using the
4-character string as the alternate value.)

 

[mjhotstone]

Hi there, Allen, thanks for your help.

Well, the DLookup returns null no matter where it's executed (ie. in
the Immediate window, in VBA code, or in an SQL statement). TranNumber
is defined as VARCHAR(6) (the table is on a SQL Server database, and
accessed using ODBC).

The data type of the field is Date/Time - as is the data type of the
field that returns correctly in my above example. I've checked that
the TranNumber is unique within the query that DLookup is operating on.
I'm at a comlete loss as to why a SELECT statement returns the field's
data but DLookup returns null. Opening the query, I can see that it
does actually contain a date value.

I'm imagining that this:
DLookup("DateContractAwardDue", "qryCSPDetails", "TranNumber='065018'")

would be equivalent to this:
SELECT TOP 1 DateContractAwardDue
FROM qryCSPDetails
WHERE TranNumber='065018'

and would therefore return the same value (assuming only a single row
matches the criterion) - that's correct isn't it?

-matt

 

[Allen Browne]

Yes:
DLookup("DateContractAwardDue", "qryCSPDetails", "TranNumber='065018'")
should be equivalent to:
SELECT TOP 1 DateContractAwardDue
FROM qryCSPDetails
WHERE TranNumber='065018'

If' it's not working, we are trying to trace what's wrong. The data types
match, so is there any possiblilty of an ambiguity? Is there more than one
field in the query named TranNumber (e.g. a primary key of one table, and a
foreign key of another)?

Are the Name AutoCorrect boxes unchecked under:
Tools | Options | General
That misfeature can confuse Access about what name refers to what object.

Any chance of a bad index? Are you able to reindex your tables (using its
own native engine)?

If none of that works, post the SQL statement behind qryCSPDetails. I'm
interested in the joins and the data types of the fields in the joins.