Query by dates

  • Thread starter Thread starter markwmiller via AccessMonster.com
  • Start date Start date
M

markwmiller via AccessMonster.com

I have a query that creates a report for over due balances. For my date field
I am using <=[Enter month & year in ##/####] as my criteria. It has work fine
for several years, but now when I try to run the query for 02/2006, it said
that there is no data, when in fact there is. Works the same for 03/2006. If
I change the criteria to >=[Enter month & year in ##/####] I get my data for
02/2006 and after. I want data for 02/2006 and before.
 
In design mode of the query, go to Query, Parameters on the menu. Do you see
[Enter month & year in ##/####] there, and if so, is the datatype Date/Time?
You did state which version of Access, but A03 is very picky about
parameters. Also I'm that your date field is actually date/time datatype and
not text.
 
Datatype is Date/Time. Database was designed in Access97. Converted to Access
2000, Same problem. Everything was working good till 02/2006. 01/2006 still
work fine.

Jerry said:
In design mode of the query, go to Query, Parameters on the menu. Do you see
[Enter month & year in ##/####] there, and if so, is the datatype Date/Time?
You did state which version of Access, but A03 is very picky about
parameters. Also I'm that your date field is actually date/time datatype and
not text.
I have a query that creates a report for over due balances. For my date field
I am using <=[Enter month & year in ##/####] as my criteria. It has work fine
for several years, but now when I try to run the query for 02/2006, it said
that there is no data, when in fact there is. Works the same for 03/2006. If
I change the criteria to >=[Enter month & year in ##/####] I get my data for
02/2006 and after. I want data for 02/2006 and before.
Click to expand...
Click to expand...
 
Well Feburary is that strange month with < 30 days. ;-)

What happens when you remove the parameter and hard code in something like
<#2/1/2006#

You just might need the day in there for some reason. I also recommend
double-checking your dates for something like the wrong year or something.
Most often the query is working right but it's either the data or how it
implemented that is the problem.

I'd also be a little worried about the parameter having ampersands and
hashes in it especially the #'s denote dates and time.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


markwmiller via AccessMonster.com said:
Datatype is Date/Time. Database was designed in Access97. Converted to Access
2000, Same problem. Everything was working good till 02/2006. 01/2006 still
work fine.

Jerry said:
In design mode of the query, go to Query, Parameters on the menu. Do you see
[Enter month & year in ##/####] there, and if so, is the datatype Date/Time?
You did state which version of Access, but A03 is very picky about
parameters. Also I'm that your date field is actually date/time datatype and
not text.
I have a query that creates a report for over due balances. For my date field
I am using <=[Enter month & year in ##/####] as my criteria. It has work fine
for several years, but now when I try to run the query for 02/2006, it said
that there is no data, when in fact there is. Works the same for 03/2006. If
I change the criteria to >=[Enter month & year in ##/####] I get my data for
02/2006 and after. I want data for 02/2006 and before.
Click to expand...
Click to expand...
Click to expand...
 
Hard coded the parameters and took out the ampersands, no go. Checked data in
the tables, dates are ok. Query worked ok with 02/2005. This has got me
stumped. Any more ideas, I'll try anything to get it to work, I needed this
report last week.

Jerry said:
Well Feburary is that strange month with < 30 days. ;-)

What happens when you remove the parameter and hard code in something like
<#2/1/2006#

You just might need the day in there for some reason. I also recommend
double-checking your dates for something like the wrong year or something.
Most often the query is working right but it's either the data or how it
implemented that is the problem.

I'd also be a little worried about the parameter having ampersands and
hashes in it especially the #'s denote dates and time.
Datatype is Date/Time. Database was designed in Access97. Converted to Access
2000, Same problem. Everything was working good till 02/2006. 01/2006 still
Click to expand...
[quoted text clipped - 11 lines]
I change the criteria to >=[Enter month & year in ##/####] I get my data for
02/2006 and after. I want data for 02/2006 and before.
Click to expand...
Click to expand...
Click to expand...
 
One of the fields in a linked table was null. Thanks for all your help.
Hard coded the parameters and took out the ampersands, no go. Checked data in
the tables, dates are ok. Query worked ok with 02/2005. This has got me
stumped. Any more ideas, I'll try anything to get it to work, I needed this
report last week.
Well Feburary is that strange month with < 30 days. ;-)
Click to expand...
[quoted text clipped - 13 lines]
I change the criteria to >=[Enter month & year in ##/####] I get my data for
02/2006 and after. I want data for 02/2006 and before.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top