Formatting Currency

  • Thread starter Thread starter Ann
  • Start date Start date
Hi Ann,
You can convert the string to a currency before you append it to the table.
The insert code would be something like this (which I haven't tested), so
you might have to tweak it a little.

Public Function AddFirstClassPostageRate(curFirstClassPostageRate As String)

Dim strSQL As String
Dim curMyPostage As Currency
curMyPostage = CCur(curFirstClassPostageRate )

'Build the SQL to add the First Class Postage Rate to the table.
'Finished SQL looks like this:
'INSERT INTO tblFirstClassPostageRates ( NameOfTableField )
strSQL = ""
strSQL = strSQL & " INSERT INTO tblFirstClassPostageRates
( NameOfTableField )"
strSQL = strSQL & " SELECT " & curMyPostage & " ; "

'Run the SQL to add the person.
CurrentDb.Execute strSQL

End Function

Note: replace NameOfTableField with the name of the field you are putting
the postage rate into.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
Hi Jeanette,

Thanks for helping me. I still had a problem though. The first two message
boxes came up but when I clicked "yes" to add it to the table the message box
"the text you entered isn't an item in the list" came up again. I had
entered .12 but if I entered $0.12 it accepted it and moved on to the next
field.
 
What is the format of the dropdown list set to?
What is the format of that field in the rowsource for the dropdown?



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
They are both Currency.

Jeanette Cunningham said:
What is the format of the dropdown list set to?
What is the format of that field in the rowsource for the dropdown?



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
Click to expand...
 
Also, it is adding the new field to the table and I can see it in the
dropdown. But I still receive the message box telling me it's not in the
list even though it is. Then I have to choose it from the list to move to
the next field.

I used this same code on fields that are formatted as text and don't have a
problem. The message boxes appear and I accept the addition. The new data
is added to the table, accepted in the field and the cursor moves to the next
field.
 
I built a small database to test this.
Personally I don't use the not in list event because it is always too
restrictive for my needs.

The easiest way I have found for your situation is to use some formatting to
simplify things.
Assuming that tblFirstRatePostageRates has the postage field as its primary
key-->
In the table, set the format for that field to Fixed and decimal places to
2.

In the postage combo on the form, set the format to Fixed and decimal places
to 2.

To get the not in list event to work for you, you will need to type the 0
followed by the decimal point and the number.
This way you are saved the bother of typing the dollar sign.

The Not In List event works well for a table with only one field and where
that field is text and can be used with long integer.
For other situations it is easier to build your own form that will add
values to the table, and let the user double click the combo to add new
values to the list.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia
 
That worked. Thank you so much Jeanette for all the help you gave me. I
really appreciate it.
 
Back
Top