Solved Data mismatch in a VBA module when ran against SQL server

[nector]

I have now a new puzzle when migrating to SQL Server, most of my modules I had to redo them including the tables data types. Now I have remained with one module below, it works fine in Ms. Access tables, but in SQL server tables its completely not working, sometimes it says data mismatch. I got this module here one of the members assisted me:

The module is attached in a note pad for easy reference

 

[Ranman256]

If you use a variant,it can be either string or number.
you can’t assign a string to a variable defined as number.

 

[Pat Hartman]

not working

Useless description.

sometimes it says data mismatch

Better but still not helpful. We have no way to test the procedure so we have no way to identify what line is failing.

Use cntl-break when you get the error message. In the err_hanler, add a new line of code after the Resume exit.... statement. Just use Resume. Then move the execution to that line (yellow right arrow) and press F8. That will take you to the line of code that is failing.