Full Version: compare two date + time fields
UtterAccess Forums > MicrosoftŪ Access > Access Date + Time
jabez
On a data input form I have:
When did the illness start?
txtA = date1 (the nurse clicks on a popup calendar)
txtB = time1 (the nurse clicks on a 24hr clock)
When was the "last symptoms"?
txtC = date2 (the nurse clicks on a popup calendar)
txtD = time2 (the nurse clicks on a 24hr clock)
Oneed to validate that date/time2 is AFTER date/time1.
I suppose I must first put the values in txtA and txtB together (=X), then put the values in txtC and txtD together (=Y) and then compare X and Y. What would the syntax look like?
I'd appreciate someone pointing me the way...
Thanks!
J
jabez
Well, I guess I have figured it out myself. This works, but I'm not sure this is the most elegant way, so if someone can show me a better or shorter way, I'd appreciate that!
*********************************
Dim X As Date
X = Me.OnsetOfIllnessDate
Dim Y As Date
Y = Me.OnsetOfIllnessTime
Dim A As Date
A = DateSerial(Year(X), Month(X), Day(X))
Dim B As Date
B = TimeSerial(Hour(Y), Minute(Y), Second(Y))

Dim C As Date
C = Me.FirstPresentationLastSymptomsDate
Dim D As Date
D = Me.FirstPresentationLastSymptomsTime
Dim E As Date
E = DateSerial(Year©, Month©, Day©)
Dim F As Date
F = TimeSerial(Hour(D), Minute(D), Second(D))

Dim DateIllnessStart As Date
DateIllnessStart = A + B
Dim DateLastSymptoms As Date
DateLastSymptoms = E + F

If DateLastSymptoms < DateIllnessStart Then
MsgBox "Please review the dates and times that you have entered" & vbCrLf & vbCrLf & "The patient's LAST SYMPTOMS occur BEFORE the ONSET of the illness!!"
End If
**********************
schroep
If you are indeed adding a true date and a true time together (date/time datatypes), you can simplify that quite a bit:
CODE
If (Me.FirstPresentationLastSymptomsDate + Me.FirstPresentationLastSymptomsTime) < (Me.OnsetOfIllnessDate + Me.OnsetOfIllnessTime) Then
  MsgBox "Please review the dates and times that you have entered" & vbCrLf & vbCrLf & "The patient's LAST SYMPTOMS occur BEFORE the ONSET of the illness!!"
End If

Dates are stored in Access as whole numbers, and times as fractions. So, they can easily be added directly together.
jabez
Hi Peter
That was my first attempt, but it gave me many false positives. I think it is because of the way my table is set up. I did not want to mess around with the table at this stage, because it would probably impact on other processes.
So, I decided to try to extract the year/month/day/hour/minute/second to have exact control over the results ... and surprisingly it works!
Cheers
J
schroep
I won't argue with that, since I don't know how your table is set up. Sounds like you may be storing more than just a date or a time in the respective fields (which would cause issues with the simple math solution).
You could try this:
CODE
If (DateValue(Me.FirstPresentationLastSymptomsDate) + TimeValue(Me.FirstPresentationLastSymptomsTime)) < (DateValue(Me.OnsetOfIllnessDate) + TimeValue(Me.OnsetOfIllnessTime)) Then
  MsgBox "Please review the dates and times that you have entered" & vbCrLf & vbCrLf & "The patient's LAST SYMPTOMS occur BEFORE the ONSET of the illness!!"
End If

...which should add only the date or time values of the respective fields.
jabez
Well, this looks like a solution. I will try it.
Thanks!!
J
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.