Query for date value - Has Date or Blank

[spectraflame]

I have a form that allows users to create a service order. It gives them the ability with a drop down date picker box to select a scheduled date or leave it blank.

I have a reporting form that allows for multiple criteria to be defined which displays records that match the criteria on a report.

Within that query I was thinking about using something along the lines of the following code to determine whether or not the service order has been scheduled, but I am not sure how to accomplish this. I need to be able to generate a report of all orders based on scheduling status, but I am not sure how to do this either. Is there an easier way?

IIf([Forms]![ReportMenufrm]![Schedopt]=3,"*",IIf([Forms]![ReportMenufrm]![Schedopt]=2,"Scheduled","Non-Scheduled")

In the table, the Scheduled Date is formatted as a date, but is not required if that is of any help.

Thanks for the assistance.
Matthew

 

[spectraflame]

I have been thinking of another approach, but not sure how to accomplish this either. This may be easier for someone to understand and may be easier to implement.

I have a START and END date range on my report criteria form that is based on Scheduled date. If a service order is within this range, it is displayed on the report. If it is not, then it is not displayed on the report. These boxes are just UNBOUND text boxes that my query references based on name.

With that above in mind, would it be possible to to have check boxes and text boxes somehow related to one another? Meaning that if a range is defined, it would be understood that I am wanting a report for all service orders between START and END date. Then just below those 2 text boxes, have a check box called "NON SCHEDULED". When that box is checked, the 2 text boxes are grayed out and not included in my query so that the result would display all service orders without a schedule date.

I guess my query would have to then be modified so that it would or would not include the START and END dates but that is something else to worry about at a later date.

If anyone has any better ideas, I am open to anything. Below is the query that I am using in conjunction with the reporting form.

SELECT [tblWorkOrder].[RecordID], [tblWorkOrder].[WOID], [tblWorkOrder].[WOType], [tblDept].[DeptID], Int([EntryDate]) AS dDate, [tblWorkOrder].[EntryDate], [tblWorkOrder].[Status], [tblWorkOrder].[Callout], [tblWorkOrder].[ScheduledDate], [tblWorkOrder].[Labor], [tblWorkOrder].[Project], [tblWorkOrder].[Tracking], [tblWorkOrder].[Truck], [tblWorkOrder].[Name], [tblWorkOrder].[Address], [tblWorkOrder].[IssuedBy], [tblWorkOrder].[WorkDetail], [tblWorkOrder].[WorkResults], [tblWorkOrder].[ReceivedBy], IIf(IsNull([ScheduledDate]),1,2) AS DateCheck
FROM tblDept INNER JOIN tblWorkOrder ON [tblDept].[DeptName]=[tblWorkOrder].[WOType]
WHERE ((([tblDept].[DeptID]) Like IIf([Forms]![ReportMenufrm]![Departmentopt]=6,"*",[Forms]![ReportMenufrm]![Departmentopt])) And ((Int([ScheduledDate])) Between [Forms]![ReportMenufrm]![DateFrom] And [Forms]![ReportMenufrm]![DateTo]) And (([tblWorkOrder].[Status]) Like IIf([Forms]![ReportMenufrm]![Statusopt]=3,"*",IIf([Forms]![ReportMenufrm]![Statusopt]=1,"Open","Closed"))) And (([tblWorkOrder].[Callout]) Like IIf([Forms]![ReportMenufrm]![Calloutopt]=3,"*",IIf([Forms]![ReportMenufrm]![Calloutopt]=1,"No","Yes"))) And (([tblWorkOrder].[Project]) Like IIf(IsNull([Forms]![ReportMenufrm]![cboProjectID]),"*",[Forms]![ReportMenufrm]![cboProjectID])) And (([tblWorkOrder].[Tracking]) Like IIf(IsNull([Forms]![ReportMenufrm]![cboTrackingID]),"*",[Forms]![ReportMenufrm]![cboTrackingID])) And ((IIf(IsNull([ScheduledDate]),1,2)) Like IIf([Forms]![ReportMenufrm]![Schedopt]=3,"*",IIf([Forms]![ReportMenufrm]![Schedopt]=1,1,""))))
WITH OWNERACCESS OPTION;

 

[cstlaurent]

First built the report without criteria. Than write some code.

dim strfilter as string

if chkbox = true
strfilter = "startdate is null
else
strfilter = "startdate >= txtstart and enddate <= txtendate"

sure you need some VBA Basic for this

 

[cstlaurent]

and with docmd


DoCmd.OpenReport stDocName, acPreview, , strfilter

I forgot, you have to declare at the beginning the report

dim stdocname as string

stdocname = "Name of the report"

sorry ,

 

[spectraflame]

As far as the report goes, its Record Source is the query (REPORTQRY) but it does have some text boxes that display the selected options from the Reporting form in the header of the report. The rest of the information on the report is populated by the query.

Would it just be easier to have two separate queries: One for all of the selected options and non-scheduled orders based on the check box and then one for all selected options based on a date range without the box checked? I would then just have to have the button setup so that it will run the correct query based on the check box.

This may be what you have suggested to me, but I am very new to Access and this is my first real attempt at database design.

Matthew