How do I sort by dates in Access project?

[Glint]

I am converting an Access file to Access Project (adp file). I have to change
a lot of code that I had taken for granted in Access. The problem now is that
when I am sorting a form (based on a simple query) by dates, I get
unpredictable responses; sometimes it works, sometimes it does not. For
example:
Me.Filter = "[TDate]>=" & "'" & Starting & "'" & " And [TDate]<=" & "'" &
Ending & "'"
works on some occasion and does not on other occasions.
I am new at this. can someone help me?

 

[Glint]

The example I stated earlier works in a form (Donations) but does not in the
Expenditure form. [Starting] and [Ending] are unbound textboxes set to date
data type with format dd-mmm-yyyy.
Maybe I should also add that another unbound textbox with control
source=Sum([Amount]) always returned the sum of the entire recordset,
regardless of the filter condition.
--
gbenga

I am converting an Access file to Access Project (adp file). I have
to change a lot of code that I had taken for granted in Access. The
problem now is that when I am sorting a form (based on a simple
query) by dates, I get unpredictable responses; sometimes it works,
sometimes it does not. For example:
Me.Filter = "[TDate]>=" & "'" & Starting & "'" & " And [TDate]<=" &
"'" & Ending & "'"
works on some occasion and does not on other occasions.
I am new at this. can someone help me?

Sorting by date should work. Are you sure the date is a date data type?
Is the problem consistent? Could you post some examples?

 

[John Vinson]

I am converting an Access file to Access Project (adp file). I have to change
a lot of code that I had taken for granted in Access. The problem now is that
when I am sorting a form (based on a simple query) by dates, I get
unpredictable responses; sometimes it works, sometimes it does not. For
example:
Me.Filter = "[TDate]>=" & "'" & Starting & "'" & " And [TDate]<=" & "'" &
Ending & "'"
works on some occasion and does not on other occasions.
I am new at this. can someone help me?

This has nothing to do with *sorting* - are you using the terms
sorting (putting selected records in order) and filtering (choosing
which records to select) interchangably?

Date/Time fields should be delimited by # rather than ', which is a
TEXT delimiter. It may be that your filter is filtering dates as
strings, which would put '12-Dec-1832' before '9-Apr-2005', since the
character 1 sorts before the character 9. Try

Me.Filter = "[TDate] >= #" & Format(Starting, "mm/dd/yyyy") & "# And
[TDate] <= #" & Format(Ending, "mm/dd/yyyy") & "#"

Note that literal dates in an Access query must be in mm/dd/yyyy
format or an unambiguous format.

John W. Vinson[MVP]

 

[Glint]

Thanx John.
Yes, I am sorry I said sorting when i meant filtering. I have tried the
filter as you suggested, and this time it does filter well. The only problem
is that it returns this error: Line 1: Incorrect syntax near '#'
In fact, such error made me abandon using the # in the first place.
--
gbenga

I am converting an Access file to Access Project (adp file). I have to change
a lot of code that I had taken for granted in Access. The problem now is that
when I am sorting a form (based on a simple query) by dates, I get
unpredictable responses; sometimes it works, sometimes it does not. For
example:
Me.Filter = "[TDate]>=" & "'" & Starting & "'" & " And [TDate]<=" & "'" &
Ending & "'"
works on some occasion and does not on other occasions.
I am new at this. can someone help me?

This has nothing to do with *sorting* - are you using the terms
sorting (putting selected records in order) and filtering (choosing
which records to select) interchangably?

Date/Time fields should be delimited by # rather than ', which is a
TEXT delimiter. It may be that your filter is filtering dates as
strings, which would put '12-Dec-1832' before '9-Apr-2005', since the
character 1 sorts before the character 9. Try

Me.Filter = "[TDate] >= #" & Format(Starting, "mm/dd/yyyy") & "# And
[TDate] <= #" & Format(Ending, "mm/dd/yyyy") & "#"

Note that literal dates in an Access query must be in mm/dd/yyyy
format or an unambiguous format.

John W. Vinson[MVP]

 

[John Vinson]

Thanx John.
Yes, I am sorry I said sorting when i meant filtering. I have tried the
filter as you suggested, and this time it does filter well. The only problem
is that it returns this error: Line 1: Incorrect syntax near '#'
In fact, such error made me abandon using the # in the first place.

Please post your actual code. The field *is* a Date/Time field then?

John W. Vinson[MVP]

 

[Glint]

The filter is:
Me.Filter = "[TDate]>=" & "'" & Starting & "'" & " And [TDate]<=" & "'" &
Ending & "'"
Starting and Ending are unbound textboxes set to date/time format
(dd-mmm-yyyy).
The form is based on a query/view: SELECT dbo.Expenses.ExpenseID,
dbo.Expenses.TDate, dbo.ExpensesDetail.ExpenseItem,
dbo.Expenditure.Expenditure, dbo.ExpensesDetail.Amount
FROM dbo.Expenses INNER JOIN
dbo.ExpensesDetail ON dbo.Expenses.ExpenseID =
dbo.ExpensesDetail.ExpenseID INNER JOIN
dbo.Expenditure ON dbo.ExpensesDetail.ExpenseItem =
dbo.Expenditure.ExpenditureID
WHERE (dbo.Expenses.PostedBy IS NOT NULL)
When I use the *#* sign instead of *'* I get error message: Line 1:
Incorrect syntax near '#'.
Meanwhile the code works well in another form. I am using Access 2002 in
2000 format, on Windows XP Pro.

 

[John Vinson]

When I use the *#* sign instead of *'* I get error message: Line 1:
Incorrect syntax near '#'.

My apologies. It appears that your data is in SQL/Server (the dbo
prefixes...) and that you're using SQL/Server syntax (with dates as
strings) rather than JET's dialect of SQL.

John W. Vinson[MVP]