DLookup with DateSerial problem

[BLFOSTER]

I am trying to use DLookup as the control source of a text box on a report. All I get is error. Can anyone help?



=DLookUp("[AllottedTickets]","Allottments","[DateIn] between DateSerial(Year(Forms!MyForm![DispenseDate])),Month(Forms!MyForm![DispenseDate]-1,1) And DateSerial(Year(Forms!MyForm![DispenseDate])),Month(Forms!MyForm![DispenseDate]),0) and [Site] = Me.[Referring Branch]) ")

[jwhite]

What is the error you are getting?

Without that, maybe this will work:

= DLookup("[AllottedTickets]", "Allottments", "([DateIn] Between #" & DateSerial(Year(Forms!MyForm![DispenseDate]), Month(Forms!MyForm![DispenseDate]) - 1, 1) & "# And #" & DateSerial(Year(Forms!MyForm![DispenseDate]), Month(Forms!MyForm![DispenseDate]), 0) & "# and ([Site] = '" & Me.[Referring Branch] & "')")

If not, please provide more information.

[BLFOSTER]

It is prompting for "Me" when I open the report and the text box says #Type!

Thank you so much for helping me!

[jwhite]

Does that error refer to your code or mine? If yours, try the replacement code I gave you.

[BLFOSTER]

Oh, I am sorry. Yours.

[jwhite]

Can you verify [Referring Branch] is a Control Name on your form? If not, then change it to the actual Control Name that is mapped to the field [Referring Branch].

[jwhite]

With something as long as this, I would be inclined to add it to the OnLoad or OnCurrent Event (whichever is appropriate in your case, or a different event) so it is clearly visible in code - easier to debug later if needed.