SQL Query with Mixed Data and Formulas incorrectly NULL

erickamiller143

New Member
Joined
Nov 29, 2017
Messages
17
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

VBA Code:
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

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

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,746
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
What are the formulas? If they return actual numbers, you shouldn't have a problem.
 

erickamiller143

New Member
Joined
Nov 29, 2017
Messages
17
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,746
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I can't replicate that. Only the values are looked at, so if they are numbers, they are returned whether static or calculated.
 

Forum statistics

Threads
1,147,479
Messages
5,741,386
Members
423,657
Latest member
Medrok2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top