CODEWITHSUNDEEP
ms-accessvba
Jeff Brady
Jeff Brady

Reputation: 1498

Access VBA - how can i convert a value in recordset from text to number?

So I have the following code in Access:

Dim db As DAO.Database
Dim qdEPH As DAO.QueryDef    
Dim rsEPH As DAO.Recordset  

Set qdEPH = DBEngine(0)(0).QueryDefs("MyQuery")  

qdEPH.Parameters(0) = Text10.Value

Set db = CurrentDb

Set rsEPH = qdEPH.OpenRecordset

Set oExcel = CreateObject("Excel.Application")
Set oBook = oExcel.Workbooks.Add
Set oSheet = oBook.Worksheets("Sheet1")
oSheet.Activate

Dim Count as Long
Count = 1

Do While Not rsEPH.EOF

    oSheet.Range("A" & Count).Value = rsEPH("Value1")
    Count = Count + 1
    rsEPH.MoveNext

Loop

A user puts a value in textbox Text10 on a form and clicks a button to run the code above. It runs the query MyQuery and dumps the results into a recordset named rsEPH. One of the fields, Value1, is stored as a text value in the table being queried. However, it's actually a number. How can I convert rsEPH("Value1") to a number (returning Null or something if it fails) and then divide it by 100?

Upvotes: 2

Views: 3697

Answers (2)

Santosh
Santosh

Reputation: 12353

Try below code

Do While Not rsEPH.EOF

  oSheet.Range("A" & Count).Value = IIf(IsNull(rsEPH("Value1")), 0, CDbl(rsEPH("Value1")))
    Count = Count + 1
    rsEPH.MoveNext

Loop

Upvotes: 0

HansUp
HansUp

Reputation: 97101

Use Nz to transform Nulls in the text field before you apply the numeric conversion function. I chose CDbl as the conversion function.

oSheet.Range("A" & Count).Value = CDbl(Nz(rsEPH("Value1"), "0"))

But you mentioned dividing by 100, so maybe you want this ...

oSheet.Range("A" & Count).Value = CDbl(Nz(rsEPH("Value1"), "0")) / 100

Upvotes: 4

Related Questions