PDA

View Full Version : CopyFromRecordset Decimal Values are copied as much longer decimals values



AZZAC01
01-16-2013, 02:54 PM
Hello, I am working with Office 2010. I do a "CopyFromRecordset" in Excel VBA to get Data From an ACCESS Query Result which contains some Decimals (EX. 12.96) When it gets put into Excel the decimal is converted to 12.9600000381469. I have had the Cells pre-formatted to General, Number with a set Number of decimal places, I have also done it programmatically before and after the CopyFromRecordset:banghead: . Also, Ofcoarse if I just Copy and Paste it from Access to Excel I dont see this issue. I would like to use "CopyFromRecordset" if possible because I have some large query results to deal with. Any Insight to this problem will be greatly appreciated. THANKS!

mohanvijay
01-17-2013, 01:23 AM
Try this

1. Activate the sheet before do the "copyfromrecordset" or

2. Convert datatype as string in query (Cstr(Filed Name))

Kenneth Hobs
01-17-2013, 06:55 AM
I have never seen a case where you could not change the number format after something like that. Please post an example xlsx file where you can not change number format if you like.

Like Excel, the real value may be that in Access. Another scenario is that it may be doing like it does in an MSWord mail merge of data. Since it has to convert it to double precision, a 12.96 in Access merged to an MSWord file does what you have seen. I just use a round and a format to get what I want in that case in the merge form but a DAO reference converted it as it one would hope.

AZZAC01
01-21-2013, 10:38 AM
Hello Thanks for the help, "mohanvijay" I used the Cstr function in the Select part of my query as an Expression. I Then add my query string to the Cmd Text using a OLEDB Connection to Microsoft Access. FYI This works Great with one small Issue that I resolved. Anything that was Null (Zero) when converted to String ave me the fllowing Error: 'Invalid use of Null'. I fixed by nesting IIf(IsNull Functins with the CStr function here is the result below for any other viewers:

ALSO, @ Kenneth Hobs I think I used poor choice of words The Format could be changed. That is only What apperars in the cell while the actual Value could be extended further past the decimal point, I was failing to realize that. I was, at first thinking by changing the format of those cells I was changing the Value, till shortly after I posted this. So, The actual Value is what I was looking at preventing Excel from Changing to be different than what was in Access.

Thanks guys! here is the SQL ( where "[zAUTO_ANALOGS CARD LVL]" and "[zAUTO_ANALOGS]" are two pre built queries in Access with lots of powerful Joins and stuff)


SQLstr = "SELECT [zAUTO_ANALOGS CARD LVL].RTU, [zAUTO_ANALOGS CARD LVL].ID, [zAUTO_ANALOGS CARD LVL].DEVTYP, "
SQLstr = SQLstr + "[zAUTO_ANALOGS CARD LVL].DEVICE, zAUTO_ANALOGS.NAME, [zAUTO_ANALOGS CARD LVL].ANALOG, "
SQLstr = SQLstr + "CStr(IIf(IsNull([zAUTO_ANALOGS CARD LVL].[RAWLOW]),0,[zAUTO_ANALOGS CARD LVL].[RAWLOW])) AS Expr1, "
SQLstr = SQLstr + "CStr(IIf(IsNull([zAUTO_ANALOGS CARD LVL].[RAWLOW]),0,[zAUTO_ANALOGS CARD LVL].[RAWHIGH])) AS Expr2, "
SQLstr = SQLstr + "CStr(IIf(IsNull([zAUTO_ANALOGS CARD LVL].[RAWLOW]),0,[zAUTO_ANALOGS CARD LVL].[ENGLOW])) AS Expr3, "
SQLstr = SQLstr + "CStr(IIf(IsNull([zAUTO_ANALOGS CARD LVL].[RAWLOW]),0,[zAUTO_ANALOGS CARD LVL].[ENGHIGH])) AS Expr4, "
SQLstr = SQLstr + "zAUTO_ANALOGS.DEVICE.AREA, "
SQLstr = SQLstr + "zAUTO_ANALOGS.ANALOG.AREA, zAUTO_ANALOGS.SITE, zAUTO_ANALOGS.LOREAS, zAUTO_ANALOGS.HIREAS, [zAUTO_ANALOGS CARD LVL].CONV, "
SQLstr = SQLstr + "[zAUTO_ANALOGS CARD LVL].DBAND, [zAUTO_ANALOGS CARD LVL].NEGATE "
SQLstr = SQLstr + "FROM [zAUTO_ANALOGS CARD LVL] LEFT JOIN zAUTO_ANALOGS ON ([zAUTO_ANALOGS CARD LVL].SUBSTN = zAUTO_ANALOGS.SUBSTN.ID) AND "
SQLstr = SQLstr + "([zAUTO_ANALOGS CARD LVL].DEVTYP = zAUTO_ANALOGS.DEVTYP.ID) AND ([zAUTO_ANALOGS CARD LVL].DEVICE = zAUTO_ANALOGS.DEVICE.ID) AND "
SQLstr = SQLstr + "([zAUTO_ANALOGS CARD LVL].ANALOG = zAUTO_ANALOGS.ANALOG.ID) "
SQLstr = SQLstr + "WHERE [zAUTO_ANALOGS CARD LVL].RTU =" & "'" & RTUofUSER.Text & "'" & " AND [zAUTO_ANALOGS CARD LVL].PHYADR >= " & ANALOGS_PhyAdr.value
If Me.ResidingSub = "*" Then SQLstr = SQLstr + " And [zAUTO_ANALOGS CARD LVL].SUBSTN =" & "'" & ResidingSub.Text & "'"

RS.Open Source:=SQLstr, ActiveConnection:=DBconn, Options:=adCmdText
If Not RS.BOF Then Worksheets("ANALOG_INS").Range("H2").CopyFromRecordset RS


SKIP A BUNCH OF OTHE CODE THEN: I set Values, which excel raises a flag on as 'NUMBERS STORED AS TEXT', back to there respected Values to get rid of conflict else where in my prog.


Dim mySheets As Variant
mySheets = Array("INPUTS", "OUTPUTS", "ANALOG_INS", "PULSE_ACCUM")
Dim ConvertedAnalogs
Application.ScreenUpdating = False
For Each mysheet In mySheets

With Worksheets(mysheet).UsedRange
.value = .value
End With

Next mysheet

Application.ScreenUpdating = True

snb
01-21-2013, 02:49 PM
You can convert numbers with x decimals to numbers with 2 decimals, using:

Sub M_snb()
[A1:A20] = [index(text(A1:A20,"0.00"),)]
End Sub