converting text to date

[wdbouk]

I was trying to convert text dates to date type and I am losing all the records. what is the appropriate way to do it?
Thx

 

[cmmrfrds]

If the text data is in one of these date formats then Cdate should work.
mm/dd/yyyy
or
yyyy-mm-dd

dim var as string
var = "2004-10-01"
debug.print Cdate(var)

 

[dhookom]

wdbouk,
Don't you think it would be important to provide some sample text dates so we could help?

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[wdbouk]

Hi,

Actually the cdate function worked well . I lost very few observations but I am ok with that. Thanks

 

[sbdeaver]

I understand what the 'cdate' is doing, but I'm unclear where to use it. I have a similar problem importing a weekly file; the dates come over as text (12/04/04). I would like to set it up so that once it is imported that field becomes dates. Any suggestions?

 

[PHV]

importing a weekly file
How you do that ?
Have you tried the import wizard and then save the specifications file ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[sbdeaver]

PHV,
Thanks for the tip. Will that work even if I have the importing automated using a Macro? Is there something I could write into the Macro that would let me change the data?

 

[zimmer9]

I imported a mainframe file to Table A. 1 of the fields I imported is "Date Of Birth" which comes into Table A in the format yymmdd. Thus, I defined a "Date Of Birth" field in the Table A as a text field since it is only 6 charactes in length (not 8 characters which is the field length of Date/Time fields).

I created a report based on Table A. If a record on Table A contains a field "Date Of Birth" with a value of 580723, how would I go about displaying it on the report as either 07/23/1958 or as 07/23/58 (whichever is easier).

Is there an expression that I could place in the FORMAT property of the text box field for Date of Birth in the Report that would convert the text value of 580723
in the Date Of Birth field from Table A.

 

[dhookom]

I would update a field to store the DOB as a date and not text. If you can't do this then try converting your text to a date with small function like:

Function FixStrDate(pstrDate As String) As Date
    'convert string like "580723" to 7/23/1958
    Dim lngYear As Long
    lngYear = IIf(Left(pstrDate, 4) > Format(Date, "yymm"), _
            1900, 2000) + Val(Left(pstrDate, 2))
    FixStrDate = DateSerial(lngYear, _
            Val(Mid(pstrDate, 3, 2)), _
            Val(Right(pstrDate, 2)))
End Function

Let us know if you need additional support on how to do this.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

If you don't want to change the field data type:
SELECT ...
,Mid([Date Of Birth],3,2) & "/" & Right([Date Of Birth],2) & "/" & Leftt([Date Of Birth],2) AS DOB ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244