Convet to Short Date format

[Libre]

I have tables that have a Date/Time data field.
I've used the general date format, so the field looks like:
5/4/2006 1:35:29 PM

I've added a new column I call ShortDate, and I've specified the short date format. I want this column to just have the date without the time:
5/4/2006
I want to convert all the legacy data and write the short date to this column.

I've written a loop that looks like:
Set rst = CurrentDb.OpenRecordset("tblSalesLog")
Dim dShortDate As Date
If rst.RecordCount > 0 Then
rst.MoveFirst
Do Until rst.EOF
dShortDate = rst![SaleDate]
rst.Edit
rst![SaleDateShort] = dShortDate
rst.Update
rst.MoveNext
Loop
MsgBox ("done")
End If

It looks like it works, because only the date without the time is in the column. But when you click in the field, the long date format with the time pops up, so the time is still in there.

The Length method might work, but all the dates are different lengths:
1/1/2006; 11/12/2005; etc

I do notice there is a space between the date and the time. Maybe I can use that with the Instr command?

Suggestions?

 

[boblarson]

Don't worry about having a column without a time. You can format it anyway you like, including a column in a query, so that you can pull and display it any way you like at any time.

When you are storing a date, it is really still all the same thing, but the formatting just displays it differently. Normally a table's display will not show the time when formatted as short date because it gets stored as mm/dd/yyyy 00:00:00 and it just doesn't display the 00:00:00. But, if you have a date that already has a different time associated with it, then it is going to show you the exact value stored when you click in the field in the table. So, don't sweat it.

 

[gemma-the-husky]

the only problem is that you the time bit of the date is not 0, then if you compare to a given date, the fractional day may cause a test to fail = be aware of this possibility

 

[boblarson]

But, at the same time you can create a column in a query:

MyDate: Format([YourDateField],"m/d/yyyy")

criteria: #4/1/2006#

that example would return all dates of 4/1/2006 regardless of time.

 

[Brianwarnock]

Where most people fall foul of the prescence of a time is when using Between... And.., as the incusive 2nd date defaults to a time of 0 as Gemma indicated, thus things can be unnescessarily messy compared to a field established with no time.

Brian

 

[RoyVidar]

If you want to remove the time part, try

rst![SaleDateShort] = Clng(dShortDate)

 

[Brianwarnock]

The simplest way to create a field without the time from a field with the time iin a table is to use an update query on the new field with the "update to" set to

Int([originaldatetimefield])

you can even do this to the original field.

Brian

 

[EMP]

It looks like it works, because only the date without the time is in the column.

The time is there. Formatting the column as short date doesn't remove the time.

But when you click in the field, the long date format with the time pops up, so the time is still in there.

In your code, you can use the DateValue() function to extract the date values:-
................
dShortDate = DateValue(rst![SaleDate])
................


I agree with Brian. Using an Update Query is the simplest. You can also use the DateValue() function in the query.

UPDATE [tblSalesLog] SET [SaleDateShort] = DateValue([SaleDate]);

^

 

[Libre]

EMP - and others;
This worked:
dShortDate = DateValue(rst![SaleDate])

I'm very grateful. Thanks. I'm sure some of the other methods would have worked too, but I tried this and voila!

A little explanation:
I like the long date on my records because the time is useful for sorting by date. If you just have the short date, records with the same date will be sorted unpredicatably. But with the time as well, it will sort more accuratly.
BUT:
On forms and for general record keeping, I don't care about the time for most purposes, so I'd rather just see the date and not have the time appear when I click on a field - or have it waste space in a list box.
For these reasons, I like BOTH the short date and the long date in some of my tables.
Many thanks again.

 

[Libre]

Edit:
I tried the update query too - it worked as well!
thx
Another edit:
Now that I've accomplished what I wanted, I realize it's probably better to just calulate the short date in a query rather than having a hard value stored in the table. That way, I know that the short date and the stored long date will always agree.
Still thinking about this - but,
thx again