Access Import Challenge!!

[simoncpage]

Wonder if anyone can help me with this one? I have a program which export values from an excel sheet into an access database. I have set up one of the columns in the database as a currency column as I want to be able to import from the database and preserve the formatting. However if one of the cells of the column in excel is a null it produces an error.

If I use vbVarWChar (instead of vbCurrency) for the column in access the export is fine and nulls are accepted BUT this writes numbers as 10000 and not 10,000. And when I import this even if I change the cell formating to #,###, it still stays as 10000 I have to actually activate the cell and return to get it to change to 10,000.

Therefore I have decided to preserve the formatting in access to a currency but how do I get around the fact that it wont accept null cell values? Would on Error Resume Next work???

Any help would be most appreciated! thanks

 

[chogben]

How about turning the problem on it's head. Change the export program to replace nulls with a space.

 

[jioek]

accept the null and use nz() in all your forms
or use an update query that updates null to zero in your field