Determining if a field on a form is Not Null (blank)?

[Gooter]

I have two fields on a form which passes the user-entered data from only one of the fields to a query in a report. I only want the user to enter data into one field. If they enter data into both fields, I want it to display an error message, something like, "Please only enter info into one of the boxes below."

I know of the function "IsNull". Is there something equivalent that will tell me if something is not blank or zero?

The following is obviously incorrect, I just wanted to give an example of what I am trying to do.

============
Private Sub Preview_Click()
If [Box1] IS NOT NULL Then
MsgBox "Only enter info into one box."
DoCmd.GoToControl "Box1"
End If
============

Thanks.

 

[scking]

I successfully handle these situations programmatically by forcing the change in form state. It depends on what controls you are using what type of events to use to trap the state. You really are trying to determine if the CONTROL has a value not the field as it sounds like it would be an unbound form with controls for the user to enter ad-hoc search strings. If the controls are ComboBoxes you can use the _Click event to null the other control. If the controls are TextBoxes you could use the OnLostFocus event of the TextBoxes to handle the same situation described for the ComboBoxes, or you could have the Button_Click event (I assume you have a button to trigger the report) to check whether both have values. If they do you could prompt the user that the report will not run with values in both and 'Cancel' the event by putting

Cancel = True

prior to any code which would run the report.



---------------------
scking@arinc.com
---------------------

 

[JimInKS]

Use the nz() function (one of my personal favorites!) it behaves differently depending on the data type of the parameter passed to it.

You can get full info from Access help. But, for string data types if the value passed is null OR "" (empty string) the function returns "". I use it all the time to prevent getting the 'value can't be null' error.

============
Private Sub Preview_Click()
    If nz([Box1]) = "" Then
        MsgBox "Only enter info into one box."
        DoCmd.GoToControl "Box1"
    End If
============