Understanding a query in VBA

[George]

Good Afternoon All. Can anyone assist me in getting this code corrected. I am not sure where to put the " and '.




Private Sub Command8_Click()

If Not IsNull(Me.Combo6) Then
strFilter = "Instructor='" & Me.Combo6.Value & Date > Me.Combo9.Value & Date < Me.Combo11.Value
End If

DoCmd.OpenReport "Individual Time Sheets", acViewPreview, , strFilter
strFilter = ""

End Sub

[orange]

What are you trying to do in plain English?
You o not need .value --that is the default.
Where does Date fit? Are you trying to do BETWEEN?

Try(untested)

strFilter = "Instructor='" & Me.Combo6  "'  AND  Date Between  #" & CDate( Me.Combo9 ) & "# AND #" & CDate(Me.Combo11) & "#"

[George]

Thanks.

On a form, clicking a button Loads a report with records based on a query where the value on Combo6 object on the form is = instructor field on the report data source. This works fine for:

strFilter = "Instructor='" & Me.Combo6.Value & "'"


However, now I want to extend to query to include date between some selected times. There is a date field in the report data source, I am trying the following:

strFilter = "Instructor='" & Me.Combo6.Value & "'" & "AND Date Between #" & CDate(Me.Text9) & "# AND #" & CDate(Me.Text11) & "#"

It seems close to working but there is some little niggling problem I cant detect.

By the way, why do we use single quotes ' ?

[orange]

Again, untested, try

strFilter = "Instructor='" & Me.Combo6.Value & "'   AND   Date Between  #" & CDate(Me.Text9) & "# AND #" &  CDate(Me.Text11) & "#"

You need the single quote to delimit a text field; # for dates and nothing for numbers

[George]

Orange it works fine. Thank you. Also thanks for the explanation on the Quotation marks. However can you further explain why single quote and double quotes are used here:


'" & Me.Combo6.Value & "'

couldn't one of them be excluded?

[davegri]

This is easier to grasp, see how single quotes and hash handled below:
The debug.print is enlightening to see the results

Dim sq as string
sq = chr$(39) 'this is a single quote
Dim sHash as string
sHash = "#"

strFilter = "Instructor= " & sq & Me.Combo6.Value & sq & " AND Date Between " & sHash & CDate(Me.Text9) & sHash & " AND " & sHash & CDate(Me.Text11) & sHash
debug.print strFilter

[George]

This make it easier because it is logical. Thanks for taking the time orange I am grateful because I think it is easier to write code this way.

However I am still curious why double and single still works as well.

[orange]

There is a reason for double and single quotes. The double quotes surround text, the single quotes are used here to put corresponding quotes around the "rendered text value" ( I'm using XXXXXXX, and some typical dates in the example)
If you take this

strFilter = "Instructor='" & Me.Combo6.Value & "'   AND   Date Between  #" & CDate(Me.Text9) & "# AND #" &  CDate(Me.Text11) & "#

Access "renders"/manipulates this string and replaces the controls with their values
( not a very elegant description) to give, as example,

strfilter = Instructor= 'XXXXXXX' AND DATE BETWEEN #5/15/2016# AND #5/31/2016#
Note: You could use all double quotes, but you have double-up on double quotes. Also called escaping double quotes



See this article for more on quotes and MSAccess.

[Micron]

If I may add to the explanation, what you are doing is concatenating. You will need to develop a mindset around how this works lest you continually stumble. So, try thinking of it this way:
you have to assemble (concatenate) all the parts into something that Access can understand. These parts are divided into two types - the string (text) that makes up the program language syntax of the statement (enclosed in quotes) that you are trying to build AND the parts you want to pass to Access as variables or parameters/criteria. If you were to write
"SELECT * FROM tblMyTable;", no other quotes are necessary since that constitutes an entire valid syntax statement - there are no variables or parameters. However, if you want to include filter criteria, you must separate the syntax parts from the variables (in your mind) and assemble (concatenate them) in a way that Access can resolve.
So with criteria:
The program language syntax part --"SELECT * FROM tblMyTable WHERE Customer = ' " (extra space for visibility)
The concatenated criteria value coming from a control named txtCust -- (which MUST be enclosed in quotes [i.e. delimited] if is text)
The concatenated syntax ending " ';"
NOTE - the concatenation operator (&) must be used, as you probably already know
The full concatenation: "SELECT * FROM tblMyTable WHERE Customer = ' " & Forms!frmMyForm.txtCust & " ';"
Everything between the quotes is concatenated AND the variable/reference is substituted for whatever it equals (e.g. ABC INC.) and the result is:
SELECT * FROM tblMyTable WHERE Customer = 'ABC INC.';

Hope that helps you get your mind around how it works. Don't worry - you will mess up again (I do!). You can always output your concatenation in the immediate window of the vb editor and see the output, or copy/paste into a new query in sql view, which has the advantage of usually pinpointing the error when you try to run it.

[George]

Friends I thank you all for the explanations and assistance. It helps but I would need some time to digest this. I did have an understanding of the use of the quotes for the string literals and the need to separate them from the variables by the &, but at times the quotes don't look like they correspond; they look like they are crossing over each other and I don't think that that's how they should work. It could help if you can send me back one of the statements with all the corresponding quote in different colors so I know which is which.

[Micron]

It could help if you can send me back one of the statements with all the corresponding quote in different colors so I know which is which.

Your sample: strFilter = "Instructor='" & Me.Combo6.Value & Date > Me.Combo9.Value & Date < Me.Combo11.Value
You are using the concatenation operator where you should be using AND, which is a Boolean operator. Your re-written sample:
strFilter = "Instructor = '" & Me.Combo6 & "' AND Date > #" & Me.Combo9 & "# AND Date < #" & Me.Combo11 & "#".
I made up blue values for illustration. The above should result in a string that is passed to Access as:
Instructor = 'Smith' AND Date > #04/15/2016# AND Date < #05/15/2016#
You also have the option of using the BETWEEN operator for dates:
strFilter = "Instructor = '" & Me.Combo6 & "' AND Date BETWEEN #" & Me.Combo9 & "# AND #" & Me.Combo11 & "#".
Instructor = 'Smith' AND Date BETWEEN #04/15/2016# AND #05/15/2016#

Your next example uses quotes around the name of an object that you have created.
DoCmd.OpenReport "Individual Time Sheets", acViewPreview, , strFilter
This has nothing to do with concatenation, but is required in many intrinsic (built in) methods and functions, or user defined functions (UDF's). In this case, Docmd is an object, OpenReport is one of it's methods. The name that follows this method must be wrapped in quotes.

DLookup is another built in function that requires quotes. Where that one can get hairy for beginners is when you pass it criteria as well:
DLookup("[My Field]","[My Table],"Instructor = '" & Me.Combo6 & "'") The brackets [] are required because I put a space in my object names (which I NEVER do).
Hope that helps.

[George]

Thanks. This is very helpful. I would work with it and try to get the hang.