type conversion failure in append query

[Zoe]

I have a pre-defined table table that I populate with an append query. 2 of
my fields (defined as text) will not populate (I received a error message
that there is a type conversion failure).

I ran the query as a make table query and those 2 fields are defined as
Binary data type.

Both fields are text. Both originally come from text boxes on a form and
are poulated as fields in the underlying queries.

How can I get the fields to be recognized as text in the table that I am
appending to?

I am using Access 2002.

Thanks,
Zoe

 

[Jerry Whittle]

Where are you getting the original data? Is it linked to something like SQL
Server or Oracle?

 

[Zoe]

The form is bound to a linked ODBC Table. One text box is bound to a field
in that table. The other text box is unbound.

The query that pulls the value of the text boxes is like:
Club Name: Forms!Menu!AgName (this is the bound text box)
Club Affiliation: Forms!Menu!aff (this is the unbound text box)

The fields that are binary in the table are Club Name and Club Affiliation.

Thanks,
Zoe

 

[John Spencer]

It would help if you posted your Append query.

As a guess you could try forcing the conversion using CStr in the append
query. Something like the following.

Club Name: IIF(Forms!Menu!AgName is Not Null,Cstr(Forms!Menu!AgName),Null)

Or to avoid problems with zero-length strings and nulls

Club Name: IIF(Len(Forms!Menu!AgName & "")> 0,Cstr(Forms!Menu!AgName),Null)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County