Invalid Syntax in Create Table Query

[SarasotaIT]

I am trying to make a simple "data-definition" query in Access 2002 and cannot due to "invalid syntax" error. Here is my Query:
CREATE TABLE Table1
(COLUMN1 number (20),
COLUMN2 text (30),
COLUMN3 text (30));

In my query, if I replace "number" with "text", the query saves and works with no problem. I have tried performing a compact and repair but that did not help. What am I doing wrong here?

Thanks in advance!

Terry

 

[DanChard]

Try this:

CREATE TABLE Table1
(COLUMN1 number,
COLUMN2 text (30),
COLUMN3 text (30));

 

[SarasotaIT]

Thank you for such a timely response! Your suggestion worked, however, the number field MUST be 20 characters max. (the table will be exported in fixed-width format txt file). Is there a way to set a maximum length of a number field in a create table query?

 

[DanChard]

No problem. Since numbers are stored a little differently to text, the maximum length is stored as a maximum value rather than a number of digits.

Have a look at the 'FieldSize Property' help topic for more information on this...

Hope this is of some use to you, sorry I couldn't give you the answer you were looking for!

 

[SarasotaIT]

Thank you again...and again your response has been very helpful. I am now going to entertain the thought of leaving Column1 a text field since a big reason I was making it a number field is because that field, after export to a txt file, must be 20 characters AND be right-justified. As I understand it, a number field is the only data-type that is right-justified on export to a txt file. Is this correct, or is there some way to right-justify a text field?

 

[DanChard]

You will run into problems if you store numbers that you later want to perform calculations on as text. Of course, it's possible to convert them back to numbers in SQL for these calculations to be possible but it's still extra work.

If it's easier to store them as text though, and the 20 characters max thing is compulsary then I say go with that but I'd be reluctant to do it myself unless I fully understood why this field MUST be 20 characters long and would like to be able to explore the alternatives.

As for the right/ left justification thing. By default, numbers are justified to the right whereas text is justified to the left in forms and reports. If you export a table to a .TXT file it's tab-delimited anyway so data is justified either way.