I have a field where I'm recording the flashpoint of materials. The majority of the time, I'll have an actual number of the flashpoint (e.g. 100 or 140 or 200) but sometimes, I won't have a number or a number is not applicable.
Is using a combo box the only way to integrate an "N/A" solution for this? Otherwise, I'll have a field for the flashpoint and a second field for "NA".
Hi
What do you need the 'N/A' for?
Is leaving the field blank an option?
What about using the NZ function in the report/form etc
=Nz([FlashPoint],"N/A")
I'll join the suggestion pool. Use 0 (zero) or another "improbable" value such as 99999 to identify the "N/A" situation. gemma-the-husky's solution, the use of a negative number, is innovative. I had not considered that approach before.
The combo box would not appear to be suitable. I assume that you would have an infinite number of flash points. What you may consider, is having VBA code examine the textbox used to record the flash point to check for the improbable value. If found to open a pop-up form for confirmation. To remind those doing data entry, under the text box, you can have a label which says enter "xxxx" for "N/A" situation. (PS: the default value for the text box could be "xxxx" so that it does not have to be entered and the data entry person could simply tab past it. Of course that creates issues if there is data to be entered and the data entry person got distracted and simply tabbed past the text box.)
If you were to use a negative value then anything below -273 centigrade (absolute zero) would be a reasonable choice. You could then hide / show the control depending on the value.
Code:
Public Sub yourControl_BeforeUpdate(Cancel as Integer)
' If value is below Absolute Zero then set value to -274
If nz([Me.yourControl], 0) < -274 Then Me.yourControl = -274
End Sub
Public Sub Form_Current()
' Only show control if value is Null or above Absolute Zero
Me.yourControl.visible = (nz([yourControl], 0) > -274)
End Sub
Thanks for the suggestions and ideas. I need to field to always have something in it, so we know if the person recording the data didn't just accidentally skip over it and forget that value.
A flashpoint can be 0 (zero) and also can be a negative (theoretically, up to absolute zero).
I had thought about using an improbable number (e.g. "9999") but I wasn't sure if that was a 'correct' way to handle this issue.
Instead of having the end user type in 9999 or some variation thereof, can I have a checkbox that indicates "N/A" and when the form is saved, if my text box is empty, and the checkbox is checked it will record 9999 to the table? Then, I can set my reports to show "N/A" if the value is 9999.
Instead of having the end user type in 9999 or some variation thereof, can I have a checkbox that indicates "N/A" and when the form is saved, if my text box is empty, and the checkbox is checked it will record 9999 to the table? Then, I can set my reports to show "N/A" if the value is 9999.
Yes a check box would work. It would be a logical (true/false) field.
Alternatively you may have an option group were one button is true and the other false. An option group could also be used to identify other testing probabilities to explain why the flash point field is null, such as the need to retest, sample bad, etc.
To assure that data is not accidentally entered, the textbox for entering your flash point would be disabled. Checking the checkbox would enable the textbox for entering the flash point.
On the form current event:
Code:
me.checkbox = false
me.flashfield.enabled = false
The following would be on the click event for the checkbox
Code:
if me.checkbox = true then me.flashfield.enabled = true else me.flashfield.enabled = false
A subtlety. You will not need to enter anything since the field associated with the checkbox records whether any data was entered into the flash point field. The flashpoint field can remain null. Nothing wrong with being redundant either, by adding an improbable number to the flash point field.
For your report to show "N/A" you will need to test both the checkbox field and the flashfield.
Something similar to the pseudo code shown below. Note the code below won't work if you use 9999, but that is an easy fix.
Code:
If checkbox and isnumber(flashfield) then print flashfield else print "N/A"
I don't know whether isnumber will return false if the field is null. I am assuming it will.
Code:
If flashfield = 9999 then print "N/A" else print flashfield
Hi all
What about...
Before the record is saved check if the field has been entered, if not put up dialog to ask if the value needs to be entered.
therefore the user entering the form is asked directly the question. It does not rely on a report for someone to chase.
On the report - if null put " No value entered"
A dialog box may be useful, but I think having a checkbox that indicates NA and enters a value in the table that will never be used (e.g. 9999) is probably the best option to make sure that something is always recorded for that field.
Right now you have data that may be "Not available", "Not Entered", what happens if you discover you need "Validated", "Missing", "Suspect", or other flags? Without knowing a lot of details it's hard to come up with a design, but my experience has been that two fields are much better in the long run. One for the actual value, a second for the Condition Status, or what ever you want to call it.
Alternatively, you could use degrees Kelvin, and use negative number as status flags (-1 = missing, -2 = Error, etc.) Just write a function FormatFlashPoint([MyFlashPoint]) that returns a text string of either the numeric value or the "condition"
This field is something that is getting manually entered into the database based on an MSDS. So a person is going to have a paper copy of an MSDS, then find the flashpoint on the MSDS and enter it into the database.
There are only two possible outcomes:
1. We enter a number based on what is listed in the MSDS
2. Enter "NA" if the product has no flashpoint (e.g. it's a solid).
The field should never be blank. It should always contain some kind of value to show that the field has been checked and not just overlooked.
You need to think "out-of-the-box". No pun intended.
The existence of a boolean "checkbox" field in your table serves as an identifier for whether your "flashpoint" field has a value or not.
If you absolutely believe that the "falshpoint" field must contain a value, save the data as a string variable, then you can have it contain "N/A". Of course you will then need to convert the string to a number before doing any calculations.
I would consider this to be a normalization issue because the checkbox could be True and no data recorded for FlashPoint or vice versa.
If you absolutely believe that the "falshpoint" field must contain a value, save the data as a string variable, then you can have it contain "N/A". Of course you will then need to convert the string to a number before doing any calculations.
The way I envisioned it, it would not be possible. But that is based on programming. So what you say is possible should the programming is circumvented.
Same here. The suggestion to use a string was a "last" suggestion.
You are correct, a number below absolute zero can be used as the improbable number to designate "N/A". I overlooked that such a value was an option. My bad. -9999 would work for any temperature scale.
The way I envisioned it, it would not be possible. But that is based on programming. So what you say is possible should the programming is circumvented.
Yes but any data structure that supports inconsistent information should be avoided whereever possible. Where it can't be avoided a constraint should be added to the table to prevent it.
Yes but any data structure that supports inconsistent information should be avoided whereever possible. Where it can't be avoided a constraint should be added to the table to prevent it.
Field value defaulted to 99999 (or any impossible positive number) in the table definition (Not entered)
Any negative value below -274 (BeforeUpdate changes to -274 and treated as N/A)
Code:
Private Sub Form_BeforeUpdate(Cancel as Integer)
If Me.yourFlashPointField <-274 Then Me.yourFlashPointField = -274
End Sub
Other values treated as valid.
For reporting purposes ...
Code:
Public Function FlashpointForReport(byval theFlashpoint as variant)
Select Case theFlashPoint
Case 99999
FlashpointForReport = "Not entered"
Case -274
FlashpointForReport = "N/A"
Case Else
FlashpointForReport = theFlashPoint
End Function
