DateSerial Type MisMatch 1

[trudye10]

Hi Guys, I'm having a problem with date coversion. I am getting a "Type Mismatch" error, here is a sample of my code. Also is this overkill moving the date so many times?

Function ReFormat_Date()
Dim db1 As Database
Dim rs1 As Recordset
Dim sDate As String
Dim strDate As Date

Set db1 = DBEngine(0)(0)
Set rs1 = db1.OpenRecordset("zz_Export_SC_Record_Layout", dbOpenDynaset)

Do While Not rs1.EOF
rs1.Edit
If Len(rs1!eff_Date) > 0 Then
sDate = rs1!eff_Date
strDate = Format(DateSerial(Left(strDate, 4), Mid(strDate, 5, 2), Right(strDate, 2)), "mmddyyyy")
rs1!eff_Date = sDate
End If
rs1.Update
If Not rs1.EOF Then
rs1.MoveNext
End If
Loop
End Function

Thank so much,
Trudye

 

[AlexCuse]

I don't like the looks of this:

strDate = Format(DateSerial(Left(strDate, 4), Mid(strDate, 5, 2), Right(strDate, 2)), "mmddyyyy")

Where are you assigning strDate?

DO you mean to be using sDate in your DateSerial operations? I never use DateSerial so I can't help you with that, but you are not going to be able to feed it a null value.

Before you go nuts with this, can I ask why you need to reformat the date in your table?

You would be much better off to store it as a date/time column, and format it in your presentation layer.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[SkipVought]

Trudye,

A DATE is a DATE, not matter how its FORMATTED.

So why all the handstands and somersaults?

This...

            sDate = rs1!eff_Date
            strDate = Format(DateSerial(Left(strDate, 4), Mid(strDate, 5, 2), Right(strDate, 2)), "mmddyyyy")
            rs1!eff_Date = sDate

has ZERO net effect, assuming

1. rs1!eff_Date is a Real Date and
2. there are no errors in logic.

However if rs1!eff_Date is TEXT, it's another issue. WHY store a DATE as TEXT??? Especially in mmddyyyy format???

Skip,

_[red][/red]

 

[missinglinq]

Also, how does the date strDate currently appear? By your code it would appear to be "2007/05/16" Is this correct?

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[missinglinq]

Since the syntax for DateSerial is

DateSerial(year, month, day)

We have to assume that your data for strDate appears as "2007/05/16"

The problem is in your Mid function:

Format(DateSerial(Left(strDate, 4), Mid(strDate, 5, 2), Right(strDate, 2)), "mmddyyyy")

With a strDate of "2007/05/16" Mid(strData, 5,2) will produce

"/0"

not "05" as you expected!

It needs to be

Format(DateSerial(Left(strDate, 4), Mid(strDate, 6, 2), Right(strDate, 2)), "mmddyyyy")

for the month arguement in DateSerial to be "05"

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[trudye10]

Hi Guys, thanks so much for the overwhelming response (smile).
I agree that a date is a date is a date. But we are required to upload this file to a system that is looking for the dates to be in the format mmddyyyy.

The dates are currently text and in format yyyymmdd (not yyyy/mm/dd).

Like you I cannot understand why DateSerial is not working. I tried the Format() function and that just gave me all kinds of problems. I searched the archives of Tek_Tips and found the DateSerial Function, I had never heard of it before.

If not DateSerial then what do you Guys suggest?

Thanx again,
Trudye

 

[SkipVought]

Like I stated, your example performs NO NET CHANGE.

If you want to send a different STRING...

            sDate = Format(rs1!eff_Date, "mm/dd/yyyy")
[code]


Skip,
[sub]
[glasses] [b][red][/red][/b]
[b][/b] [tongue][/sub]

 

[PHV]

Why not simply this ?
rs1!eff_Date = Format(DateSerial(Left(rs1!eff_Date, 4), Mid(rs1!eff_Date, 5, 2), Right(rs1!eff_Date, 2)), "mmddyyyy")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Missed your, "...The dates are currently text and in format yyyymmdd"

PHV has your solution.

Skip,

_[red][/red]

 

[trudye10]

Thanks Guys for trying, this is killing me. I tried PHV's solution and got a "Type Mismatch". I tried coverting it first using CInt and CDate but I got an Overflow again, even tho the result field was dimmed Long.

It won't format it, it won't convert it, this field needs to be exorcised.

Sincerely,
Trudye

 

[PHV]

And what about this ?
rs1!eff_Date = Mid(rs1!eff_Date, 5, 2) & Right(rs1!eff_Date, 2) & Left(rs1!eff_Date, 4)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[trudye10]

You were correct O'Wise One. It didn't work before because I did something wrong. I corrected MY error, reran and Voila! it worked.

Thank you so much,
Trudye