DLookUp finding wrong records - record doesn't even exist

[malibu65k]

Hi All,

I've been at this for a few days using several DLookUps. DLookUp is finding a record ID for a record not even in the table.

ReqNo = 23a21 and SPID = 11
ReqNo 23a21 doesn't even exist in the table

I cleared RQID beforehand to be sure nothing is in it
What am I doing wrong?

Code:

RQID = ""
RQID = DLookup("REQ_ID", "tSPEC_REQUIREMENT", "[REQ_NO] = '" & ReqNo & "' AND [SPEC_ID] = " & SPID)

 

[kjv1611]

Have you printed that statement to make sure that the INPUTS are correct? Sorry so late a reply - I just now saw this question. I guess no one else has seen it or didn't have time perhaps.

Build the DLookup string behind a Debug.Print command to see what it gives.

Also, from much of my experience, I'd suggest you'll be better off using a DAO Recordset, and using a SQL String you pass to the recordset instead of DLOOKUP for performance if you have more than a few hundred records in the underlying table.

Is ReqNo a variable, or is it pointing to a field on a form, or what?

When I run into things like this, I oftentimes use Debug.Print to show me what the code is seeing. That way I can find the issue. Manytimes, I have to say, "Oh yeah - THAT's what the problem is" - something I didn't consider, or else forgot about a given scenario.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[Andrzejek]

What do you get when you run this:
[tt]
Select REQ_ID
From tSPEC_REQUIREMENT
Where REQ_NO = '23a21' AND SPEC_ID = 11[/tt]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.