Haveing an issues with query not limiting output to dates.

[PuddinPie]

Hello.

I have a form where the user puts in a start date and and end date and selects an employee. It runs a query and makes a table and that table is used in a report.
The issues I am having is that the user selects a start date and end date and name and it displays the information for thouse date ranges. When they leave the name blank it show all names, which is what I want it to do but it also shows all dates instead of the ranges. I have an AND critera in for the workdate and looking at the forms start and end date and an is null statement for the name.
Here's the code I'm working with.

SELECT quniDataSYS1.UserID, quniDataSYS1.SystemID, quniDataSYS1.WorkDate, quniDataSYS1.DateStamp, quniDataSYS2.TrxTypeID, quniDataSYS2.TrxTypeValue, qryUserActive.CommaName, tblTrxType.TrxType INTO tblUsageAllData
FROM ((quniDataSYS1 INNER JOIN quniDataSYS2 ON (quniDataSYS1.SystemID = quniDataSYS2.SystemID) AND (quniDataSYS1.PAutoID = quniDataSYS2.PAutoID)) INNER JOIN qryUserActive ON quniDataSYS1.UserID = qryUserActive.UserID) INNER JOIN tblTrxType ON quniDataSYS2.TrxTypeID = tblTrxType.TrxTypeID
WHERE (((quniDataSYS1.WorkDate)>=[Forms]![frmDataUsage]![cboStartDate] And (quniDataSYS1.WorkDate)<=[Forms]![frmDataUsage]![cboEndDate]) AND ((qryUserActive.CommaName)=[Forms]![frmDataUsage]![cboEmployee])) OR (((quniDataSYS1.WorkDate) Is Null) AND (([forms]![frmDataUsage]![cboEmployee]) Is Null));

Please let me know if you see anything off.
Thank you.

 

[vbaInet]

You need to use BETWEEN. But I don't understand why you're making a table for use in a report?

 

[PuddinPie]

Where would and how would I use BETWEEN?
It's not a report, report. It's an exported to excel report.

 

[vbaInet]

You can still export the resulting query.

WHERE (Date1 BETWEEN #1/2/2010# AND #10/10/2010#) AND ...

For brevity, enclose it in brackets like I've done. I would advise you play with static values like I've done before doing it in code.

 

[PuddinPie]

I have it now set like this.

SELECT quniDataSYS1.UserID, quniDataSYS1.SystemID, quniDataSYS1.DateStamp, quniDataSYS2.TrxTypeID, quniDataSYS2.TrxTypeValue, qryUserActive.CommaName, tblTrxType.TrxType, [WorkingDate] AS Expr1 INTO tblUsageAllData
FROM ((quniDataSYS1 INNER JOIN quniDataSYS2 ON (quniDataSYS1.PAutoID = quniDataSYS2.PAutoID) AND (quniDataSYS1.SystemID = quniDataSYS2.SystemID)) INNER JOIN qryUserActive ON quniDataSYS1.UserID = qryUserActive.UserID) INNER JOIN tblTrxType ON quniDataSYS2.TrxTypeID = tblTrxType.TrxTypeID
WHERE (((qryUserActive.CommaName)=[Forms]![frmDataUsage]![cboEmployee]) AND (([WorkingDate]) Between [Forms]![frmDataUsage]![cboStartDate] And [Forms]![frmDataUsage]![cboEndDate])) OR ((([forms]![frmDataUsage]![cboEmployee]) Is Null));

Is that what you were talking about?

 

[PuddinPie]

Ok. I figured it out. I neede to put the same
>=[Forms]![frmDataUsage]![cboStartDate] And <=[Forms]![frmDataUsage]![cboEndDate]
on the or line with the Is Null and it work fine.

 

[vbaInet]

It's always about getting the bracketting right.

Glad you got that sorted.