Compare two date fields

[spikepl]

so why don't you debug.print the entire condition-part and check it?

 

[AndyCompanyZ]

Yes I have done that and it just says syntax error

 

[boblarson]

Yes I have done that and it just says syntax error

I don't know if you saw my code after I had modified it. I originally posted it with an extra paren. So the code should be:

If DCount("*", "Event", "[EventTimeStartDay] = #" & Format(Me.txtEventStartDate, "mm\/dd\/yyyy") & "#") And DLookup("Event", "EventDelegate", "EventID= " & Me.cmboEvent & " AND [DelegateID]= " & Me.DelegateID) > 0 Then

I put that into my Access and it does not generate a syntax error.

 

[AndyCompanyZ]

Thanks yes that did it although it6 gave the wrong result so I need to look at it more tomorrow but at least I now have the syntax right so it'll be just a matter of changing the fields I need.

 

[AndyCompanyZ]

Looking at this now I realsie i have my logic wrong as it returns an error "The expression you entered as a query parameter produced this error:
'Event'"
My code is:

 If DCount("*", "Event", "[EventTimeStartDay] = #" & Format(Me.txtEventStartDate, "mm\/dd\/yyyy") & "#") And DLookup("EventStartDate", "EventDelegate", "EventID= " & Me.cmboEvent & " AND [DelegateID]= " & Me.DelegateID) > 0 Then

What I am trying to do is lookup the Event table and find all records that have an eventstartdate the same as the txtEventStartdate on the form and then check the EventDelegate table to see if the DelegateID that has been selected on the form has been scheduled onto an event with the same EventStartdate as the Event selected in the combobox cmboEvent.
I see its wrong but I can't work out why.

 

[AndyCompanyZ]

I realise where I was going wrong now because I had a wrong field in the EventDelegate table. However now it produces a box to enter a parameter value for EventStartDate so I may have made it worse now. I think I'll think about this more and try and work another way of doing it.

 

[AndyCompanyZ]

Ahh if i enter the date in the parameter box it works so my SQL statement must be wrong to be causing this but anyway i just tried to enter the same date as 1 I have scheduled and it didn't work so looks like another day trying to work this code out.

 

[boblarson]

However now it produces a box to enter a parameter value for EventStartDate

That would tend to indicate that there is a reference to it that is misspelled, or it is being used as the criteria but it doesn't exist in the recordset where the search is taking place.

 

[AndyCompanyZ]

Yes thanks Bob I had found the problem field and I had wrong name as you rightly point out

 

[boblarson]

Yes thanks Bob I had found the problem field and I had wrong name as you rightly point out

Glad to hear you got it sorted.