Question Problem with multiple criteria query

[Glimmerman]

Hello,

I have an issue with a multiple criteria query in Access 2007.

I have a database where we do reviews on tickets logged in a helpdesk tool.

Now im building a search form where i can set certain criteria on multiple fields so i can refine my search.
I want to do this by selecting a start and end date and then selecting one or more fields by selecting a value by pull down or text box.


I was able to refer to the controls on my search field without problems for the first 11 fields via a Like [Forms]![frmGraphsCasesCriteria]![txtCaseText] & "*" but when i try to add 2 more i run into problems that whatever value i enter it keeps displaying the result in the query of the 12th field while i have not entered a value for it.

This is wat my query looks like in the design mode:



And SQL view:

SELECT tblCases.casCaseNumber, tblCases.casTitle, tblCases.casOwner, tblCases.casCreateDate, tblCases.casPartNumber, tblCases.casRevision, tblCases.casStatus, tblCases.casResolveGroup, tblCases.casCaseText, tblCases.casSolutionText, tblCases.casRemarks1stLine, tblCases.casRemarks2ndLine, tblCases.casRemarks3rdLine
FROM tblCases

WHERE (((tblCases.casCreateDate) Between [Forms]![frmGraphsCasesCriteria]![txtStartDate] And [Forms]![frmGraphsCasesCriteria]![txtEndDate]) AND ((tblCases.casPartNumber) Like [Forms]![frmGraphsCasesCriteria]![txtPartNumber] & "*") AND ((tblCases.casRevision) Like [Forms]![frmGraphsCasesCriteria]![txtRevision] & "*") AND ((tblCases.casStatus) Like [Forms]![frmGraphsCasesCriteria]![cmbStatus] & "*") AND ((tblCases.casResolveGroup) Like [Forms]![frmGraphsCasesCriteria]![cmbResolveGroup] & "*") AND ((tblCases.casCaseText) Like [Forms]![frmGraphsCasesCriteria]![txtCaseText] & "*") AND ((tblCases.casSolutionText) Like [Forms]![frmGraphsCasesCriteria]![txtSolutionText] & "*") AND ((tblCases.casRemarks1stLine) Like [Forms]![frmGraphsCasesCriteria]![txt1stLineRemarks] & "*") AND ((tblCases.casRemarks2ndLine) Like [Forms]![frmGraphsCasesCriteria]![txt2ndLineRemarks] & "*") AND ((tblCases.casRemarks3rdLine) Like [Forms]![frmGraphsCasesCriteria]![txt3rdLineRemarks] & "*"))
ORDER BY tblCases.casCreateDate;


I hope you can help me as its driving me nuts all day

 

[Mihail]

Kmb is another spam here or another idiot ?

Glimmerman, take a look to this thread.
There are attached two small databases (by me).
The second one contain a query with multiple criteria that seems to be what you are looking for
Take a look to the first one too. Can be something that you wish to learn.

Good luck !

 

[Glimmerman]

thnx ill have a look!

 

[Glimmerman]

Unfortunatly its not working for me, im not getting any results back.
Ive included the criteria query with source table and form from my database, perhaps you or someone else can figure out what im doing wrong then.

Basicly what i want for results is that after i specify the persiod, i want to make a selection from the various other fields you can choose from so i can refine my query.

 

[Mihail]

For the life of me, I can't understand why this refuse to work.
I can obtain what you wish but not by using this approach.
Are you open to other way ?

 

[Glimmerman]

You see what got me puzzeled as well?

Im open to any kind of solution, but my knowledge of VBA is still very limited unfortunatly.

 

[Mihail]

Should I say thank you for this programming test ?
Try this database.

Unfortunately I don't think that will be a useful one.

 

[Glimmerman]

sorry for the late reply, i had some other things to attend to, i will look closely at what you have created and let you know if its applicaple thanks for the effort so far anyway, its really appreciated.

 

[Glimmerman]

Ok it was not really doing it for me but i did manage to setup a table search form that would plow through the whole table by using this in as field criteria:

Like nz([Forms]![frmSearchForValue]![txtSearchfield] & "*")

I used this on all fields as criteria and or

So im going to use this to make a multi criteria input form