Using a excel cell value in sql statement

mrichard

New Member
Joined
Mar 23, 2020
Messages
14
Office Version
  1. 2016
Platform
  1. Windows
Hi All,

I have extensive skills in excel but very little in sql (just learning). I have a Access database that I have a odbc connection to from Microsoft 2016.
I have this statement that pulls in the entire access table (this works):

SELECT MasterRates.[Vehicle Holder], MasterRates.Type
FROM `C:\Users\Mark Richard\Documents\CIOSP3 V1.0.accdb`.`MasterRates`
WHERE MasterRates.[Vehicle Holder]='ablevets llc';

In the where statement I want to replace 'ablevets llc' with a cell value in excel. I am having syntax errors big time.
What I am trying to do is simple but I am stuck now.

Any help would be appreciated.

Thanks

Mrichard
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

kennypete

Board Regular
Joined
Apr 19, 2008
Messages
235
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Check out my post in this thread Index Match Multiple Criteria VBA - a modified statement to bring in cell values rather than user input works fine, so I guess this will give you a clear pointer to a solution:
VBA Code:
    sql = "SELECT * FROM [Sheet1$] WHERE period=" & Cells(4, 1).Value & " and " & _
                                        "product='" & Cells(4, 2).Value & "'"
 

mrichard

New Member
Joined
Mar 23, 2020
Messages
14
Office Version
  1. 2016
Platform
  1. Windows
Thanks for your response.
So If I only have one variable I would just use:
sql = "SELECT * FROM [Sheet1$] WHERE period=" & Cells(4, 1).Value
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,719
Office Version
  1. 2019
Platform
  1. Windows
Notice that the first criteria (period) is (probably) a number and has not quotes around it. The second is text (probably) and has single quotes around it>
Your criteria is text and so you'd be more likely wanting to follow the example of the product criteria: product = 'something'

Text literals in SQL always are surrounded by single quotes (technically in MSAccess you can use double quotes but with vba prefer single quotes to avoid the quotes in quotes problem.
 

kennypete

Board Regular
Joined
Apr 19, 2008
Messages
235
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
yes @mrichard as @xenou observes you'll need quotes for text or no quotes for a number, but since ablevets llc is text it should be something like:
...WHERE MasterRates.[Vehicle Holder]='" & Cells(4, 2).Value & "'"
 

mrichard

New Member
Joined
Mar 23, 2020
Messages
14
Office Version
  1. 2016
Platform
  1. Windows
Thanks I will work on this and let you know how it turns out.

Again...your responses are much appreciate!

MRR
 

Watch MrExcel Video

Forum statistics

Threads
1,114,608
Messages
5,548,968
Members
410,886
Latest member
sjohn627
Top