Compare two date fields

[AndyCompanyZ]

Hi all

I have a form allows a user to an select an event and then schedule trainees onto that event. I need a way of checking if the trainee hasn't already been scheduled onto an event on the same date. I want to do this in VBA so that I can bring a messagebox up alerting the user to this fact. I can't seem to find anything about comparing 2 specific dates. I'm wodering if a query would work and then a DoCmd to run it but I already have a number of functions already running from the same routine so it could get messy. can anyone point me in the direction so I can try and work it out myself.

Thanks

 

[Mj-Egerton]

Hi all

I have a form allows a user to an select an event and then schedule trainees onto that event. I need a way of checking if the trainee hasn't already been scheduled onto an event on the same date. I want to do this in VBA so that I can bring a messagebox up alerting the user to this fact. I can't seem to find anything about comparing 2 specific dates. I'm wodering if a query would work and then a DoCmd to run it but I already have a number of functions already running from the same routine so it could get messy. can anyone point me in the direction so I can try and work it out myself.

Thanks

i could be wrong, and have recommended this somewhere else on the forum, but have you tried a recordset search?

 

[AndyCompanyZ]

I was thinking a recordset search but I already have 2 rsets in my routine and i thought it mught slow it all down but I'll try that.

 

[boblarson]

A Dcount should do it.

So, let's say you have a date in a text box and you want to find out if they are scheduled for that date, you could do something like:

If DCount("*", "TableNameHere", "[DateFieldInTable] = #" & Format(Me.TextBoxNameHere, "mm\/dd\/yyyy") & "# AND [PersonID]= " & Me.PersonID) > 0 Then
   MsgBox "Already Scheduled"
End If

 

[Mj-Egerton]

Bobs so correct he's solved my problem as well, and i didnt even have to post it! Haha

Cheers bob

 

[AndyCompanyZ]

Thanks again Bob but I'm just a bit confused with the logic before I run it. I have modified it for my own purposes and have:

If DCount("*", "Event", "[EventTimeStartDay] = #" & Format(Me.txtDelegateID, "mm\/dd\/yyyy") & "#") > 0 Then
MsgBox "Already Scheduled"
End If

But I know looking at it it won't work because it is looking for an date (EventTimeStartDay) but has nothing to compare it to. I just tried and obviously it didn't but neither did it give any errors. I'm not sure of the logic behind. I want to check the events that the delegate is scheduled on already and check if the new event doesn't clash with any dates already scheduled.

 

[AndyCompanyZ]

OOps didn't scroll on the code above now I see the problem I had lol

 

[AndyCompanyZ]

Hmm didn't work but again no errors I know have:

If DCount("*", "Event", "[EventTimeStartDay] = #" & Format(Me.cmboEvent, "mm\/dd\/yyyy") & "# AND [DelegateID]= " & Me.DelegateID) > 0 Then
MsgBox "Already Scheduled"
End If

Does it make a difference if the date format is different or is that MS standard and what i have is just for display.

 

[AndyCompanyZ]

Aah I see what is wrong I'm pulling an ID for the event from a combobox but that doesn't have a date field though I could alter the combobox to show a date instead of an ID.

 

[AndyCompanyZ]

Hmm I can't select the EventStartTimeDay as a field for my combobox as it asks for a parameter before it runs...next problem to solve lol

 

[boblarson]

Maybe we'd get there faster if you can post a copy of your database (with bogus data of course) and post what to look at.

 

[AndyCompanyZ]

Thanks for your help but it's late in the day now and holidays beckon. If I haven't worked it out over the long weekend I'll post the db up.

 

[Mj-Egerton]

Hmm I can't select the EventStartTimeDay as a field for my combobox as it asks for a parameter before it runs...next problem to solve lol

i imagine that youve either got the SQL syntax wrong or the Field name wrong
(assuming there's SQL involved)

 

[AndyCompanyZ]

After a long time thinking about this I still am no nearer. So far I have:

If DCount("*", "Event", "[EventTimeStartDay] = #" & Format(Me.cmboEvent, "mm\/dd\/yyyy") & "# AND [DelegateID]= " & Me.DelegateID) > 0 Then
  MsgBox "Already Scheduled for an Event on same day"
End If

This doesn't work and when I debug it it just steps through it.
Is this the right way to do this or is there another way as I will have to do a similar procedure elsewhere on the db.

 

[spikepl]

Dim myCondition as String
myCondition="[EventTimeStartDay] = #" & Format(Me.cmboEvent, "mm\/dd\/yyyy") & "# AND [DelegateID]= " & Me.DelegateID
debug.print myCondition

 

[AndyCompanyZ]

I have changed tack on this now by adding a txtbox to the form which has a dlookup as a control source which picks up the date of the event and displays it on the form. This way I can compare it easier I think. I now have the line of code as;

If DCount("*", "Event", "[EventTimeStartDay] = #" & Format(Me.txtEventStartDate, "mm\/dd\/yyyy") & "# AND [DelegateID]= " & Me.DelegateID) > 0 Then
MsgBox "etc
End If

This sort of works in the sense that it displays the msg but not for the right reason ie it sometimes displays it and sometimes not but never because the dates are the same. I am now thinking of another way but need to know if I can run 2 DLookups and compare them. For example I would lookup the event date and compare that to a lookup based on the eventdate of an event that the delegate is already scheduled on.

 

[spikepl]

What hinders you? Just remember a DLookUp can return a Null

 

[AndyCompanyZ]

I'm on it now but not sure of my syntax so far I have:

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

But it says there is a compile error on the "Event" after DLookup "Expected list separator or )".So I gather from this the syntax is wrong but not sure what the correct syntax is I assume its how I've bracketed the different lookups. I'm trying different brackets but they all throw up same error and I'm not sure where the" > 0 should go

 

[boblarson]

You had a few misplaced parens and quotes.

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

 

[AndyCompanyZ]

Still gives an error that there is a missing , or ) at the EventID and DelegateID query expression but when I add one where I think it is wrong after the & Me.cmboEvent & " part it says there is an extra ) inj the expression.