have a code to look for 1 variable but i want to look for 2 variabes

[justlearning123]

so this part of the code looks for variable named "var2" in the Shift field but I want it to also look for the variable named "var" in the date field.. bith the variables should be in the same row.

 'set the search variable
 var = Sheet1.Range("U2").Value
 var2 = Sheet1.Range("R2").Value
 
 Set cnn = New ADODB.Connection ' Initialise the collection class variable

 cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath
'Create the SQL statement to retrieve the data from table.
 SQL = "SELECT * FROM Scrap WHERE Shift = '" & var2 & "'"

[justlearning123]

I tried this and got an error 13 tyle mismatch

 Var = Sheet1.Range("U2").Value
 Var2 = Sheet1.Range("R2").Value
 Set cnn = New ADODB.Connection ' Initialise the collection class variable

 cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath
'Create the SQL statement to retrieve the data from table.

 
 
 SQL = "SELECT * FROM Scrap WHERE shift = '" & Var2 & "'" And " WHERE Trans_date = '" & Var & "'"

[June7]

Parameters for date fields require # delimiter instead of apostrophe. Don't repeat the WHERE keyword. The AND must be within quote marks.


SQL = "SELECT * FROM Scrap WHERE shift = '" & Var2 & "' And Trans_date = #" & Var & "#;"

[justlearning123]

perfect thyank you... and also if I wanted to add a 3rd variable with a specific word for example from the status field and word is"Broke"
would that look something like this? thank you

SQL = "SELECT * FROM Scrap WHERE shift = '" & Var2 & "' And Trans_date = #" & Var & "#;" and  status = "broke"

[June7]

Move the semicolon and use apostrophe delimiters. The new criteria must be within the quote marks.

SQL = "SELECT * FROM Scrap WHERE shift = '" & Var2 & "' And Trans_date = #" & Var & "# And status = 'broke';"

Use the query builder to help construct SQL strings then copy/paste to VBA and adjust as needed to concatenate variables and use correct delimiters.

[justlearning123]

Thank you!!