If Form Field is not null then...

I have a field called [Warning] in a form that I want to simply populate with the word NOT under a particular condition. That condition being if a field called [Model] is Not Null -AND- a field called [Year] is NULL then the [Warning] field will be the word NOT. I tried putting an expression together in the Control Source for the [Warning] field but was getting errors.

Sponsored Links:

Related Topics

updating field whose value is the word NULL and Not a typical 'null' value
I was given a table where in a text field, the value is NULL. (Actually spelled out like that.)
I need to convert that field to a date/time one. Which is being hindered by these values of NULL.

The problem is that when I put the word NULL in the field criteria spot, Access believes that I am referring to a value of 'null' and will not do the update.
I have done "NULL", NULL, =NULL, ="NULL", is NULL. and no success.
Please remember that the actual value is not 'null'. It is a text field that has the word NULL in the field.

Null Values in queries
This has probably been discussed but I can't find the answer.

I want to query using a form. If a form field is empty I want the query to return all results from the table including null fields.

If I query 'Is Null' then it does return the null fields (indicating that the fields are, in fact bona fide null fields).

If I query Like IIf(IsNull([Forms]![Form1]![Date]),Is Null,"*12") - then, when the form field is null, it doesn't return the Is Null fields. (I've checked by substituting another expression that the Iif is returning 'True

Passing criteria "NULL" or "IS NOT NULL" to a query
I want to pass the criteria "NULL" or "IS NOT NULL" to the underlying query in a report from a field on a form where all criteria for the particular report is selected before running the report. Typicially the procedure I use is to place the name of the control from the form in the criteria field of the query. example. if I'm providing a date to the query, I put a date field on the form and put the full name of the field "forms!myform!fieldname" in the criteria field of the query underlying the report. This is pretty bullet proof, except when I want to pass a value of "null", or "not null" as the criteria for a given field in the query. How can I set up a combo box, or command button, or selection box to feed the values "NULL" or "NOT NULL" as criteria the underlying query

Concatenate multiple fields that might have a null value
Hope someone can help. I'm trying to concatenate many fields into one field and some have a null value. And some need to have a description added in front of the value.
Field1 = Bldg Name (maybe null)
Field2 = Street
Field3 = Floor (maybe null)
Field4 = Room (maybe null)
Field5 = City

My problem is that in front of [Floor] should be "FL:" only if that field is not null. However, I keep getting the "FL:" in front of everything. How do I not include that when that specific field is null?

Control not picking up value from other field/How do I save with a null?
I have added 2 fields (combo boxes) to a form. A value is chosen for the first field. The second field chooses a value based on the selection from the first field.

The second field doesn't't seem to be picking up this value. When I click on the down arrow, there are no selections.

Secondly, I want to be able to save the form with both of these fields being blank. However, when I try to do this, I get an error message:"You tried to assign a Null value to a variable that is not a Variant data type". How do I allow the form to save when these 2 fields are null?

Form Error 'Field Cannot be Null' but field has data
I am having a problem with a form when it posts to the table.

Most of the form is filled automatically by a query, I only select an 'option' from a dropdown list and then enter 2 dates.

When I try to submit the form I get an error that the 'Option' field cannot be null. However the field isn't null it contains the value that I selected from the list AND the record is beingcreated in the db despite the error message indicating that it can't be.

Could this be related to the 'Option' field being unbound?

Using Filter by Form
I have a form (Access 2003) that I want to use Filter by Form on. There are over 50,000 records behind this form. When I use the dropdown lists beside the fields, it say is Null or is not null.

I know I have used filter by form before and the dropdown lists contain what is actually in the field. Is there anyway to get this data to appear instead of Is Null or Is not Null?

How to filter form when value selected from combobox is null?
I have a combobox which has null as a valid value.I want the form to get filtered when user selects null as a value also.However no filtering happens when the value is null.I would liketo know is whether it is possible to filter if the record selected is null as the filter criteria will be empty string then?I am not posting any code as my question is just that can criteriabe null and still filter?

Updating Null Field
When I click on a field O# in a form and if the field in T# is null I want to update the T# to

Auto Populate Field Based on other fields
I am a total access newbie when it comes to designing a database. I have managed to create a simple web database for project management but have run into a snag.

I want to have a field (field 3) auto-populate based on what is entered into two other fields (field 1 & field 2).

Field 1 is a date field (when a presentation is due). Field 2 is a yes/no checkbox (if checked, it means no presentation is necessary). Field 3 would be a calculated text field (?) because text cannot be entered into Field 1.

If field 1 is null and field 2 is checked, then I want "NA" to auto populate into field 3. However, if field 1 is null and field 2 is not checked, I want "TBD" to auto populate into field 3. If field 1 is not null, then I want field 3 to be null

VBA Code - Replacing Null Values with 0
I'm a struggling to come up with a custom function that replaces Null values in a table with Zero.

My table in its basic form has two fields.
Field A: Client
Field B: Quantity

The data is pulled from elsewhere in the database, but when it extracts it will have the client, and quantities. but some clients will have a Null quantity. e.g. below:

Multiple validations in same field
I need to enter into a field either Null OR "N" OR "Pn" The Pn is a unique number between P001 and P999.

Obviously the "no duplicates" rule for Pn will interfere with the "N" entries. Is there a way around this?

Alternately, the operators who enter Null or "N" use one form, and the operators who enter Null or Pn use a different form. Is it possible to create different validation rules that are tied to the respective forms?

duplicate alias ??
I have what might be a simple problem if I knew access/sql better. I have 12 fields (field1 through field12)which may or may not be populated. I know how to do this with other languages with if/else structures but it looks like access/sql doesn't have an ELSE structure.

I have to create a new table, with a new field populated with the contents of the last populated field.

in other words. if the 12th field is not null, populate fieldnew with the contents of field12
if field12 is null, then if field11 is not null,populate fieldnew with the contents of field11 |
if field11 is null, then if field10 is not null,populate fieldnew with the contents of fieldl10
if field10 is null, then if field9 is not null,populate fieldnew with the contents of fieldl9
if field9 is null, then if field8 is not null,populate fieldnew with the contents of fieldl8

Field Visible If other fields are null
I have a row of fields (call them 1-4) set up and have a field (with label) below (5) it that is not visible. I want field 5 to be visible if any of 1-4 are not null but I want it to go back to being non-visible if all of 1-4 are null.

So far I've tried many ways of addressing this with the latest being:

Private Sub IiF()
IiF OtherDeliveriestoUNK.Value = Null
IiF OtherAlternatePort.Value = Null
IiF OtherNameofAltPort.Value = Null
IiF OtherPreferUNK.Value = Null
ListOtherSpeciesUNK.Visible = False
Label445.Visible = False
ListOtherSpeciesUNK.Visible = True
Label445.Visible = True
End If
End If
End If
End If

If/Then Null based on Null Value of Another Field
I am having trouble creating an expression whereby the value of [ThisExpr] is conditioned upon whether (NZ[OtherExpr]) is Null. So, If (NZ[OtherExpr]) returns a null value then 0, or If[OtherExpr] is not Null then [ThisExpr].

How to Check If Two Fields are Null?
I have two currency fields in a form: [Salary1] and [Salary2].
I would like VBA to take action only if both of them are null.

I know how to do this for one field:
If (IsNull(Me.[Salary1]) = False) Then

But what is the syntax for checking if both fields are null

How to Check If Two Fields are Null?
I have two currency fields in a form: [Salary1] and [Salary2].
I would like VBA to take action only if both of them are null.

I know how to do this for one field:
If (IsNull(Me.[Salary1]) = False) Then

But what is the syntax for checking if both fields are null?

Conditional statement not working
This is making me crazy.

I have an if.then statement that is not working. I have field on my form called EndDate. If no date is in the field then I need to pop a msgbox reminding the user to enter a date. currently it looks like this:

If Me![EndDate] = Null Then
Response = MsgBox("Please enter ending date", vbDefaultButton1,)
Exit Sub
End If

I put a breakpoint on the code and when the process goes past that first line it tells me that Me.EndDate = Null. Yet it drops right through each time without executing the commands in the If block.
I tried writing it several different ways"
If Me.EndDate = Null Then
If Me!EndDate = Null Then
If (Me.EndDate) = Null Then

Unless I have hit a bug, it must be a syntax error on my part, yet I don't see it.

Auto populate field#1 if field#2 is null
Can someone tell me the correct macro condition, item, and expression to populate field # 1 with a word if field # 2 is null and populate field #1 with a different word is field # 2 is not null

Multiple Search Criterias with Null Values
I have created a database to sort out 1000 different types of forms that our company uses. I wanted to create a query that will allow me to search for multiple fields we have captured.

The general condition I use:
Like "*" & [Forms]![AdvSearchF]![FormTitl] & "*" Or Is Null

I apply this similar condition to all the other fields changing out the field name.
The example listed is for the Form Title.

The problem I'm encounter is that it it will give me a list that has the form title but it will also include forms that have null values. I understand it is because I put "or is null" butif I don't, when I'm searching another field it will return nothing because the search value is null and is not accepted.