Formatting dates while appending queries

[Guest]

Hello all

I have one table (field=[start_date]) where dates appear like this:
3/28/2006 12:00:00 PM

I am appending this data to another table (field named [date] where I need
the time trimmed off of the end so that it would appear like this:
3/28/2006

Is there a way to code this into the append query?

Thanks!

 

[Allen Browne]

In the Field row of your append query, replace:
MyDateField
with:
DateValue(MyDateField)

 

[John Vinson]

Hello all

I have one table (field=[start_date]) where dates appear like this:
3/28/2006 12:00:00 PM

I am appending this data to another table (field named [date] where I need
the time trimmed off of the end so that it would appear like this:
3/28/2006

Is there a way to code this into the append query?

Thanks!

These are the same date and the same value. A Date/Time field is not
stored as text. It's actually stored as a Double Float number; either
of the above is actually stored internally as 38804.0000000000.

You can set the Format property of any Form or Report control in which
you're displaying this date however you like - "mm/dd/yyyy" or "Short
Date" or "dddd, dd mmmm yyyy" or "hh:nn:ss \o\n mm/dd/yy", it makes no
difference to what's stored.

John W. Vinson[MVP]

 

[Guest]

Okay, thanks, going to try that!

In the Field row of your append query, replace:
MyDateField
with:
DateValue(MyDateField)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello all

I have one table (field=[start_date]) where dates appear like this:
3/28/2006 12:00:00 PM

I am appending this data to another table (field named [date] where I need
the time trimmed off of the end so that it would appear like this:
3/28/2006

Is there a way to code this into the append query?

Thanks!

 

[Guest]

Thanks for the reply!

The problem is that I have a report that is ultimately based on that date
field. In the query I prompt the user to enter a date to generate a report
that shows all that happened on any given date. Without truncating the time
off then the user has to enter date and time. Based on what you wrote I'd
guess there's a way around that.. I just don't know what it is.

Thanks guys for the responses, I appreciate your time!

Hello all

I have one table (field=[start_date]) where dates appear like this:
3/28/2006 12:00:00 PM

I am appending this data to another table (field named [date] where I need
the time trimmed off of the end so that it would appear like this:
3/28/2006

Is there a way to code this into the append query?

Thanks!

These are the same date and the same value. A Date/Time field is not
stored as text. It's actually stored as a Double Float number; either
of the above is actually stored internally as 38804.0000000000.

You can set the Format property of any Form or Report control in which
you're displaying this date however you like - "mm/dd/yyyy" or "Short
Date" or "dddd, dd mmmm yyyy" or "hh:nn:ss \o\n mm/dd/yy", it makes no
difference to what's stored.

John W. Vinson[MVP]

 

[Guest]

Thanks Allen

This did exactly what I was looking for!

Cheers!
Matt

In the Field row of your append query, replace:
MyDateField
with:
DateValue(MyDateField)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello all

I have one table (field=[start_date]) where dates appear like this:
3/28/2006 12:00:00 PM

I am appending this data to another table (field named [date] where I need
the time trimmed off of the end so that it would appear like this:
3/28/2006

Is there a way to code this into the append query?

Thanks!

 

[John Vinson]

The problem is that I have a report that is ultimately based on that date
field. In the query I prompt the user to enter a date to generate a report
that shows all that happened on any given date. Without truncating the time
off then the user has to enter date and time. Based on what you wrote I'd
guess there's a way around that.. I just don't know what it is.

sorry - I misread your post! It seems that the incoming dates have a
time of noon (.5 in the fraction) which does need to be truncated.

To filter records for a given date, whether or not they contain a time
portion, you can use a criterion of

= [Enter date:] AND < DateAdd("d", 1, [Enter date:])

John W. Vinson[MVP]