Dlookup with Between Dates 1

[MSealy]

Hi,

I have an unbound control on my main form where I type a date. This should produce a search on table data between 2 dates which would then populate another control on the same form with a file reference (a box file number).

My table is "tblPYTRUN" and has fields:
"FILE REF:" (datatype Number) - this is a file number
"INCLUDES:" (datatype Short Date) - a starting date for the box file
"to" (datatype Short Date) - the final date in the box file

My form is "frmMAIN" and has these controls:

"txtPytRunSearch" (Unbound) - I would type a date here.
"txtPytRunBoxNo" with the following control source:

=DLookUp("[FILE REF:]","tblPYTRUN",[Forms]![frmMAIN]![txtPytRunSearch] & " Between [tblPYTRUN]![INCLUDES:] And [to]")

I had a similar problem before with another table, and it worked thanks to this forum. However, only numbers were involved.

This time though, when I type a date into "txtPytRunSearch" (tried various formats), nothing is appearing in the "txtPytRunBoxNo" box. Why I'm not sure. I thought of changing the format of "txtPytRunSearch" to Short Date, but this didn't help.

Is the dlookup code wrong ? - does it need to be re-worded when dealing with 'Between' and 'Dates'

Regards.

 

[Golom]

Text fields that you want interpreted as dates should be enclosed in # ... # signs.

..., "#" & [Forms]![frmMAIN]![txtPytRunSearch] & "# Between [tblPYTRUN]![INCLUDES:] And [to]")

 

[PWise]

=DLookUp("[FILE REF:]","tblPYTRUN","[INCLUDES:] Between #" &  [Forms]![frmMAIN]![txtPytRunSearch] & "# And #" & [Forms]![frmMAIN]![to] & "#")

 

[MSealy]

Thanks but for some reason, I'm just getting #Error.

Regards.

 

[lespaul]

try something like this....I'm sure I've got the """ wrong for the date delimiters, but you want to find where what you've entered (txtPytRunSearch) is BETWEEN the fields in the table (INCLUDES and TO):

=DLookUp("[FILE REF:]","tblPYTRUN", "#" & [Forms]![frmMAIN]![txtPytRunSearch]  & "#" Between INCLUDES AND [to]")

HTH
Leslie

 

[MSealy]

I'm afraid this hasn't worked either, but thanks for the help.

 

[lespaul]

I know it didn't work, the """" are wrong....you'll need to tweak them to get them to work correctly, but the rest of the statement is in the correct order....maybe:

=DLookUp("[FILE REF:]","tblPYTRUN", "#" & [Forms]![frmMAIN]![txtPytRunSearch]  & "# Between INCLUDES AND [to]")

Leslie

Have you met Hardy Heron?

 

[MSealy]

Well that's odd - it still shows #Error, but the text is flickering slightly - suggesting it's caught in a loop or still trying to calculate.

 

[MSealy]

Aha - the INCLUDES field was [INCLUDES:]

=DLookUp("[FILE REF:]","tblPYTRUN","#" & Forms!frmMAIN!txtPytRunSearch & "# Between [INCLUDES:] AND [to]")

now works.

Thanks.