If you want to inspect the details of the datatypes of some import specs you made
SELECT S.SpecName
, C.DataType
, C.FieldName
FROM MSysIMEXSpecs S
INNER JOIN MSysIMEXColumns C
ON S.SpecID = C.SpecID;
but are confused about when the numbers in the DataType column mean this can help.
I just went through made a spec with each of the data type options in the Data Type drop down then looked at how the numbers matched to the names in MSysIMEXColumns table.
From that I made this function:
Public Function SpecDataType(asNumber) As String
Select Case asNumber
Case 1: SpecDataType = "Yes/No"
Case 2: SpecDataType = "Byte"
Case 3: SpecDataType = "Integer"
Case 4: SpecDataType = "Long"
Case 5: SpecDataType = "Currency"
Case 6: SpecDataType = "Single"
Case 7: SpecDataType = "Double"
Case 8: SpecDataType = "Date/Time"
Case 10: SpecDataType = "Text"
Case 11: SpecDataType = "OLE Object"
Case 12: SpecDataType = "Hyperlink"
Case 12: SpecDataType = "Memo"
Case Else: SpecDataType = "Undefined data type"
End Select
End Function
Now if you can see the name with the data type number.
SELECT S.SpecName
, SpecDataType(C.DataType) as DataTypeName
, C.DataType
, C.FieldName
FROM MSysIMEXSpecs S
INNER JOIN MSysIMEXColumns C
ON S.SpecID = C.SpecID;
Update:
These are the mappings for the DateOrder numbers, too.
Public Function SpecDateOrder(asNumber) As String
Select Case asNumber
Case 0: SpecDateOrder = "DMY"
Case 1: SpecDateOrder = "DYM"
Case 2: SpecDateOrder = "MDY"
Case 3: SpecDateOrder = "MYD"
Case 4: SpecDateOrder = "YDM"
Case 5: SpecDateOrder = "YMD"
Case Else: SpecDateOrder = "Undefined DateOrder"
End Select
End Function
Which can be used in the same way as the DataTypes
SELECT S.SpecName
, S.DateOrder
, SpecDateOrder(S.DateOrder) as DateOrderName
FROM MSysIMEXSpecs S