Cut off part of a text/date field

[TJT]

I have a date field populated with "8/18/2008 1:10:32 PM"

my goal is to update the table field so that just the "8/18/2008" is retained.

 

[KARL DEWEY]

You try and do that you will get "8/18/2008 12:00:00 AM" in a DateTime field.
Use formatting to display it like this --
My_Date: Format([YourDateField], "m/d/yyyy")

 

[TJT]

I need to actually trim down the field...I tried the formatting approach, but
it was affecting some of my queries based on date range selection. I
converted the field to text as I assumed that would be the easist format to
trim off the portion that I don't want.

Thank you for your quick response, but I'm still in need of parsing that
data out

You try and do that you will get "8/18/2008 12:00:00 AM" in a DateTime field.
Use formatting to display it like this --
My_Date: Format([YourDateField], "m/d/yyyy")

--
KARL DEWEY
Build a little - Test a little

I have a date field populated with "8/18/2008 1:10:32 PM"

my goal is to update the table field so that just the "8/18/2008" is retained.

 

[KARL DEWEY]

Use this --
DateValue([DateTimeField])
as it strips time and leave date only for your date range queries.
--
KARL DEWEY
Build a little - Test a little

I need to actually trim down the field...I tried the formatting approach, but
it was affecting some of my queries based on date range selection. I
converted the field to text as I assumed that would be the easist format to
trim off the portion that I don't want.

Thank you for your quick response, but I'm still in need of parsing that
data out

You try and do that you will get "8/18/2008 12:00:00 AM" in a DateTime field.
Use formatting to display it like this --
My_Date: Format([YourDateField], "m/d/yyyy")

--
KARL DEWEY
Build a little - Test a little

I have a date field populated with "8/18/2008 1:10:32 PM"

my goal is to update the table field so that just the "8/18/2008" is retained.

 

[TJT]

I need to parse out just the date component...not account for the time
component.

xx/xx/xxxx

Use this --
DateValue([DateTimeField])
as it strips time and leave date only for your date range queries.
--
KARL DEWEY
Build a little - Test a little

I need to actually trim down the field...I tried the formatting approach, but
it was affecting some of my queries based on date range selection. I
converted the field to text as I assumed that would be the easist format to
trim off the portion that I don't want.

Thank you for your quick response, but I'm still in need of parsing that
data out

You try and do that you will get "8/18/2008 12:00:00 AM" in a DateTime field.
Use formatting to display it like this --
My_Date: Format([YourDateField], "m/d/yyyy")

--
KARL DEWEY
Build a little - Test a little


:

I have a date field populated with "8/18/2008 1:10:32 PM"

my goal is to update the table field so that just the "8/18/2008" is retained.

 

[KARL DEWEY]

I need to parse out just the date component...not account for the time
component.
What I posted does just that for a DateTime field.
--
KARL DEWEY
Build a little - Test a little

I need to parse out just the date component...not account for the time
component.

xx/xx/xxxx

Use this --
DateValue([DateTimeField])
as it strips time and leave date only for your date range queries.
--
KARL DEWEY
Build a little - Test a little

I need to actually trim down the field...I tried the formatting approach, but
it was affecting some of my queries based on date range selection. I
converted the field to text as I assumed that would be the easist format to
trim off the portion that I don't want.

Thank you for your quick response, but I'm still in need of parsing that
data out

:

You try and do that you will get "8/18/2008 12:00:00 AM" in a DateTime field.
Use formatting to display it like this --
My_Date: Format([YourDateField], "m/d/yyyy")

--
KARL DEWEY
Build a little - Test a little


:

I have a date field populated with "8/18/2008 1:10:32 PM"

my goal is to update the table field so that just the "8/18/2008" is retained.

 

[TJT]

Thank you, but I continuously receive:

"compile error. in query expression 'DateValue([CREATE_DATE_TIME])'."

component.
What I posted does just that for a DateTime field.
--
KARL DEWEY
Build a little - Test a little

I need to parse out just the date component...not account for the time
component.

xx/xx/xxxx

Use this --
DateValue([DateTimeField])
as it strips time and leave date only for your date range queries.
--
KARL DEWEY
Build a little - Test a little


:

I need to actually trim down the field...I tried the formatting approach, but
it was affecting some of my queries based on date range selection. I
converted the field to text as I assumed that would be the easist format to
trim off the portion that I don't want.

Thank you for your quick response, but I'm still in need of parsing that
data out

:

You try and do that you will get "8/18/2008 12:00:00 AM" in a DateTime field.
Use formatting to display it like this --
My_Date: Format([YourDateField], "m/d/yyyy")

--
KARL DEWEY
Build a little - Test a little


:

I have a date field populated with "8/18/2008 1:10:32 PM"

my goal is to update the table field so that just the "8/18/2008" is retained.

 

[KARL DEWEY]

What is the DataType of [CREATE_DATE_TIME] ?
Post sample data.

--
KARL DEWEY
Build a little - Test a little

Thank you, but I continuously receive:

"compile error. in query expression 'DateValue([CREATE_DATE_TIME])'."

I need to parse out just the date component...not account for the time

component.
What I posted does just that for a DateTime field.
--
KARL DEWEY
Build a little - Test a little

I need to parse out just the date component...not account for the time
component.

xx/xx/xxxx

:

Use this --
DateValue([DateTimeField])
as it strips time and leave date only for your date range queries.
--
KARL DEWEY
Build a little - Test a little


:

I need to actually trim down the field...I tried the formatting approach, but
it was affecting some of my queries based on date range selection. I
converted the field to text as I assumed that would be the easist format to
trim off the portion that I don't want.

Thank you for your quick response, but I'm still in need of parsing that
data out

:

You try and do that you will get "8/18/2008 12:00:00 AM" in a DateTime field.
Use formatting to display it like this --
My_Date: Format([YourDateField], "m/d/yyyy")

--
KARL DEWEY
Build a little - Test a little


:

I have a date field populated with "8/18/2008 1:10:32 PM"

my goal is to update the table field so that just the "8/18/2008" is retained.

 

[John W. Vinson]

I need to actually trim down the field...I tried the formatting approach, but
it was affecting some of my queries based on date range selection. I
converted the field to text as I assumed that would be the easist format to
trim off the portion that I don't want.

If you want to permanently and irrevokably destroy the time portion of the
field, run an Update query on the table updating it to

=DateValue([fieldname])

If instead (as I would recommend!) you want to keep the times but have your
query return all records during a given day, use a criterion of

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

 

[TJT]

My goal is the irrevokably destruction of the time component of this field,
yet when I run the update query I continually get the compile error.

my data type for the field is: Date/Time

sample field data:
10/20/2008 10:01:05 AM
10/20/2008 9:58:39 AM
9/4/2008 3:12:40 PM

my update query: (THAT KEEPS GIVING ME A COMPILE ERROR)
UPDATE cust_met SET cust_met.CREATE_DATE_TIME = DateValue([CREATE_DATE_TIME]);

I need to actually trim down the field...I tried the formatting approach, but
it was affecting some of my queries based on date range selection. I
converted the field to text as I assumed that would be the easist format to
trim off the portion that I don't want.

If you want to permanently and irrevokably destroy the time portion of the
field, run an Update query on the table updating it to

=DateValue([fieldname])

If instead (as I would recommend!) you want to keep the times but have your
query return all records during a given day, use a criterion of

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

 

[Bob Barrows]

My goal is the irrevokably destruction of the time component of this
field, yet when I run the update query I continually get the compile
error.

my data type for the field is: Date/Time

sample field data:
10/20/2008 10:01:05 AM
10/20/2008 9:58:39 AM
9/4/2008 3:12:40 PM

my update query: (THAT KEEPS GIVING ME A COMPILE ERROR)
UPDATE cust_met SET cust_met.CREATE_DATE_TIME =
DateValue([CREATE_DATE_TIME]);

Are there any nulls in the data? If so, you need to use Nz to eliminate
them:

DateValue(Nz([CREATE_DATE_TIME],#1900-01-01#))

 

[Bob Barrows]

My goal is the irrevokably destruction of the time component of this
field, yet when I run the update query I continually get the compile
error.

my data type for the field is: Date/Time

sample field data:
10/20/2008 10:01:05 AM
10/20/2008 9:58:39 AM
9/4/2008 3:12:40 PM

my update query: (THAT KEEPS GIVING ME A COMPILE ERROR)
UPDATE cust_met SET cust_met.CREATE_DATE_TIME =
DateValue([CREATE_DATE_TIME]);

.... OR
you can add a WHERE clause to prevent it from processing the Nulls:

WHERE Not CREATE_DATE_TIME Is Null

 

[TJT]

thanks for the replies, but my issue was that I need to load the reference
file:
microsoft excel 9.0 object library...then the datevalue formula worked!!

Thank you all for your effort!!

My goal is the irrevokably destruction of the time component of this
field, yet when I run the update query I continually get the compile
error.

my data type for the field is: Date/Time

sample field data:
10/20/2008 10:01:05 AM
10/20/2008 9:58:39 AM
9/4/2008 3:12:40 PM

my update query: (THAT KEEPS GIVING ME A COMPILE ERROR)
UPDATE cust_met SET cust_met.CREATE_DATE_TIME =
DateValue([CREATE_DATE_TIME]);

.... OR
you can add a WHERE clause to prevent it from processing the Nulls:

WHERE Not CREATE_DATE_TIME Is Null

 

[Bob Barrows]

Strange, it worked fine for me without explicitly loading the excel
library ... in fact, I just checked the references in my test database
and there is no reference at all to the Excel library.
DateValue is part of the VBA object library from what I can see, nothing
to do with Excel.
Maybe you're using a reserved keyword somewhere in your schema ... ?

Oh well, at least you've got it working.

thanks for the replies, but my issue was that I need to load the
reference file:
microsoft excel 9.0 object library...then the datevalue formula
worked!!

Thank you all for your effort!!

My goal is the irrevokably destruction of the time component of this
field, yet when I run the update query I continually get the compile
error.

my data type for the field is: Date/Time

sample field data:
10/20/2008 10:01:05 AM
10/20/2008 9:58:39 AM
9/4/2008 3:12:40 PM

my update query: (THAT KEEPS GIVING ME A COMPILE ERROR)
UPDATE cust_met SET cust_met.CREATE_DATE_TIME =
DateValue([CREATE_DATE_TIME]);

.... OR
you can add a WHERE clause to prevent it from processing the Nulls:

WHERE Not CREATE_DATE_TIME Is Null

 

[John W. Vinson]

thanks for the replies, but my issue was that I need to load the reference
file:
microsoft excel 9.0 object library...then the datevalue formula worked!!

I was guessing that it was a references problem... but why the EXCEL library!?
The Access 11.0 (or appropriate version) would have this function.