Dlookup with date

[AndyCompanyZ]

I have posted this on another access forum but thought i'd try here too.
I am writing some code for a combobox on click event. I need to check the date that has been selected in the combobox against a table (tblEventDelegates) that holds a list of delegates who have been scheduled onto events with the dates of that event. I need to see if the event that had been selected in the combobox (cmboEvents) is more or less than 14 days from any event that the dlegate has already been scheduled on. I have the following so far:


If DLookup("[EventStartDate]", "tblEventDelegate", "DelegateID = " & Me.DelegateID And "EventStartDate = " & DateAdd("d", -14, [Me.txtEventStart])) Then

This is to see if the date is less than 14 days before the event stored in the tblEventDelegate. But this says it "can't find the field `¦1` referred in your expression". I have no idea what that means or which field it refers to.

 

[pbaldy]

Date values need a delimiter:

http://www.mvps.org/access/general/gen0018.htm

Also, you didn't restart the quotes after the DelegateID, and you don't want the quotes right after And.

 

[AndyCompanyZ]

Thanks Is this right for the delimiters:
If DLookup("[EventStartDate]", "tblEventDelegate", "DelegateID = " & Me.DelegateID " And EventStartDate = " & #DateAdd("d", -14, [Me.txtEventStart])#) Then

 

[pbaldy]

No; notice in the link that they are concatenated as part of the string, not part of the value. You also missed the & to restart the string. You also bracketed the entire form reference, which would make Access think it was a field name. Try

DLookup("[EventStartDate]", "tblEventDelegate", "DelegateID = " & Me.DelegateID & " And EventStartDate = #" & DateAdd("d", -14, Me.txtEventStart) & "#")

 

[AndyCompanyZ]

Thanks for that It does compile now but doesn't do what I thought it would. I thought it would work out the days up to 14 days before the date selected in the form but I think it works out just 14 days and not upto. Does anyone know how to do what is needed. I imagine I will need 2 bits of code one up to 14 days and one 14 days after the event and have copied the same code with 14 as well as -14. Do I need < in some form (I have tried that but it gives me an error "Expected expression". Maybe it isn't possible this way at all. The code I have so far is:

If DLookup("[EventStartDate]", "tblEventDelegate", "DelegateID = " & Me.DelegateID & " And EventStartDate = #" & DateAdd("d", -14, Me.cmboEvents.Column(1)) & "#") Then

Note I have changed the form field to a combobox.

 

[pbaldy]

If you want to find anything less that 14 days before, change = to <.