Type Conversion Failure Error

[Jon22]

I keep getting a Type Conversion Failure error when I run a particular append
query. Four of the fields being appended are derived from Expressions. I've
worked out that two of these four Exp fields are my culprits. if I remove
these the append query runs. The expressions of these two problematic fields
are merely references to text fields in an open form. Should they not work
too? I really need them in there.

 

[Clifford Bass]

Hi,

Try wrapping the results of your expressions with one of the Cxxx()
functions. For example, if you want to make sure that the expression results
in a double value you might use CDbl().

If you need further help, post back with your query's SQL and
information about the types of values that are causing trouble. Also,
indicate if any of those expressions use fields or form data that can be null.

Clifford Bass

 

[John W. Vinson]

I keep getting a Type Conversion Failure error when I run a particular append
query. Four of the fields being appended are derived from Expressions. I've
worked out that two of these four Exp fields are my culprits. if I remove
these the append query runs. The expressions of these two problematic fields
are merely references to text fields in an open form. Should they not work
too? I really need them in there.

Correct the error in your expressions. For help doing so please post the SQL
of the query and indicate the datatype of the fields you're trying to append
to.

 

[Jon22]

Thanks, I would have posted it before but it was enormous. This is it with
the four expression fields and a couple of the straight field to field ones.
(sorry, about my poor terminology):

INSERT INTO [Quotes Sub] ( [Quote Number], Customer, [Order], [Sub Date],
[Design Number], [Unit Cost], [Unit Sale], [Unit Profit], Quantity, [QS
Number] )
SELECT DISTINCT [Forms]![Quotes New]![Quote Number] AS [Quote Number],
[Forms]![Quotes New]![Customer] AS Customer, Nz(DMax("[Order]","[Quotes
Sub]","[Quote Number]=Forms![Quotes New]![Quote Number]"),0)+1 AS [Order],
Date() AS [Sub Date], [Quotes Sub].[Design Number], [Quotes Sub].[Unit Cost],
[Quotes Sub].[Unit Sale], [Quotes Sub].[Unit Profit], [Quotes Sub].Quantity,
[Quotes Sub].[QS Number]
FROM [Quotes Sub]
WHERE ((([Quotes Sub].[QS Number])=[Forms]![Quotes New - Copy Sub]![QS
Number List]));

The two problem ones are [Quote Number] and [Customer]. Both of which are
just text fields in the form "Quotes New"

I have got around it by using a third SQL statement which uses the above
query name ("Copy Sub - Selected") adding the two problem fields separately
like this:

DoCmd.RunSQL "INSERT INTO [Quotes Sub] ( Customer, [Quote Number] )" & _
"SELECT [Copy Sub - Selected].*, [Forms]![Quotes New]![Customer]
AS Customer, [Forms]![Quotes New]![Quote Number] AS [Quote Number]" & _
"FROM [Copy Sub - Selected]"

It probably isn't the most efficient way to do it though. I did come across
an article here:

http://support.microsoft.com/kb/125259

which might have something to do with it.

 

[Clifford Bass]

Hi,

Are the Customer and Quote Number on the form formatted as numbers? If
not, you might try setting their Format properties to an appropriate numeric
format.

Clifford Bass

Thanks, I would have posted it before but it was enormous. This is it with
the four expression fields and a couple of the straight field to field ones.
(sorry, about my poor terminology):

INSERT INTO [Quotes Sub] ( [Quote Number], Customer, [Order], [Sub Date],
[Design Number], [Unit Cost], [Unit Sale], [Unit Profit], Quantity, [QS
Number] )
SELECT DISTINCT [Forms]![Quotes New]![Quote Number] AS [Quote Number],
[Forms]![Quotes New]![Customer] AS Customer, Nz(DMax("[Order]","[Quotes
Sub]","[Quote Number]=Forms![Quotes New]![Quote Number]"),0)+1 AS [Order],
Date() AS [Sub Date], [Quotes Sub].[Design Number], [Quotes Sub].[Unit Cost],
[Quotes Sub].[Unit Sale], [Quotes Sub].[Unit Profit], [Quotes Sub].Quantity,
[Quotes Sub].[QS Number]
FROM [Quotes Sub]
WHERE ((([Quotes Sub].[QS Number])=[Forms]![Quotes New - Copy Sub]![QS
Number List]));

The two problem ones are [Quote Number] and [Customer]. Both of which are
just text fields in the form "Quotes New"

I have got around it by using a third SQL statement which uses the above
query name ("Copy Sub - Selected") adding the two problem fields separately
like this:

DoCmd.RunSQL "INSERT INTO [Quotes Sub] ( Customer, [Quote Number] )" & _
"SELECT [Copy Sub - Selected].*, [Forms]![Quotes New]![Customer]
AS Customer, [Forms]![Quotes New]![Quote Number] AS [Quote Number]" & _
"FROM [Copy Sub - Selected]"

It probably isn't the most efficient way to do it though. I did come across
an article here:

http://support.microsoft.com/kb/125259

which might have something to do with it.