Update fields using SQL

  • Thread starter Thread starter JasonS
  • Start date Start date
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!
 
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.
 
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...
 
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...
 
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???).
 
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.
 
Back
Top