Data Type Conversion Failure when running an update/append query

A

AMHodgetts

Hi,

I have an update/append query as follows,

UPDATE TabTapes SET TabTapes.ContractNumber =
[Forms]![TapeDispatch]![TXTContractNo], TabTapes.Customer =
[Forms]![TapeDispatch]![cmbCustomer], TabTapes.TapeID =
[Forms]![TapeDispatch]![tapeid1], TabTapes.IDate =
[Forms]![TapeDispatch]![datToday], TabTapes.RDate =
[Forms]![TapeDispatch]![datReturn], TabTapes.Returned = 'FALSE',
TabTapes.Stamped = 'FALSE'
WHERE (((TabTapes.ContractNumber)=[Forms]![TapeDispatch]![TXTContractNo])
AND ((TabTapes.TapeID)=[Forms]![TapeDispatch]![tapeid1])) OR
(((TabTapes.ContractNumber)="1") AND ((TabTapes.TapeID) Is Null));

When I run the query I get the following message;

'Microsoft Access can't update all the records in the update query.
Microsoft Access didn't update 1 field due to a type conversion failure'.

When I check the table 'tabTapes', the query has updated all fields but the
'TapeID' field.

In the design of 'tabTapes' the field 'tapeID' is a txt field and
[Forms]![TapeDispatch]![tapeid1] is also a txt field.

Can anyone point me in the right direction.
 
J

JohnFol

Just as a method of proving which bit is causing the problem, change

TabTapes.TapeID = [Forms]![TapeDispatch]![tapeid1],

to

TabTapes.TapeID = "Test"

This rules out the value on the form as being the incorrect data type.

Also, check the tabTapes TapeID field is set to Allow Nulls as your criteria
imples this is allowed.

Let us know how it goes.
 
A

AMHodgetts

Thanks for that.

I have changed TabTapes.TapeID = [Forms]![TapeDispatch]![tapeid1], to
TabTapes.TapeID = "Test". I am still getting the error message, although the
field is being updated in the table.

One thing I failed to mention (should've really), is that I have 20
'tapeid*' fields (numbered 1 to 20) on the form (for users to enter up to 20
tapes) and am therefore running 20 queries when the form is closed. I get
the same message every time the query runs. If I enter multiple tapes into
the form, the queries only the tapeid field once and nulls the other entries
(if i enter 20 records, i get 20 extra rows on the table but only one of them
will have the tapeid filled in). All other fields get filled in though. All
the queries are the same apart from the obvious field name changes. I'm
stuck with this one. If there an easier way of doing this or am I just
making a basic and obvious error.
--
Ann Hodgetts


JohnFol said:
Just as a method of proving which bit is causing the problem, change

TabTapes.TapeID = [Forms]![TapeDispatch]![tapeid1],

to

TabTapes.TapeID = "Test"

This rules out the value on the form as being the incorrect data type.

Also, check the tabTapes TapeID field is set to Allow Nulls as your criteria
imples this is allowed.

Let us know how it goes.


AMHodgetts said:
Hi,

I have an update/append query as follows,

UPDATE TabTapes SET TabTapes.ContractNumber =
[Forms]![TapeDispatch]![TXTContractNo], TabTapes.Customer =
[Forms]![TapeDispatch]![cmbCustomer], TabTapes.TapeID =
[Forms]![TapeDispatch]![tapeid1], TabTapes.IDate =
[Forms]![TapeDispatch]![datToday], TabTapes.RDate =
[Forms]![TapeDispatch]![datReturn], TabTapes.Returned = 'FALSE',
TabTapes.Stamped = 'FALSE'
WHERE (((TabTapes.ContractNumber)=[Forms]![TapeDispatch]![TXTContractNo])
AND ((TabTapes.TapeID)=[Forms]![TapeDispatch]![tapeid1])) OR
(((TabTapes.ContractNumber)="1") AND ((TabTapes.TapeID) Is Null));

When I run the query I get the following message;

'Microsoft Access can't update all the records in the update query.
Microsoft Access didn't update 1 field due to a type conversion failure'.

When I check the table 'tabTapes', the query has updated all fields but
the
'TapeID' field.

In the design of 'tabTapes' the field 'tapeID' is a txt field and
[Forms]![TapeDispatch]![tapeid1] is also a txt field.

Can anyone point me in the right direction.
Click to expand...
Click to expand...
 
Top