Smart questions
Smart answers
Smart people
Join Tek-Tips Forums

Member Login

Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

CarpalT (MIS) (OP)
14 Mar 03 16:47
I have a field downloaded from another db that is a text field for a date in yyyymmdd order. I am trying to get it into Access date format so I can create a cross tab and a graph with it further down the line.
I used a make table query to convert the date using this expression
Dateval: (Val(Right([tdate],4)) & Val(Left([tdate],4)))

However that makes a number field and I can't change it to date in the table without losing the data.
If  make the expression
Dateval:CDate((Val(Right([tdate],4)) & Val(Left[tdate],4))))
in the query, the query returns #error.
Helpful Member!  Tranman (Programmer)
14 Mar 03 17:07
CDate usually does better with strings than with numbers.  If you just reorder the input field in a string like so:

myDate = mid(tdate,5,2) & "/" & right(tdate,2) & "/" & left(tdate,4)


Dateval = cDate(myDate)

you should be ok.

Good luck,

CarpalT (MIS) (OP)
14 Mar 03 17:17
Thank you Tranman, you deserve a star!
I was trying to use cdate on a number as my big fat Que book says "Cdate converts a numberic value to a date value. format Cdate(NumValue)"
So I didn't even try it on a string - Ha!
Happy Friday
Tranman (Programmer)
14 Mar 03 17:22
Not quite Miller time in Kansas, but soon, VERY SOON!
Travlnbard (MIS)
14 Aug 03 15:29
I have the exact same problem, but I do not know where to type the input field string referenced above.  I would like to covert the fields permanently from text to date.

Confused in NY (a.k.a Cathy)
Tranman (Programmer)
14 Aug 03 15:49
Not to worry, this is a pretty easy fix.

You can't convert yyyymmdd text fields directly to date/time fields, so here's what you do:

Create a new date/time field called DDATE (or whatever).

*for our purposes, I'll refer to your text date field as TDATE

Create an update query that includes the old text date field and the new DDATE field.

In the "update to" row for the DDATE column, put:

CDate(Mid([TDATE],5,2) & "/" & Right([TDATE],2) & "/" & Left([TDATE],4))

Run the query.

After it's done, look over your converted data, and if it's ok, delete your old text date column, and rename the DDATE column to that name.

That's it.

Good Luck,
Tranman   (Paul)
Tranman (Programmer)
14 Aug 03 15:56
Hi Again Cathy,
Something just crossed my mind (it wasn't a long trip):).

If you're concerned about the position of the column in the table, it might be better to just update your old date column to the right format, then change the type to date/time.

First you'd run an update query with just the text date column, updating it to:

Mid([TDATE],5,2) & "/" & Right([TDATE],2) & "/" & Left([TDATE],4)

Then go into design view on your table, and change the type to date/time.

I just tested this, and it worked fine....

CarpalT (MIS) (OP)
14 Aug 03 16:03
I imported the file into a table, then used a make-table query to make a new table from the file. In the query,(qryStep1ConvertDates I named it),  I typed this expression in the Field: box. It creates a new field from the old field tdate. Hope this helps! -

Newdate: Mid([tdate],5,2) & "/" & Right([tdate],2) & "/" & Left([tdate],4)

The world is full of good people.

skyel (Programmer)
19 Aug 03 11:42
Can you update the fields through a sql statement? I would like to change a yyyymmdd into a mm/dd/yyyy.  

Once that's done, I need to combine this column with a time column so it becomes a date time column...

can it be done in sql? or do I need to create a new column first.  

Thanks!  Tell me if this is really confusing!
CarpalT (MIS) (OP)
19 Aug 03 11:50
SKYEL, someone smarter than I am probably can! But I'm not a programmer, so I make do with what I can accomplish with queries, formulas, macros, and the like. If I had to do it, I would probably try to expand the expression in my 8/14 post to combine the date conversion with a time stamp in one expression. I know that when I write an expression in a query, Access generates sql, and I have looked at it using the view sql menu selection, but that's as far as I get.

I'm going to bow out of the sql part of this conversation and leave it to the experts.

The world is full of good people.

skyel (Programmer)
19 Aug 03 11:57
Ok, I'm thinking of doing something like this...

sql = "UPDATE PRSUDataAll SET APPDATE = Mid(APPDATE, 1, 4) & "/" & Mid(APPDATE, 5, 6) & "/"
    cn.Execute sql
    cn.Execute sql

where APPDATE is the yyyymmdd that I want to change in to yyyy/mm/dd and FIRSTAPP is the time, and PRSUDataAll is the Table name... I could be completely off track... I don't know...
skyel (Programmer)
19 Aug 03 12:02
OK, let's just forget I said anything... I'm going off on some tangent that you people probably don't need to be bothered about...  Sorry...
CarpalT (MIS) (OP)
19 Aug 03 12:09
Sorry I didn't mean to be a wet blanket! Just didn't want you to wonder why there would be no reply from me on that one. My intent is to observe and try to learn from someone else's answer to your question. Maybe this thread is old enough that you need to start a new one with your question. (And attract the attention of someone who REALLY knows something!)
Hope you get your answer - have a great day!

The world is full of good people.

GoDawgs (MIS)
19 Aug 03 12:09
You can do it all in one step:

sql = "UPDATE PRSUDataAll SET APPDATE = Left(APPDATE, 4) & "/" & Mid(APPDATE, 5, 2) & "/" & Right(APPDATE, 2) & " " & FIRSTAPP

That should work fine...problem is that you are putting it into the APPDATE field, which is either a text field or number field, not a date field (and actually if it's a number field then this won't work since we're trying to put text in there).  Basically what I'm saying is I'd suggest creating a new date/time field and appending the data into there instead of the APPDATE field.  Hope that helps.

skyel (Programmer)
19 Aug 03 13:14
Thanks for your help, but I'm still having some trouble.  I wrote this sql statement:

sql = "UPDATE PRSUDataAll SET FIRSTAPPDATE = Left(APPDATE, 4) & "/" & Mid(APPDATE, 5, 2) & "/" & Right(APPDATE, 2) & " & FIRSTAPP
    cn.Execute sql

where FIRTAPPDATE is my new field for date/time.  I got this message:

Microsoft VBScript runtime error '800a000d'

Type mismatch: '[string: "UPDATE PRSUDataAll S"]'

Does this mean that the field data type and the data I'm trying to input are not the same?  If so, what type should they be and how might I do this?


GoDawgs (MIS)
19 Aug 03 13:28
Hmmm...well first off I'm not sure if the post did this or not, but there's a bit of a difference in your posted query...this part:


should be:


Basically that's putting a space between the date and the time.  Hopefully that solves it, if not then let me know.

skyel (Programmer)
19 Aug 03 13:35
Well, when I put the space and " in, I get a message saying there's an unterminated string constant at the end.  So I tried to put a " at the end of the sql statement and it says that there's an expected end of statement after the " which is after the space...
GoDawgs (MIS)
19 Aug 03 13:39
One last quick fix try...change the end to:

(APPDATE, 2) & " " & FIRSTAPP & ";"

skyel (Programmer)
19 Aug 03 13:51
I get "expected end of statement"

sql = "UPDATE PRSUDataAll SET FIRSTAPPDATE = Left(APPDATE, 4) & "/" & Mid(APPDATE, 5, 2) & "/" & Right(APPDATE, 2) & " "[here] & FIRSTAPP & ";"

... should I be using any single quotes?

Thanks for your patience!

skyel (Programmer)
19 Aug 03 14:06
This works:

sql = "UPDATE PRSUDataAll SET FIRSTAPPDATE =" & Left(APPDATE, 4) & "/" & Mid(APPDATE, 5, 2) & "/" & Right(APPDATE, 2)& FIRSTAPP
    //cn.Execute sql

I'm not sure why the // is in front of the Execute statement, but so far, no errors... I guess I'll find out when I try to read the data... Thanks for your help!
skyel (Programmer)
19 Aug 03 14:29
hmmm... nothing in my new column... sigh...
GoDawgs (MIS)
19 Aug 03 14:49
You know what, I've been reading your query wrong the entire time I think...AppDate and FirstApp are actual fields in the PRSUData table, not variables in the code.  If that's true then the fields should be included in the sqlstring.  

SQLString = "UPDATE __test SET [__test].FIRSTAPPDATE = Left([appdate],4) & "/" & Mid([appdate],5,2) & "/" & Right([appdate],2) & " " & [firstapp];"

Then do:

CurrentDb.Execute SQLString

Make sure you change your variable to SQLString...just to be safe.  Hope that finally helps...
GoDawgs (MIS)
19 Aug 03 14:51
This came out wrong when it got posted...use apostrophes (single quotes) instead of double quotes everywhere except for the very beginning and the very end.
Travlnbard (MIS)
28 Aug 03 12:23
Thanks so much for your assistance.  It works great! (And since I have had to it for 18 date field this posting has been a great help.)
PS Next time I will remember to mark the post for e-mail notification so that I know when someone replies (duh).

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close