Bill-
When referring to the name of a control or a field in a recordset that has
spaces or any character other than letters, numbers, and _, you must enclose the
name in brackets. To avoid having to do this, I religiously use field and
control names that I can use without the brackets. For example, instead of
Customer Name, I'll name my field CustName. When referring to the field or
control in code, I can do:
Me.CustName
.. instead of:
Me.[Customer Name]
Quotes are a bit harder. Any string literal must be surrounded by double quotes
in Visual Basic.
Me.CustName = "John Viescas"
If I want a string literal that has quotes inside it, it gets tricky. Let's say
I want to assign a string containing:
Bill said: "The quick brown fox jumped over the lazy dog."
To put the quotes inside, I have to double them up so that the parser recognizes
you want a " inside the string - that it's not just the end of the string in
your code. Like this:
Me.BillQuote = "Bill said: ""The quick brown fox jumped over the lazy
dog."""
Embedding a single quote when you're using double quote for the delimiter is no
problem:
Me.BillQuote = "You can't be too careful when using quotes in strings."
It turns out the SQL accepts either single or double quotes as a string
delimiter. You can take advantage of this to construct the required delimiters
when building a filter or an SQL string.
Me.strFilter = "CustLastName = 'Viescas'"
Because you're asking the query engine to compare to a string, you must put
quotes around it, even if there are no spaces in the string.
Now suppose you want to embed the value from some control on your form. You
have to use the concatenate operator (&) and be careful to put the quotes in the
right place.
Me.strFilter = "CustLastName = '" & Me.CustLast & "'"
Ah, but there's a catch. You don't know what might be in CustLast. It could be
something like "O'Reilly"! If you just use the line of code above, the query
engine sees:
CustLastName = 'O'Reilly'
Ooops. There's an embedded single quote that will throw everything off. You
have two ways to deal with this.
1) Use double-quotes inside the string to act as your delimiter:
Me.strFilter = "CustLastName = """ & Me.CustLast & """"
Note that I doubled up the quotes to get a single quote INSIDE the string. The
result is:
CustLastName = "O'Reilly"
No problem!
2) Use the Replace function to look for any embedded single quotes and change
them to two single quotes:
Me.strFilter = "CustLastName = '" & Replace(Me.CustLast, "'", "''") & "'"
That should get you started. Feel free to come back with specific examples
that confuse you.
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
<http://www.viescas.com/> http://www.viescas.com/
(Paris, France)
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Bill Singer
Sent: Monday, July 02, 2012 4:43 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Quotations
I have a few books I have been reading on how to write code. I have no
problems understanding the books but when I try it in Access the quotations
and brackets seem to trip me up. None of the books I am reading seem to
cover the reason and rule for quotation. Can anyone point me to a resource
that can explain why and how to use the quotations and brackets when writing
Access Code?
I need something to read over the 4th.
Thanks,
Bill Singer
MN
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
Tidak ada komentar:
Posting Komentar