Date range criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can Some one please help me with this;

I want to display records between dates selected by the user;
Fort his I have two Calendars cal1 and cal2 in the form FrmReconall to select date ranges;
In the query criteria I'm using the expression Between [Forms]![FrmReconall]![cal1] And [Forms]![FrmReconall]![cal2]

But the problem is this works fine and displays all records when the range selected is say from 6/1/2004 to 6/9/2004. But when I select the dates from 6/1/2004 to 6/10/2004 it displays only the first and last records. I'm really not able to figure the problem here.

Thanks in advance.
 
Can Some one please help me with this;

I want to display records between dates selected by the user;
Fort his I have two Calendars cal1 and cal2 in the form FrmReconall to select date ranges;
In the query criteria I'm using the expression Between [Forms]![FrmReconall]![cal1]
Click to expand...
And [Forms]![FrmReconall]![cal2]
But the problem is this works fine and displays all records when the range selected
Click to expand...
is say from 6/1/2004 to 6/9/2004. But when I select the dates from 6/1/2004 to
6/10/2004 it displays only the first and last records. I'm really not able to figure
the problem here.Hi Sahill,

Your query is treating your parameters as text.

In query designer, select "Query" in top menu
and click on "Parameters"

Parameter DataType
[Forms]![FrmReconall]![cal1] Date/Time
[Forms]![FrmReconall]![cal2] Date/Time

Please respond back if I have misunderstood
or was not clear about something.

Good luck,

Gary Walter
 
Thanks Gary

I tried assigning data types to the criteria expression just as the way you had suggested but this gives me an error saying "The expression may be invalid or is too complicated to be evaluated as it may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables"

Can you please help me out of this?

Gary Walter said:
Can Some one please help me with this;

I want to display records between dates selected by the user;
Fort his I have two Calendars cal1 and cal2 in the form FrmReconall to select date ranges;
In the query criteria I'm using the expression Between [Forms]![FrmReconall]![cal1]
Click to expand...
And [Forms]![FrmReconall]![cal2]
But the problem is this works fine and displays all records when the range selected
Click to expand...
is say from 6/1/2004 to 6/9/2004. But when I select the dates from 6/1/2004 to
6/10/2004 it displays only the first and last records. I'm really not able to figure
the problem here.Hi Sahill,

Your query is treating your parameters as text.

In query designer, select "Query" in top menu
and click on "Parameters"

Parameter DataType
[Forms]![FrmReconall]![cal1] Date/Time
[Forms]![FrmReconall]![cal2] Date/Time

Please respond back if I have misunderstood
or was not clear about something.

Good luck,

Gary Walter
Click to expand...
 
Hi Sahil,

1) what version of Access?

2) "where are the tables?"
non-Access/ODBC tables?

3) how are you using this query?
is it the source of a control on a form/report?
do you get same error when run from query designer?
the form was open when you ran it (sorry...had to ask)?

4) how are date values coming from form?
"all" US-date format?

5) what is the type of the field you are running criteria against?

6) what happens if you delete the beginning parameter clause
and simply format the form criteria as Dates?

WHERE
[yourdatefield]
Between
Format([Forms]![FrmReconall]![cal1], "#mm\/dd\/yyyy#")
And
Format([Forms]![FrmReconall]![cal2], "#mm\/dd\/yyyy#")

7) **what is the SQL stmt of your query?**
(copy and paste from SQL View in query designer)

Thanks,

Gary Walter



I tried assigning data types to the criteria expression just as the way you had
Click to expand...
suggested but this gives me an error saying "The expression may be invalid or is too
complicated to be evaluated as it may contain too many complicated elements. Try
simplifying the expression by assigning parts of the expression to variables"
Can you please help me out of this?

Gary Walter said:
Can Some one please help me with this;

I want to display records between dates selected by the user;
Fort his I have two Calendars cal1 and cal2 in the form FrmReconall to select
Click to expand...
date
ranges;
In the query criteria I'm using the expression Between
Click to expand...
[Forms]![FrmReconall]![cal1]
And [Forms]![FrmReconall]![cal2]
But the problem is this works fine and displays all records when the range
Click to expand...
selected
is say from 6/1/2004 to 6/9/2004. But when I select the dates from 6/1/2004 to
6/10/2004 it displays only the first and last records. I'm really not able to figure
the problem here.Hi Sahill,

Your query is treating your parameters as text.

In query designer, select "Query" in top menu
and click on "Parameters"

Parameter DataType
[Forms]![FrmReconall]![cal1] Date/Time
[Forms]![FrmReconall]![cal2] Date/Time

Please respond back if I have misunderstood
or was not clear about something.

Good luck,

Gary Walter
Click to expand...
Click to expand...
 
Hi Gary,

Thanks for your help.

I got it after using the parameter; the problem was because of using incorrect data type.

It was a blunder, but this is the way you learn, I guess!!

Anyway thanks for your valuable time and for being a great help.

Sahil


Gary Walter said:
Hi Sahil,

1) what version of Access?

2) "where are the tables?"
non-Access/ODBC tables?

3) how are you using this query?
is it the source of a control on a form/report?
do you get same error when run from query designer?
the form was open when you ran it (sorry...had to ask)?

4) how are date values coming from form?
"all" US-date format?

5) what is the type of the field you are running criteria against?

6) what happens if you delete the beginning parameter clause
and simply format the form criteria as Dates?

WHERE
[yourdatefield]
Between
Format([Forms]![FrmReconall]![cal1], "#mm\/dd\/yyyy#")
And
Format([Forms]![FrmReconall]![cal2], "#mm\/dd\/yyyy#")

7) **what is the SQL stmt of your query?**
(copy and paste from SQL View in query designer)

Thanks,

Gary Walter



I tried assigning data types to the criteria expression just as the way you had
Click to expand...
suggested but this gives me an error saying "The expression may be invalid or is too
complicated to be evaluated as it may contain too many complicated elements. Try
simplifying the expression by assigning parts of the expression to variables"
Can you please help me out of this?

Gary Walter said:
Can Some one please help me with this;

I want to display records between dates selected by the user;
Fort his I have two Calendars cal1 and cal2 in the form FrmReconall to select date
ranges;
In the query criteria I'm using the expression Between [Forms]![FrmReconall]![cal1]
And [Forms]![FrmReconall]![cal2]

But the problem is this works fine and displays all records when the range selected
is say from 6/1/2004 to 6/9/2004. But when I select the dates from 6/1/2004 to
6/10/2004 it displays only the first and last records. I'm really not able to figure
the problem here.

Hi Sahill,

Your query is treating your parameters as text.

In query designer, select "Query" in top menu
and click on "Parameters"

Parameter DataType
[Forms]![FrmReconall]![cal1] Date/Time
[Forms]![FrmReconall]![cal2] Date/Time

Please respond back if I have misunderstood
or was not clear about something.

Good luck,

Gary Walter
Click to expand...
Click to expand...
Click to expand...
 
Back
Top