Using a excel cell value in sql statement

mrichard

New Member
Joined
Mar 23, 2020
Messages
29
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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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 & "'"
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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 & "'"
 
Upvote 0
Thanks I will work on this and let you know how it turns out.

Again...your responses are much appreciate!

MRR
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

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
Back
Top