wildcard in a query form

[rocketD]

Hi All,
I'm trying to generate a report based on a query that receives its
input from a form. Specifically, I need to be able to select all
specimens collected of a particular genus/species combo, specified by a
user, during a date range specified by the user. The form itself is
unbound. The genus and species fields are separate (one genus to many
species)and bound to lookup tables, so that I can limit the species to
the genus selected. When all 4 fields in the form are filled, the query
works fine. However, leave a field blank, and no records are returned.

My two goals:
1) I want the user to be able to specify a genus and leave species
blank/provide a wildcard so that ALL specimens with that selected genus
are returned (not just a particular species);
2) I want the user to be able to leave the dates blank/provide a
wildcard so that he can get a report of all specimens of a particular
genus or genus/species combo ever entered.

I have this statement in the query under the genus field:
Like [forms]![fm_reportGenus]![genus]
and under the species field:
Like [forms]![fm_reportGenus]![species]
and finally, I have this statement in the query under the date field:
Between [forms]![fm_reportGenus]![StartDate] And
[forms]![fm_reportGenus]![EndDate]

I have a separate form for a report of just a date range, but I'd like
to be able to query based on date or on genus/species, or just genus.
Can anyone tell me how to make this report work if the species or date
range fields are blank?

Thanks!
Dara

 

[Guest]

Try this --
SELECT RocketD.Species, RocketD.Genus, RocketD.YourDateField
FROM RocketD
WHERE (((RocketD.Species) Like [forms]![fm_reportGenus]![species] & "*") AND
((RocketD.Genus) Like [forms]![fm_reportGenus]![genus] & "*") AND
((RocketD.YourDateField) Between [forms]![fm_reportGenus]![StartDate] And
[forms]![fm_reportGenus]![EndDate])) OR (((RocketD.Species) Like
[forms]![fm_reportGenus]![species] & "*") AND ((RocketD.Genus) Like
[forms]![fm_reportGenus]![genus] & "*") AND ((RocketD.YourDateField) Like
IIf([forms]![fm_reportGenus]![StartDate] Is Null,"*")));

 

[Guest]

I had the same problem. I setup an unbound form that use drop down list for
multiple search criteria. If any of the fields were blank I got no results.
if they ALL had selections then it would work.

What I did was use ubound Comboboxes the query was setup with the field I
wanted the combobox to display (Colum 2) and the recordID (Colum 1). Bound
the combobox to Colum 1 so it returns the Numerical Value not the Text Value.
T

Then on the Form I hid 2 unbound textboxes and set their default values to "
", this ensures that they are not Null.

Then set the OnChange of the Combobox through CodeBuilder to:

[TextBoxName1].Value = [ComboBoxName].Value
[TextBoxName2].Value = [ComboBoxName].Value

I did this for all the Comboboxes that have Text Values instead of Numerical
Values, because I have found that the codebuilder in Access tends to
completely ignore nulls and *.

Once the users selects all the criteria (and ignores the ones they do not
want) for their search I setup a button to "View Selected" That opens the
form I want to display.

Then on the OnCLick of the Button in the CodeBuilder, just before the
"stDocName = "FormName"" I have

If [TextBoxName1] = " " Then
[TextBoxName1] = "1"
End If

If [TextBoxName2] = " " Then
[TextBoxName2] = "9999"
End If

(note: I use 9999 as the second value as my records will never exceed that.
You may need to use a higher number)

This ensures that in my query (that the form I am opening is attached to) I
will grab all records if the user does not make a selection for that field.

The final step will be in your query that your form is attached to. Ensure
that your relationships are defined correctly your query should include both
tables (your Genus and Specimens) make sure that the RecordIDs for each are
included in your query.

Then in the Ciriteria area of the RecordID for the field you used to link to
your ComboBox in the Form you would place,

=[Forms]![FormName]![TextBoxName1] And <=[Forms]![FormName]![TextBoxName2]

This will make it so that if your user selects a specific Species, then
[TextBoxName1] and [TextBoxName2] will have the same value and only that
specific Species will be returned on your form. But if Species is ignored by
your user then the differnance in Values becomes a range and the system will
return all the records.

Hope this helps.

--
David O'Malley

Hi All,
I'm trying to generate a report based on a query that receives its
input from a form. Specifically, I need to be able to select all
specimens collected of a particular genus/species combo, specified by a
user, during a date range specified by the user. The form itself is
unbound. The genus and species fields are separate (one genus to many
species)and bound to lookup tables, so that I can limit the species to
the genus selected. When all 4 fields in the form are filled, the query
works fine. However, leave a field blank, and no records are returned.

My two goals:
1) I want the user to be able to specify a genus and leave species
blank/provide a wildcard so that ALL specimens with that selected genus
are returned (not just a particular species);
2) I want the user to be able to leave the dates blank/provide a
wildcard so that he can get a report of all specimens of a particular
genus or genus/species combo ever entered.

I have this statement in the query under the genus field:
Like [forms]![fm_reportGenus]![genus]
and under the species field:
Like [forms]![fm_reportGenus]![species]
and finally, I have this statement in the query under the date field:
Between [forms]![fm_reportGenus]![StartDate] And
[forms]![fm_reportGenus]![EndDate]

I have a separate form for a report of just a date range, but I'd like
to be able to query based on date or on genus/species, or just genus.
Can anyone tell me how to make this report work if the species or date
range fields are blank?

Thanks!
Dara