Problem with DLookup cutting off the last character of the criteria

[Thirith]

On my main form, I've got a date field that should update another field based on a range of dates. The field is called txtDateSubmissionPhase1 and it should look up information in the table tblStageLookup, which has these relevant columns: StageDescription and StageEndDate.

This is the DLookup code I'm using:

varTest = DLookup("[StageDescription]", "tblStageLookup", "[StageEndDate] <= # " & [txtDateSubmissionPhase1] & "#")

However, weirdly I keep getting an error - and in this error it looks like the final character (in this case the second #) is omitted from the criteria.

Can anyone here tell me why this is happening and how I can avoid it? It seems a pretty strange thing for the code to do, but I have to admit that I'm always a bit at a loss with respect to code that needs to be in string format.

 

[Norie]

I can't see why that would happen.

Does this make a difference?

strCrit =  "[StageEndDate] <= # " & [txtDateSubmissionPhase1] & "#"

Debug.Print strCrit

varTest = DLookup("[StageDescription]", "tblStageLookup",strCrit)

P.S. You can see the value of strCrit in the Immediate Window (CTRL+G).

 

[Thirith]

How utterly weird... I tried the code you posted (taking out the space after the first #), with the following results: 1) The value of strCrit is [StageEndDate] <= #01.01.2019#, but I then get the following error: "Syntax error in date in query expression '[StageEndDate] <= #01.01.2019". It's like DLookup is waiting at the end of the expression with a big knife in order to cut off that final character...

 

[Norie]

Perhaps the problem is with the way your date is formatted?

 

[Thirith]

It's possible, but I wouldn't know where the problem lies. All the relevant dates are formatted in the same format, which is the default here (dd.mm.yyyy).

 

[Thirith]

Okay, I'm not quite sure how I got down the garden path of strings, hashes and the like - but I now went back to what I thought I'd tried to begin with, and it works:

Stage = DLookup("[StageDescription", "tblStageLookup", "[StageEndDate] >= [Forms]![frmSPIRITapplications]![txtDateSubmissionPhase1]")

And it works. I'm not entirely certain what I did wrong the first time, but it was probably something minor and I mistakenly thought it was some big underlying issue, which sent me on a wild goose chase. In any case, it works now and does exactly what it needs to do. Yay! Nonetheless, thanks for your help, which kept me looking for possible solutions.

 

[welshgasman]

Except you have gone from <= to >= ?

 

[Thirith]

You're right, that might be it. Though I'm not sure it's the only thing, since that in itself shouldn't result in a syntax error, should it?

 

[welshgasman]

No, I am not saying that.
I am saying you have perhaps inadvertently changed your criteria trying to get to the bottom of this.?
I *thought* it might be the space after the initial #, as that does not work, but you say you removed that. I tried something similar without the space and it worked fine.?

 

[Thirith]

The >=/<= issue most likely was down to me making mistakes when retyping the code for my post. (Since it wasn't line after line of VBA code, I didn't just copy and paste.)

I'm still weirded out by the underlying problem; it's puzzling to me why a string would display correctly using Debug.Print, but that same string used in conjunction with DLookup giving me an error that indicates that the last character was omitted? That's plain weird.