Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rating: Thread Rating: 4 votes, 5.00 average. Display Modes
Old 07-09-2011, 02:12 PM   #1
Leigh46137
Newly Registered User
 
Join Date: Dec 2009
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Leigh46137 is on a distinguished road
Question Decimal Field Type Using SQL

I have a runtime Access database that I have developed using Access 2007. I need to change my price field type from Currency to Decimal.
I have to have 2 decimal places and I cannot use the Currency type because I have to export this table to a text file; the program I have to import it into chokes on the dollar signs.

In my code I am executing this SQL statement:

ALTER TABLE [History] ALTER COLUMN [Price] Decimal (5,2);

When I run the code, Access tells me that there is a syntax error in my SQL. (Everthing I've found on the internet in the last 3 hours of research says to use the syntax I'm using)

Can anyone tell me what the syntax ought to be?

Thanks!

Leigh46137 is offline   Reply With Quote
Old 07-10-2011, 12:07 AM   #2
spikepl
Eledittingent Beliped
 
spikepl's Avatar
 
Join Date: Nov 2010
Location: San Serriffe
Posts: 6,144
Thanks: 77
Thanked 980 Times in 958 Posts
spikepl is a jewel in the rough spikepl is a jewel in the rough spikepl is a jewel in the rough
Re: Decimal Field Type Using SQL

http://www.w3schools.com/sql/sql_datatypes.asp
spikepl is offline   Reply With Quote
Old 07-10-2011, 04:36 AM   #3
jdraw
Super Moderator
 
jdraw's Avatar
 
Join Date: Jan 2006
Location: Ottawa, Ontario, Canada;West Palm Beach, Florida
Posts: 11,552
Thanks: 54
Thanked 1,872 Times in 1,822 Posts
jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice jdraw is just really nice
Re: Decimal Field Type Using SQL

I did a little googling and found an example that said you must use ADO with Decimal.
Here's a routine that should work.
Quote:
' Procedure : AlterTable
' Author : Jack
' Created : 7/10/2011
' Purpose : To alter a table with a field of Long Integer datatype to Decimal.
' Must use ADO based on a lot of Google work. n my example I created a table with 3 fields, ID Long Integer, a text field and another field.
' This example shows how to Alter the Long Integer field [ID] to decimal.
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'------------------------------------------------------------------------------
'
Private Sub AlterTable()
Dim cnn As New ADODB.Connection
On Error GoTo AlterTable_Error

Set cnn = CurrentProject.Connection
cnn.Execute ("Alter TABLE [TEST] ALTER COLUMN [ID] Decimal(7,3)")

Set cnn = Nothing

On Error GoTo 0
Exit Sub

AlterTable_Error:

MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure AlterTable of Module AWF_Related"

End Sub

jdraw is offline   Reply With Quote
Old 07-10-2011, 11:48 AM   #4
Leigh46137
Newly Registered User
 
Join Date: Dec 2009
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Leigh46137 is on a distinguished road
Re: Decimal Field Type Using SQL

Thanks, but I found that one too. Here's what happened when I tried it:

cnn As New ADODB.Connection

Compile error: User-defined type not defined
Leigh46137 is offline   Reply With Quote
Old 07-10-2011, 12:27 PM   #5
spikepl
Eledittingent Beliped
 
spikepl's Avatar
 
Join Date: Nov 2010
Location: San Serriffe
Posts: 6,144
Thanks: 77
Thanked 980 Times in 958 Posts
spikepl is a jewel in the rough spikepl is a jewel in the rough spikepl is a jewel in the rough
Re: Decimal Field Type Using SQL

You just need to set the reference to ADO library
CodeWindow->Tools->References

I just tried and Alter TABLE [TEST] ALTER COLUMN [ID] Decimal(7,3) worked just fine
spikepl is offline   Reply With Quote
Old 07-10-2011, 12:46 PM   #6
spikepl
Eledittingent Beliped
 
spikepl's Avatar
 
Join Date: Nov 2010
Location: San Serriffe
Posts: 6,144
Thanks: 77
Thanked 980 Times in 958 Posts
spikepl is a jewel in the rough spikepl is a jewel in the rough spikepl is a jewel in the rough
Re: Decimal Field Type Using SQL

"The decimal data type isn't supported in the default Jet 4.0 mdb file. You have to use the SQL Server compatibility syntax (ANSI 92) setting to use the decimal data type in the SQL Window.
Click on the menu, Tools > Options. Click on the Tables/Query tab. Mark the check box for "This database" in the SQL Server compatibility syntax (ANSI 92) section. This mode will affect the entire db, including queries with wildcards, so you may want to try this on a copy of your db.
"

from http://stackoverflow.com/questions/1...th-alter-table
spikepl is offline   Reply With Quote
Old 07-10-2011, 07:22 PM   #7
Leigh46137
Newly Registered User
 
Join Date: Dec 2009
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Leigh46137 is on a distinguished road
Re: Decimal Field Type Using SQL

I'm working with an Access Runtime database. I had to split it in order to be able to make modifications to the user interface. I cannot modify the backend database directly - I can only do it through code. (If I could do it directly, this wouldn't be an issue ) As far as I know, I cannot add references or change settings like that in the back end database.

Anyway, I finally found a work-around. Not pretty or proper, but it gets the job done! Since the field is in a temporary table whose sole purpose in life is to export records to a text file, I can get away with storing the data in a text field. The numbers and decimal point is there, the dollar sign isn't and the program it geting imported into appears to be happy.

Thanks fo all your suggestions and help!

Leigh46137 is offline   Reply With Quote
Old 07-22-2017, 08:02 AM   #8
Terry Lawson
Newly Registered User
 
Join Date: Mar 2010
Location: West Sussex, England
Posts: 12
Thanks: 5
Thanked 0 Times in 0 Posts
Terry Lawson is on a distinguished road
Re: Decimal Field Type Using SQL

Thanks guys

Use the sub, it works perfectly

Terry Lawson

Terry Lawson is offline   Reply With Quote
Reply

Tags
alter , decimal , sql

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
convert long decimal number to 2 decimal places jammyp Reports 5 11-11-2011 06:49 AM
SQL Statement for Decimal Data Type bnw Queries 0 01-15-2009 09:30 AM
sql data type Decimal and numeric treated as text in access mcgrcoAgain General 1 05-29-2008 08:20 AM
[SOLVED] a problem with decimal field tooty SQL Server 1 01-23-2006 09:14 AM
Decimal precision in derived field scotty8369 Forms 4 07-30-2004 04:06 AM




All times are GMT -8. The time now is 01:29 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World