Insert decimal? - Microsoft Access / VBA

Andy Jenkins

I'm thinking this ought to be easy (just not for me - lol).

Field Price has values like this:

12500
9999
12999
105099

What's happened is that the person who originally populated it neglected to
put in the decimal point. So I need to start from the right, move 2 digits
to the left and drop a decimal in to get:

125.00
99.99
129.99
1050.99

Mid function? Not sure...

Thanks.

Mar 24 '06 #1

Subscribe Reply

8155

fredg

On Fri, 24 Mar 2006 13:16:25 -0600, Andy Jenkins wrote:

I'm thinking this ought to be easy (just not for me - lol).

Field Price has values like this:

12500
9999
12999
105099

What's happened is that the person who originally populated it neglected to
put in the decimal point. So I need to start from the right, move 2 digits
to the left and drop a decimal in to get:

125.00
99.99
129.99
1050.99

Mid function? Not sure...

Thanks.

It depends.
What is the field's Datatype.
If it is Number Integer or Long integer, you cannot have a decimal
value.
If the datatype is Number (Double) or Currency,
=[FieldName]/100

125 becomes 1.25
12500 becomes 125

The above are still numbers.

If you just wish to display the value with 2 decimals, use:
=Format([FieldName]/100,"#.00")
but this is now a string value and cannot be used in future
calculations.
125 becomes 1.25
12500 becomes 125.00

You can use the above expressions in an Update query to permanently
change the field values.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Mar 24 '06 #2

Similar topics

3449

Insert Trigger Help

by: Martin | last post by:

Hello, I'm new with triggers and I can not find any good example on how to do the following: I have two tables WO and PM with the following fields: WO.WONUM, VARCHAR(10) WO.PMNUM, VARCHAR(10) WO.PROBLEMCODE, VARCHAR(8)

Microsoft SQL Server

1977

how let insert real number

by: SAN CAZIANO | last post by:

how can i insert only a real number (an integer with only one decimal separator) in a edit field perhaps in onchange event I have to test if the decimal separator in the text is >0 I simply have to put it as a null key

Javascript

11951

Type Mismatch Error On Bulk Insert

by: Mike | last post by:

I am getting a type mismatch error when I do a bulk insert. ---Begin Error Msg--- Server: Msg 4864, Level 16, State 1, Line 1 Bulk insert data conversion error (type mismatch) for row 1, column 14 (STDCOST). ---End Error Msg--- The STDCOST is set to decimal (28,14) and is a formatted in Access as a number, single with 14 decimal. I...

Microsoft SQL Server

2252

"Connection is dead" on ODBC insert of decimal into DB2

by: John D. | last post by:

I am having problems inserting Decimal values into a DB2 database via a .NET/C# application which is using an OdbcDataAdapter. Other field types such as VarChar, Int, Date, etc work ok, and I can insert literal values into a decimal field, but any attempt to insert a decimal field via an OdbcParameter causes a System.InvalidOperationException...

DB2 Database

7646

insert decimal value into MS SQL database

by: ven | last post by:

hello i`m makin a asp.net service with database connection where i have an insert with decimal value, when i run my function i get this error : There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT...

ASP.NET

2293

Bug (?) in SQL: Global temporary table gets truncated on failed insert.

by: fintudet | last post by:

Hello. During some experimentation with DB2 decimal numbers representations, I have discovered very unexpected behaviour of the global temporary table. Consider this example. Such I had executed the example: # db2 -td@ -f FILENAME. When an single Insert into table fails, the table gets empty. Could anybody shed some light on this ?

DB2 Database

6204

SQL Insert Help

by: Dot Net Daddy | last post by:

Hello, I cannot get the following Insert Command work. I get the error: Error converting data type varchar to numeric. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Microsoft SQL Server

9395

Rowlock in insert query

by: Santy | last post by:

Hello All, I am new to DB2, Currently i am using DB2 version 8.02 personal edition on Windows XP Professional. And Facing problem with rowlock in insert query. I have created a sample database as CREATE TABLE SCEMANAME.LOG ( Sequence DECIMAL(9 ,0) NOT NULL ,

DB2 Database

4040

Insert Data Into Table with GridView or Calling Function

by: troydixon | last post by:

Hello, I am new at this, and have been trying to insert data into a table by using the footer of a gridview (which I dont like) or by using a detials view on the same page that is doing the following: 1) gets query string from url and filters records (works great) via a Details View 2) shows notes that have been added to the record shown in...

.NET Framework

3552

MySQL Insert

by: maestroQC | last post by:

Hi, Its one of those days. I cannot solve this. Any help would be greatly appreciated! When I execute this: class Db(object): def insertAccount(self, date, accountNumber, description, openingBalance): dec = decimal.Decimal(openingBalance)

Python

7420

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...

Windows Server

7680

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...

C / C++

7778

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...

General

5349

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...

Microsoft Access / VBA

4966

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...

C# / C Sharp

3476

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...

Networking - Hardware / Configuration

3459

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

1033

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

731

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

General