Union statement issue

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

Guest

I created a form that links to a report that uses the statement below as a
record source.

Example:

Select value_name,value_yr,value3
From Test
Where value name = forms!testform!nametxt AND value_yr=forms!testform!yrtxt

UNION

Select value_name,value_yr,value3
From Test
Where value_yr=forms!testform!yrtxt


How can i get the result of the first select statement without getting the
result of the second one?
 
I wanted to eliminate the amount of reports by making one that covers a
variety of search criteria.
If the user enters info for value_name AND value_yr, i want to run the first
query. If the user only enters info in the Value_yr field, then the second
statement will run. I think i was going in the wrong direction with the union
keyword. How can this be done?
 
One method that should work unless you get too many criteria is the
following

Select value_name,value_yr,value3
From Test
Where value_name = forms!testform!nametxt
AND (value_yr=forms!testform!yrtxt OR Forms!TestForm!YrTxt is Null)

That filters the record by value_name and value_yr when Nametxt and YrTxt
have values

WHEN only NameTxt has a value and YrTxt is null, it filters by Value_Name
and the fact that yrTxt is null

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
What can i do when there is a lot of criteria?

John Spencer said:
One method that should work unless you get too many criteria is the
following

Select value_name,value_yr,value3
From Test
Where value_name = forms!testform!nametxt
AND (value_yr=forms!testform!yrtxt OR Forms!TestForm!YrTxt is Null)

That filters the record by value_name and value_yr when Nametxt and YrTxt
have values

WHEN only NameTxt has a value and YrTxt is null, it filters by Value_Name
and the fact that yrTxt is null

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
Click to expand...
 
You can put the criteria in field of a table with a Yes/No and display it in
a subform datasheet view. Check off the criteria you want and fire the
report command button that uses a query referencing the subform as criteria.
 
If you have more than 2 or 3 fields that you need to use the option on, you
will probably need to build the criteria for the report using VBA and pass
that information to the report when you open the report.

IF the field you are testing against is required (will never be null) there
are other techniques you can use.

If Value_Yr is a text field and is never null then you can use

Value_yr LIKE NZ(Forms!TestForm!yrText,"*")

If Value_Yr is a dateField and always has a value between 1/1/1890 and
12/31/2990 then you can use

Value_Yr Between Nz(Forms!TestForm!yrText,#1/1/1890) and
Nz(Forms!TestForm!yrText,#1/1/2991)

If Value_Yr is a number then use a range that is outside the allowable range
of values
Value_Yr Between Nz(Forms!TestForm!yrText,-1000000) and
Nz(Forms!TestForm!yrText,9999999)

I can't find a reference to the technique to build the criteria and pass it.
I thought that I had one, but cannot locate it right now.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Whoops! Error. I missed the ending # delimiter on the dates. The lines
should have read:

Value_Yr Between Nz(Forms!TestForm!yrText,#1/1/1890#) and
Nz(Forms!TestForm!yrText,#1/1/2991#)


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Back
Top