"Access can't append all the records..." through VBA

[johan_a83]

Hi, I’m been searching this and other forums for the answer to this question.

I'm trying to append data from one table to another through VBA but get a run-time error messages saying “Data type mismatch in criteria expression”.
If I run the same code in a query in access I get the message that "access can't append all the records in the appended query....Access set 12726 field(s) to Null due to type conversion failure,....."

This is due to that I format the fields as the following syntax:
“Format(Round(Sum([tblMaster]![Vv_Trans]),0),""# ###"") AS Vv_Trans”
and the syntax "# ###" does not work when the sum of tblMaster.Vv_Trans is less then 1 (∑tblMaster.Vv_Trans < 1), in this case in 12726 times.

This is no problem if I would use the query in access since I just can choose “Yes” and then replace the Null-values with an update query (if filed=Null then update to 0). But since this should be tool with an interface that only contains of bottoms it’s not an option for a solution.

My question is what syntax to use in VBA to “tell” access that is it OK to give me Null values, aka. “clicking Yes”.

Pls find it in your time to help me with this or redirect me to a thread that answer this question.

Best regards
Johan Andersson

 

[PeterF]

I assume Vv_Trans is a numeric field.
Formatting a number turns it in a string, that's why you get “Data type mismatch in criteria expression”.
Why store a formatted number if you can format it when making a report or query?

 

[johan_a83]

Hi Peter, yes Vv_Trans is formated as number, the reason for this is that tblMaster is constructed by data from other calculations and I thought it was to time consuming to enter Format([tbl]![field], "# ###") in every string since the data in tblMaster is used to display values in other forms in Access.

Do you know any syntax to disregard the "error" message, since I can managed to get it right in a access query.