0

I have a subroutine that fills cells with data from an SQL DB. Here is a sample of the code that I'm having trouble with:

For varTempInt = 1 To varHdrRow - 2
    Cells(varTempInt + 1, 2).Value = varRecordset.Fields(varTempInt - 1).Value
Next

The problem with the code is that it ocassionally fills the cell with the recordset field value with a space at the beginning. I'm not sure why this happens and it happens to be extremely troublesome.

I've troubleshooted with the Immediate Window and found this problem to be even more strange. By stopping directly after running the code for the particular counter that returns an extra space, I get the following results:

?varRecordset.Fields(varTempInt - 1).Value
1517
?Cells(varTempInt + 1, 2).Value
 1517

To interpret, the value of the recordset field that I just pulled from has no space, yet the value of the cell that it was placed into contains a space at the beginning.

Why is this happening, and how can I prevent it?

Edit 1: Yes, I am aware that I could use the Trim() function, but I'd really rather not add extra steps that shouldn't be necessary. I will if it comes down to that being the only option, but I'd love someone to explain the problem first.

Community
  • 1
  • 1
jaysoncopes
  • 805
  • 3
  • 13
  • 26

1 Answers1

0

The reason that it was showing up as inconsistent is because the recordset field value was stored as String and the cell value was being stored as an Int. This is the same reason it came out so strangely in the Immediate window: a space before and after indicates the value being an Int, whereas a string comes out as the same.

The solution to this problem was simply to CStr() each value before comparing them.

jaysoncopes
  • 805
  • 3
  • 13
  • 26