CODEWITHSUNDEEP
ms-accessvba
Big Pimpin
Big Pimpin

Reputation: 437

Use Date In If Statement

This syntax presents an error of

Data type conversion error

I thought I was comparing a string to a string?

Sub Test()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim dte As Date
Dim formatteddate As String
Set db = CurrentDb
dte = "01/01/2015 00:00:00"
formatteddate = Format(dte, "mm/dd/yyyy")
For Each tdf In db.TableDefs
    If (CurrentDb.TableDefs(tdf).DateCreated <= formatteddate) Then
      Debug.Print tdf.DateCreated
    End If
Next
Set db = Nothing
Set tdf = Nothing

End Sub

Upvotes: 2

Views: 1586

Answers (1)

HansUp
HansUp

Reputation: 97101

"I thought I was comparing a string to a string?"

DateCreated returns a Date/Time value, so the comparison DateCreated <= formatteddate is comparing a Date/Time to a String value.

However that is not the cause of error #3421, "Data type conversion error."

In CurrentDb.TableDefs(Item), Item must be the TableDef's ordinal number or Name property. (See the TableDefs Collection topic in Access' help system.)

But you're giving it tdf, a TableDef object, as Item. That mismatch triggers the error. You could avoid the error by supplying tdf.Name for Item:

CurrentDb.TableDefs(tdf.Name).DateCreated

However, since you already have tdf, there is no need to revisit CurrentDb.TableDefs ... just ask for tdf.DateCreated directly.

That will make the error go away, but you won't get any results when comparing DateCreated to the formatteddate String value. The solution for that problem is to compare tdf.DateCreated with a Date/Time value.

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim dte As Date

Set db = CurrentDb
dte = #1/1/2015#
For Each tdf In db.TableDefs
    If tdf.DateCreated <= dte Then
        Debug.Print tdf.Name, tdf.DateCreated
    End If
Next
Set db = Nothing
Set tdf = Nothing

Upvotes: 1

Related Questions