Using a excel cell value in sql statement

[mrichard]

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

 

[kennypete]

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:

    sql = "SELECT * FROM [Sheet1$] WHERE period=" & Cells(4, 1).Value & " and " & _
                                        "product='" & Cells(4, 2).Value & "'"

 

[mrichard]

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]

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]

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]

Thanks I will work on this and let you know how it turns out.

Again...your responses are much appreciate!

MRR