Access listboxes - format datetime values

[Guest]

Hi all,
I am displaying a bound listbox in a form. One of the columns of this
listbox displays a datetime value. This dateTime value needs to be formatted
and shown in military time format.

In the rowsource property of the listbox, I am specifying an Access query.
The Access query formats the datetime value using the function Format(input,
"mm/dd/yyyy hh:mm")

The problem is when I scroll the listbox, the datetime column becomes null
sometimes. I don't know why this happens. If I don't format the datetime
value, then the problem does not occur.

Any help is appreciated.
Thanks.

-Lak.

 

[Ken Snell \(MVP\)]

My guess is that the data in that field for those records is not properly a
date/time value that ACCESS can recognize. Look at the original data in the
table for those particular records and see if the data are valid. Also look
at the query that you're using for the listbox's Row Source and see if the
values show as "null" in the query or not.

 

[Guest]

Thanks for your reply Ken. I will check what the query is returning. However,
all rows in the listbox go null for this particular column. This happens when
I scroll to the second page of the listbox, open a different form and come
back to the form which contains the listbox. Please share your valuable
insights.
-Lak.

 

[Ken Snell \(MVP\)]

I'd need to see the SQL statement of the listbox's Row Source query, to know
the listbox's Bound Column value, and to know more about the data in the
table first.

 

[Guest]

SELECT A_vwRT510Effectivity.EDR_SiteID,
IIf([A_vwRT510Effectivity.Enabled],"Yes"," - ") AS Enabled,
A_vwRT510Effectivity.RT510_ID, A_vwRT510Effectivity.MonitoringSystemID AS
[System ID], A_vwRT510Effectivity.SystemParameterMonitored AS Param,
A_vwRT510Effectivity.PrimaryBackupDesignation AS [P/B],
A_vwRT510Effectivity.ComponentID AS [Component ID],
A_vwRT510Effectivity.ComponentTypeCode AS [Component Type],
A_vwRT510Effectivity.Manufacturer, A_vwRT510Effectivity.ModelOrVersion AS
[Model/Version], A_vwRT510Effectivity.SerialNumber AS [Serial Number],
IIf([Active],"Yes"," - ") AS [Active Component],
Format([EffectiveFrom],"mm/dd/yyyy hh:nn") AS [Active Since]
FROM A_vwRT510Effectivity
ORDER BY A_vwRT510Effectivity.MonitoringSystemID;

This is the SQL query which is the listbox's rowsource.
Format([EffectiveFrom],"mm/dd/yyyy hh:nn") AS [Active Since] is the code that
apparently is causing problems.

I am really stuck with this problem. I appreciate your help.
Thanks.

 

[Ken Snell \(MVP\)]

Didn't get a chance to reply last night... should have a reply for you later
today. In the meantime, what are the values of all the listbox's properties
(e.g., ColumnCount, ColumnWidths, BoundColumn, etc.) that pertain to the
display of information in the listbox? And show an example of data from a
record that is displaying this "blank" behavior.

--

Ken Snell
<MS ACCESS MVP>

SELECT A_vwRT510Effectivity.EDR_SiteID,
IIf([A_vwRT510Effectivity.Enabled],"Yes"," - ") AS Enabled,
A_vwRT510Effectivity.RT510_ID, A_vwRT510Effectivity.MonitoringSystemID AS
[System ID], A_vwRT510Effectivity.SystemParameterMonitored AS Param,
A_vwRT510Effectivity.PrimaryBackupDesignation AS [P/B],
A_vwRT510Effectivity.ComponentID AS [Component ID],
A_vwRT510Effectivity.ComponentTypeCode AS [Component Type],
A_vwRT510Effectivity.Manufacturer, A_vwRT510Effectivity.ModelOrVersion AS
[Model/Version], A_vwRT510Effectivity.SerialNumber AS [Serial Number],
IIf([Active],"Yes"," - ") AS [Active Component],
Format([EffectiveFrom],"mm/dd/yyyy hh:nn") AS [Active Since]
FROM A_vwRT510Effectivity
ORDER BY A_vwRT510Effectivity.MonitoringSystemID;

This is the SQL query which is the listbox's rowsource.
Format([EffectiveFrom],"mm/dd/yyyy hh:nn") AS [Active Since] is the code
that
apparently is causing problems.

I am really stuck with this problem. I appreciate your help.
Thanks.

I'd need to see the SQL statement of the listbox's Row Source query, to
know
the listbox's Bound Column value, and to know more about the data in the
table first.