Union query and search problem

[Tony Scullion]

Hi Group,

I have set up a Union query (qrySharedRecordsUnion) which
pulls together records from 3 tables. I have then
designed a new query (qrySharedRecords) based on the union
query. This new query produces a complete list of ALL
farmers and ALL types of Measures and amounts that they
have applied for. (the query shows a blank if a Measure
and amount has not been applied for). I now need to be
able to search this data.

Having previuosly built a search query beforeI used the
following criteria statement...

Like "*" & [Forms]![frmSearchForm]![xMeasuresMeasures]
& "*"

The problem with this statement is that is affects the
records diaplayed. If I remove the criteria statement the
query displays ALL records and if I add it back in it
restricts the records by removing the Measures and amounts
that they have not applied for (blanks). I need to be
able to search on all of the records that is produced by
the query (including blanks). I've tried changing the
relationships within the query but to no avail. Would be
grateful for help/advice on how how do I display ALL
records in my search form.

Many thanks

Tony
For info...here is the SQL of my query...

SELECT tblFarmers.FarmerID, tblFarmers.RefNo,
tblFarmers.Surname, tblFarmers.Forename,
qrySharedRecordsUnion.Amount, qrySharedRecordsUnion.Measure
FROM qrySharedRecordsUnion RIGHT JOIN tblFarmers ON
qrySharedRecordsUnion.FarmerID = tblFarmers.FarmerID
WHERE (((qrySharedRecordsUnion.Measure) Like "*" & [Forms]!
[frmSearchForm]![xMeasuresMeasures] & "*"));

 

[John Spencer (MVP)]

When you put the criteria in, the SQL statement restricts the results to
matches.

If what you want is matches and blanks (Nulls) you need to change the where
clause

SELECT tblFarmers.FarmerID, tblFarmers.RefNo,
tblFarmers.Surname, tblFarmers.Forename,
qrySharedRecordsUnion.Amount,
qrySharedRecordsUnion.Measure
FROM qrySharedRecordsUnion RIGHT JOIN tblFarmers ON
qrySharedRecordsUnion.FarmerID = tblFarmers.FarmerID
WHERE qrySharedRecordsUnion.Measure Like
"*" & [Forms]![frmSearchForm]![xMeasuresMeasures] & "*" OR
qrySharedRecordsUnion.Measure Is Null

That will show records where Measure is like
[Forms]![frmSearchForm]![xMeasuresMeasures] or where Measure is blank. It will
exclude any records that have a value that is not like
[Forms]![frmSearchForm]![xMeasuresMeasures]

 

[Tony Scullion]

John,

Apologies...I was a bit hasty on my reply.

The code works to show all records, including Nulls, but
how do I exclude Nulls from the list AFTER I perform a
search. (I'm using a combo box on a form to filter the
query). I still need the query to show Nulls at the start
though.

Thanks in anticipation

Tony

-----Original Message-----
When you put the criteria in, the SQL statement restricts the results to
matches.

If what you want is matches and blanks (Nulls) you need to change the where
clause

SELECT tblFarmers.FarmerID, tblFarmers.RefNo,
tblFarmers.Surname, tblFarmers.Forename,
qrySharedRecordsUnion.Amount,
qrySharedRecordsUnion.Measure
FROM qrySharedRecordsUnion RIGHT JOIN tblFarmers ON
qrySharedRecordsUnion.FarmerID = tblFarmers.FarmerID
WHERE qrySharedRecordsUnion.Measure Like
"*" & [Forms]![frmSearchForm]![xMeasuresMeasures] & "*" OR
qrySharedRecordsUnion.Measure Is Null

That will show records where Measure is like
[Forms]![frmSearchForm]![xMeasuresMeasures] or where Measure is blank. It will
exclude any records that have a value that is not like
[Forms]![frmSearchForm]![xMeasuresMeasures]

Hi Group,

I have set up a Union query (qrySharedRecordsUnion) which
pulls together records from 3 tables. I have then
designed a new query (qrySharedRecords) based on the union
query. This new query produces a complete list of ALL
farmers and ALL types of Measures and amounts that they
have applied for. (the query shows a blank if a Measure
and amount has not been applied for). I now need to be
able to search this data.

Having previuosly built a search query beforeI used the
following criteria statement...

Like "*" & [Forms]![frmSearchForm]![xMeasuresMeasures]
& "*"

The problem with this statement is that is affects the
records diaplayed. If I remove the criteria statement the
query displays ALL records and if I add it back in it
restricts the records by removing the Measures and amounts
that they have not applied for (blanks). I need to be
able to search on all of the records that is produced by
the query (including blanks). I've tried changing the
relationships within the query but to no avail. Would be
grateful for help/advice on how how do I display ALL
records in my search form.

Many thanks

Tony
For info...here is the SQL of my query...

SELECT tblFarmers.FarmerID, tblFarmers.RefNo,
tblFarmers.Surname, tblFarmers.Forename,
qrySharedRecordsUnion.Amount, qrySharedRecordsUnion.Measure
FROM qrySharedRecordsUnion RIGHT JOIN tblFarmers ON
qrySharedRecordsUnion.FarmerID = tblFarmers.FarmerID
WHERE (((qrySharedRecordsUnion.Measure) Like "*" & [Forms]!
[frmSearchForm]![xMeasuresMeasures] & "*"));

.