Convert string to date

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

Guest

I need to copy one field in one table to another field in another table:

rec2("Pat_BD") = rec("Birthday")

The "Pat_BD" field is a Date field. The "Birthday" filed is a Text field
with the following format YYYYMMDD.

Any suggestions? Thanks.
 
Ken

Take a look at Access HELP on the CDate() function. You could use this in a
query to try to 'coerce' your [Birthday] value into a date/time value.

If that doesn't work, you could use the Left(), Mid() and Right() functions
to parse out each component of the date, and use the DateSerial() function
to generate a date/time value from the component values.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I don't think you can use CDate on a string in yyyymmdd format. However, you
can use the Format function to put that string into yyyy-mm-dd format, which
CDate will accept:

CDate(Format([Birthday], "####\-##\-##))

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Jeff Boyce said:
Ken

Take a look at Access HELP on the CDate() function. You could use this in
a query to try to 'coerce' your [Birthday] value into a date/time value.

If that doesn't work, you could use the Left(), Mid() and Right()
functions to parse out each component of the date, and use the
DateSerial() function to generate a date/time value from the component
values.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Ken said:
I need to copy one field in one table to another field in another table:

rec2("Pat_BD") = rec("Birthday")

The "Pat_BD" field is a Date field. The "Birthday" filed is a Text field
with the following format YYYYMMDD.

Any suggestions? Thanks.
Click to expand...
Click to expand...
 
I need to copy one field in one table to another field in another table:

rec2("Pat_BD") = rec("Birthday")

The "Pat_BD" field is a Date field. The "Birthday" filed is a Text field
with the following format YYYYMMDD.

Any suggestions? Thanks.
Click to expand...

A couple of ways to do this - one would be

Dim strDate As String
....
strDate = Rec("birthday")
rec2("Pat_BD") = DateSerial(Val(Left(strDate,4)), _
Val(Mid(strDate, 5, 2)),Val(Right(strDate,2))
....

John W. Vinson [MVP]
 
Back
Top