Help needed re: parameter query

[s.f.]

I create a parameter query to allow users to check records with interview
dates that fall between a specific time window on a timely basis. To do so, I
first create 2 fields as:

start_date: [On! or AFTER (m/d/yyyy):]
end_date: [On or BEFORE (m/d/yyyy):]

Then for the "interview date" field, I put in the criteria:

=[On or AFTER (m/d/yyyy):] And <=[On or BEFORE (m/d/yyyy):]

This way when opening/running the query, the user will be prompted to enter
a start date and an end date.

However, the test run picks up one record that falls outside the range
(along with all other records that are correctly selected). Would that be
possible that there's a bug? Any suggestions would be highly appreciated!

 

[Allen Browne]

Perhaps Access is misunderstanding the criteria.
To test that idea, declare your parameters.

In query design view, choose Parameters on the Query menu.
Enter 2 rows into the dialog:
[On! or AFTER (m/d/yyyy):] Date/Time
[On or BEFORE (m/d/yyyy):] Date/Time

Not sure the bang (!) is a good idea in the name, even though it's in square
brackets.

If it still fails, try a compact/repair, in case there is a bad index.

If that doesn't work tell us:
a) In table design view, what data type is the [interview date] field?
b) What is the bad value that gets returned?
c) What criteria values did you enter for the 2 parameters?

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

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

I create a parameter query to allow users to check records with interview
dates that fall between a specific time window on a timely basis. To do
so, I
first create 2 fields as:

start_date: [On! or AFTER (m/d/yyyy):]
end_date: [On or BEFORE (m/d/yyyy):]

Then for the "interview date" field, I put in the criteria:

=[On or AFTER (m/d/yyyy):] And <=[On or BEFORE (m/d/yyyy):]

This way when opening/running the query, the user will be prompted to
enter
a start date and an end date.

However, the test run picks up one record that falls outside the range
(along with all other records that are correctly selected). Would that be
possible that there's a bug? Any suggestions would be highly appreciated!

 

[Jerry Whittle]

Check if the problem record has a time portion to it. It's possible that you
can't see the time as it's formatted to not show it.

I'm assuming that the data is stored in an actual date/time field and the
parameters are defined as date/time up in Query, Parameters.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I create a parameter query to allow users to check records with interview
dates that fall between a specific time window on a timely basis. To do so, I
first create 2 fields as:

start_date: [On! or AFTER (m/d/yyyy):]
end_date: [On or BEFORE (m/d/yyyy):]

Then for the "interview date" field, I put in the criteria:

=[On or AFTER (m/d/yyyy):] And <=[On or BEFORE (m/d/yyyy):]

This way when opening/running the query, the user will be prompted to enter
a start date and an end date.

However, the test run picks up one record that falls outside the range
(along with all other records that are correctly selected). Would that be
possible that there's a bug? Any suggestions would be highly appreciated!

 

[s.f.]

Thank you very much -- problem fixed by declaring query parameters!

Perhaps Access is misunderstanding the criteria.
To test that idea, declare your parameters.

In query design view, choose Parameters on the Query menu.
Enter 2 rows into the dialog:
[On! or AFTER (m/d/yyyy):] Date/Time
[On or BEFORE (m/d/yyyy):] Date/Time

Not sure the bang (!) is a good idea in the name, even though it's in square
brackets.

If it still fails, try a compact/repair, in case there is a bad index.

If that doesn't work tell us:
a) In table design view, what data type is the [interview date] field?
b) What is the bad value that gets returned?
c) What criteria values did you enter for the 2 parameters?

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

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

I create a parameter query to allow users to check records with interview
dates that fall between a specific time window on a timely basis. To do
so, I
first create 2 fields as:

start_date: [On! or AFTER (m/d/yyyy):]
end_date: [On or BEFORE (m/d/yyyy):]

Then for the "interview date" field, I put in the criteria:

=[On or AFTER (m/d/yyyy):] And <=[On or BEFORE (m/d/yyyy):]

This way when opening/running the query, the user will be prompted to
enter
a start date and an end date.

However, the test run picks up one record that falls outside the range
(along with all other records that are correctly selected). Would that be
possible that there's a bug? Any suggestions would be highly appreciated!

 

[s.f.]

Thank you very much -- problem fixed by declaring "query parameters"!

Check if the problem record has a time portion to it. It's possible that you
can't see the time as it's formatted to not show it.

I'm assuming that the data is stored in an actual date/time field and the
parameters are defined as date/time up in Query, Parameters.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I create a parameter query to allow users to check records with interview
dates that fall between a specific time window on a timely basis. To do so, I
first create 2 fields as:

start_date: [On! or AFTER (m/d/yyyy):]
end_date: [On or BEFORE (m/d/yyyy):]

Then for the "interview date" field, I put in the criteria:

=[On or AFTER (m/d/yyyy):] And <=[On or BEFORE (m/d/yyyy):]

This way when opening/running the query, the user will be prompted to enter
a start date and an end date.

However, the test run picks up one record that falls outside the range
(along with all other records that are correctly selected). Would that be
possible that there's a bug? Any suggestions would be highly appreciated!