null value pass from form into query

[Guest]

For some reason I can't seem to get this to work and was hoping posting here
would speed up finding the resolution.

I have a form that the users a specifying a value from based on a list box.
The users press a button on the form after doing the selection and it
executes a query with criteria based on the value from the field; the query
is using [Forms]![frmBatchesNeedingIndexing]![Contract].[Value] as the
critera. This works fine when a users specifies a value.

The problem I'm experiencing is I need this list box to have the capability
to have a null value passed to the query so that when the query utilizes this
value if it is null, it queries records as if there is no criteria defined
displaying everything. This seems to be a similar situation as to the "Null
value won't pass into function" post but I'm not using a function. I even
tried adjusting the form to use an "on open" event procedure on the form and
an "after update" event procedure to set a value ContractValue for the form
(so I could define ContractValue as a variant in the code) and then call
[Forms]![frmBatchesNeedingIndexing]![ContractValue] and that doesn't work
either.

How can I get this to work where I can call a null or non-null value into
the query and get it to give the appropriate output?

 

[Guest]

If you want all records to be displayed if no value was selected in the list
box, and another criteria to the query

If that what you have now:

Select * From TableName Where FieldName =
[Forms]![frmBatchesNeedingIndexing]![Contract]

Change it to

Select * From TableName Where FieldName =
[Forms]![frmBatchesNeedingIndexing]![Contract] Or
[Forms]![frmBatchesNeedingIndexing]![Contract] Is Null

So the second criteria will return True (and all the records) if no value
was selected

 

[Guest]

Very good call. What ticks me off is it was really that easy; I was making
it more complicated than what it was trying to play with IIf statements and
following what some of the other posts were doing. Thanks again.

If you want all records to be displayed if no value was selected in the list
box, and another criteria to the query

If that what you have now:

Select * From TableName Where FieldName =
[Forms]![frmBatchesNeedingIndexing]![Contract]

Change it to

Select * From TableName Where FieldName =
[Forms]![frmBatchesNeedingIndexing]![Contract] Or
[Forms]![frmBatchesNeedingIndexing]![Contract] Is Null

So the second criteria will return True (and all the records) if no value
was selected

--
Good Luck
BS"D

For some reason I can't seem to get this to work and was hoping posting here
would speed up finding the resolution.

I have a form that the users a specifying a value from based on a list box.
The users press a button on the form after doing the selection and it
executes a query with criteria based on the value from the field; the query
is using [Forms]![frmBatchesNeedingIndexing]![Contract].[Value] as the
critera. This works fine when a users specifies a value.

The problem I'm experiencing is I need this list box to have the capability
to have a null value passed to the query so that when the query utilizes this
value if it is null, it queries records as if there is no criteria defined
displaying everything. This seems to be a similar situation as to the "Null
value won't pass into function" post but I'm not using a function. I even
tried adjusting the form to use an "on open" event procedure on the form and
an "after update" event procedure to set a value ContractValue for the form
(so I could define ContractValue as a variant in the code) and then call
[Forms]![frmBatchesNeedingIndexing]![ContractValue] and that doesn't work
either.

How can I get this to work where I can call a null or non-null value into
the query and get it to give the appropriate output?