Member Avatar for Nebil

Hello guys,
I have a problem with data type.I want to store a field with a currency format in access with vb.net.i did it by using the format built-in function in vb.net but it only stores the field in access if it's only in text data-type.If the field is in number format it would result in data type mismatch.

what is the solution for this?

  • 2 Contributors
  • 7 Replies
  • 566 Views
  • 2 Weeks Discussion Span
  • Latest Post Latest Post by tinstaafl

Dani AI

Generated

Short diagnosis and the right direction: as discovered, storing a formatted currency string in a Text field will let it look like money but prevents numeric operations (SUM, AVG) in reports. As noted, VB’s format/ToString produce strings. Best practice is to store raw numeric values in the database (Access Currency or a Number/Decimal field) and apply currency formatting only when displaying the data (forms, reports, labels). That keeps math correct and Crystal Reports able to sum the column.

Practical notes for Access: use the Currency data type (it’s intended for money and keeps fixed decimal precision) or Number with a Decimal field size. The table’s Format property (set to Currency) only affects display in Access, it does not change the stored value. If existing rows hold "$1,000.00" text, create a new numeric column, convert the old text into numbers (sanitise/remove currency symbols and thousands separators) and populate the numeric column, then drop/rename columns.

Example migration and insert patterns

  • Access (one-step conversion example):

    UPDATE MyTable
SET AmountCurrency =
CCur(Replace(Replace([AmountText], '$', ''), ',', ''))
WHERE IsNumeric(Replace(Replace([AmountText], '$', ''), ',', '')) = True;

  • VB.NET: parse currency input and send a numeric parameter (do not insert formatted text)

    Dim amt As Decimal
If Decimal.TryParse(txtAmount.Text,
  Globalization.NumberStyles.Currency,
  Globalization.CultureInfo.CurrentCulture,
  amt) Then
cmd.CommandText = "INSERT INTO MyTable (AmountCurrency) VALUES (?)"
cmd.Parameters.Add(New OleDb.OleDbParameter("?", OleDb.OleDbType.Decimal)).Value = amt
cmd.ExecuteNonQuery()
End If

Troubleshooting tips: a “data type mismatch” usually means a string is being written into a numeric column or the parameter type doesn’t match—use parameterized commands and numeric parameter types (Decimal/Currency). If some rows are malformed (parentheses for negatives, different symbols), clean them first. ’s boolean-flag idea can be used when the same numeric field sometimes needs different presentation, but in most cases keeping the value numeric and formatting at display/report time is simpler and safer.

Recommended Answers

All 7 Replies

Member Avatar for tinstaafl

Format is a string function. Since it returns a text string, your problem is probably somewhere else. Either in the format you're using, or the data type the database expects, isn't what you think it is.

Edited by tinstaafl
Member Avatar for Nebil

Yes my datatype in the database is of text datatype.you can store the field in the desired way using the format property.but what i wanted is that i want to make the datatype to number and store the field in a way that i showed you in previous code or using access.

Member Avatar for tinstaafl

The thing is that currency is not a datatype that I'm aware of. Usually all currency means is a string representation of the number with a currency denomionation symbol in front and thousands separators if necessary. To store a number as currency use the .ToString method and set the format there.

Label12.Text = bridgeasset.ToString("c")

This will format the string as currency specific to the locale of the computer. For me the string is "$1,234.56".

If I understand what you want, it's pretty much impossible to display a number as currency without converting it to a string. A number only contains digits and decimal. As soon as you want anything else in there, it has to become a string.

Edited by tinstaafl
Member Avatar for Nebil

You are right,currency is not a datatype,ist's a format.now you are getting my idea.i want a number to both be stored with a specific format,currency format in the db either set the format from excel or from the code level.
finally i would use it in crystal reports for total calculation using "sum".

Member Avatar for tinstaafl

We seem to be going in circles. Do you want an indication in the database when the number is supposed to represent currency so that you can supply the proper formatting when you read/write it? Or do you want to be able to parse the currency string to return a number to use in calculations?

Member Avatar for Nebil

my intention is your first question

Member Avatar for tinstaafl

You'll probably have to add a boolean flag tied to that field. If it's true format as currency, if it's false format as double.

You could also try creating a custom data-type, something along the lines of the return type being double and the tostring returning a currency format. The code is easy, but I'm not sure about adding it to your database.

Edited by tinstaafl
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.