Field Issues

[Novice1]

I have a field called AppointmentTime. The drop down field has several options: N/A, 07:00 am, 07:15 am, etc. The field defaults to "N/A" since most customers don't have an appointment.

I don't want the field blank (null). I'm trying to write VBA to execute After Update in the field. If the person selects a time, then no action. If the person deletes the "N/A" then I want the "N/A" to reappear.

I tried ... If IsNull([AppointmentTime]) Then Me.AppointmentTime = "N/A"

No luck

 

[Uncle Gizmo]

Change the field properties to disallow Nulls.

Sent from my SM-G925F using Tapatalk

 

[Minty]

Also if this is a combo box and you have set the default value to N/A , if you set it to limit to list the field can't be set to null, and null won't be in the combo values.

 

[missinglinq]

What, exactly, does 'no luck' mean? Obviously, if AppointmentTime is defined as a DateTime Field, you can't set it to N/A.

Linq ;0)>

 

[jdraw]

Novice1,
There's more to your database than you have told us. What is the purpose of the database? A few more etails may help readers understand.
Do you plan on doing any arithmetic with AppointmentTime?

 

[Novice1]

It's a customer database. I have an appointment time field. I use a combo box (value list): N/A";"7:00 AM";"7:15 AM";"7:30 AM";"7:45 AM";"8:00 AM";"8:15 AM";" etc.

I have another field that captures the time a customer signs in. In a query I compute which customer is served first. This works fine.

My problem is that a customer can delete the data in the appointment time field, which generates an Null error.

If I can prevent the customer from deleting the combo box data, then there isn't a problem but I can't figure out how. I tried making the field as a required data field but the customer can still delete the data. I tried the AfterAction event as stated above, but again the customer can delete the data.

I don't know how to keep the customer from deleting the default value of "N/A."

 

[missinglinq]

...I tried the AfterAction event as stated above, but again the customer can delete the data...

I assume you mean AfterUpdate event, not AfterAction! If the AppointmentTime_AfterUpdate event contains

If IsNull(Me.AppointmentTime) Then Me.AppointmentTime = "N/A"

the user can delete the N/A? How?

Private Sub AppointmentTime_AfterUpdate()
  If IsNull(Me.AppointmentTime) Then Me.AppointmentTime = "N/A"
End Sub

doesn't allow a Null for me!

Linq ;0)>

 

[Galaxiom]

Firstly, time should be stored in a DateTime field type. Storing "N/A" in the table would require strings and hence the ordering would be wrong/complex.

One solution is to use a two-column combo. The displayed column holds what you want to be seen while the hidden column is bound to the field in the recordset and holds what is to be stored in the table.

Most entries would simply have the same in both columns. One entry would have N/A in the displayed column and a Null in the bound column. Deleting a value from the combo on the form would cause N/A to be displayed.

BTW, another way to show a string for Nulls in a control that is bound to a numeric (including DateTime) field is the Format Property of the control. The following format will show the date formatted as a time for any date after December 30, 1899 and N/A for all others, including Null.

hh:nn:ss AM/PM;"N/A";"N/A";"N/A"

The semicolons break the format into four sections. The first section is the format to be used for positive values, the second for negative values, the third for zero and the fourth for Nulls.

 

[missinglinq]

Firstly, time should be stored in a DateTime field type. Storing "N/A" in the table would require strings and hence the ordering would be wrong/complex.

This would be true, of course, if you were strictly talking about Time with a capital T, but the argument could be made that you're actually talking about 'appointments,' not Times! As long as there is going to be no manipulation using the Field I don't really see a problem...it's really no different then using using a Text Field for a serial number or Social Security Number, although they're composed entirely of 'numbers.'

Linq ;0)>

 

[Galaxiom]

This would be true, of course, if you were strictly talking about Time with a capital T, but the argument could be made that you're actually talking about 'appointments,' not Times! As long as there is going to be no manipulation using the Field I don't really see a problem...it's really no different then using using a Text Field for a serial number or Social Security Number, although they're composed entirely of 'numbers.'

Manipulation would include ordering by the Appointment field. As such the last thing you would want is to have it holding time as strings with an AM/PM format.

An appointment number or other code that orders correctly could be stored as numeric or alpha but an actual Time seems more to the point.

Either way the two-column combo is an appropriate solution.