Deleting data using a query

[Raj]

Is it possible to delete only part of the data in a
field, see below. I would like to delete only the time
00:00:00 and leave the date using a query?
thank you in advance

ex. "9/30/03 00:00:00"

 

[Liz]

Use an UPDATE QUERY, not a Delete Query.
In the Update To row of the field (in this case time) type
in Null.
Only enter the field you are updating in the design grid,
UNLESS you require criteria to be associated with the
update. In that case include the criteria required field
and leave the Update To row of this field blank.

Good Luck!

 

[Cheryl Fischer]

You could use the following update query:

UPDATE MyTable SET [MyTable].[MyDate] = Format([MyDate],"Short Date")

 

[Van T. Dinh]

Not delete. You want to UPDATE the Field Value to something else.

If you mean you get rid of the *non-zero* time component, try:

UPDATE YourTable
SET DateTimeField = DateValue(DateTimeField)

If your DateTime values already have zero time component but you want to
display the values with no time component, use an appropriate date format
when you display.

Note that a DateTimeField ALWAYS has a time component. If you only assign a
date value, the time component is zero but it is still there!

 

[Raj]

I would like to thank all that replied to my post.
Please forgive my ignorance, but I am a neophyte to
Access and trying to teach myself on a project for work.
I also think I stated my question wrong..
I have data that was pulled form a commercial database
and it has the date and time both in the field and what I
would like to do is strip the time data and leave the
date. I am not sure if one or all of the examples will
help me..
Thanks again..

 

[Immanuel Sibero]

Hello,
Looks like you are trying to parse the data into its components - date and
time, keep the date and discard the time. The following link to Dev Ashish's
site can be used to do this.

http://www.mvps.org/access/strings/str0003.htm


Hope this helps.

Immanuel Sibero

 

[Van T. Dinh]

Firstly, open the Table in DesginView and *make sure* it is a DateTime
Field. Quite often, when you pull data from other database engine, the data
may look like date/time but can be stored as Text.

If the Field is a DateTime Field, use either Cheryl or my SQL String.
Adjust the Table name and Field name to suit your Table.

Perhaps mine is a bit more efficient and safer. My SQL String simply set
the time component to zero while Cheryl's SQL actually converts the DateTime
to Text (without the text for time) and then relies on JET to type-cast the
Text back to date correctly.

(Sorry Cheryl)

Note that DateTime Field ALWAYS has the time-component. If you only set the
date, the time component is 0.0 = 00:00:00 in time notation.
If you don't want to display the time component, you can simply set
appropriate format where you display the DateTime Field values.

 

[Cheryl Fischer]

(Sorry Cheryl)

No prob, Van! As so frequently happens when I read your posts, I have
learned something valuable.

 

[Van T. Dinh]

and I have certainly learnt from your posts.