Parameter Query using Custom Diologe Box

[lpgagirl]

I have a query that I want to use to solicit information regarding work orders. the custom diologe box I created has four controls:

1 combo box for technicians name
2 Beginning Date
3 Ending Date
4 combo box for File Number

+ the Cancel and Ok buttons

I would like the user to input required info in all, just one or a combination of them.

The query SQL code I have is:
SELECT tblWorkorders.Year, [tblWorkorder Labor].WorkorderID, [tblWorkorder Labor].Technician, [tblWorkorder Labor].[Technician Hours], tblWorkorders.DateReceived, tblWorkorders.DateFinished, [tblWorkorder Labor].Comment
FROM tblWorkorders INNER JOIN [tblWorkorder Labor] ON tblWorkorders.WorkorderID = [tblWorkorder Labor].WorkorderID
WHERE ((([tblWorkorder Labor].WorkorderID)=[Forms]![frmReportParameters]![cboFileNumber]) AND (([tblWorkorder Labor].Technician)=[Forms]![frmReportParameters]![cboEmployeeName]) AND ((tblWorkorders.DateReceived) Between [Forms]![frmReportParameters]![BeginningDate] And [Forms]![frmReportParameters]![EndingDate])) OR ((([tblWorkorder Labor].Technician)=[Forms]![frmReportParameters]![cboEmployeeName]) AND ((tblWorkorders.DateReceived) Between [Forms]![frmReportParameters]![BeginningDate] And [Forms]![frmReportParameters]![EndingDate]) AND ((([tblWorkorder Labor].[WorkorderID]) Like [Forms]![frmReportParameters]![cboFileNumber]) Is Null)) OR (((tblWorkorders.DateReceived) Between [Forms]![frmReportParameters]![BeginningDate] And [Forms]![frmReportParameters]![EndingDate]) AND ((([tblWorkorder Labor].[Technician]) Like [Forms]![frmReportParameters]![cboEmployeeName]) Is Null)) OR (((tblWorkorders.DateReceived) Between [Forms]![frmReportParameters]![BeginningDate] And [Forms]![frmReportParameters]![EndingDate])) OR ((([tblWorkorder Labor].Technician)=[Forms]![frmReportParameters]![cboEmployeeName]) AND ((([tblWorkorders].[DateReceived]) Like (([tblWorkorders].[DateReceived]) Between [Forms]![frmReportParameters]![BeginningDate] And [Forms]![frmReportParameters]![EndingDate])) Is Null));


Currently the query works when I just select a tech name, I get all workorders where the tech is listed. It also works when I enter the two dates, I get all workorders listed between those two dates. However when I enter a name AND the dates the query ignores the name criteria and lists all workorders between the dates. The file number criteria doesn't work at all.

What am I missing?




Jeannie

http://thefloyds.50megs.com/index.html

 

[PHV]

You may try something like this:
WHERE ([tblWorkorder Labor].WorkorderID=[Forms]![frmReportParameters]![cboFileNumber] OR [Forms]![frmReportParameters]![cboFileNumber] Is Null)
AND ([tblWorkorder Labor].Technician=[Forms]![frmReportParameters]![cboEmployeeName] OR [Forms]![frmReportParameters]![cboEmployeeName] Is Null)
AND (tblWorkorders.DateReceived>=[Forms]![frmReportParameters]![BeginningDate] OR [Forms]![frmReportParameters]![BeginningDate] Is Null)
AND (tblWorkorders.DateReceived)<=[Forms]![frmReportParameters]![EndingDate] OR [Forms]![frmReportParameters]![EndingDate] Is Null)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lpgagirl]

PHV: You ARE the best!



Jeannie

http://thefloyds.50megs.com/index.html