problem with access data type

[QASA25]

Hi all . I have a function that is displaying table datatype. I created a table in access table that has most of data type but when i run sql statement below(genrated wit the help of my function) in access 2000 sql window i get syntax error for the field definition for the bolded parts. I be happy if some one help me fix these data type so the sql statment runs on access 2000 sql window and creates the tables.I know i can use table designer but i need this method for my applicatin.It is strange that dattype is produced by fieldproperties of system tables but it gives syntax error on sql window!!.Thanks

part of my code that identifies the data type and it is called from my main function.

Function ShowTableFields(tdf As TableDef) As String
Dim fld As Field
Dim fldstr As String, fldtype As String, fldnull As String

    For Each fld In tdf.Fields
        fldtype = FieldType(fld.Type)
        If fldtype = "CHAR" Then
            fldtype = fldtype & "(" & fld.Size & ")"
        End If
        If fld.Properties("Required") Then
           fldnull = "NOT NULL"
        End If
        fldstr = fldstr & fld.Name & " " & fldtype & " " & fldnull & ", " & vbCrLf
    Next
ShowTableFields = Left(fldstr, Len(fldstr) - 4)
End Function

Create Table Table1testtype(textfeild Text , 
memofeild Memo , 
NumberField1 Byte , 
NumberField Integer , 
datetimeField DateTime , 
currentyField Currency , 
[b]automnumberfeild Longint , [/b]
[b]bolenField Yes/No , [/b]
[b]oleobjectFiled Decimal , [/b]
[b]numberfield2 Longint ,[/b] 
numberfiled3 Single , 
numbefiled4 Double , 
  
[b])[/b]

After removing the bold parts i get" syntax error on feild definition "on the last prantice.

Create Table Table1testtype2
(
textfeild Text , 
memofeild Memo , 
NumberField1 Byte , 
NumberField Integer , 
datetimeField DateTime , 
currentyField Currency , 
automnumberfeild Long , 
numberfield2 Long , 
numberfiled3 Single , 
numbefiled4 Double ,
[b] )[/b] ===============> syntax error

 

[PHV]

You have to get rid of the last comma.
Replace Yes/No by either YesNo or Boolean.
Replace Longint by either Long or Integer.


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[QASA25]

PHP many thanks for you reply. There is a data type for number which is Decimal. when i use Decimal word in create table sql the access sql window complains about its syntax. Could u tell me how to solve this problem.It is strange that e under Number field size there an Decimal option to select but when u use it in sql window it complains about its syntax .Thanks

 

[PHV]

The standard syntax is DECIMAL(precision,scale) but JetSQL is far from ANSI compliant and I guess the Decimal data type is not supported in DDL.
So replace Decimal by Numeric

FYI, an AutoNumber is created like this:
AutoNumberField COUNTER(1,1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886