12 query criteria from "Search" form...

G

Guest

I have a form/sub form set-up whereby a user can enter search criteria into
one of any 12 fields. These fields are either text or pull down menus. I
have tried every kind of And/Or combination, but am unable to get an accurate
return. I believe this has to do with the fact that four of the fields to be
searched do not always have data to query on. These fields in the table are
not required to be completed and therefore have a null value.

Using Like [Forms]![frmMain]![Entity] Or Like "*" does not return records
that may have a null value for "Entity"

Using Like [Forms]![frmMain]![Entity] Or Is Null does not return records
that might have values that match other criteria entered

Using Like [Forms]![frmMain]![Entity] Or Like "*" Or Is Null does not return
any records

I have searched other sites and it is suggested that with this many
criterion I should ues VBA to run the query. I have followed the steps
listed and here is my code, however when I tried to view it I get an error
"Characters found after the end of SQL Statement". Help - please!

SELECT tblFiles.[File ID], tblFiles.Entity, tblFiles.Location,
tblFiles.[Record Series], tblFiles.[Document Type], tblFiles.[File Name],
tblFiles.[File Description], tblFiles.[Entered By], tblFiles.[Creation Date],
tblFiles.[Project Number], tblFiles.[Project Name], tblFiles.[Project Manager]
FROM tblFiles; WHERE (((FileID)=[Forms]![frmMain]![FileID] Or
(IsNull([Forms]![frmMain]![FileID]))) AND
((Entity)=[Forms]![frmMain]![Entity] Or (IsNull([Forms]![frmMain]![Entity])))
AND ((Location)=[Forms]![frmMain]![Location] Or
(IsNull([Forms]![frmMain]![Location]))) AND ((Record
Series)=[Forms]![frmMain]![RecordSeries] Or
(IsNull([Forms]![frmMain]![RecordSeries]))) AND ((Document
Type)=[Forms]![frmMain]![DocumentType] Or
(IsNull([Forms]![frmMain]![DocumentType]))) AND ((File
Name)=[Forms]![frmMain]![FileName] Or (IsNull([Forms]![frmMain]![FileName])))
AND ((File Description)=[Forms]![frmMain]![FileDescription] Or
(IsNull([Forms]![frmMain]![FileDescription]))) AND ((Entered
By)=[Forms]![frmMain]![EnteredBy] Or (IsNull([Forms]![frmMain]![EnteredBy])))
AND ((Creation Date)=[Forms]![frmMain]![CreationDate] Or
(IsNull([Forms]![frmMain]![CreationDate]))) AND ((Project
Number)=[Forms]![frmMain]![ProjectNumber] Or
(IsNull([Forms]![frmMain]![ProjectNumber]))) AND ((Project
Name)=[Forms]![frmMain]![ProjectName] Or
(IsNull([Forms]![frmMain]![ProjectName]))) AND ((Project
Manager)=[Forms]![frmMain]![ProjectManager] Or
(IsNull([Forms]![frmMain]![ProjectManager]))));
 
D

Douglas J. Steele

You've got a semi-colon after tblFiles, before the keyword WHERE. That's
what's causing your error.

In addition, I think it would be better to use

((FileID=[Forms]![frmMain]![FileID]) Or ([Forms]![frmMain]![FileID] IS
NULL))

rather than the IsNull function.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ember said:
I have a form/sub form set-up whereby a user can enter search criteria into
one of any 12 fields. These fields are either text or pull down menus. I
have tried every kind of And/Or combination, but am unable to get an
accurate
return. I believe this has to do with the fact that four of the fields to
be
searched do not always have data to query on. These fields in the table
are
not required to be completed and therefore have a null value.

Using Like [Forms]![frmMain]![Entity] Or Like "*" does not return records
that may have a null value for "Entity"

Using Like [Forms]![frmMain]![Entity] Or Is Null does not return records
that might have values that match other criteria entered

Using Like [Forms]![frmMain]![Entity] Or Like "*" Or Is Null does not
return
any records

I have searched other sites and it is suggested that with this many
criterion I should ues VBA to run the query. I have followed the steps
listed and here is my code, however when I tried to view it I get an error
"Characters found after the end of SQL Statement". Help - please!

SELECT tblFiles.[File ID], tblFiles.Entity, tblFiles.Location,
tblFiles.[Record Series], tblFiles.[Document Type], tblFiles.[File Name],
tblFiles.[File Description], tblFiles.[Entered By], tblFiles.[Creation
Date],
tblFiles.[Project Number], tblFiles.[Project Name], tblFiles.[Project
Manager]
FROM tblFiles; WHERE (((FileID)=[Forms]![frmMain]![FileID] Or
(IsNull([Forms]![frmMain]![FileID]))) AND
((Entity)=[Forms]![frmMain]![Entity] Or
(IsNull([Forms]![frmMain]![Entity])))
AND ((Location)=[Forms]![frmMain]![Location] Or
(IsNull([Forms]![frmMain]![Location]))) AND ((Record
Series)=[Forms]![frmMain]![RecordSeries] Or
(IsNull([Forms]![frmMain]![RecordSeries]))) AND ((Document
Type)=[Forms]![frmMain]![DocumentType] Or
(IsNull([Forms]![frmMain]![DocumentType]))) AND ((File
Name)=[Forms]![frmMain]![FileName] Or
(IsNull([Forms]![frmMain]![FileName])))
AND ((File Description)=[Forms]![frmMain]![FileDescription] Or
(IsNull([Forms]![frmMain]![FileDescription]))) AND ((Entered
By)=[Forms]![frmMain]![EnteredBy] Or
(IsNull([Forms]![frmMain]![EnteredBy])))
AND ((Creation Date)=[Forms]![frmMain]![CreationDate] Or
(IsNull([Forms]![frmMain]![CreationDate]))) AND ((Project
Number)=[Forms]![frmMain]![ProjectNumber] Or
(IsNull([Forms]![frmMain]![ProjectNumber]))) AND ((Project
Name)=[Forms]![frmMain]![ProjectName] Or
(IsNull([Forms]![frmMain]![ProjectName]))) AND ((Project
Manager)=[Forms]![frmMain]![ProjectManager] Or
(IsNull([Forms]![frmMain]![ProjectManager]))));
Click to expand...
 
A

Allen Browne

As Doug says, the semicolon before WHERE indicates the end of the SQL
statement, so that makes sense of the error message.

A more efficient solution might be to build the SQL statement (or just the
WHERE clause) from only the boxes where the user enters something. This
should be much more effient than the WHERE clause you have with its 24
phrases.

For an example of how to do that, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Ember said:
I have a form/sub form set-up whereby a user can enter search criteria into
one of any 12 fields. These fields are either text or pull down menus. I
have tried every kind of And/Or combination, but am unable to get an
accurate
return. I believe this has to do with the fact that four of the fields to
be
searched do not always have data to query on. These fields in the table
are
not required to be completed and therefore have a null value.

Using Like [Forms]![frmMain]![Entity] Or Like "*" does not return records
that may have a null value for "Entity"

Using Like [Forms]![frmMain]![Entity] Or Is Null does not return records
that might have values that match other criteria entered

Using Like [Forms]![frmMain]![Entity] Or Like "*" Or Is Null does not
return
any records

I have searched other sites and it is suggested that with this many
criterion I should ues VBA to run the query. I have followed the steps
listed and here is my code, however when I tried to view it I get an error
"Characters found after the end of SQL Statement". Help - please!

SELECT tblFiles.[File ID], tblFiles.Entity, tblFiles.Location,
tblFiles.[Record Series], tblFiles.[Document Type], tblFiles.[File Name],
tblFiles.[File Description], tblFiles.[Entered By], tblFiles.[Creation
Date],
tblFiles.[Project Number], tblFiles.[Project Name], tblFiles.[Project
Manager]
FROM tblFiles; WHERE (((FileID)=[Forms]![frmMain]![FileID] Or
(IsNull([Forms]![frmMain]![FileID]))) AND
((Entity)=[Forms]![frmMain]![Entity] Or
(IsNull([Forms]![frmMain]![Entity])))
AND ((Location)=[Forms]![frmMain]![Location] Or
(IsNull([Forms]![frmMain]![Location]))) AND ((Record
Series)=[Forms]![frmMain]![RecordSeries] Or
(IsNull([Forms]![frmMain]![RecordSeries]))) AND ((Document
Type)=[Forms]![frmMain]![DocumentType] Or
(IsNull([Forms]![frmMain]![DocumentType]))) AND ((File
Name)=[Forms]![frmMain]![FileName] Or
(IsNull([Forms]![frmMain]![FileName])))
AND ((File Description)=[Forms]![frmMain]![FileDescription] Or
(IsNull([Forms]![frmMain]![FileDescription]))) AND ((Entered
By)=[Forms]![frmMain]![EnteredBy] Or
(IsNull([Forms]![frmMain]![EnteredBy])))
AND ((Creation Date)=[Forms]![frmMain]![CreationDate] Or
(IsNull([Forms]![frmMain]![CreationDate]))) AND ((Project
Number)=[Forms]![frmMain]![ProjectNumber] Or
(IsNull([Forms]![frmMain]![ProjectNumber]))) AND ((Project
Name)=[Forms]![frmMain]![ProjectName] Or
(IsNull([Forms]![frmMain]![ProjectName]))) AND ((Project
Manager)=[Forms]![frmMain]![ProjectManager] Or
(IsNull([Forms]![frmMain]![ProjectManager]))));
Click to expand...
 
G

Guest

Thanks, that got me to be able to run the query. Now it will not filter
based on any selection from the form.

Any ideas?

Ember

Douglas J. Steele said:
You've got a semi-colon after tblFiles, before the keyword WHERE. That's
what's causing your error.

In addition, I think it would be better to use

((FileID=[Forms]![frmMain]![FileID]) Or ([Forms]![frmMain]![FileID] IS
NULL))

rather than the IsNull function.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ember said:
I have a form/sub form set-up whereby a user can enter search criteria into
one of any 12 fields. These fields are either text or pull down menus. I
have tried every kind of And/Or combination, but am unable to get an
accurate
return. I believe this has to do with the fact that four of the fields to
be
searched do not always have data to query on. These fields in the table
are
not required to be completed and therefore have a null value.

Using Like [Forms]![frmMain]![Entity] Or Like "*" does not return records
that may have a null value for "Entity"

Using Like [Forms]![frmMain]![Entity] Or Is Null does not return records
that might have values that match other criteria entered

Using Like [Forms]![frmMain]![Entity] Or Like "*" Or Is Null does not
return
any records

I have searched other sites and it is suggested that with this many
criterion I should ues VBA to run the query. I have followed the steps
listed and here is my code, however when I tried to view it I get an error
"Characters found after the end of SQL Statement". Help - please!

SELECT tblFiles.[File ID], tblFiles.Entity, tblFiles.Location,
tblFiles.[Record Series], tblFiles.[Document Type], tblFiles.[File Name],
tblFiles.[File Description], tblFiles.[Entered By], tblFiles.[Creation
Date],
tblFiles.[Project Number], tblFiles.[Project Name], tblFiles.[Project
Manager]
FROM tblFiles; WHERE (((FileID)=[Forms]![frmMain]![FileID] Or
(IsNull([Forms]![frmMain]![FileID]))) AND
((Entity)=[Forms]![frmMain]![Entity] Or
(IsNull([Forms]![frmMain]![Entity])))
AND ((Location)=[Forms]![frmMain]![Location] Or
(IsNull([Forms]![frmMain]![Location]))) AND ((Record
Series)=[Forms]![frmMain]![RecordSeries] Or
(IsNull([Forms]![frmMain]![RecordSeries]))) AND ((Document
Type)=[Forms]![frmMain]![DocumentType] Or
(IsNull([Forms]![frmMain]![DocumentType]))) AND ((File
Name)=[Forms]![frmMain]![FileName] Or
(IsNull([Forms]![frmMain]![FileName])))
AND ((File Description)=[Forms]![frmMain]![FileDescription] Or
(IsNull([Forms]![frmMain]![FileDescription]))) AND ((Entered
By)=[Forms]![frmMain]![EnteredBy] Or
(IsNull([Forms]![frmMain]![EnteredBy])))
AND ((Creation Date)=[Forms]![frmMain]![CreationDate] Or
(IsNull([Forms]![frmMain]![CreationDate]))) AND ((Project
Number)=[Forms]![frmMain]![ProjectNumber] Or
(IsNull([Forms]![frmMain]![ProjectNumber]))) AND ((Project
Name)=[Forms]![frmMain]![ProjectName] Or
(IsNull([Forms]![frmMain]![ProjectName]))) AND ((Project
Manager)=[Forms]![frmMain]![ProjectManager] Or
(IsNull([Forms]![frmMain]![ProjectManager]))));
Click to expand...
Click to expand...
 
G

Guest

Thanks, but all 12 boxes are used by the user. They all can be used in
conjunction to filter the records to locate the one they are searching for.
Ember

Allen Browne said:
As Doug says, the semicolon before WHERE indicates the end of the SQL
statement, so that makes sense of the error message.

A more efficient solution might be to build the SQL statement (or just the
WHERE clause) from only the boxes where the user enters something. This
should be much more effient than the WHERE clause you have with its 24
phrases.

For an example of how to do that, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Ember said:
I have a form/sub form set-up whereby a user can enter search criteria into
one of any 12 fields. These fields are either text or pull down menus. I
have tried every kind of And/Or combination, but am unable to get an
accurate
return. I believe this has to do with the fact that four of the fields to
be
searched do not always have data to query on. These fields in the table
are
not required to be completed and therefore have a null value.

Using Like [Forms]![frmMain]![Entity] Or Like "*" does not return records
that may have a null value for "Entity"

Using Like [Forms]![frmMain]![Entity] Or Is Null does not return records
that might have values that match other criteria entered

Using Like [Forms]![frmMain]![Entity] Or Like "*" Or Is Null does not
return
any records

I have searched other sites and it is suggested that with this many
criterion I should ues VBA to run the query. I have followed the steps
listed and here is my code, however when I tried to view it I get an error
"Characters found after the end of SQL Statement". Help - please!

SELECT tblFiles.[File ID], tblFiles.Entity, tblFiles.Location,
tblFiles.[Record Series], tblFiles.[Document Type], tblFiles.[File Name],
tblFiles.[File Description], tblFiles.[Entered By], tblFiles.[Creation
Date],
tblFiles.[Project Number], tblFiles.[Project Name], tblFiles.[Project
Manager]
FROM tblFiles; WHERE (((FileID)=[Forms]![frmMain]![FileID] Or
(IsNull([Forms]![frmMain]![FileID]))) AND
((Entity)=[Forms]![frmMain]![Entity] Or
(IsNull([Forms]![frmMain]![Entity])))
AND ((Location)=[Forms]![frmMain]![Location] Or
(IsNull([Forms]![frmMain]![Location]))) AND ((Record
Series)=[Forms]![frmMain]![RecordSeries] Or
(IsNull([Forms]![frmMain]![RecordSeries]))) AND ((Document
Type)=[Forms]![frmMain]![DocumentType] Or
(IsNull([Forms]![frmMain]![DocumentType]))) AND ((File
Name)=[Forms]![frmMain]![FileName] Or
(IsNull([Forms]![frmMain]![FileName])))
AND ((File Description)=[Forms]![frmMain]![FileDescription] Or
(IsNull([Forms]![frmMain]![FileDescription]))) AND ((Entered
By)=[Forms]![frmMain]![EnteredBy] Or
(IsNull([Forms]![frmMain]![EnteredBy])))
AND ((Creation Date)=[Forms]![frmMain]![CreationDate] Or
(IsNull([Forms]![frmMain]![CreationDate]))) AND ((Project
Number)=[Forms]![frmMain]![ProjectNumber] Or
(IsNull([Forms]![frmMain]![ProjectNumber]))) AND ((Project
Name)=[Forms]![frmMain]![ProjectName] Or
(IsNull([Forms]![frmMain]![ProjectName]))) AND ((Project
Manager)=[Forms]![frmMain]![ProjectManager] Or
(IsNull([Forms]![frmMain]![ProjectManager]))));
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Your query is attempting to handle the case where they didn't enter values
for one of more of the 12 boxes. Allen's suggestion (with which I heartily
concur) is that you dynamically generate the WHERE clause based on only
those boxes which have a value.

Having said that, did you try my suggestion of using

((FileID=[Forms]![frmMain]![FileID]) Or ([Forms]![frmMain]![FileID] IS
NULL))

rather than

((FileID=[Forms]![frmMain]![FileID]) Or IsNull([Forms]![frmMain]![FileID]))

?

If so, what does your revised query look like?



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ember said:
Thanks, but all 12 boxes are used by the user. They all can be used in
conjunction to filter the records to locate the one they are searching
for.
Ember

Allen Browne said:
As Doug says, the semicolon before WHERE indicates the end of the SQL
statement, so that makes sense of the error message.

A more efficient solution might be to build the SQL statement (or just
the
WHERE clause) from only the boxes where the user enters something. This
should be much more effient than the WHERE clause you have with its 24
phrases.

For an example of how to do that, see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Ember said:
I have a form/sub form set-up whereby a user can enter search criteria
into
one of any 12 fields. These fields are either text or pull down menus.
I
have tried every kind of And/Or combination, but am unable to get an
accurate
return. I believe this has to do with the fact that four of the fields
to
be
searched do not always have data to query on. These fields in the
table
are
not required to be completed and therefore have a null value.

Using Like [Forms]![frmMain]![Entity] Or Like "*" does not return
records
that may have a null value for "Entity"

Using Like [Forms]![frmMain]![Entity] Or Is Null does not return
records
that might have values that match other criteria entered

Using Like [Forms]![frmMain]![Entity] Or Like "*" Or Is Null does not
return
any records

I have searched other sites and it is suggested that with this many
criterion I should ues VBA to run the query. I have followed the steps
listed and here is my code, however when I tried to view it I get an
error
"Characters found after the end of SQL Statement". Help - please!

SELECT tblFiles.[File ID], tblFiles.Entity, tblFiles.Location,
tblFiles.[Record Series], tblFiles.[Document Type], tblFiles.[File
Name],
tblFiles.[File Description], tblFiles.[Entered By], tblFiles.[Creation
Date],
tblFiles.[Project Number], tblFiles.[Project Name], tblFiles.[Project
Manager]
FROM tblFiles; WHERE (((FileID)=[Forms]![frmMain]![FileID] Or
(IsNull([Forms]![frmMain]![FileID]))) AND
((Entity)=[Forms]![frmMain]![Entity] Or
(IsNull([Forms]![frmMain]![Entity])))
AND ((Location)=[Forms]![frmMain]![Location] Or
(IsNull([Forms]![frmMain]![Location]))) AND ((Record
Series)=[Forms]![frmMain]![RecordSeries] Or
(IsNull([Forms]![frmMain]![RecordSeries]))) AND ((Document
Type)=[Forms]![frmMain]![DocumentType] Or
(IsNull([Forms]![frmMain]![DocumentType]))) AND ((File
Name)=[Forms]![frmMain]![FileName] Or
(IsNull([Forms]![frmMain]![FileName])))
AND ((File Description)=[Forms]![frmMain]![FileDescription] Or
(IsNull([Forms]![frmMain]![FileDescription]))) AND ((Entered
By)=[Forms]![frmMain]![EnteredBy] Or
(IsNull([Forms]![frmMain]![EnteredBy])))
AND ((Creation Date)=[Forms]![frmMain]![CreationDate] Or
(IsNull([Forms]![frmMain]![CreationDate]))) AND ((Project
Number)=[Forms]![frmMain]![ProjectNumber] Or
(IsNull([Forms]![frmMain]![ProjectNumber]))) AND ((Project
Name)=[Forms]![frmMain]![ProjectName] Or
(IsNull([Forms]![frmMain]![ProjectName]))) AND ((Project
Manager)=[Forms]![frmMain]![ProjectManager] Or
(IsNull([Forms]![frmMain]![ProjectManager]))));
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I am not sure I understand what you mean by "value". I can reduce the number
to search on as they are not as "critical" as others, however they all have
value to the system it is wether or not a value has been entered.

I did try your suggestion and at least it will run now, but no filter occurs
when I select or enter criteria in my fields.

I did look at Allen's link, but it looks as if it is too "simple". My
options are of greater number than true/false or client/company.

Here is the revised SQL with the reduced number of search criteria.

SELECT tblFiles.[File ID], tblFiles.Entity, tblFiles.Location,
tblFiles.[Record Series], tblFiles.[Document Type], tblFiles.[File Name],
tblFiles.[File Description], tblFiles.[Entered By], tblFiles.[Creation Date],
tblFiles.[Project Number], tblFiles.[Project Name], tblFiles.[Project Manager]
FROM tblFiles
WHERE ((Entity=Forms!frmMain!Entity) Or (Forms!frmMain!Entity Is Null)) And
((Location=Forms!frmMain!Location) Or (Forms!frmMain!Location Is Null)) And
(([Record Series]=Forms!frmMain![Record Series]) Or (Forms!frmMain![Record
Series] Is Null)) And (([Document Type]=Forms!frmMain![Document Type]) Or
(Forms!frmMain![Document Type] Is Null)) And (([File
Name]=Forms!frmMain![File Name]) Or (Forms!frmMain![File Name] Is Null)) And
(([File Description]=Forms!frmMain![File Description]) Or
(Forms!frmMain![File Description] Is Null)) And (([Project
Number]=Forms!frmMain![Project Number]) Or (Forms!frmMain![Project Number] Is
Null)) And (([Project Name]=Forms!frmMain![Project Name]) Or
(Forms!frmMain![Project Name] Is Null));

Thanks for your help!
Ember
 
D

Douglas J. Steele

You'd do something like:

Dim qdfQuery As DAO.QueryDef
Dim strSQL As String
Dim strWhere As String

strSQL = "SELECT tblFiles.[File ID], tblFiles.Entity, " & _
"tblFiles.Location, tblFiles.[Record Series], " & _
"tblFiles.[Document Type], tblFiles.[File Name], " & _
"tblFiles.[File Description], tblFiles.[Entered By], " & _
"tblFiles.[Creation Date], tblFiles.[Project Number], " & _
"tblFiles.[Project Name], tblFiles.[Project Manager] " & _
"FROM tblFiles "

If IsNull(Forms!frmMain!Entity) = False Then
strWhere = strWhere & "Entity=" & Forms!frmMain!Entity & " And "
End If

If IsNull(Forms!frmMain!Location) = False Then
strWhere = strWhere & "Location=" & Forms!frmMain!Location & " And "
End If

' and so on for each of the 12 fields

If Len(strWhere) > 0 Then
' At least one field was non-null.
' Strip off the extra " And " from the end.
strWhere = "Where " & Left$strWhere, Len(strWhere) - 5)
End If

strSQL = strSQL & strWhere

Set qdfQuery = CurrentDb().QueryDefs("NameOfQuery")
qdfQuery.SQL = strSQL

Note that the two samples above assumed that Entity and Location were
numeric fields. If, say, Entity was a text field, you'd use

If IsNull(Forms!frmMain!Entity) = False Then
strWhere = strWhere & "Entity=" & Chr$(34) & Forms!frmMain!Entity &
Chr$(34) & " And "
End If

If you were dealing with a date field, you'd use

If IsNull(Forms!frmMain!MyField) = False Then
strWhere = strWhere & "MyField=" & Format$(Forms!frmMain!MyField,
"\#mm\/dd\/yyyy\#") & " And "
End If

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ember said:
I am not sure I understand what you mean by "value". I can reduce the
number
to search on as they are not as "critical" as others, however they all
have
value to the system it is wether or not a value has been entered.

I did try your suggestion and at least it will run now, but no filter
occurs
when I select or enter criteria in my fields.

I did look at Allen's link, but it looks as if it is too "simple". My
options are of greater number than true/false or client/company.

Here is the revised SQL with the reduced number of search criteria.

SELECT tblFiles.[File ID], tblFiles.Entity, tblFiles.Location,
tblFiles.[Record Series], tblFiles.[Document Type], tblFiles.[File Name],
tblFiles.[File Description], tblFiles.[Entered By], tblFiles.[Creation
Date],
tblFiles.[Project Number], tblFiles.[Project Name], tblFiles.[Project
Manager]
FROM tblFiles
WHERE ((Entity=Forms!frmMain!Entity) Or (Forms!frmMain!Entity Is Null))
And
((Location=Forms!frmMain!Location) Or (Forms!frmMain!Location Is Null))
And
(([Record Series]=Forms!frmMain![Record Series]) Or (Forms!frmMain![Record
Series] Is Null)) And (([Document Type]=Forms!frmMain![Document Type]) Or
(Forms!frmMain![Document Type] Is Null)) And (([File
Name]=Forms!frmMain![File Name]) Or (Forms!frmMain![File Name] Is Null))
And
(([File Description]=Forms!frmMain![File Description]) Or
(Forms!frmMain![File Description] Is Null)) And (([Project
Number]=Forms!frmMain![Project Number]) Or (Forms!frmMain![Project Number]
Is
Null)) And (([Project Name]=Forms!frmMain![Project Name]) Or
(Forms!frmMain![Project Name] Is Null));

Thanks for your help!
Ember

Douglas J. Steele said:
Your query is attempting to handle the case where they didn't enter
values
for one of more of the 12 boxes. Allen's suggestion (with which I
heartily
concur) is that you dynamically generate the WHERE clause based on only
those boxes which have a value.

Having said that, did you try my suggestion of using

((FileID=[Forms]![frmMain]![FileID]) Or ([Forms]![frmMain]![FileID] IS
NULL))

rather than

((FileID=[Forms]![frmMain]![FileID]) Or
IsNull([Forms]![frmMain]![FileID])) ?

If so, what does your revised query look like?

Doug Steele, Microsoft Access MVP

(no private e-mails, please)
Click to expand...
Click to expand...
 
G

Guest

I will give that a try (not sure I understand it all, but it looks
impressive). I might just have to re-think the search idea and perhaps break
it up into smaller searches.

Thanks again!
Ember

Douglas J. Steele said:
You'd do something like:

Dim qdfQuery As DAO.QueryDef
Dim strSQL As String
Dim strWhere As String

strSQL = "SELECT tblFiles.[File ID], tblFiles.Entity, " & _
"tblFiles.Location, tblFiles.[Record Series], " & _
"tblFiles.[Document Type], tblFiles.[File Name], " & _
"tblFiles.[File Description], tblFiles.[Entered By], " & _
"tblFiles.[Creation Date], tblFiles.[Project Number], " & _
"tblFiles.[Project Name], tblFiles.[Project Manager] " & _
"FROM tblFiles "

If IsNull(Forms!frmMain!Entity) = False Then
strWhere = strWhere & "Entity=" & Forms!frmMain!Entity & " And "
End If

If IsNull(Forms!frmMain!Location) = False Then
strWhere = strWhere & "Location=" & Forms!frmMain!Location & " And "
End If

' and so on for each of the 12 fields

If Len(strWhere) > 0 Then
' At least one field was non-null.
' Strip off the extra " And " from the end.
strWhere = "Where " & Left$strWhere, Len(strWhere) - 5)
End If

strSQL = strSQL & strWhere

Set qdfQuery = CurrentDb().QueryDefs("NameOfQuery")
qdfQuery.SQL = strSQL

Note that the two samples above assumed that Entity and Location were
numeric fields. If, say, Entity was a text field, you'd use

If IsNull(Forms!frmMain!Entity) = False Then
strWhere = strWhere & "Entity=" & Chr$(34) & Forms!frmMain!Entity &
Chr$(34) & " And "
End If

If you were dealing with a date field, you'd use

If IsNull(Forms!frmMain!MyField) = False Then
strWhere = strWhere & "MyField=" & Format$(Forms!frmMain!MyField,
"\#mm\/dd\/yyyy\#") & " And "
End If

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ember said:
I am not sure I understand what you mean by "value". I can reduce the
number
to search on as they are not as "critical" as others, however they all
have
value to the system it is wether or not a value has been entered.

I did try your suggestion and at least it will run now, but no filter
occurs
when I select or enter criteria in my fields.

I did look at Allen's link, but it looks as if it is too "simple". My
options are of greater number than true/false or client/company.

Here is the revised SQL with the reduced number of search criteria.

SELECT tblFiles.[File ID], tblFiles.Entity, tblFiles.Location,
tblFiles.[Record Series], tblFiles.[Document Type], tblFiles.[File Name],
tblFiles.[File Description], tblFiles.[Entered By], tblFiles.[Creation
Date],
tblFiles.[Project Number], tblFiles.[Project Name], tblFiles.[Project
Manager]
FROM tblFiles
WHERE ((Entity=Forms!frmMain!Entity) Or (Forms!frmMain!Entity Is Null))
And
((Location=Forms!frmMain!Location) Or (Forms!frmMain!Location Is Null))
And
(([Record Series]=Forms!frmMain![Record Series]) Or (Forms!frmMain![Record
Series] Is Null)) And (([Document Type]=Forms!frmMain![Document Type]) Or
(Forms!frmMain![Document Type] Is Null)) And (([File
Name]=Forms!frmMain![File Name]) Or (Forms!frmMain![File Name] Is Null))
And
(([File Description]=Forms!frmMain![File Description]) Or
(Forms!frmMain![File Description] Is Null)) And (([Project
Number]=Forms!frmMain![Project Number]) Or (Forms!frmMain![Project Number]
Is
Null)) And (([Project Name]=Forms!frmMain![Project Name]) Or
(Forms!frmMain![Project Name] Is Null));

Thanks for your help!
Ember

Douglas J. Steele said:
Your query is attempting to handle the case where they didn't enter
values
for one of more of the 12 boxes. Allen's suggestion (with which I
heartily
concur) is that you dynamically generate the WHERE clause based on only
those boxes which have a value.

Having said that, did you try my suggestion of using

((FileID=[Forms]![frmMain]![FileID]) Or ([Forms]![frmMain]![FileID] IS
NULL))

rather than

((FileID=[Forms]![frmMain]![FileID]) Or
IsNull([Forms]![frmMain]![FileID])) ?

If so, what does your revised query look like?

Doug Steele, Microsoft Access MVP

(no private e-mails, please)
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Queries & multiple Form inputs 1
Very slow query 3
Criteria to return all records if selection from form is null 3
Parameterised query to return all records or those Between two val 2
PopUp Date Picker Form 4
Getting a query to accept a null value 2
IIF statement in query criteria to have Like * or Is Null as the result if true 5
QUERY MULTIPLE CRITERIA 2

Top