Message To User - Date/Time Incorrect

[nubianqueen]

I have two fields (CallInDateTime)&(CallFinishDateTime)with the format as mm/dd/yyyy h:nnAM/PM. Big problem with users forgetting to put in the time portion and of course 12AM defaults in. To prevent all the time spent on cleaning up my tables I put code in the BeforeUpdate event on my form so the record would not be saved until time is corrected by user. I need some help, code not working, doesn't even create error message.

Private Sub Form_BeforeUpdate(Cancel As Integer)

If CallInDateTime = "#mm/dd/yyyy# 12:00AM" Then
MsgBox "You must enter a time that is before midnight.", vbExclamation
CallInDateTime.SetFocus 'Go back to DateTimefield.
Cancel = True 'Cancel saving the record.
End If

Thanks,
nubianqueen

 

[FoxProProgrammer]

Hi nubianqueen,

Th If statment checks to see if the time entered is equal to midnight, but the MsgBox suggests that you want to check for a time before midnight. If you want to check that a time has even been entered, do this:

If Hour(CallInDateTime) = 0 Then
MsgBox "You must enter a time.", vbExclamation
CallInDateTime.SetFocus 'Go back to DateTimefield.
Cancel = True 'Cancel saving the record.
End If

If you want to check for a range of time, you could do this:

If (Hour(CallInDateTime) > 17) And (Hour(CallInDateTime) < 24) Then
MsgBox &quot;You must enter a time before 5:00 PM.&quot;, vbExclamation
CallInDateTime.SetFocus 'Go back to DateTimefield.
Cancel = True 'Cancel saving the record.
End If

dz
dzaccess@yahoo.com

 

[FoxProProgrammer]

Sorry, there's a typo in my last post:

If you want to check for a range of time, you could do this:

If (Hour(CallInDateTime) >= 17) And (Hour(CallInDateTime) <= 24) Then
MsgBox &quot;You can not enter a time between 5:00PM and 12:00 midnight.&quot;, vbExclamation
CallInDateTime.SetFocus 'Go back to DateTimefield.
Cancel = True 'Cancel saving the record.
End If

dz
dzaccess@yahoo.com

 

[mph1]

Have had similiar problem where a certain field must be filled in before saving. I have done 2 different things.
1. after the first field and then several others put in code OnExit if field is null, to remind user to go back. If the tab or enter keys are only used, you are auotmatically taken back to the null field. However, I found some were bypassing using the mouse. To compenstate for that...
2. I have an Add button on the form. I put in the OnCurrent event of the form code to make the Add button invisible until the specific field was filled in. If not filled in, no Add button.

Hope this concept helps.

Maurie

 

[nubianqueen]

Thanks for the response, my question is for FoxProProgrammer
I made an adjustment on the first line of your code,

I don't need to check for a range of time in the call in field so this is what I have:

&quot;If (Hour(CallInDateTime) = 24)Then..........

I only want to make sure the time is not 12 midnight. I put it in the before_update but it's not working, what have I done wrong?

Thanks,
nubianqueen

 

[FoxProProgrammer]

If you only want to check for 12:00 midnight, this should work.

If Hour(CallInDateTime) = 0 Then
MsgBox &quot;You can not enter midnight.&quot;, vbExclamation
CallInDateTime.SetFocus 'Go back to DateTimefield.
Cancel = True 'Cancel saving the record.
End If

You need to check for 0, not 24. The Hour function returns a number between 0 and 23, not 1 and 24. It treats 12:00:00 AM as 0 hours instead of 24 hours.

Hope this works.
dz
dzaccess@yahoo.com