Validation Rule

[jake7363]

Hello,
I have two fields (call them field 1 and field 2), which are both formatted for "General Date". Since they are used in a calculation of Date and time, I need both segments to be entered.
Is there a way I can create a validation rule that doesn't allow data unless it is mm/dd hh:mm am/pm? The users are entering one part or another, but not both, and I would like to deter that.

If this is not possible, any suggestions are welcome.

Thanks in advance,
Jake

 

[Bodisathva]

Set the input mask and create a default value, this should prevent null entries as well as restrict the user input to the proper format.

 

[jake7363]

That's where I run into the problem. Since I am using a "General Date" format, seconds are part of the format. I am trying not to require the users to enter the seconds, so I am not sure how to set the mask for a two digit month, two digit date, two digit hours, two digit minutes and the am/pm.

Any suggestions?

 

[Bodisathva]

after a quick test, if you create a text box with a general date format and enter the following: "12/25/2006 10:56" and press return, the system configures the date as "12/25/2006 10:56:00 AM". entering "12/25/2006 20:56", the system interprets the date as "12/25/2006 8:56:00 PM". You do not need to enter the seconds and you may enter 24/12 hr format and the system will initialize the date variable regardless of the missing pieces.

 

[jake7363]

Right. That is what I have been using. But I still have the question of how to set a mask for that format, so that the "mm/dd h:n ampm" is entered without needing to put the "s".

That way, they have to enter both the date and time, not just one or the other. Left without masking, the user can enter either the date or the time.

jake

 

[Bodisathva]

In that event, you would need to change the format of the box to string and enter the input mask as "00/00/0000 00:00 AA". The data stored in the box will display as "12/25/2006 08:56 AM" however what will actually be stored will be "122520060856AM". In order to get the parts you need you need to parse the string, which will be simple since you've restricted the input and know where each part is located in the string.

You also may want to consider breaking up the pieces so you can use a short date format and a short time format in two separate boxes and you wouldn't need to do the parsing.

 

[Adeptus]

0#/0#\ 0#:00\ >L?!;0;_

# is an optional number or whitespace (whitespaces removed after entry)
? is an optional letter
L is a required letter
0 is a required number

This lets them be extra lazy & just type "1/1 1:00 A", rather than "01/01 01:00 AM"

In fact you could replace the L with another ? which will let them enter either 12hr or 24hr time.

 

[WrHn85]

Bodhisathva,

Your reply set me wondering:
If a date/time were broken up into 5 comboboxes

cboMonth, cboDay, cboHour, cboMinute, cboAMPM plus txtYear(default=CurrentYr)

to facilitate quick data entry, how would you combine them together again to get an actual date/time value into your underlying table?

Thanks

 

[Adeptus]

Have a look at "Microsoft Date and Time Picker Control".
I'm not sure if it comes standard with Access or is part of another MS package.
By default, it gives you a dropdown calendar to select a date, but if you change its options, you can have a custom date & time format, with up/down arrows to adjust each section.
When setting the format, use VB standard not Access standard.
(MM = month, mm = minute, tt = AM/PM)

One problem I noticed with it - it doesn't like being set to Null. This means, if you have it bound to a field, you must have a default value declared for that field.

 

[WrHn85]

Hey, Adeptus, thanks for the suggestion. Unfortunately, we can't use ActiveX or OCX here and I was planning to try a non-OCX version found on the net.

The phone staff have to log all offers made to contractors by date/time and often the entries are only a minute or so apart. So in this case having to click a bunch of times to open the control and set the time is more effort than just using default=previous record, then change the minutes.

Originally had a simple way around it with a date/time stamp, but as phone staff don't have access to the system yet they are writing the times and someone else has to enter them all - extremely tedious and error-prone when it's not in real time. So now I'm using combo boxes and looking to find how to make all the information into a date/time string.

Any ideas?

thanks

 

[Adeptus]

So in this case having to click a bunch of times to open the control and set the time is more effort than just using default=previous record, then change the minutes.

Well, you could get it to work that way... you can type the date/time as well as using the arrows.

So now I'm using combo boxes and looking to find how to make all the information into a date/time string.

how about something like...
cDate([DayControl] & "/" & [MonthControl] & "/" & [YearControl] & " " & [HourControl] & ":" & [MinuteControl] & " " & [AmPmControl])

 

[WrHn85]

Adeptus,

I tried your code out on some unbound combos and set it to send the result to another textbox, which is bound to the table. It seems to change all the combos (continuous form) but can get them to go back to "" on Current, so data entry folks aren't confused.

So it works great and will make their lives easier - thanks very much for your help!

Kay