Searching through with query

[Guest]

Hello all,

Here is my query is SQL

SELECT TblClientData.*
FROM TblClientData
WHERE (((TblClientData.Delivery_Address) Like "*" &
[Forms]![AFrmCustomerSearch].[TxtAddress] & "*") AND ((TblClientData.City)
Like "*" & [Forms]![AFrmCustomerSearch].[TxtCity] & "*") AND
((TblClientData.State) Like "*" & [Forms]![AFrmCustomerSearch].[CmbState] &
"*") AND ((TblClientData.ZIP4) Like "*" &
[Forms]![AFrmCustomerSearch].[TxtZip] & "*"));

With this I had hoped that a user could type in a variey of search criteria
and the query returns the best possible results and it works for the most
part. Unless that is, one of the fields happens to be blank, if that is the
case it renders the whole record unsearchable.

So if I leave all fields on the form blank and click search I would want to
see all records with anything in any of the searched fields but intead I see
all records where the value is not null in all of the searched fields. I
tried adding or is null to the field criteria but that returns too many
results. Is there a way I can do this without creating a ton of small
queries? Thanks!

 

[Guest]

Hi,

You need to add Or Is Nulls to the Where clause.

SELECT TblClientData.*
FROM TblClientData
WHERE ((((TblClientData.Delivery_Address) Like "*" &
[Forms]![AFrmCustomerSearch].[TxtAddress] & "*")
Or (TblClientData.Delivery_Address) Is Null)
and (((TblClientData.City) Like "*" &
[Forms]![AFrmCustomerSearch].[TxtCity] & "*")
Or (TblClientData.City) Is Null)
and (((TblClientData.State) Like "*" &
[Forms]![AFrmCustomerSearch].[CmbState] & "*")
Or (TblClientData.State) Is Null)
and (((TblClientData.ZIP4) Like "*" & [Forms]![AFrmCustomerSearch].[TxtZip]
& "*")
Or (TblClientData.ZIP4) Is Null));

 

[Guest]

Thanks for the Reply Jerry, unfortunately this does not work for me as it
causes the query to return too many results. It forces it to return all blank
fields whether there is another partially matching field or not. One work
around I found was to populate all blank fields with the word "null" though I
do not know if this is considered sloppy db management. The other option I
was considering was to write out the macro's in SQL for every possible way
they could search (I.E. populate address but leave city blank and so on) and
then connect them to a button with a bunch of if statements to fire the
correct query depending on how they search but this would require alot of
time. Just curious if there may be an easier way. Thanks!

Hi,

You need to add Or Is Nulls to the Where clause.

SELECT TblClientData.*
FROM TblClientData
WHERE ((((TblClientData.Delivery_Address) Like "*" &
[Forms]![AFrmCustomerSearch].[TxtAddress] & "*")
Or (TblClientData.Delivery_Address) Is Null)
and (((TblClientData.City) Like "*" &
[Forms]![AFrmCustomerSearch].[TxtCity] & "*")
Or (TblClientData.City) Is Null)
and (((TblClientData.State) Like "*" &
[Forms]![AFrmCustomerSearch].[CmbState] & "*")
Or (TblClientData.State) Is Null)
and (((TblClientData.ZIP4) Like "*" & [Forms]![AFrmCustomerSearch].[TxtZip]
& "*")
Or (TblClientData.ZIP4) Is Null));

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hello all,

Here is my query is SQL

SELECT TblClientData.*
FROM TblClientData
WHERE (((TblClientData.Delivery_Address) Like "*" &
[Forms]![AFrmCustomerSearch].[TxtAddress] & "*") AND ((TblClientData.City)
Like "*" & [Forms]![AFrmCustomerSearch].[TxtCity] & "*") AND
((TblClientData.State) Like "*" & [Forms]![AFrmCustomerSearch].[CmbState] &
"*") AND ((TblClientData.ZIP4) Like "*" &
[Forms]![AFrmCustomerSearch].[TxtZip] & "*"));

With this I had hoped that a user could type in a variey of search criteria
and the query returns the best possible results and it works for the most
part. Unless that is, one of the fields happens to be blank, if that is the
case it renders the whole record unsearchable.

So if I leave all fields on the form blank and click search I would want to
see all records with anything in any of the searched fields but intead I see
all records where the value is not null in all of the searched fields. I
tried adding or is null to the field criteria but that returns too many
results. Is there a way I can do this without creating a ton of small
queries? Thanks!

 

[Pieter Wijnen]

You can always Build The SQL on the Fly...

Sub Apply_Click()
' Assumes Tag Property of All Search fields to be set to "Criteria"
' Also Assumes Controls To have same name as Table Fields (I don't like
prefixes like Txt & cbo or whatever for input Controls)
' However if you're consistent the code is easily adjustable to comply
Dim C AS Access.Control
Dim sC As Variant
Dim Qdef AS DAO.QueryDef
Dim Db AS DAO.Database

sC=Null
Set Db = Access.CurrentDb()
Set Qdef = Db.QueryDefs("qrySearch")
Qdef.SQL = "SELECT * FROM TblClientData A "

For Each C in Me.Controls
If TypeOf C Is Access.TextBox Or TypeOf C Is Access.ComboBox Then
If VBA.Instr(C.Tag,"Criteria") >0 And Not VBA.IsNull(C.Value)
Then
sC = (sC + " AND " ) & "[" & C.Name & "] Like '*Forms![" &
Me.Name & "]![" & C.Name & "]*'"
End If
End If
Next
If Not VBA.IsNull(sC) Then Qdef.SQL = Qdef.SQL & " WHERE " & sC
'....................
'...................

End Sub

HTH

Pieter

Thanks for the Reply Jerry, unfortunately this does not work for me as it
causes the query to return too many results. It forces it to return all
blank
fields whether there is another partially matching field or not. One work
around I found was to populate all blank fields with the word "null"
though I
do not know if this is considered sloppy db management. The other option I
was considering was to write out the macro's in SQL for every possible way
they could search (I.E. populate address but leave city blank and so on)
and
then connect them to a button with a bunch of if statements to fire the
correct query depending on how they search but this would require alot of
time. Just curious if there may be an easier way. Thanks!

Hi,

You need to add Or Is Nulls to the Where clause.

SELECT TblClientData.*
FROM TblClientData
WHERE ((((TblClientData.Delivery_Address) Like "*" &
[Forms]![AFrmCustomerSearch].[TxtAddress] & "*")
Or (TblClientData.Delivery_Address) Is Null)
and (((TblClientData.City) Like "*" &
[Forms]![AFrmCustomerSearch].[TxtCity] & "*")
Or (TblClientData.City) Is Null)
and (((TblClientData.State) Like "*" &
[Forms]![AFrmCustomerSearch].[CmbState] & "*")
Or (TblClientData.State) Is Null)
and (((TblClientData.ZIP4) Like "*" &
[Forms]![AFrmCustomerSearch].[TxtZip]
& "*")
Or (TblClientData.ZIP4) Is Null));

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hello all,

Here is my query is SQL

SELECT TblClientData.*
FROM TblClientData
WHERE (((TblClientData.Delivery_Address) Like "*" &
[Forms]![AFrmCustomerSearch].[TxtAddress] & "*") AND
((TblClientData.City)
Like "*" & [Forms]![AFrmCustomerSearch].[TxtCity] & "*") AND
((TblClientData.State) Like "*" &
[Forms]![AFrmCustomerSearch].[CmbState] &
"*") AND ((TblClientData.ZIP4) Like "*" &
[Forms]![AFrmCustomerSearch].[TxtZip] & "*"));

With this I had hoped that a user could type in a variey of search
criteria
and the query returns the best possible results and it works for the
most
part. Unless that is, one of the fields happens to be blank, if that is
the
case it renders the whole record unsearchable.

So if I leave all fields on the form blank and click search I would
want to
see all records with anything in any of the searched fields but intead
I see
all records where the value is not null in all of the searched fields.
I
tried adding or is null to the field criteria but that returns too many
results. Is there a way I can do this without creating a ton of small
queries? Thanks!