Access 2003 query for listbox sorts by Date but not most recent Time

[RCGUA]

I have an Access 2003 Form with a listbox on it. The row source is a
Table/Query. The query sorts the records according a Date/Time field
in the table. The Date/Timefield is Short Date.

When I use the form and add a record to the list box, the code below
scrolls down the listbox to bottom and selects the last record. The
problem is that the last few records are sorted by the Date but not
by
the Time. For example, if I have added five records today, the
listbox will show them in random order, except that all the records
entered today are on the bottom of the list.

first record added
third record added
fifth record added
second record added
fourth record added

== code that updates the list box ==

Me.lstEmployeePayments.SetFocus
Dim intListCount As Integer
intListCount = Me.lstEmployeePayments.ListCount
intListCount = intListCount - 1
Me.lstEmployeePayments.ListIndex = intListCount

=== this is also posted in ( comp . databases . ms-access ), ===
sorry for the cross posting but I accidentally posted in there and
there was no response, I meant to post it in this group

 

[Dirk Goldgar]

I have an Access 2003 Form with a listbox on it. The row source is a
Table/Query. The query sorts the records according a Date/Time field
in the table. The Date/Timefield is Short Date.

When I use the form and add a record to the list box, the code below
scrolls down the listbox to bottom and selects the last record. The
problem is that the last few records are sorted by the Date but not
by
the Time. For example, if I have added five records today, the
listbox will show them in random order, except that all the records
entered today are on the bottom of the list.

first record added
third record added
fifth record added
second record added
fourth record added

== code that updates the list box ==

Me.lstEmployeePayments.SetFocus
Dim intListCount As Integer
intListCount = Me.lstEmployeePayments.ListCount
intListCount = intListCount - 1
Me.lstEmployeePayments.ListIndex = intListCount

=== this is also posted in ( comp . databases . ms-access ), ===
sorry for the cross posting but I accidentally posted in there and
there was no response, I meant to post it in this group

What is the rowsource of the list box?

 

[ron]

I have an Access 2003 Form with a listbox on it. The row source is a
Table/Query. The query sorts the records according a Date/Time field
in the table. The Date/Timefield is Short Date.

When I use the form and add a record to the list box, the code below
scrolls down the listbox to bottom and selects the last record. The
problem is that the last few records are sorted by the Date but not
by
the Time. For example, if I have added five records today, the
listbox will show them in random order, except that all the records
entered today are on the bottom of the list.

first record added
third record added
fifth record added
second record added
fourth record added

== code that updates the list box ==

Me.lstEmployeePayments.SetFocus
Dim intListCount As Integer
intListCount = Me.lstEmployeePayments.ListCount
intListCount = intListCount - 1
Me.lstEmployeePayments.ListIndex = intListCount

=== this is also posted in ( comp . databases . ms-access ), ===
sorry for the cross posting but I accidentally posted in there and
there was no response, I meant to post it in this group

 

[RCGUA]

What is the rowsource of the list box?

--
Dirk Goldgar, MS Access MVP
Access tips:www.datagnostics.com/tips.html

(please reply to the newsgroup)

The rowsource is a table query (copied below)

qryListOfEmployeeANDManoPayments

 

[fredg]

The rowsource is a table query (copied below)

qryListOfEmployeeANDManoPayments

Open the query in Design View. Click on View + SQL View.
Please copy and paste the query SQL into a reply message.
Select .... etc ....

That is what we need.

Also, are you sure the Time (as well as the date) is stored in this
datefield?

 

[RCGUA]

Open the query in Design View. Click on View + SQL View.
Please copy and paste the query SQL into a reply message.
Select .... etc ....

That is what we need.

Also, are you sure the Time (as well as the date) is stored in this
datefield?

SELECT tblPayments.PaymentAmount AS Expr1, Format
(tblPayments.PaymentDate,"dd mmmm, yyyy") AS Expr2,
tblPayments.MatchingAmount
FROM tblPayments
WHERE (((tblPayments.WorkerName)=[Forms]![frmCASA-4payments]!
[cboWorkerName]))
ORDER BY tblPayments.PaymentDate;

As far as the Time being stored in this datefield, as far as I can
tell, the Access "Short Date" format includes the time. Thanks for
your help.

 

[RCGUA]

The format is completely irrelevant to the value stored, its merely how you
see a date/time value.  By default you see them in the system's short date
format.  Date/time values are in fact stored as a 64 bit floating point
number with its origin at 30 December 1899 00:00:00.  If you enter a date
without a time you are in fact entering a date/time value which corresponds
to midnight at the start of the day.

To see if your 'dates' do have non-zero times of day create a query which
returns the values formatted to show the dates and times, e.g.

SELECT FORMAT([MyDate],"dd mmmm yyyy hh:nn:ss")
AS [MyDateFormatted]
FROM [MyTable]
ORDER BY [MyDate];

If the values returned are like 03 November 2009 00:00:00 then the times of
day are zero so sorting the column will only sort on the date.

Ken Sheridan
Stafford, England

On Nov 2, 4:32 pm, "Dirk Goldgar"
<[email protected]> wrote:

[quoted text clipped - 56 lines]

Please respond only to this newsgroup.
I do not reply to personal e-mail

SELECT tblPayments.PaymentAmount AS Expr1, Format
(tblPayments.PaymentDate,"dd mmmm, yyyy") AS Expr2,
tblPayments.MatchingAmount
FROM tblPayments
WHERE (((tblPayments.WorkerName)=[Forms]![frmCASA-4payments]!
[cboWorkerName]))
ORDER BY tblPayments.PaymentDate;

As far as the Time being stored in this datefield, as far as I can
tell, the Access "Short Date" format includes the time.  Thanks for
your help.

The "dates" are coming back from your query as: 02 November 2009
00:00:00

I created a brand new table with a field that has the date format set
as "General Date" which the description shows that it includes the
hours and minutes. I entered a few dates into this table and ran your
query and it still comes up with
02 November 2009 00:00:00
Do I need to set my vba code to enter the hour and minute, when the
date is entered?

 

[Dirk Goldgar]

The "dates" are coming back from your query as: 02 November 2009 00:00:00

I created a brand new table with a field that has the date format set as
"General Date" which the description shows that it includes the hours and
minutes. I entered a few dates into this table and ran your query and it
still comes up with 02 November 2009 00:00:00 Do I need to set my vba code
to enter the hour and minute, when the date is entered?

If you enter a date into a date/time field manually, that's what you get --
just the date; Access isn't going to guess what time on that date you might
want. Not to mention that in many applications, you *only* want the date,
and it's a lot easier in those cases if you don't have any time portion to
worry about.

So if you are entering dates manually, and you want there to be a time as
well, you have to either enter the time along with the date. As you
suggest, you could write code for a control's AfterUpdate event to put in a
time if you didn't enter one, but what time would you want it to choose?

If you use code to assign the current date/time to the field, instead of
doing it manually, you should be aware that the Date() function returns only
the current system date, with no time portion. If you want the date *and*
the time, use the Now() function instead. And of course you can use the
Time() function to return the time alone, with no date portion.

Be aware that, because of the way date/time fields are stored, dates with no
time portion are the same as midnight on that date, while times with no date
portion are represented as occurring on the "zero" date, December 30, 1899.

 

[RCGUA]

If you enter a date into a date/time field manually, that's what you get --  
just the date; Access isn't going to guess what time on that date you might
want.  Not to mention that in many applications, you *only* want the date,
and it's a lot easier in those cases if you don't have any time portion to
worry about.

So if you are entering dates manually, and you want there to be a time as
well, you have to either enter the time along with the date.  As you
suggest, you could write code for a control's AfterUpdate event to put ina
time if you didn't enter one, but what time would you want it to choose?

If you use code to assign the current date/time to the field, instead of
doing it manually, you should be aware that the Date() function returns only
the current system date, with no time portion.  If you want the date *and*
the time, use the Now() function instead.  And of course you can use the
Time() function to return the time alone, with no date portion.

Be aware that, because of the way date/time fields are stored, dates withno
time portion are the same as midnight on that date, while times with no date
portion are represented as occurring on the "zero" date, December 30, 1899.

--
Dirk Goldgar, MS Access MVP
Access tips:www.datagnostics.com/tips.html

(please reply to the newsgroup)

Bingo -- Dirk, you said "If you want the date *and* the time, use the
Now() function instead"
I looked to see how the "Date" was being set, I thought it was in the
code but it was coming from a text box on the Form that had a default
value of Date() I switched that default to Now() and
Viola! all is right with the world.
Everytime I enter a new record the listbox scrolls right to the bottom
so the record I just added can be seen.
Thank You Dirk! and Thank You All who bit by bit, helped tease
out the solution, Thanks all your help, I really, really appreciate
it.