Update fields using SQL

J

JasonS

Hello!
I've got a question regarding formatting data in database using SQL
statement.
I've got a field (Field2) containg such values:
20031202
20031215
20031231
20040123
etc...
They are stored as text values.
What I want to do using SQL statement is to change these values to:
2003/12/02
2003/12/15
2003/12/31
2004/01/23
I want to make them "look like" date in yyyy/mm/dd format, not changing
field's properties - it will still be a text field.

What I've written so far is

Dim MySQLString
MySQLString = "UPDATE Table1 SET Field2 = Format$([Field2],"yyyy/mm/dd")"
DoCmd.RunSQL MySQLString

But Office Assistant says that he can't run this query due to field's
property confilcts :(
How can I fix that?

Thanx for helping me!
 
M

Michael Keating

Hi,

there are a few string handling functions available, and they would help you
here.

You could use the following:

Dim MySQLString
MySQLString = "UPDATE Table1 SET Field2 = Left([Field2],4) + '/' +
SubSrting([Field2],5,2) + '/' + Right([Field2],2)"
DoCmd.RunSQL MySQLString

or you could play with SubString thoughout .. you'll find them all in Books
Online.

HTH

MFK.
 
V

Van T. Dinh

SubSrting() or SubString()???

I can't find either in VBA. I rhink Michael meant Mid().

HTH
Van T. Dinh
MVP (Access)


-----Original Message-----
Hi,

there are a few string handling functions available, and they would help you
here.

You could use the following:

Dim MySQLString
MySQLString = "UPDATE Table1 SET Field2 = Left ([Field2],4) + '/' +
SubSrting([Field2],5,2) + '/' + Right([Field2],2)"
DoCmd.RunSQL MySQLString

or you could play with SubString thoughout .. you'll find them all in Books
Online.

HTH

MFK.


JasonS said:
Hello!
I've got a question regarding formatting data in database using SQL
statement.
I've got a field (Field2) containg such values:
20031202
20031215
20031231
20040123
etc...
They are stored as text values.
What I want to do using SQL statement is to change these values to:
2003/12/02
2003/12/15
2003/12/31
2004/01/23
I want to make them "look like" date in yyyy/mm/dd format, not changing
field's properties - it will still be a text field.

What I've written so far is

Dim MySQLString
MySQLString = "UPDATE Table1 SET Field2 = Format$([Field2],"yyyy/mm/dd")"
DoCmd.RunSQL MySQLString

But Office Assistant says that he can't run this query due to field's
property confilcts :(
How can I fix that?

Thanx for helping me!
Click to expand...


.
Click to expand...
 
D

Douglas J. Steele

I suspect Michael read the "using SQL" in the title as meaning "using SQL
Server", in which case it is SubString, not Mid.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


Van T. Dinh said:
SubSrting() or SubString()???

I can't find either in VBA. I rhink Michael meant Mid().

HTH
Van T. Dinh
MVP (Access)


-----Original Message-----
Hi,

there are a few string handling functions available, and they would help you
here.

You could use the following:

Dim MySQLString
MySQLString = "UPDATE Table1 SET Field2 = Left ([Field2],4) + '/' +
SubSrting([Field2],5,2) + '/' + Right([Field2],2)"
DoCmd.RunSQL MySQLString

or you could play with SubString thoughout .. you'll find them all in Books
Online.

HTH

MFK.


JasonS said:
Hello!
I've got a question regarding formatting data in database using SQL
statement.
I've got a field (Field2) containg such values:
20031202
20031215
20031231
20040123
etc...
They are stored as text values.
What I want to do using SQL statement is to change these values to:
2003/12/02
2003/12/15
2003/12/31
2004/01/23
I want to make them "look like" date in yyyy/mm/dd format, not changing
field's properties - it will still be a text field.

What I've written so far is

Dim MySQLString
MySQLString = "UPDATE Table1 SET Field2 = Format$([Field2],"yyyy/mm/dd")"
DoCmd.RunSQL MySQLString

But Office Assistant says that he can't run this query due to field's
property confilcts :(
How can I fix that?

Thanx for helping me!
Click to expand...


.
Click to expand...
Click to expand...
 
V

Van T. Dinh

Hi Doug

Yes, you are correct that Michael was thinking of SQL Server. I didn't pick
up that Michael mentioned B.O.L.

However, the O.P. use DoCmd.RunSQL so I think it has to be JET SQL and
therefore Mid() rather than SubString() would be the go (unless ADP???).
 
M

Michael Keating

Yup, you guys are quite right .. I'd got my head stuck into transact sql ..
I need to check which group I'm in more carefully <G>

Thanks for clearing up the confusion.

MFK.
 
Top