Running a query from a form when values are empty. How do I handle

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hey there

I'm doing an access db 03. I have a form with aournd 10 fields that is
supposed to pass values to a query that builds a report. I.e. "FIELD A" and
"FIELD B" sends data to my query, but when "FIELD B" is left empty, the query
expects data that doesn't come. SO, the result is blank, no data at all is
displayed.

I'd like the report/query to display a result where, when "FIELD B" is left
empty, the report shows data from "FIELD B" as if everything was selected.??

I hope I make myself clear. I often find that, since I use a Danish
Access-version, it's harder to find the right terms :)

Cheers all
Rgds.

Jakob
http://www.finco.dk/laan
 
If you added the filter to the query using a reference to the text boxes in
the form, and the current filter of the query look like

Select TableName.* From TableName Where FieldB =
Forms![FormName]![TextBoxBName] And FieldA = Forms![FormName]![TextBoxAName]

Then you need to add another filter incase the field left empty, something
like
Select FieldA, FieldB, FieldC From TableName Where (FieldB =
Forms![FormName]![TextBoxBName] Or Forms![FormName]![TextBoxBName] Is Null)
And (FieldA = Forms![FormName]![TextBoxAName] Or
Forms![FormName]![TextBoxAName] Is Null)

In that case, if fieldB will be left empty, the second criteria will return
True and the query will return all the records
 
Hi Cohen

Tried it, it worked, partly. I acn make it work, but now I've created
another problem that neither I or my collegues can solve. I need to created
reports from several fields in a form. One field is a combobox that requires
a value in the table.

I'd still like to make my report show the selected value or all values from
that field. But I think that, since the field requires a value in the table,
I can only make my report show one value from that combobox. (The field shows
where the entered persons works).

I tried to remore the required setting from the table. This I believe would
make my report able to show more values - all or selected. BUT then I'd set
the field as required in the form. But using the "ValidationRule Property",
and setting to "Is not null" or "<>0" or " <>"" " doesn't work. Access
ignores those validation settings. Very strange. Any idea how to solve ?

Cheers
Jakob







Ofer Cohen said:
If you added the filter to the query using a reference to the text boxes in
the form, and the current filter of the query look like

Select TableName.* From TableName Where FieldB =
Forms![FormName]![TextBoxBName] And FieldA = Forms![FormName]![TextBoxAName]

Then you need to add another filter incase the field left empty, something
like
Select FieldA, FieldB, FieldC From TableName Where (FieldB =
Forms![FormName]![TextBoxBName] Or Forms![FormName]![TextBoxBName] Is Null)
And (FieldA = Forms![FormName]![TextBoxAName] Or
Forms![FormName]![TextBoxAName] Is Null)

In that case, if fieldB will be left empty, the second criteria will return
True and the query will return all the records


jakob boyer said:
Hey there

I'm doing an access db 03. I have a form with aournd 10 fields that is
supposed to pass values to a query that builds a report. I.e. "FIELD A" and
"FIELD B" sends data to my query, but when "FIELD B" is left empty, the query
expects data that doesn't come. SO, the result is blank, no data at all is
displayed.

I'd like the report/query to display a result where, when "FIELD B" is left
empty, the report shows data from "FIELD B" as if everything was selected.??

I hope I make myself clear. I often find that, since I use a Danish
Access-version, it's harder to find the right terms :)

Cheers all
Rgds.

Jakob
http://www.finco.dk/laan
Click to expand...
Click to expand...
 
Back
Top