strSQL is a string variable, so you have to pass it a string. To indicate
what you are passing is a string, you enclose it in quotes ("). If what you
are passing contains items that will also be acted on and they also want a
string, then the inner item also needs to be enclosed in quotes. To make it
easier to read, you can usually use single quotes here in order to
differentiate between the inner quotes and the outter quotes. This won't
work if the data has appostrophes in it. For example, if the string you are
passing is a last name, then the single quotes won't work because if you
pass a name such as O'Reilly, the apostrophe in the name will be taken to be
the 2nd single quote and you will wind up with unmatched quotes. To get
around this, you wind up having to put double quotes (") into the string.
However, this presents its own problem, because when VBA sees this double
quote it will try to pair it up with the quote at the start of the line
which will make VBA think that this is actually the end of your string
instead of the quote being an inner quote. The way around this is that the
programmers told VBA that when it sees 2 double quotes together it means
that a single double quote should be part of the string instead of it being
the end of the string.
So, if you used the immediate window to examine this equation:
strSQL = "SELECT * FROM tblTable WHERE [LastName]=""O'Rielly"";"
what you would get is
?strSQL
SELECT * FROM tblTable WHERE [LastName]="O'Rielly";
which is what you want the query to look like.
Now, some of what you have in your statement includes "concatenation". This
is tying 2 or more strings together to make on long string. The underscore
character at the end of the line means that the line is continued on the
next line. This just makes it easier to read in the code window. You could
put the whole thing on one line, but then it would probably scroll off to
the right of the screen, making it hard to read. You are going in and out of
quoted strings in your concatenation to include values from the form itself
in the resulting string. If you placed the variables or names of controls
inside the string, then the variable or control name would be what was in
the string, not the value of them. VBA takes what is inside the quotes and
passes it to strSQL, it doesn't try to evaluate it. If the item needs to be
evaluated then it has to be outside the quotes. Now, if the result of the
evaluated item is supposed to be a string value, then you have to include
the wrapping quotes inside the quoted part of the string so that when it is
all put together it looks like the example above where the result has quotes
around O'Rielly.
So, to make equipment type a string, assuming there are no apostrophes in
the equipement type, you could use this:
strSQL = "DELETE * FROM logTable WHERE " & _
"[sheetNumber]=" & Me.List71.Value & _
" And [equipmentType]='" & Me.List71.Column(1) _
& "';"
Note the single quotes inside the double quote on each side of the
Me.List71.Column(1) statement. If the equipment type did include
apostrophes, then you would need to handle it as we did above.
strSQL = "DELETE * FROM logTable WHERE " & _
"[sheetNumber]=" & Me.List71.Value & _
" And [equipmentType]=""" & Me.List71.Column(1) _
& """;"
As you can see, the singel quotes were replaced with 2 double quotes. As
stated above, the double quotes will show up in the resulting string as
single quotes around the value. This will give you something like this as a
result in the strSQL variable.
DELETE * FROM logTable WHERE [sheetNumber]=21 And
[equipmentType]="ATextValue";
This assumes that Me.List71.Value is 21 and Me.List71.Column(1) is
ATextValue.
--
Wayne Morgan
MS Access MVP
Miranda said:
hi guys, could someone please explain to me what the " and ' mean in VB. for
example,
strSQL = "DELETE * FROM logTable WHERE " & _
"[sheetNumber]=" & Me.List71.Value & _
" And [equipmentType]=" & Me.List71.Column(1) _
& ";"
so, if i wanna make equipmentType in the above text type not numeric...how
do i do this?
thanks a lot!
miranda
