Multiple Query Criterea and text boxes- If one is blank, no query result

[dhicks19]

Hi, I have a form which is fed by a query. The query gets info from tblworders, the query has multiple criteria that is specified in the text boxes on the form.



Wodate - From and to date
Department
Week
Year
job type
status

All of the above are specified in the form, my problem is if I just want to filter by date (or filter by only one and leave the other text boxes blank there are no results returned even though there should be.

On the query design, if I delete all of the criteria other than the wodate, it will filter by date and return result but as soon as I put in the other criteria it will not bring anything through.

Any reason for this, I thought a query could be filtered by multiple criteria

Thanks

Heres the sql code:

SELECT TblWOrders.WONum, TblWOrders.WODate, TblWOrders.CompBy, TblWOrders.WOtime, TblWOrders.department, TblWOrders.description, TblWOrders.site, TblWOrders.type, TblWOrders.Reason, TblWOrders.person, TblWOrders.JobType, TblWOrders.status, TblWOrders.Signoff, TblWOrders.SOdate, TblWOrders.SOtime, TblWOrders.SOcomments, TblWOrders.week, TblWOrders.pyear, TblWOrders.assetcattop, TblWOrders.risk, TblWOrders.tracker, TblWOrders.ResponsiblePerson, TblWOrders.printed, TblWOrders.plannedmaintadded, TblAssets.Desc
FROM TblAssets INNER JOIN TblWOrders ON TblAssets.AssetNum = TblWOrders.description
WHERE (((TblWOrders.WODate) Between [FROM DATE] And [TO DATE]) AND ((TblWOrders.department)=[forms]![frmwostatusfilter].[department]) AND ((TblWOrders.JobType)=[forms]![frmwostatusfilter].[jobtype]) AND ((TblWOrders.status)=[forms]![frmwostatusfilter].[status]) AND ((TblWOrders.week)=[forms]![frmwostatusfilter].[wweek]) AND ((TblWOrders.pyear)=[forms]![frmwostatusfilter].[wyear]));

[alansidman]

Try this methodology

http://www.datapigtechnologies.com/f...earchform.html

[dhicks19]

Try this methodology

http://www.datapigtechnologies.com/f...earchform.html

Thanks for reply, I have had a look at that video and he is basically doing what I am doing. He has multiple criteria, but he is using "like" which I dont need to use. Other than that I cant really see what else he is doing differently?

[alansidman]

He is using like to allow him to use the wildcard "*" which gives you more flexibility. If you don't have all your search boxes entered with data, then without the wildcard, you will continue to bomb out. Currently your SQL is telling Access to look in the search box and if there is nothing there, it will return nothing.

[dhicks19]

He is using like to allow him to use the wildcard "*" which gives you more flexibility. If you don't have all your search boxes entered with data, then without the wildcard, you will continue to bomb out. Currently your SQL is telling Access to look in the search box and if there is nothing there, it will return nothing.

Ah i see, so if i use wild card * it should be ok? Do I need to use wild card on all of the criterias?

Thanks for your help

[alansidman]

Yes. Just like Mike Alexander did in the video.

[dhicks19]

Yes. Just like Mike Alexander did in the video.

I did what he did in the video and still having the exact same issue, if i have more than the date criteria it wont filter on date at all. However the other criteria work fine and filters on them, it just wont filter the date unless the date is the only criteria even with the wildcard and like statement.

[orange]

My suspicion is that if there is No Date, there will be no result. Not tested.

[alansidman]

Time to post a copy of your db with some sample data so we can diagnose it. Dummy up any confidential data. Run a compact and repair. Save as an .mdb file to reach the broadest audience and then upload.