SQL Query with Mixed Data and Formulas incorrectly NULL

[erickamiller143]

I have been searching the forums but keep coming across what to do with NULL values that are actually null or blank in the dataset. My problem is that I have a mixed data dataset. Some values in the column are numbers and some are formulas that show as numbers in the dataset. Previously, it was only a mix of numbers saved as text and numbers saved as numbers. I resolved this issue by going into the dataset and doing a text to columns and that would convert all the numbers to text and my SQL query would run perfectly. Recently, we added formulas to the column to increase the values by a specific percentage. I cannot, for the sake of recordkeeping, convert the formulas to numbers. Now when I run the SQL Query, the cells that have formulas (with values and are NOT null) are being pulled as NULL. Is there a VBA solution around to pull the formula result as a number? Below is the connection string for the workbook to query itself and then a sample of my SQL Query. Any help would be greatly appreciated

Sub OpenDB()
'This macro creates a connection that will allow Excel to run queries against itself
'OpenDB must be called before any SQL queries can be ran

   If cnn.State = adStateOpen Then cnn.Close  'If the connection is already active, close it and reopen it
    cnn.ConnectionString = "ImportMixedTypes=Text;Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};DBQ=" & _
    ActiveWorkbook.Path & Application.PathSeparator & ActiveWorkbook.Name
    cnn.Open
End Sub

strSQL = _
        "SELECT DISTINCT " & _
            "[Sheet1$].[Price], " & _
        "FROM " & _
            "[Sheet1$] " & _
        "WHERE " & _
            [Sheet1$].[PartNumber] = '" & PartNumberVar & "'"
            
    PriceQRY.Open strSQL, cnn, adOpenKeyset, adLockOptimistic

 

[RoryA]

What are the formulas? If they return actual numbers, you shouldn't have a problem.

 

[erickamiller143]

What are the formulas? If they return actual numbers, you shouldn't have a problem.

The formulas in the spreadsheet are =A1*.065 and it results in a number. But the sql statement is reading it as text. But not all values in the column were created with formulas. Some are numbers and some are numbers created from formulas. This variable data is creating a null when a formula is hit.

 

[RoryA]

I can't replicate that. Only the values are looked at, so if they are numbers, they are returned whether static or calculated.