determining variable type 'compatibility'

[normalice]

i am importing data from many text files which sometimes contain an error. These text files include dates, strings, and integers, but in order to make this program as hands-free as possible, i need to import all fields as text - in the event of an error in the text field, a field that is usually a date may actually turn out to be something like "12/2/20x[38475934f59dr.gpi9087304d0i". If i try to set the field in the destination table to accept nothing but dates, this will cause a run-time error, which requires human intervention (particularly mine).

Anyway, importing is no longer a problem, as long as i set everythng to text. However, I run into basically the same problem when i attempt to direct the data to its appropriate permanent table, as those must be "data-type" specific. Is there some function that can determine if a value is compatible with a variable type?

i.e. consider the pseudo-function:

function CanBeDate(str)
If str 'can be a date' then
  CanBeDate = True
else
  CanBeDate = False
End If
End Function

From here, it would be easy to handle the error (a simple

if nz(dlookup(True,"[Table]","CanBeDate([Field])=False"),False) then goto handle_errors

would be sufficient)

It's the second line of code that is my question - is there a function already in VB or even a simple one I could write to test "If str 'can be a date'"

[TheSmileyCoder]

There is a built in function called isDate and isNumeric.
Example usage:

? isdate("12/2/20x[38475934f59dr.gpi9087304d0i") returns False
? isdate("12/2/20") returns True

[NeoPa]

There is a whole set of functions starting Is... which can be used to determine whether strings are compatible with various datatypes. IsDate() is the first one you need of course. If you look it up under Help you'll find links to all the others that are available (under See Also).

[ADezii]

@normalice - Like yourself, and especially for larger Imports, I will Import all the Data as Text into a Temporary Table. I will then write Custom Code to make sure that all Field Data types match those of the MASTER. The following Code will create a Recordset based on the Imported Data, then check Fields 3 and 4 to make sure that they are in a Valid Date Format. It will also check Field 6 which must be Numeric. If either of these Fields does not have the correct Data Type, then I will post, in some manner, the Primary Key to uniquely identify the Field(s) in question, as well as the offending Fields. Null Values will show up as Invalid Data Type, but that can easily be adjusted. I'll simply post my Code, and should you have any questions, please feel free to ask:

Dim MyDB As Database
Dim rst As DAO.Recordset
Dim intFldctr As Integer
 
Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset(conTABLE_NAME, dbOpenSnapshot)
 
Debug.Print "------------------------------------------------------------------"
Debug.Print rst.Fields(0).Name, rst.Fields(2).Name, rst.Fields(3).Name, rst.Fields(5).Name
Debug.Print "------------------------------------------------------------------"
 
With rst
  Do While Not .EOF
    If Not IsDate(.Fields(2)) Or Not IsDate(.Fields(3)) Or Not IsNumeric(.Fields(5)) Then
      Debug.Print rst.Fields(0), IIf(Not IsDate(.Fields(2)), "Invalid Date", "OK"), _
                                 IIf(Not IsDate(.Fields(3)), "Invalid Date", "OK"), _
                                 IIf(Not IsNumeric(.Fields(5)), "Invalid Number", "OK")
    End If
     .MoveNext
  Loop
End With
 
rst.Close
Set rst = Nothing

Sample OUTPUT:

------------------------------------------------------------------
OrderID       OrderDate     RequiredDate  ShipVia
------------------------------------------------------------------
 10600        OK            OK            Invalid Number
 10603        OK            OK            Invalid Number
 10611        Invalid Date  OK            Invalid Number
 10616        Invalid Date  Invalid Date  OK
 10620        Invalid Date  OK            OK
 10627        OK            Invalid Date  OK
 10633        Invalid Date  OK            OK
 10640        OK            OK            Invalid Number
 10660        Invalid Date  OK            OK
 10668        OK            Invalid Date  OK
 10669        OK            OK            Invalid Number
 10676        Invalid Date  Invalid Date  OK
 10687        OK            Invalid Date  OK
 10693        Invalid Date  OK            OK
 10697        OK            Invalid Date  OK
 10728        Invalid Date  Invalid Date  Invalid Number
 10799        Invalid Date  OK            OK