date filter for reports

[Rafi]

Hi,
I have a report based on query, which has one date field.
I am using form with combos for filtering report before printing, and all
works fine,- except the date...
When I manualy enter criteria into query, like " between #dd/mm/yy# and
#dd/mm/yy# " it works fine,- but since I need users to choose,- I have split
the Date field on form into 2 text boxes: From and To. Then I combined
the selected dates into 1 string for filtering, i.e. ** Me.LDate.Value =
"BETWEEN #" & Me.From.Value & "# AND #" & Me.To.Value & "#" ** and then **
strWhere = "[LoadDate] = " & Me!LDate.Value **
[LoadDate] is the name of the field in query.
In my opinion there is something wrong with my use of syntax, or the way
I am using for transfering parameters between form and query.

Appreciate anyone's help on this subject.

 

[John Spencer]

I think that you have to format the dates in US format or in yyyy-mm-dd
format.

StrWhere = "LoadDate BETWEEN " & Format(Me.From,"\#yyyy\-mm\-dd\#") & "
AND " & Format(Me.To,"\#yyyy\-mm\-dd\#") & "#"

Also noted that you were using "LoadDate =". That meant that your
"where" clause ended up being

LoadDate = Between ...
You should obviously be using only one operator or the other.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Rafi]

Sory John,
I am using European date definitions in Windows and also in all Access tables.
I can see the date in dd/mm/yy format in tables and queries.
As I said before,- if I put the filter manually in the query criteria,- it
works
100% as expected. The problem is to pass the same parameter thru string
from Filter form to query that opens report after filter aplied.
Somehow I am sure that I have the syntax kind of problem of defining exact
field in query where the criteria aplies. I don't understand why Microsoft
didn't define the word "criteria" in object's properties, even thought they
gave this option under query's feld definitions.
It's second time I am asking in Access and seems like it's Microsoft's bug...

Thanks anyway,

Rafi

I think that you have to format the dates in US format or in yyyy-mm-dd
format.

StrWhere = "LoadDate BETWEEN " & Format(Me.From,"\#yyyy\-mm\-dd\#") & "
AND " & Format(Me.To,"\#yyyy\-mm\-dd\#") & "#"

Also noted that you were using "LoadDate =". That meant that your
"where" clause ended up being

LoadDate = Between ...
You should obviously be using only one operator or the other.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi,
I have a report based on query, which has one date field.
I am using form with combos for filtering report before printing, and all
works fine,- except the date...
When I manualy enter criteria into query, like " between #dd/mm/yy# and
#dd/mm/yy# " it works fine,- but since I need users to choose,- I have split
the Date field on form into 2 text boxes: From and To. Then I combined
the selected dates into 1 string for filtering, i.e. ** Me.LDate.Value =
"BETWEEN #" & Me.From.Value & "# AND #" & Me.To.Value & "#" ** and then **
strWhere = "[LoadDate] = " & Me!LDate.Value **
[LoadDate] is the name of the field in query.
In my opinion there is something wrong with my use of syntax, or the way
I am using for transfering parameters between form and query.

Appreciate anyone's help on this subject.