Between Date

[Guest]

I have a query which i would like have a parameter use when a user runs the
query it would promt for a date between recvd date and invdate. Though some
records do have null values in the date field, i want to be sure that the
quey picks up all records. How is this set up?

 

[Guest]

If I understand you correctly try something like this:

PARAMETERS [Enter date:] DATETIME;
SELECT *
FROM [YourTable]
WHERE [recvd date] < [Enter date:] + 1
AND ([invdate] >= [Enter date:]
OR [invdate] IS NULL);

I've assumed that it’s the invdate which might be NULL, not the recvd date,
which seems logical to me at least. If its possible either or both could be
NULL then the WHERE clause would be:

WHERE ([recvd date] < [Enter date:] + 1
OR [recvd date] IS NULL)
AND ([invdate] >= [Enter date:]
OR [invdate] IS NULL);

By testing for the recvd date being before one day after the parameter value
it caters for the possibility of inv date having a non-zero time of day
element. Note that with date/time parameters its prudent to declare them as
a value in short date format might otherwise be misinterpreted as an
arithmetical expression and give the wrong result.

Ken Sheridan
Stafford, England

 

[Guest]

Thanks Ken. That worked and i am also using the following for one field:
SELECT VendorInv.EnterDate, *
FROM VendorInv
Where RecvdDate Between IIf([Enter Start Date] Is Null,#1/01/1800#,[Enter
Start Date]) And IIf([Enter End Date] Is Null,#1/01/2300#,[Enter End Date])

Problems is that parameter asks for two prompts of date when all i need is
check between two dates and return results even if null.How can this be
configured for one prompt?

If I understand you correctly try something like this:

PARAMETERS [Enter date:] DATETIME;
SELECT *
FROM [YourTable]
WHERE [recvd date] < [Enter date:] + 1
AND ([invdate] >= [Enter date:]
OR [invdate] IS NULL);

I've assumed that it’s the invdate which might be NULL, not the recvd date,
which seems logical to me at least. If its possible either or both could be
NULL then the WHERE clause would be:

WHERE ([recvd date] < [Enter date:] + 1
OR [recvd date] IS NULL)
AND ([invdate] >= [Enter date:]
OR [invdate] IS NULL);

By testing for the recvd date being before one day after the parameter value
it caters for the possibility of inv date having a non-zero time of day
element. Note that with date/time parameters its prudent to declare them as
a value in short date format might otherwise be misinterpreted as an
arithmetical expression and give the wrong result.

Ken Sheridan
Stafford, England

I have a query which i would like have a parameter use when a user runs the
query it would promt for a date between recvd date and invdate. Though some
records do have null values in the date field, i want to be sure that the
quey picks up all records. How is this set up?

 

[Guest]

You'll need to create an unbound dialogue form and reference controls on the
form as the parameters. The report would be opened from a button on the form
( or you might have two buttons, one to preview the report, one to print it),
so instead of opening the report you'd open the form, enter the dates and
click the button.

Create the dialogue form and put two text boxes, txtStart and txtEnd on it,
labelling them appropriately. Add a button to the form to open the report.
You don't need to test for the artificially low and high dates to return all
rows, you can simply examine the parameters for NULL in parenthesised Boolean
OR operations:

PARAMETERS
Forms![YourForm]![txtStart] DATETIME,
Forms![YourForm]![txtEnd] DATETIME;
SELECT *
FROM VendorInv
WHERE (RecvdDate >= Forms![YourForm]![txtStart]
OR Forms![YourForm]![txtStart] IS NULL])
AND (RecvdDate < Forms![YourForm]![txtEnd]+1
OR Forms![YourForm]![txtEnd] IS NULL]);

As in my earlier example testing for the recvd date being before one day
after the parameter value rather than using a BETWEEN….AND operation is more
bullet-proof as it caters for the possibility of a date/time value
inadvertently having a non-zero time of day element. If a BETWEEN….AND is
used any rows containing date/time values with a non-zero time of day (which
would not be readily apparent if the values are formatted in a date format)
would not be returned. Also note that I've declared the parameters again.

Ken Sheridan
Stafford, England

Thanks Ken. That worked and i am also using the following for one field:
SELECT VendorInv.EnterDate, *
FROM VendorInv
Where RecvdDate Between IIf([Enter Start Date] Is Null,#1/01/1800#,[Enter
Start Date]) And IIf([Enter End Date] Is Null,#1/01/2300#,[Enter End Date])

Problems is that parameter asks for two prompts of date when all i need is
check between two dates and return results even if null.How can this be
configured for one prompt?

If I understand you correctly try something like this:

PARAMETERS [Enter date:] DATETIME;
SELECT *
FROM [YourTable]
WHERE [recvd date] < [Enter date:] + 1
AND ([invdate] >= [Enter date:]
OR [invdate] IS NULL);

I've assumed that it’s the invdate which might be NULL, not the recvd date,
which seems logical to me at least. If its possible either or both could be
NULL then the WHERE clause would be:

WHERE ([recvd date] < [Enter date:] + 1
OR [recvd date] IS NULL)
AND ([invdate] >= [Enter date:]
OR [invdate] IS NULL);

By testing for the recvd date being before one day after the parameter value
it caters for the possibility of inv date having a non-zero time of day
element. Note that with date/time parameters its prudent to declare them as
a value in short date format might otherwise be misinterpreted as an
arithmetical expression and give the wrong result.

Ken Sheridan
Stafford, England

I have a query which i would like have a parameter use when a user runs the
query it would promt for a date between recvd date and invdate. Though some
records do have null values in the date field, i want to be sure that the
quey picks up all records. How is this set up?