Query With Multiple Wildcards in Multiple Fields

[grendell2099]

Hi all, I need some help creating a search query that has multiple fields & wildcards. I just can't get it working. I created an unbound form with text boxes for each field that a user can search on. the default value is "*" for each box/ criteria.
After the user fills in their criteria, they click search and a results form opens based on the search query. Unfortunately, I do not return any records- even when "*" is set for all the criteria (except the dates).
Can anyone help me straighten this out?

THIS IS THE SQL VIEW OF MY QUERY (I didn't bother to change the "*" to '*' for this posting):
SELECT InspectionRecords.PartNum, InspectionRecords.PartName, InspectionRecords.Weight, InspectionRecords.Project, InspectionRecords.Priority, InspectionRecords.WBS, InspectionRecords.WD, InspectionRecords.InspectDate, InspectionRecords.NumApproved, InspectionRecords.NumNonConform, InspectionRecords.Disposition, InspectionRecords.Vendor, InspectionRecords.Comments, InspectionRecords.InspectionWaived, InspectionRecords.Engineer, InspectionRecords.PoNum, InspectionRecords.ReceivedDate, InspectionRecords.LotSize, InspectionRecords.Hrs, InspectionRecords.Inspector, InspectionRecords.CertConform, InspectionRecords.CertMaterial, InspectionRecords.CertWeld, InspectionRecords.CertFinish, InspectionRecords.InspectReport, InspectionRecords.INDEX
FROM InspectionRecords
WHERE (((InspectionRecords.PartNum)="6004835") AND ((InspectionRecords.PartName) Like "*") AND ((InspectionRecords.Project) Like "*") AND ((InspectionRecords.Priority) Like "*") AND ((InspectionRecords.WBS) Like "*") AND ((InspectionRecords.WD) Like "*") AND ((InspectionRecords.InspectDate) Between "#01/01/2000#" And "#01/01/2040#") AND ((InspectionRecords.Vendor) Like "*") AND ((InspectionRecords.InspectionWaived) Like "*") AND ((InspectionRecords.Engineer) Like "*") AND ((InspectionRecords.PoNum) Like "*") AND ((InspectionRecords.Inspector) Like "*"))
ORDER BY InspectionRecords.InspectDate;

 

[DCrake]

Does it work when you do it in the query itself, if so, examine the sql of the query to check your syntax.

David

 

[grendell2099]

I cannot get it to work from the query builder. I can add Like "*" to a couple of the fields, but then I hit a wall and nothing returns... I have checked and the fields are all text.

 

[SimonB1978]

Hi,

I think your problem is here:

((InspectionRecords.InspectDate) Between "#01/01/2000#" And "#01/01/2040#")

is InspectDate of type Date or Text? If Date, no " , if Text no #.

Simon B.

 

[grendell2099]

the inspectDate is a date field- I will correct that. But it still does not work even if I remove that criteria.

 

[Scooterbug]

Build the SELECT part in query builder....then change the view to Sql. Compare the two and look for anything that is different.

 

[David Eagar]

I have been having similar issues with complex queries (ie multiple wildcards) and seem to have resolved by running a series of sequential queries. It seems to cope with 1 Like per query, but starts to fall over if I have 2 or more per query

 

[boblarson]

Or build it in VBA and if you don't need to limit by that field, leave off the LIKE altogether. You don't need to include fields in the Where that aren't being limited. I would just do something like in this sample to build the where clause.

 

[grendell2099]

I appreciate all of the feedback...
Well, I could not get the thing to work in the query builder. While I was surfing around I found a very nice bit from DCrake that basically parses together a "where" statement based on what a user fills out. That worked very well.

Sorry I haven't figured out how to link, but this is the Dcrake thread I used:
(http://www.access-programmers.co.uk...p?t=168098&highlight=query+multiple+wildcards)

 

[MSAccessRookie]

Hi all, I need some help creating a search query that has multiple fields & wildcards. I just can't get it working. I created an unbound form with text boxes for each field that a user can search on. the default value is "*" for each box/ criteria.
After the user fills in their criteria, they click search and a results form opens based on the search query. Unfortunately, I do not return any records- even when "*" is set for all the criteria (except the dates).
Can anyone help me straighten this out?

THIS IS THE SQL VIEW OF MY QUERY (I didn't bother to change the "*" to '*' for this posting):
SELECT InspectionRecords.PartNum, InspectionRecords.PartName, InspectionRecords.Weight, InspectionRecords.Project, InspectionRecords.Priority, InspectionRecords.WBS, InspectionRecords.WD, InspectionRecords.InspectDate, InspectionRecords.NumApproved, InspectionRecords.NumNonConform, InspectionRecords.Disposition, InspectionRecords.Vendor, InspectionRecords.Comments, InspectionRecords.InspectionWaived, InspectionRecords.Engineer, InspectionRecords.PoNum, InspectionRecords.ReceivedDate, InspectionRecords.LotSize, InspectionRecords.Hrs, InspectionRecords.Inspector, InspectionRecords.CertConform, InspectionRecords.CertMaterial, InspectionRecords.CertWeld, InspectionRecords.CertFinish, InspectionRecords.InspectReport, InspectionRecords.INDEX
FROM InspectionRecords
WHERE (((InspectionRecords.PartNum)="6004835") AND ((InspectionRecords.PartName) Like "*") AND ((InspectionRecords.Project) Like "*") AND ((InspectionRecords.Priority) Like "*") AND ((InspectionRecords.WBS) Like "*") AND ((InspectionRecords.WD) Like "*") AND ((InspectionRecords.InspectDate) Between "#01/01/2000#" And "#01/01/2040#") AND ((InspectionRecords.Vendor) Like "*") AND ((InspectionRecords.InspectionWaived) Like "*") AND ((InspectionRecords.Engineer) Like "*") AND ((InspectionRecords.PoNum) Like "*") AND ((InspectionRecords.Inspector) Like "*"))
ORDER BY InspectionRecords.InspectDate;

Are all (or even any) of the references to ((ColumnName) Like "*") even necessary? I a not sure what the intention of them is, because I thought that the default case was (Like "*"), which would make these redundant and unnecessary. Doesn't the code below have the same effect?

WHERE ((InspectionRecords.PartNum)="6004835") AND 
          ((InspectionRecords.InspectDate) Between #01/01/2000# And " #01/01/2040#)))
ORDER BY InspectionRecords.InspectDate;[/quote]

 

[grendell2099]

I was trying to create a search form that the user could search for a value (or combination of values) in one or more fields. So I assumed if a user did not want to search on "PartNum" for example, that the criteria should "*", and so on. This however did not work for some reason.
Constructing the where statement using DCrake's example got me where I wanted to go, and I must say it is pretty elegant (at least compared to my fumblings).