Validation error

[Monty]

I hope this is the right forum for this, please direct me to another
if it's not.

While attmepting to run and SQL update in Access, I get the following
error message:

Microsoft Access didn't update 1 field(s) due to a type conversion
failure, 0 record(s) due to key violations, 0 record(s) due to lock
violations, and 0 record(s) due to validation rule violations.

Two things: does this tell me there are four errors, or only the one
typ conversion error? And is there a way to trap this error to
examine what specifically caused the error? I've search the forms and
tables endlessly, but I can't figure out where I might be running into
a type conversion failure, let alone the other errors.

Thanks

 

[J_Goddard via AccessMonster.com]

There is only one error occuring. The error message indicates your
attempting to update a field with the wrong type of data, for example text in
a numeric field. Please post the sql you are using, and tell us the data
type of the table fields that are being updated.

John

 

[Monty]

Wow, that would be a whole lot of pasting.

The surety of your answer though, let me to wonder about this: I'm
updating a table called Packages from a form full of text boxes. One
of the fields in Packages, Cost, is defined as a number with a
currency format. I'm attempting to update it with a value from the
form, Me.cost, which as I said, is a text box. Could that be
incompatible enough? Does Me.cost need to undergo some kind of
internal conversion?

 

[J_Goddard via AccessMonster.com]

Hi -

The term "text box" for a form control is somewhat misleading; a text box is
typeless, and can contain any type of data. If when you are building up your
SQL statement you are enclosing the [cost] value in quotation marks, that is
causing the error, because [cost] is numeric in the table and the quotes
would try to assign a string value to it.

Use: ".... [cost] = " & me![cost] ...."

Note: You can format the [cost] as currency on the form with no problem,
because a text box Format property only affects how it is displayed, not how
it is stored internally.

John

 

[Monty]

The snippet of code I was questioning looked like

"[Cost] = '" & Me.cost & "', (etc...)

I got that part of it right. While waiting for your reply I tried

"[Cost] = '" & Val(Me.cost) & "', (etc...)

....which didn't do any good. Everything else is straight text, both
in the table and on the form. The only possible exception might be
that one of the fields in the table, License Type, while text, is
limited to four values:

In ("Floating","Node-Locked","N/A",Null)

However, since the form is populated with info from this field, I make
the assumption that writing that same info back to the table wouldn't
produce a conversion failure: the data came from the table to begin
with.

These are the only two fields I can see causing the error. All other
fields in the table are text and have no validation rules associated
with them.

I'm stumped.

 

[Monty]

Ok, I'm no longer stumped. Hope you're not replying to my previous
post.

The [Version] field in the table doesn't exactly relate to Me.Version:
it relates to Me.Version1 and Me.Version2, the appropriate one being
enabled on the form, depending whether or not there are several
versions to choose from. if a package has only one version in the
table, that version is displayed in a simple text box. If a package
has multiple versions in the table, they're all displayed in a combo
box.

Having coded for these two fields, everything's working as it should.

Thanks for your input, though.