query

[Guest]

I have a form that is linked to a query.

There are four text boxes in the form where the user inputs the search
criteria. ON click the query is refereshed.

The criteria in each of the four fields are like this.... Like "*" &
[Forms]![search1]![Text65] & "*" Or Is Not Null

The problem is .. the query displays records where the fields are blank for
all the four fields and the required record.

Is there a way around this?

 

[Guest]

Hi flow, can you post the SQL for your query?

 

[Guest]

here it is

SELECT [Resource Library].[Contact ID], [Resource Library].[Postal Code],
[Resource Library].[First Name], [Resource Library].[Last Name], [Resource
Library].Address1, [Resource Library].Address2, [Resource Library].City,
[Resource Library].County, [Resource Library].Phone, [Resource Library].Fax,
[Resource Library].Mobile, [Resource Library].[Email Address], [Resource
Library].[Alms reference], [Resource Library].[Job Title], [Resource
Library].Organisation, [Resource Library].[Local Group], [Resource
Library].Sex, [Resource Library].Individual, [Resource Library].School,
[Resource Library].Faith, [Resource Library].Youth, [Resource
Library].Artist, [Resource Library].Royalties, [Resource Library].Website,
[correspondence log].[Event Type], [correspondence log].[Event Date],
[correspondence log].[Material sent], [correspondence log].[£ Raised],
[correspondence log].[TY Sent], [correspondence log].[TY Date],
[correspondence log].Notes
FROM [Resource Library] INNER JOIN [correspondence log] ON [Resource
Library].[Contact ID] = [correspondence log].[Contact ID]
WHERE ((([Resource Library].[Postal Code]) Like "*" &
[Forms]![search1]![Text65] & "*" Or ([Resource Library].[Postal Code]) Is
Null) AND (([Resource Library].[Last Name]) Like "*" &
[Forms]![search1]![Text42] & "*" Or ([Resource Library].[Last Name]) Is Null)
AND (([Resource Library].[Alms reference]) Like "*" &
[Forms]![search1]![Text67] & "*" Or ([Resource Library].[Alms reference]) Is
Null) AND (([Resource Library].Organisation) Like "*" &
[Forms]![search1]![Text77] & "*" Or ([Resource Library].Organisation) Is
Null));

Hi flow, can you post the SQL for your query?

I have a form that is linked to a query.

There are four text boxes in the form where the user inputs the search
criteria. ON click the query is refereshed.

The criteria in each of the four fields are like this.... Like "*" &
[Forms]![search1]![Text65] & "*" Or Is Not Null

The problem is .. the query displays records where the fields are blank for
all the four fields and the required record.

Is there a way around this?

 

[Guest]

Whoa, be careful what you ask for right?

The first thing I noticed was in your WHERE statement, your OR condition is
IS NULL rather than IS NOT NULL for all four fields. Please correct this,
test it again, and post back the results.

here it is

SELECT [Resource Library].[Contact ID], [Resource Library].[Postal Code],
[Resource Library].[First Name], [Resource Library].[Last Name], [Resource
Library].Address1, [Resource Library].Address2, [Resource Library].City,
[Resource Library].County, [Resource Library].Phone, [Resource Library].Fax,
[Resource Library].Mobile, [Resource Library].[Email Address], [Resource
Library].[Alms reference], [Resource Library].[Job Title], [Resource
Library].Organisation, [Resource Library].[Local Group], [Resource
Library].Sex, [Resource Library].Individual, [Resource Library].School,
[Resource Library].Faith, [Resource Library].Youth, [Resource
Library].Artist, [Resource Library].Royalties, [Resource Library].Website,
[correspondence log].[Event Type], [correspondence log].[Event Date],
[correspondence log].[Material sent], [correspondence log].[£ Raised],
[correspondence log].[TY Sent], [correspondence log].[TY Date],
[correspondence log].Notes
FROM [Resource Library] INNER JOIN [correspondence log] ON [Resource
Library].[Contact ID] = [correspondence log].[Contact ID]
WHERE ((([Resource Library].[Postal Code]) Like "*" &
[Forms]![search1]![Text65] & "*" Or ([Resource Library].[Postal Code]) Is
Null) AND (([Resource Library].[Last Name]) Like "*" &
[Forms]![search1]![Text42] & "*" Or ([Resource Library].[Last Name]) Is Null)
AND (([Resource Library].[Alms reference]) Like "*" &
[Forms]![search1]![Text67] & "*" Or ([Resource Library].[Alms reference]) Is
Null) AND (([Resource Library].Organisation) Like "*" &
[Forms]![search1]![Text77] & "*" Or ([Resource Library].Organisation) Is
Null));

Hi flow, can you post the SQL for your query?

I have a form that is linked to a query.

There are four text boxes in the form where the user inputs the search
criteria. ON click the query is refereshed.

The criteria in each of the four fields are like this.... Like "*" &
[Forms]![search1]![Text65] & "*" Or Is Not Null

The problem is .. the query displays records where the fields are blank for
all the four fields and the required record.

Is there a way around this?