Help! Problem with Form-Based Parameter Query

[Scott A]

I'm having problems with the way a form-based parameter
query is handling Null values. I've got three values
passed to the query using a form: two dates (start and
end), and a combo box for departments. I've set the query
to return all values if the cbo is Null, and the query
does return all records if no parameters are entered in
the form.

However, if I enter a start and end date for the query,
but do not select a department from the combo box, the
query does not return any records.

Here's my SQL statement:

SELECT tblLocations.RoomNo, tblDepartments.DeptCode,
tblEquipment.EmpID, tblPurchases.PurchaseDate,
tblManufacturers.ManufName, tblVendors.VendorName,
tblPurchases.OfferDate, tblPurchases.OfferNumber,
tblEquipment.EqName, tblDeliveries.DeliveryDate,
tblPurchases.InvoiceAmount, tblDepartments.DeptName
FROM tblVendors RIGHT JOIN (tblManufacturers RIGHT JOIN
(tblLocations RIGHT JOIN (((tblDepartments RIGHT JOIN
tblEquipment ON tblDepartments.DeptID =
tblEquipment.DeptID) LEFT JOIN tblDeliveries ON
tblEquipment.EqID = tblDeliveries.EqID) LEFT JOIN
tblPurchases ON tblEquipment.EqID = tblPurchases.EqID) ON
tblLocations.LocationID = tblEquipment.LocationID) ON
tblManufacturers.ManufacturerID =
tblEquipment.ManufacturerID) ON tblVendors.VendorID =
tblPurchases.VendorID
WHERE (((tblPurchases.PurchaseDate) Between [Forms]!
[dlgDateParameter2]![txtStartDate] And [Forms]!
[dlgDateParameter2]![txtEndDate]) AND
((tblDepartments.DeptID)=[Forms]![dlgDateParameter2]!
[cboDept])) OR ((([Forms]![dlgDateParameter2]!
[txtStartDate]) Is Null) AND (([Forms]![dlgDateParameter2]!
[cboDept]) Is Null))
ORDER BY tblPurchases.PurchaseDate;


Hopefully someone has advice to offer...

Thanks!

Scott

 

[Michel Walsh]

Hi,


Instead of:

WHERE someDateField BETWEEN starting AND ending

try:

WHERE someDateField BETWEEN Nz(starting, #1-1-1900#) AND Nz(ending,
#1-1-3000#)


so that leaving the starting date parameter null would be equivalent to pick
up everything since 1900, etc.

Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

I'm having problems with the way a form-based parameter
query is handling Null values. I've got three values
passed to the query using a form: two dates (start and
end), and a combo box for departments. I've set the query
to return all values if the cbo is Null, and the query
does return all records if no parameters are entered in
the form.

However, if I enter a start and end date for the query,
but do not select a department from the combo box, the
query does not return any records.

Here's my SQL statement:

SELECT tblLocations.RoomNo, tblDepartments.DeptCode,
tblEquipment.EmpID, tblPurchases.PurchaseDate,
tblManufacturers.ManufName, tblVendors.VendorName,
tblPurchases.OfferDate, tblPurchases.OfferNumber,
tblEquipment.EqName, tblDeliveries.DeliveryDate,
tblPurchases.InvoiceAmount, tblDepartments.DeptName
FROM tblVendors RIGHT JOIN (tblManufacturers RIGHT JOIN
(tblLocations RIGHT JOIN (((tblDepartments RIGHT JOIN
tblEquipment ON tblDepartments.DeptID =
tblEquipment.DeptID) LEFT JOIN tblDeliveries ON
tblEquipment.EqID = tblDeliveries.EqID) LEFT JOIN
tblPurchases ON tblEquipment.EqID = tblPurchases.EqID) ON
tblLocations.LocationID = tblEquipment.LocationID) ON
tblManufacturers.ManufacturerID =
tblEquipment.ManufacturerID) ON tblVendors.VendorID =
tblPurchases.VendorID
WHERE (((tblPurchases.PurchaseDate) Between [Forms]!
[dlgDateParameter2]![txtStartDate] And [Forms]!
[dlgDateParameter2]![txtEndDate]) AND
((tblDepartments.DeptID)=[Forms]![dlgDateParameter2]!
[cboDept])) OR ((([Forms]![dlgDateParameter2]!
[txtStartDate]) Is Null) AND (([Forms]![dlgDateParameter2]!
[cboDept]) Is Null))
ORDER BY tblPurchases.PurchaseDate;


Hopefully someone has advice to offer...

Thanks!

Scott