How do I change data types in an update query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have imported data as text into a table, I want to change one of the
columns to a date then to date format "MMM" - is this possible with an update
query?
 
Yes, it is possible. You will need to use Left, Right and Mid functions to
crop the text parts for year, month and day, a Val function on each so you
get a numerical value, and all of those combined in a DateSerial function to
get the actual date in date/time format. A format function on it will return
just the month part in the desired format for storing (in the same field, as
text). Here's an example, assuming the original diled is called fDate, and
its format is yyyy/mm/dd:

Format(DateSerial(Val(Left([fDate],4)),Val(Mid([fDate],4,2)),Val(Right([fDat
e],2)),"mmm")

(watch out for wrapping in the post, it's all in one line!).

HTH,
Nikos
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Create new date column 4
Append/Update Query 6
Create Date Field from Text 2
text to Date/time 1
Changed Date in Query 3
Importing Date fields from Excel 2
Combine Duplicates in Query 0
Date Sorting in Crosstab 3

Back
Top