Microsoft Access field need to convert to date. 5/17/2018.

donnie91910
donnie91910 used Ask the Experts™
on
I have a field an MSAccess table where I need to strip off the literal part in the field.
The table is called User_Time and the field is called Stat_1.

This is what the data looks like:
5/17/2018 6:07 PM by Smith, Kyle

I need the data to look like:
5/17/2018

Then I need to convert the new data to a date time field.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
=mid([Stat_1],1,instr(1[Stat_1]," "))
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
You can create a query like this after you have added new date field to the table:

Update User_Time
Set NewDateField = DateValue(Left(Replace([Stat_1], " ", "  "), 10))

Open in new window

David BernsteinChief Developer/ Microsoft Access

Commented:
Use an update query to
NewDatefield=Format(Stat_1,"Mm/dd/yyyy")

Where NewDateField is a date / time field
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
David, did you even try to check that?
Format returns a string, not a date, and - in this case - that would be the full string.
David BernsteinChief Developer/ Microsoft Access

Commented:
UPDATE tablel SET date2 = Format([date1],"mm/dd/yy");

date1 and date2 are both date fields
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
I have no idea what you are trying to do, nor why you are posting these.
For your last post to work, it should simply read:

UPDATE tablel SET date2 = [date1]

Open in new window

though this has no relevance to the question.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial