Using a excel cell value in sql statement

mrichard

New Member
Joined
Mar 23, 2020
Messages
3
Office Version
2016
Platform
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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

kennypete

Board Regular
Joined
Apr 19, 2008
Messages
161
Office Version
365, 2019
Platform
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
3
Office Version
2016
Platform
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, Moderator
Joined
Mar 2, 2007
Messages
16,601
Office Version
2013
Platform
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
161
Office Version
365, 2019
Platform
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
3
Office Version
2016
Platform
Windows
Thanks I will work on this and let you know how it turns out.

Again...your responses are much appreciate!

MRR
 

Forum statistics

Threads
1,089,220
Messages
5,406,927
Members
403,113
Latest member
ms_excel_recal_or_die

This Week's Hot Topics

Top