Data Type Conversion Error

[SBBmaster09]

I am having trouble in editing my program. I have this table and form. I have to input from a form [decimal in percent] (ex. 99.45%), to be added in my table. How can I add that, I am using MS Access 2007. What code should I used and do I have to change the data types in my table.

This is the code i used to compute for the score.

txtQAScore.Value = Format((txtAPE.Value / txtTPP.Value), "Percent")

This is the code inside my SQL. [Submit Button]

rst("QUALITY SCORE") = txtQAScore.Value

 

[Mihail]

At table level:
The data type for this field must allow decimals. So: Single, Double or (not sure) Currency.
If you wish you also can set the Format property to Percent but this is not important.

At form level:
Set the Format property for the bounded control to Percent.

 

[CJ_London]

using the format function converts the datatype to text.

So assuming [Quality Score] is a decimal type datatype you are trying to assign a text value to a number.

In your form, I would simply use

txtQAScore= txtAPE / txtTPP

and set the controls format property to percent

Then in your assignment

rst("QUALITY SCORE") = txtQAScore

Note1. if txtTPP is null or 0 then the code will fail (division by zero error) so you may need some additional code to handle this

Note2: there is not need to use .Value in this situation

 

[SBBmaster09]

Hi All,

I already got it, my problem now is even the date fields shows data conversion error. If the Date Field has date, it doesnt have errors but if the Date Field is empty it shows that the Data Conversion Error. How would i solve it? Thanks.

 

[CJ_London]

if your date field is empty you are trying to assign a null value which is generating the error, however need more info to advise - can you post your code/assignments etc - you may be using a reserved word

 

[SBBmaster09]

Under Form Properties:
my textfield name is: txtDate
Format: Short Date

Table Properties:
Data Type: Date/Time
Format: Short Date

In codes:

rst("Date Released") = txtDate.Value

Is that what you mean??

 

[CJ_London]

Thats the sort of thing -Try

rst("Date Released") = nz(txtDate)

 

[spikepl]

rst("Date Released") = nz(txtDate)

Not really. Fields in a record can take Nulls so the above will not resolve anything. I suspect that OP has confused "empty" and Null and "" (vbNullString)

BTW: Feilds are in records. Controls are in forms. Controls can be bound to fields. Hence

rst("Date REleased") is a field

txtDate is a control in your form.

If you do

Me.txtDate = vbNullSTring (As I have seen people erroneously do)

then you have put a 0-length string in the txtDate control. And so you will get a conversion error if you attempt to

rst("Date Released") = txtDate.Value

 

[SBBmaster09]

Thanks for all the help.