Query to Return Between And Dates and Unknown Date

[Elizabeth]

I have a parameter query to find reports in a database.
I need to find reports between one date and another date,
and to include all Unknown dates.

Under the field Item Type, I have [Enter Item Type].
Under the Date field, I have on first line Between [Enter
First Date] And [Enter Last Date].
On the criteria line below, I have "Unknown".
Unfortunately, this returns all Unknowns and I only what
Unknowns for the specific Item Type. Can anyone tell me
how to write this?

 

[Ken Snell]

I assume that the date field is formatted as Date/Time. So that makes me
curious: when you say "unknown" date, do you mean that those records contain
the text "Unknown" in the date field? Or do you mean that the date field
contains no value at all when it's Unknown?

 

[John Vinson]

I have a parameter query to find reports in a database.
I need to find reports between one date and another date,
and to include all Unknown dates.

"Unknown" is not a valid value for a Date. Are you storing these
"dates" in a Text field?

Under the field Item Type, I have [Enter Item Type].
Under the Date field, I have on first line Between [Enter
First Date] And [Enter Last Date].
On the criteria line below, I have "Unknown".

Unfortunately, this returns all Unknowns and I only what
Unknowns for the specific Item Type. Can anyone tell me
how to write this?

Put the [Enter Item Type] criterion on BOTH lines of the query grid.

 

[Elizabeth]

Hi John,

This database is used for a Lost & Found operation. We
get reports of lost property and we receive all property
that is found. We try to match found items with lost
reports so we can send the property back to its owner.

The Date field is a text field. "Unknown" is entered
into all reports when the date is unknown.

Re: [Enter Item Type] criterion on both lines of the
query grid. Do you mean in the date field? If so, how
do I word the criteria? (Between [Enter First Date] And
[Enter Last Date][Item Type])? and ("Unknown"[Item
Type]? I do not have a good grasp when it comes to
wording criteria.

The SQL statement follows:

SELECT [Main Table Information].[Item Type], [Main Table
Information].[Lost/Found Date], [Main Table Information].
[Item Brand], [Main Table Information].[Item Model],
[Main Table Information].[Item Color], [Main Table
Information].[Item Notes], [Main Table Information].
[Lost/Found Location], [Main Table Information].[Item
Number]
FROM [Main Table Information]
WHERE ((([Main Table Information].[Item Type])=[Enter
Item Type]) AND (([Main Table Information].[Lost/Found
Date]) Between [Enter First Date] And [Enter Last Date])
AND (([Main Table Information].Status)="Found Item")) OR
((([Main Table Information].[Lost/Found Date])="Unknown")
AND (("Unknown")=[Enter Item Type]))
ORDER BY [Main Table Information].[Item Type], [Main
Table Information].[Lost/Found Date], [Main Table
Information].[Item Brand];

Elizabeth

-----Original Message-----

I have a parameter query to find reports in a database.
I need to find reports between one date and another date,
and to include all Unknown dates.

"Unknown" is not a valid value for a Date. Are you storing these
"dates" in a Text field?

Under the field Item Type, I have [Enter Item Type].
Under the Date field, I have on first line Between [Enter
First Date] And [Enter Last Date].
On the criteria line below, I have "Unknown".

Unfortunately, this returns all Unknowns and I only what
Unknowns for the specific Item Type. Can anyone tell me
how to write this?

Put the [Enter Item Type] criterion on BOTH lines of the query grid.



.

 

[Ken Snell]

This database is used for a Lost & Found operation. We
get reports of lost property and we receive all property
that is found. We try to match found items with lost
reports so we can send the property back to its owner.

The Date field is a text field. "Unknown" is entered
into all reports when the date is unknown.

The SQL statement follows:

SELECT [Main Table Information].[Item Type], [Main Table
Information].[Lost/Found Date], [Main Table Information].
[Item Brand], [Main Table Information].[Item Model],
[Main Table Information].[Item Color], [Main Table
Information].[Item Notes], [Main Table Information].
[Lost/Found Location], [Main Table Information].[Item
Number]
FROM [Main Table Information]
WHERE ((([Main Table Information].[Item Type])=[Enter
Item Type]) AND (([Main Table Information].[Lost/Found
Date]) Between [Enter First Date] And [Enter Last Date])
AND (([Main Table Information].Status)="Found Item")) OR
((([Main Table Information].[Lost/Found Date])="Unknown")
AND (("Unknown")=[Enter Item Type]))
ORDER BY [Main Table Information].[Item Type], [Main
Table Information].[Lost/Found Date], [Main Table
Information].[Item Brand];

Thanks.

Elizabeth

-----Original Message-----
I assume that the date field is formatted as Date/Time. So that makes me
curious: when you say "unknown" date, do you mean that those records contain
the text "Unknown" in the date field? Or do you mean that the date field
contains no value at all when it's Unknown?

--
Ken Snell
<MS ACCESS MVP>

I have a parameter query to find reports in a database.
I need to find reports between one date and another date,
and to include all Unknown dates.

Under the field Item Type, I have [Enter Item Type].
Under the Date field, I have on first line Between [Enter
First Date] And [Enter Last Date].
On the criteria line below, I have "Unknown".
Unfortunately, this returns all Unknowns and I only what
Unknowns for the specific Item Type. Can anyone tell me
how to write this?

.

 

[Ken Snell]

Try this SQL (it changes the [Lost/Found Date] into a date value before it's
compared to the dates entered):

SELECT [Main Table Information].[Item Type], [Main Table
Information].[Lost/Found Date], [Main Table Information].
[Item Brand], [Main Table Information].[Item Model],
[Main Table Information].[Item Color], [Main Table
Information].[Item Notes], [Main Table Information].
[Lost/Found Location], [Main Table Information].[Item
Number]
FROM [Main Table Information]
WHERE ((([Main Table Information].[Item Type])=[Enter
Item Type]) AND ((IIf(IsDate([Main Table Information].[Lost/Found
Date]),CDate([Main Table Information].[Lost/Found
Date]),[Main Table Information].[Lost/Found
Date])) Between [Enter First Date] And [Enter Last Date])
AND (([Main Table Information].Status)="Found Item")) OR
((([Main Table Information].[Lost/Found Date])="Unknown")
AND (("Unknown")=[Enter Item Type]))
ORDER BY [Main Table Information].[Item Type], [Main
Table Information].[Lost/Found Date], [Main Table
Information].[Item Brand];



--
Ken Snell
<MS ACCESS MVP>

This database is used for a Lost & Found operation. We
get reports of lost property and we receive all property
that is found. We try to match found items with lost
reports so we can send the property back to its owner.

The Date field is a text field. "Unknown" is entered
into all reports when the date is unknown.

The SQL statement follows:

SELECT [Main Table Information].[Item Type], [Main Table
Information].[Lost/Found Date], [Main Table Information].
[Item Brand], [Main Table Information].[Item Model],
[Main Table Information].[Item Color], [Main Table
Information].[Item Notes], [Main Table Information].
[Lost/Found Location], [Main Table Information].[Item
Number]
FROM [Main Table Information]
WHERE ((([Main Table Information].[Item Type])=[Enter
Item Type]) AND (([Main Table Information].[Lost/Found
Date]) Between [Enter First Date] And [Enter Last Date])
AND (([Main Table Information].Status)="Found Item")) OR
((([Main Table Information].[Lost/Found Date])="Unknown")
AND (("Unknown")=[Enter Item Type]))
ORDER BY [Main Table Information].[Item Type], [Main
Table Information].[Lost/Found Date], [Main Table
Information].[Item Brand];

Thanks.

Elizabeth

-----Original Message-----
I assume that the date field is formatted as Date/Time. So that makes me
curious: when you say "unknown" date, do you mean that those records contain
the text "Unknown" in the date field? Or do you mean that the date field
contains no value at all when it's Unknown?

--
Ken Snell
<MS ACCESS MVP>

I have a parameter query to find reports in a database.
I need to find reports between one date and another date,
and to include all Unknown dates.

Under the field Item Type, I have [Enter Item Type].
Under the Date field, I have on first line Between [Enter
First Date] And [Enter Last Date].
On the criteria line below, I have "Unknown".
Unfortunately, this returns all Unknowns and I only what
Unknowns for the specific Item Type. Can anyone tell me
how to write this?

.