IsDate problem 2

[trudye10]

Heey guys I'm still struggling with this yyyymmdd date format. The pgm will not recognize it as a date. I coded:

If Not IsDate(rs1![Effective Date]) Then
GoSub write_Exception
GoTo KeepGoing
End If

rs1![Effective Date is yyyymmdd (ie. 20070131). It is writing an exception record for every record in the table. I even tried converting it to mmddyyyy and that didn't work.

Any suggestions??

Thanx,
Trudye

 

[PHV]

You may try this:
Dim strDate As String
strDate = Left(rs1![Effective Date], 4) & "-" & Mid(rs1![Effective Date], 5, 2) & "-" & Right(rs1![Effective Date], 2)
If Not IsDate(strDate) Then
...

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

 

[SkipVought]

Hi,

"...this yyyymmdd date format. The pgm will not recognize it as a date."

Strings like yyyymmdd, are not Real Dates.

Real Dates are NUMBERS like today is 39222. It can be Displayed by a Format to look like a date.

20070502 is merely a string of digits, that must be CONVERTED into a Date in order to do the kinds of things that are often doen with dates -- comparisons, arithmetic, sorting. String LIMIT you in this respect.

Skip,

_[red][/red]

 

[TheAceMan1]

How are ya trudye10 . . .

You post origination is indicitive of [blue]Text[/blue] data type and not [blue]Date[/blue] data type. [blue]Date[/blue] data type can be formatted for as you've shown and return [blue]true[/blue] for [blue]IsDate()[/blue]. Hence [blue]PHV's[/blue] string manipulation for [blue]Text[/blue] data type . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[trudye10]

Heey Guys thanx so much for responding. I tried what PHV suggested and still no luck.

So what you are saying is that in order to be recognized as a date the field must be numeric? The format does not matter (ie, yyyymmdd, ddmmyy, mmddyy, mmddyyyy), all will work as long as the field is numeric?

So all I have to do is convert my text date field to a numeric date field and my IsDate test should work?

Thanks again,
Trudye

 

[trudye10]

I imported the file, manually changed [Effecive Date] to a numeric field (the first date value processed was 20070412) and the pgm still did not recognize it as a date.

Do I have to reformat it to mmddyyyy or mm/dd/yyyy before it will recognize it as a date?

Thanx,
Trudye

 

[trudye10]

PHV you are Gooooood! Please ignore my previous posts, PHV's suggetion worked like a charm. If I could follow instructions I would be a smart as you are.

Thanx to everyone who responded,
Be well,
Trudye

 

[SkipVought]

"...you are saying is that in order to be recognized as a date the field must be numeric?"

No. Strings of digits in yyyymmdd or ddmmyyyy or any other combination is just TEXT -- not a REAL DATE.

Does 20070521 look ANYTHING like 39223. Yet 39223 is today's date VALUE that you can format to LOOK LIKE 20070521

You can't do much with 20070521, but you can do alot more with 39223. 39223 is how Access STORES today's date. If you have 20070521, then that all you have, a dumb string.

Skip,

_[red][/red]

 

[trudye10]

Thanx Skip it's clear as mud now (smile).

Be well,
Trudye

 

[SkipVought]

It ought to be as cllllear as dilithium crrrrrrrrystal, cahptin! ;-)

Skip,

_[red][/red]