Dateformat in Listbox

[Doekoe]

In Access I created a query with two dates, 'Inputdate' and 'Duedate'.
'Inputdate' has datatype 'smalldatetime' and 'Duedate' has datatype
'datetime'. If I open this query I get the 'Inputdate' as date/time
and 'Duedate' only shows the date. In my opinion this is strange. In
the qyery properties I set for 'Inputdate' the Appereance to Short
DateTime and then it is OK, it shows now for both dates only the date
(as I want).

I put a listbox on a form which has this query as rowsource. Again in
the list, my 'Inputdate' is shown as date/time and the 'Duedate' only
shows the date. I need for the 'Inputdate' only the date and not the
time.

I cannot figure out where this goes wrong. I hope somebody can give a
hand with this.

Thanks,

Dirk

 

[Klatuu]

There is nothing wrong. In SQL Server, datetime contains date and time,
smalldatetime carries only the date. If you want them both to show only the
date, you can format them in your query

SELECT Format([InputDate], "mm/dd/yyyy") As InDate, Format([DueDate],
"mm/ff/yyyy") As DueOn .....

 

[Marshall Barton]

In Access I created a query with two dates, 'Inputdate' and 'Duedate'.
'Inputdate' has datatype 'smalldatetime' and 'Duedate' has datatype
'datetime'. If I open this query I get the 'Inputdate' as date/time
and 'Duedate' only shows the date. In my opinion this is strange. In
the qyery properties I set for 'Inputdate' the Appereance to Short
DateTime and then it is OK, it shows now for both dates only the date
(as I want).

I put a listbox on a form which has this query as rowsource. Again in
the list, my 'Inputdate' is shown as date/time and the 'Duedate' only
shows the date. I need for the 'Inputdate' only the date and not the
time.

I cannot figure out where this goes wrong. I hope somebody can give a
hand with this.

Don't confuse the format with the data type. All date
fields contain both a data and time value. How the
date/time value is displayed is specified by formatting the
value.

If you want the list box data formatted in a specific way,
then I suggest that you explicitly specify the formatting by
using the Format function instead of just using the field.
Form example, the query should probably be more like:

SELECT Format(Inputdate, "m/d/yyyy") As InDate,
Format(Duedate, "m/d/yyyy") As DueDate
FROM your table
ORDER BY Inputdate

 

[Doekoe]

Doekoewrote:

In Access I created a query with two dates, 'Inputdate' and 'Duedate'.
'Inputdate' has datatype 'smalldatetime' and 'Duedate' has datatype
'datetime'. If I open this query I get the 'Inputdate' as date/time
and 'Duedate' only shows the date. In my opinion this is strange. In
the qyery properties I set for 'Inputdate' the Appereance to Short
DateTime and then it is OK, it shows now for both dates only the date
(as I want).

I put a listbox on a form which has this query as rowsource. Again in
the list, my 'Inputdate' is shown as date/time and the 'Duedate' only
shows the date. I need for the 'Inputdate' only the date and not the
time.

I cannot figure out where this goes wrong. I hope somebody can give a
hand with this.

Don't confuse the format with the data type.  All date
fields contain both a data and time value.  How the
date/time value is displayed is specified by formatting the
value.

If you want the list box data formatted in a specific way,
then I suggest that you explicitly specify the formatting by
using the Format function instead of just using the field.
Form example, the query should probably be more like:

SELECT Format(Inputdate, "m/d/yyyy") As InDate,
                                        Format(Duedate, "m/d/yyyy") As DueDate
FROM your table
ORDER BY Inputdate

--
Marsh
MVP [MS Access]- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Hi Marsh,

Thanks for answering my question. All you and Dave wrote I understand,
however in my case the problem was that I want to filter on a date
typed by the user. This did not work because the date field contained
the time..., so to look for a date you even needed to specify the
time. On my DueDate field this was nog the case. I solved it by using
the CAST function in the query, I changed the datatype to a FLOAT and
then back to DATETIME. And now the date only shows the date and I can
filter on it...

Problem solved but still I think that something is not right.

Thanks.

Dirk