Query filter problem

[scubadiver]

Hi folks,

I have a query with the following criteria which are based on a form with
two unbound text boxes:

=[forms]![CST Reports]![datefrom] And <=[forms]![CST reports]![dateto].

The column that uses this criteria is based on a field with the "general
time/date" format and I am using

Date: (Format([Time_Admin],"Short Date"))

to get the date.

When i enter a couple of dates into the form and open the query no records
are retrieved (I am using the correct dates).

What could be wrong.

thanks.

 

[Allen Browne]

Could be several factors, e.g.:

A) You may have left one the the dates blank on your form.

B) If the cursor is still in one of the text boxes on your form, the Value
of the text box may not have been updated, and so no match is found.

C) Your date/time field may contain a time component that means it is not
less than or equal to the last date you specied.

D) Access may have misinterpreted the dates. To avoid this, set the Format
property of the unbound text boxes to:
Short Date
or similar, and explicitly delcare the 2 paramters in the query (Parameters
on Query menu) so you can indicate their type. More info:
http://allenbrowne.com/ser-45.html
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi folks,

I have a query with the following criteria which are based on a form with
two unbound text boxes:

=[forms]![CST Reports]![datefrom] And <=[forms]![CST reports]![dateto].

The column that uses this criteria is based on a field with the "general
time/date" format and I am using

Date: (Format([Time_Admin],"Short Date"))

to get the date.

When i enter a couple of dates into the form and open the query no records
are retrieved (I am using the correct dates).

What could be wrong.

thanks.

 

[scubadiver]

thanks

A) It isn't this one
B) Or this one
C) That sounds logical but I am assuming the "short date" format would
exclude the time component completely and the parameter would include the
data regardless of the time.

D) Unbound forms are in "short date" format.

As far as the parameters go I have inserted

=[forms]![CST Reports]![datefrom] on the first line

<=[forms]![CST Reports]![dateto] on the second line

and changed the format. Is this correct?

If I put the parameter on one line I get an "invalid bracketing" error
message.

Could be several factors, e.g.:

A) You may have left one the the dates blank on your form.

B) If the cursor is still in one of the text boxes on your form, the Value
of the text box may not have been updated, and so no match is found.

C) Your date/time field may contain a time component that means it is not
less than or equal to the last date you specied.

D) Access may have misinterpreted the dates. To avoid this, set the Format
property of the unbound text boxes to:
Short Date
or similar, and explicitly delcare the 2 paramters in the query (Parameters
on Query menu) so you can indicate their type. More info:
http://allenbrowne.com/ser-45.html
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi folks,

I have a query with the following criteria which are based on a form with
two unbound text boxes:

=[forms]![CST Reports]![datefrom] And <=[forms]![CST reports]![dateto].

The column that uses this criteria is based on a field with the "general
time/date" format and I am using

Date: (Format([Time_Admin],"Short Date"))

to get the date.

When i enter a couple of dates into the form and open the query no records
are retrieved (I am using the correct dates).

What could be wrong.

thanks.

 

[scubadiver]

It has occurred to me that I have been entering "26/03/2007" in "Datefrom"
and "04/04/2007" in "DateTo".

Sorted in ascending order "04/04/2007" comes before "26/03/2007" so I
changed the dates around and now I am getting records, BUT I am only getting
records for these two dates.

Could be several factors, e.g.:

A) You may have left one the the dates blank on your form.

B) If the cursor is still in one of the text boxes on your form, the Value
of the text box may not have been updated, and so no match is found.

C) Your date/time field may contain a time component that means it is not
less than or equal to the last date you specied.

D) Access may have misinterpreted the dates. To avoid this, set the Format
property of the unbound text boxes to:
Short Date
or similar, and explicitly delcare the 2 paramters in the query (Parameters
on Query menu) so you can indicate their type. More info:
http://allenbrowne.com/ser-45.html
http://allenbrowne.com/ser-36.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi folks,

I have a query with the following criteria which are based on a form with
two unbound text boxes:

=[forms]![CST Reports]![datefrom] And <=[forms]![CST reports]![dateto].

The column that uses this criteria is based on a field with the "general
time/date" format and I am using

Date: (Format([Time_Admin],"Short Date"))

to get the date.

When i enter a couple of dates into the form and open the query no records
are retrieved (I am using the correct dates).

What could be wrong.

thanks.

 

[Allen Browne]

I'm not sure you entered the parameters:

1. In query design view, choose Parameters on the Query menu.
Access opens the parameter dialog.

2. Enter 2 rows like this:
[forms]![CST Reports]![datefrom] Date
[forms]![CST reports]![dateto] Date

If the query results are sorting as text, do you have this criteria under a
date/time field? Under a Text field? Under a calculated field?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

It has occurred to me that I have been entering "26/03/2007" in "Datefrom"
and "04/04/2007" in "DateTo".

Sorted in ascending order "04/04/2007" comes before "26/03/2007" so I
changed the dates around and now I am getting records, BUT I am only
getting
records for these two dates.

Could be several factors, e.g.:

A) You may have left one the the dates blank on your form.

B) If the cursor is still in one of the text boxes on your form, the
Value
of the text box may not have been updated, and so no match is found.

C) Your date/time field may contain a time component that means it is not
less than or equal to the last date you specied.

D) Access may have misinterpreted the dates. To avoid this, set the
Format
property of the unbound text boxes to:
Short Date
or similar, and explicitly delcare the 2 paramters in the query
(Parameters
on Query menu) so you can indicate their type. More info:
http://allenbrowne.com/ser-45.html
http://allenbrowne.com/ser-36.html

Hi folks,

I have a query with the following criteria which are based on a form
with
two unbound text boxes:

=[forms]![CST Reports]![datefrom] And <=[forms]![CST reports]![dateto].

The column that uses this criteria is based on a field with the
"general
time/date" format and I am using

Date: (Format([Time_Admin],"Short Date"))

to get the date.

When i enter a couple of dates into the form and open the query no
records
are retrieved (I am using the correct dates).

What could be wrong.

thanks.

 

[scubadiver]

I still get an "invalid bracketing" error.

I'm not sure you entered the parameters:

1. In query design view, choose Parameters on the Query menu.
Access opens the parameter dialog.

2. Enter 2 rows like this:
[forms]![CST Reports]![datefrom] Date
[forms]![CST reports]![dateto] Date

If the query results are sorting as text, do you have this criteria under a
date/time field? Under a Text field? Under a calculated field?

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

It has occurred to me that I have been entering "26/03/2007" in "Datefrom"
and "04/04/2007" in "DateTo".

Sorted in ascending order "04/04/2007" comes before "26/03/2007" so I
changed the dates around and now I am getting records, BUT I am only
getting
records for these two dates.

Could be several factors, e.g.:

A) You may have left one the the dates blank on your form.

B) If the cursor is still in one of the text boxes on your form, the
Value
of the text box may not have been updated, and so no match is found.

C) Your date/time field may contain a time component that means it is not
less than or equal to the last date you specied.

D) Access may have misinterpreted the dates. To avoid this, set the
Format
property of the unbound text boxes to:
Short Date
or similar, and explicitly delcare the 2 paramters in the query
(Parameters
on Query menu) so you can indicate their type. More info:
http://allenbrowne.com/ser-45.html
http://allenbrowne.com/ser-36.html

Hi folks,

I have a query with the following criteria which are based on a form
with
two unbound text boxes:

=[forms]![CST Reports]![datefrom] And <=[forms]![CST reports]![dateto].

The column that uses this criteria is based on a field with the
"general
time/date" format and I am using

Date: (Format([Time_Admin],"Short Date"))

to get the date.

When i enter a couple of dates into the form and open the query no
records
are retrieved (I am using the correct dates).

What could be wrong.

thanks.

 

[Allen Browne]

Post the SQL statement.

(Switch the query to SQL View (on the View menu.))

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I still get an "invalid bracketing" error.

I'm not sure you entered the parameters:

1. In query design view, choose Parameters on the Query menu.
Access opens the parameter dialog.

2. Enter 2 rows like this:
[forms]![CST Reports]![datefrom] Date
[forms]![CST reports]![dateto] Date

If the query results are sorting as text, do you have this criteria under
a
date/time field? Under a Text field? Under a calculated field?

It has occurred to me that I have been entering "26/03/2007" in
"Datefrom"
and "04/04/2007" in "DateTo".

Sorted in ascending order "04/04/2007" comes before "26/03/2007" so I
changed the dates around and now I am getting records, BUT I am only
getting
records for these two dates.

:

Could be several factors, e.g.:

A) You may have left one the the dates blank on your form.

B) If the cursor is still in one of the text boxes on your form, the
Value
of the text box may not have been updated, and so no match is found.

C) Your date/time field may contain a time component that means it is
not
less than or equal to the last date you specied.

D) Access may have misinterpreted the dates. To avoid this, set the
Format
property of the unbound text boxes to:
Short Date
or similar, and explicitly delcare the 2 paramters in the query
(Parameters
on Query menu) so you can indicate their type. More info:
http://allenbrowne.com/ser-45.html
http://allenbrowne.com/ser-36.html

Hi folks,

I have a query with the following criteria which are based on a form
with
two unbound text boxes:

=[forms]![CST Reports]![datefrom] And <=[forms]![CST
reports]![dateto].

The column that uses this criteria is based on a field with the
"general
time/date" format and I am using

Date: (Format([Time_Admin],"Short Date"))

to get the date.

When i enter a couple of dates into the form and open the query no
records
are retrieved (I am using the correct dates).

 

[scubadiver]

SELECT (Format([Time_Admin],"Short Date")) AS [Date], tble_Query.QueryID,
tble_CCode.Type
FROM tble_Query INNER JOIN tble_CCode ON tble_Query.QueryID =
tble_CCode.QueryID
WHERE ((((Format([Time_Admin],"Short Date")))>=[forms]![CST
Reports]![datefrom] And ((Format([Time_Admin],"Short Date")))<=[forms]![CST
reports]![dateto]));

Post the SQL statement.

(Switch the query to SQL View (on the View menu.))

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I still get an "invalid bracketing" error.

I'm not sure you entered the parameters:

1. In query design view, choose Parameters on the Query menu.
Access opens the parameter dialog.

2. Enter 2 rows like this:
[forms]![CST Reports]![datefrom] Date
[forms]![CST reports]![dateto] Date

If the query results are sorting as text, do you have this criteria under
a
date/time field? Under a Text field? Under a calculated field?


It has occurred to me that I have been entering "26/03/2007" in
"Datefrom"
and "04/04/2007" in "DateTo".

Sorted in ascending order "04/04/2007" comes before "26/03/2007" so I
changed the dates around and now I am getting records, BUT I am only
getting
records for these two dates.

:

Could be several factors, e.g.:

A) You may have left one the the dates blank on your form.

B) If the cursor is still in one of the text boxes on your form, the
Value
of the text box may not have been updated, and so no match is found.

C) Your date/time field may contain a time component that means it is
not
less than or equal to the last date you specied.

D) Access may have misinterpreted the dates. To avoid this, set the
Format
property of the unbound text boxes to:
Short Date
or similar, and explicitly delcare the 2 paramters in the query
(Parameters
on Query menu) so you can indicate their type. More info:
http://allenbrowne.com/ser-45.html
http://allenbrowne.com/ser-36.html

Hi folks,

I have a query with the following criteria which are based on a form
with
two unbound text boxes:

=[forms]![CST Reports]![datefrom] And <=[forms]![CST
reports]![dateto].

The column that uses this criteria is based on a field with the
"general
time/date" format and I am using

Date: (Format([Time_Admin],"Short Date"))

to get the date.

When i enter a couple of dates into the form and open the query no
records
are retrieved (I am using the correct dates).

 

[Allen Browne]

Try this instead:

PARAMETERS [forms]![CST Reports]![datefrom] DateTime,
[forms]![CST reports]![dateto] DateTime;
SELECT [Time_Admin]),
tble_Query.QueryID,
tble_CCode.Type
FROM tble_Query INNER JOIN tble_CCode
ON tble_Query.QueryID = tble_CCode.QueryID
WHERE [Time_Admin] >= [forms]![CST Reports]![datefrom]
AND [Time_Admin] < ([forms]![CST reports]![dateto] + 1);

The differences are:
a) The parameters are declared.
b) It returns a real date (not a piece of text that sorts wrongly.)
c) Times on the last day are included by specifiying less than the day
after.
d) The query could run orders of magnitude faster, since it can use any
index you have on Time_Admin, and it avoids the function calls.
e) It avoids the DATE keyword as a field name, which is probably jamming up
your current query.

The result will be the complete date and time.
If you really want just the date, replae the 3rd line with:
SELECT DateValue([Time_Admin]) AS TheDate,

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

SELECT (Format([Time_Admin],"Short Date")) AS [Date], tble_Query.QueryID,
tble_CCode.Type
FROM tble_Query INNER JOIN tble_CCode ON tble_Query.QueryID =
tble_CCode.QueryID
WHERE ((((Format([Time_Admin],"Short Date")))>=[forms]![CST
Reports]![datefrom] And ((Format([Time_Admin],"Short
Date")))<=[forms]![CST
reports]![dateto]));

Post the SQL statement.

 

[scubadiver]

that works. Cheers!

Try this instead:

PARAMETERS [forms]![CST Reports]![datefrom] DateTime,
[forms]![CST reports]![dateto] DateTime;
SELECT [Time_Admin]),
tble_Query.QueryID,
tble_CCode.Type
FROM tble_Query INNER JOIN tble_CCode
ON tble_Query.QueryID = tble_CCode.QueryID
WHERE [Time_Admin] >= [forms]![CST Reports]![datefrom]
AND [Time_Admin] < ([forms]![CST reports]![dateto] + 1);

The differences are:
a) The parameters are declared.
b) It returns a real date (not a piece of text that sorts wrongly.)
c) Times on the last day are included by specifiying less than the day
after.
d) The query could run orders of magnitude faster, since it can use any
index you have on Time_Admin, and it avoids the function calls.
e) It avoids the DATE keyword as a field name, which is probably jamming up
your current query.

The result will be the complete date and time.
If you really want just the date, replae the 3rd line with:
SELECT DateValue([Time_Admin]) AS TheDate,

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

SELECT (Format([Time_Admin],"Short Date")) AS [Date], tble_Query.QueryID,
tble_CCode.Type
FROM tble_Query INNER JOIN tble_CCode ON tble_Query.QueryID =
tble_CCode.QueryID
WHERE ((((Format([Time_Admin],"Short Date")))>=[forms]![CST
Reports]![datefrom] And ((Format([Time_Admin],"Short
Date")))<=[forms]![CST
reports]![dateto]));

Post the SQL statement.