VBA IsDate function not appearing to work

[Ecniv]

Hello.

A weird one - wondered if anyone has come across this.

Usually when using isdate function it tells me whether the string/number sent to it is a date.

Appeared to work fine.

However now it is not working fine and I cannot see why.

I'm pulling data from Excel which is in a date format, but not a proper date (I think). I change the cell formatting to text (via number format and '@')
and the contents change to a number. Ah ha. IsDate should work. It doesn't.

Just so people can check to see if maybe something is corrupted/running wrong here...
The date, number
24/08/1971 , 26169

If any one has come across this and knows how to fix Access VBA to work, please let me know. For now I'm going to use the format function which (for some reason) is changing the value to a date correctly.

Thanks in advance

[Static]

change the format to Date and select 03/14/01 or 3/14/2001 or something
Excel likes to switch dates to julian format for some silly reason

[Static]

wait.. I misread that..

how are u pulling data? is the sheet linked? imported? etc...

[Ecniv]

instance of excel opened, the sheet opened in the instance. The cell accessed via the objects. I used to put it straight into the recordset field, but when data overflows the db field instead of the usual errors, the recordset seemed to corrupt and had to be cancelled/closed and reopened.

So the value/text goes into a temporary string variable. Then into the db field.

So I am changing those formatted fields from a date (with a * (star) in excel formatting) to text then using that text to check whether it is a date, and processing accordingly.

Only as originally posted - it didn't like it.

[DKenny]

Ecniv
From the VBA help file on Isdate

Syntax

IsDate(expression)

The required expression argument is a Variant containing a date expression or string expression recognizable as a date or time.

IsDate will always return an error if you pass it a LONG or INT value.
Here's a proc showing this

Sub TestISDate()
Dim dTestDate As Date
Dim sTestString As Date
Dim lTestLong As Long
    
    dTestDate = "24/08/1971"
    
    sTestString = CStr(dTestDate)
    lTestLong = CLng(dTestDate)
    
    Debug.Print dTestDate, IsDate(dTestDate)
    Debug.Print sTestString, IsDate(sTestString)
    Debug.Print lTestLong, IsDate(lTestLong)
End Sub

[Ecniv]

Hi,

Wow didn't know that. Did know about numbers, but not longs... tried your example and yeah it didn't like it. Possibly it is converting to a long then ....

Hmmm I'll try some things

[RobDog888]

I think you wanted to show this as a String

Dim sTestString As Date

You can use DateSerial if you need to pass a number.

Function DateSerial(Year As Integer, Month As Integer, Day As Integer)

[DKenny]

No, in my naming convention a prefix of 's' means a date, honestly it does....

You can also use a cdate to convert a LONG to a DATE

Sub TestISDate()
Dim dTestDate As Date
Dim sTestString As String
Dim lTestLong As Long
    
    dTestDate = "24/08/1971"
    
    sTestString = CStr(dTestDate)
    lTestLong = CLng(dTestDate)
    
    Debug.Print dTestDate, IsDate(dTestDate)
    Debug.Print sTestString, IsDate(sTestString)
    Debug.Print lTestLong, IsDate(lTestLong)
    Debug.Print CDate(lTestLong), IsDate(CDate(lTestLong))
End Sub

[Ecniv]

Don't mind how it gets there as long as it works.

At the moment, when I change the cells formatting to text it converts the date to a number.

Now those at the top half of the sheet are fine as proper dates. Those on the bottom on some sheets (out of 1000+) are formatted to a different date (one with a * next to it).

And ofcourse there are ones with crap data in

Just I used to think isdate worked with numbers and dates and strings, but it is not liking it - even when I put a number into the string variable before comparing.

Either that or this is a complete waste of your professional times (respectively) as I feel like I am getting thicker by the second in work heheh