Form Filter

[Guest]

Hi - I need to create a form where users can select certain data; for
example: cases opened between two dates. They should be able to enter a
start date for the search in one box and the through date in another box. I
need the results returned in a datasheet view. Can anybody help?
Thanks
Tammy

 

[Guest]

Tammy:

Create a query which references the text boxes on your form as parameters.
With dates it’s a good idea to declare the parameters in the query as a value
in a text box with a date in short date format can be interpreted as an
arithmetical expression and give the wrong results. So a query which
returned the matching rows in descending date order query would go something
like this:

PARAMETERS
Forms!YourForm!txtStart DATETIME,
Forms!YourForm!xtEnd DATETIME;
SELECT *
FROM Your Table
WHERE YourDateField >= Forms!YourForm!txtStart
AND YourDateField < Forms!YourForm!txtEnd + 1
ORDER BY YourDateField DESC;

The syntax used above for determining the date range is more reliable than a
BETWEEN…..AND operation as it caters for poor quality data where a 'date'
might include a non-zero time of day (which normally you would not see unless
the date/time value is formatted to do so). With a BETWEEN…..AND operation
dates on the last day of the range would not be returned if they included a
non-zero time of day. There is actually no such thing as a date value in
Access only a date/time value. A date entered without a time has a time of
day of zero, i.e. midnight at the start of the day; and a time entered
without a date is actually a time on 30 December 1899, which is day-zero in
Access's date/time implementation.

Then with a button on the form open the query:

DoCmd.OpenQuery "YourQueryName"

Or you can get a more elegantly presented result by opening a report based
on the query:

DoCmd.OpenReport "YourReportName", View:=acViewPreview

Ken Sheridan
Stafford, England

 

[Guest]

Ken - I typed in the parameters area of a new query
Forms!Issues1!OpenedDate1 on the first line, then
Forms!Issues1!OpenedDate3 on the second line both datatypes date/time.

then the design view of the query builder I typed:

WHERE [OpenedDate] >=Forms!Issues1!OpenedDate1 AND [OpenedDate] <
Forms!Issues1!OpenedDate3 +1 ORDER BY [OpenedDate] DESC

It's not workingn can you please help?

 

[Guest]

Tammy:

In query design view you don’t need to enter the WHERE keyword or the name
of the field the first time round; you only do that if entering it directly
in SQL view; so in the criteria row of the Opened Date column enter the
following as a single uninterrupted line:

= Forms!Issues1!OpenedDate1 AND [OpenedDate] < Forms!Issues1!OpenedDate3 +1

To sort by date descending select 'Descending' in the 'Sort' row of the
OpendedDate column in design view.

You'll find that Access will put square brackets around the object names in
the criteria row. To be honest I'm not sure whether you need to do the same
in the parameters dialogue. I most of the time write queries in SQL, so am
unfamiliar with this. If in doubt put square brackets around the object
names in the parameters dialogue so they match exactly with those in the
criteria.

Ken Sheridan
Stafford, England

Ken - I typed in the parameters area of a new query
Forms!Issues1!OpenedDate1 on the first line, then
Forms!Issues1!OpenedDate3 on the second line both datatypes date/time.

then the design view of the query builder I typed:

WHERE [OpenedDate] >=Forms!Issues1!OpenedDate1 AND [OpenedDate] <
Forms!Issues1!OpenedDate3 +1 ORDER BY [OpenedDate] DESC

It's not workingn can you please help?

Tammy:

Create a query which references the text boxes on your form as parameters.
With dates it’s a good idea to declare the parameters in the query as a value
in a text box with a date in short date format can be interpreted as an
arithmetical expression and give the wrong results. So a query which
returned the matching rows in descending date order query would go something
like this:

PARAMETERS
Forms!YourForm!txtStart DATETIME,
Forms!YourForm!xtEnd DATETIME;
SELECT *
FROM Your Table
WHERE YourDateField >= Forms!YourForm!txtStart
AND YourDateField < Forms!YourForm!txtEnd + 1
ORDER BY YourDateField DESC;

The syntax used above for determining the date range is more reliable than a
BETWEEN…..AND operation as it caters for poor quality data where a 'date'
might include a non-zero time of day (which normally you would not see unless
the date/time value is formatted to do so). With a BETWEEN…..AND operation
dates on the last day of the range would not be returned if they included a
non-zero time of day. There is actually no such thing as a date value in
Access only a date/time value. A date entered without a time has a time of
day of zero, i.e. midnight at the start of the day; and a time entered
without a date is actually a time on 30 December 1899, which is day-zero in
Access's date/time implementation.

Then with a button on the form open the query:

DoCmd.OpenQuery "YourQueryName"

Or you can get a more elegantly presented result by opening a report based
on the query:

DoCmd.OpenReport "YourReportName", View:=acViewPreview

Ken Sheridan
Stafford, England