Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


SQL Query Create Table Decimal Field

Posted on 2006-04-26
Medium Priority
Last Modified: 2008-01-09

Either my brain isnt quite functioning tonight or there is something wrong.

In MS Access 2003 i open up the Query designer and select SQL View

I type in CREATE TABLE ProductCode(MinWeight Decimal(5, 2))

And the Decimal comes up as a syntax error

So does

How do i create a Decimal field in MS Access using the query designer in SQL View.

Question by:adam_pedley
  • 3
  • 2
  • 2
  • +2
LVL 77

Expert Comment

ID: 16542804
Hi adam_pedley,
I don't believe you can.
I think you have to use an ADO statement in VBA to execute the SQL string.

    CurrentProject.Connection.Execute strSql

Your first attempt looks OK to me.

LVL 44

Expert Comment

by:Leigh Purvis
ID: 16543279
What is it you're trying to achieve?
You'll have to use the Jet accepted equivalents.

You mentioned Double - but just substituting Decimal in your statement won't work but
CREATE TABLE ProductCode (MinWeight Double)
LVL 85

Accepted Solution

Rory Archibald earned 2000 total points
ID: 16543302
Not sure if it's the same in 2003 as it is in 2002, but under Tools-Options on the Tables/Queries tab, there is an option to use ANSI 92 syntax. If you check this, it will allow your statement to run. However, it will also expect all queries to use ANSI 92 syntax and expect different wildcards (% rather than *) so may well not be worth it! The alternative is to create the table in code, I guess.

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

LVL 44

Expert Comment

ID: 16543836
This from A2K Help - probably the same still for A2003

Decimal data type
A data type that contains decimal numbers scaled by a power of 10. For zero-scaled numbers, that is, numbers with no decimal places, the range is +/-79,228,162,514,264,337,593,543,950,335. For numbers with 28 decimal places the range is +/-7.9228162514264337593543950335. The smallest non-zero number that can be represented as a Decimal is 0.0000000000000000000000000001.

Note that at this time the Decimal data type can only be used within a Variant. You cannot declare a variable to be of type Decimal. You can, however, create a Variant whose subtype is Decimal using the CDec function.

Author Comment

ID: 16549699
Thanks all

rorya solution worked

I was actually just using it to create SQL statements to test them.

LVL 44

Expert Comment

ID: 16553060
Just curious, do you have a Decimal datatype in A2003?

Author Comment

ID: 16558740
Well when you create a table in design view you choose the data type of number then field size of decimal. I thought it was always there though.

Im creating the tables in SQL using queries in SQL View.
LVL 85

Expert Comment

by:Rory Archibald
ID: 16560227
Incidentally, if you run that SQL in ADO - a simple currentproject.Connection.Execute "CREATE TABLE ProductCode(MinWeight Decimal(5, 2))" command - it works perfectly, so it seems to be purely a limitation in the query designer.
LVL 44

Expert Comment

ID: 16563225
That SQL in the Query Builder in A2K generates an error on the datatype.  Ergo, I think you have a Decimal datatype in A2003.

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question