excel to access problems
 

Hi,

I've created an Excel worksheet that has been configured to updated
records of an access database. The records have a mixture of strings
and numbers. All the numbers in the database have been configured with
the REAL data type.

The problem I'm having is with the numbers e.g. I've added a record
with one of the fields having a value of say 4.2. When I retrieve the
record back into Excel it comes back as 4.199283734675 why is? Could it
be a problem with my programming and the way I add a record?

Below is the part of the SQL statement that adds the value. The
function fGetCellFormat checks the format of the cell so that the wrap
character is correct.


strSQL = strSQL & " VALUES ("

For intCount = 1 To ctiDataField
Select Case fGetCellFormat(.Range(ctsNcRecipe &
intCount).Value)
Case "TEXT"
strWrapChar = """"
Case "DATETIME"
strWrapChar = "#"
Case Else
strWrapChar = ""
End Select

strSQL = strSQL & strWrapChar & .Range(ctsNcRecipe &
intCount).Value & strWrapChar

If intCount < ctiDataField Then
strSQL = strSQL & ", "
Else
strSQL = strSQL & ")"
End If
Next


I'm still quite new to programming so any help or ideas would be
appricated.

Thanks in advance

Rich

excel to access problems
 

I would guess that the cell really contains 4.199283734675 and the cell is
formatted to show only 1 decimal place, so appears as 4.2, but the underlying
value is what is transferred.

--
Regards,
Tom Ogilvy


"rich" wrote:


Hi,

I've created an Excel worksheet that has been configured to updated
records of an access database. The records have a mixture of strings
and numbers. All the numbers in the database have been configured with
the REAL data type.

The problem I'm having is with the numbers e.g. I've added a record
with one of the fields having a value of say 4.2. When I retrieve the
record back into Excel it comes back as 4.199283734675 why is? Could it
be a problem with my programming and the way I add a record?

Below is the part of the SQL statement that adds the value. The
function fGetCellFormat checks the format of the cell so that the wrap
character is correct.


strSQL = strSQL & " VALUES ("

For intCount = 1 To ctiDataField
Select Case fGetCellFormat(.Range(ctsNcRecipe &
intCount).Value)
Case "TEXT"
strWrapChar = """"
Case "DATETIME"
strWrapChar = "#"
Case Else
strWrapChar = ""
End Select

strSQL = strSQL & strWrapChar & .Range(ctsNcRecipe &
intCount).Value & strWrapChar

If intCount < ctiDataField Then
strSQL = strSQL & ", "
Else
strSQL = strSQL & ")"
End If
Next


I'm still quite new to programming so any help or ideas would be
appricated.

Thanks in advance

Rich