D
DS
Whats with the quotes, ampersands and _ in SQL. All of the examples
that I see are without them.
Thanks
DS
that I see are without them.
Thanks
DS
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
D
Douglas J. Steele
In what context are you asking?
If you're typing SQL directly into a query, you don't normally need quotes
and ampersands, and you definitely don't need the _ continuation character.
If you're building an SQL string in VBA code, then you do need those things.
If you're typing SQL directly into a query, you don't normally need quotes
and ampersands, and you definitely don't need the _ continuation character.
If you're building an SQL string in VBA code, then you do need those things.
M
M.L. Sco Scofield
If you're talking about the SQL in the post you made earlier:
1 - The _ has nothing to do with the SQL. It is the VBA line continuation
character. Otherwise, your VBA code would be strung out on one long line.
2 - The & has nothing to do with the SQL. It is the VBA concatenation
character. It connects the various pieces together into one long group of
characters.
3 - Most of the " belong to VBA telling it that what is in the line is just
"literal" characters. Some of the " belong to SQL because text criteria in a
WHERE clause needs to be delimited.
Why you have all of these extra characters is because you are not writing
"pure" SQL. This is VBA code who's task is to create a usable SQL
expression. Most of the concatenation (&) is because you are not writing
your criteria in the SQL. You are picking up pieces of information from a
list box and incorporating it into the SQL.
Using VBA to create SQL like this is one of the more powerful things about
access. It is also one of the more complicated because you have to
understand what parts are VBA and what parts are SQL. For something like
this to work properly, you must make both VBA and SQL happy.
Good luck.
Sco
M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Denver Area Access Users Group Vice President www.DAAUG.org
MS Colorado Events Administrator www.MSColoradoEvents.com
Useful Metric Conversion #18 of 19: 8 nickels = 2 paradigms (My personal
favorite)
Miscellaneous Access and VB "stuff" at www.ScoBiz.com
1 - The _ has nothing to do with the SQL. It is the VBA line continuation
character. Otherwise, your VBA code would be strung out on one long line.
2 - The & has nothing to do with the SQL. It is the VBA concatenation
character. It connects the various pieces together into one long group of
characters.
3 - Most of the " belong to VBA telling it that what is in the line is just
"literal" characters. Some of the " belong to SQL because text criteria in a
WHERE clause needs to be delimited.
Why you have all of these extra characters is because you are not writing
"pure" SQL. This is VBA code who's task is to create a usable SQL
expression. Most of the concatenation (&) is because you are not writing
your criteria in the SQL. You are picking up pieces of information from a
list box and incorporating it into the SQL.
Using VBA to create SQL like this is one of the more powerful things about
access. It is also one of the more complicated because you have to
understand what parts are VBA and what parts are SQL. For something like
this to work properly, you must make both VBA and SQL happy.
Good luck.
Sco
M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Denver Area Access Users Group Vice President www.DAAUG.org
MS Colorado Events Administrator www.MSColoradoEvents.com
Useful Metric Conversion #18 of 19: 8 nickels = 2 paradigms (My personal
favorite)
Miscellaneous Access and VB "stuff" at www.ScoBiz.com
D
DS
Wow! Thank you. That pretty much explains it for me...So now not onlyM.L. Sco Scofield said:If you're talking about the SQL in the post you made earlier:
1 - The _ has nothing to do with the SQL. It is the VBA line continuation
character. Otherwise, your VBA code would be strung out on one long line.
2 - The & has nothing to do with the SQL. It is the VBA concatenation
character. It connects the various pieces together into one long group of
characters.
3 - Most of the " belong to VBA telling it that what is in the line is just
"literal" characters. Some of the " belong to SQL because text criteria in a
WHERE clause needs to be delimited.
Why you have all of these extra characters is because you are not writing
"pure" SQL. This is VBA code who's task is to create a usable SQL
expression. Most of the concatenation (&) is because you are not writing
your criteria in the SQL. You are picking up pieces of information from a
list box and incorporating it into the SQL.
Using VBA to create SQL like this is one of the more powerful things about
access. It is also one of the more complicated because you have to
understand what parts are VBA and what parts are SQL. For something like
this to work properly, you must make both VBA and SQL happy.
Good luck.
Sco
M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Denver Area Access Users Group Vice President www.DAAUG.org
MS Colorado Events Administrator www.MSColoradoEvents.com
Useful Metric Conversion #18 of 19: 8 nickels = 2 paradigms (My personal
favorite)
Miscellaneous Access and VB "stuff" at www.ScoBiz.com
it seems I have to learn more about SQL but also how to use it with
VBA...any good books out there. Once again thank you. this cleared it
up for me.
DS
T
Tom Collins\(Home\)
| Whats with the quotes, ampersands and _ in SQL. All of the examples
| that I see are without them.
| Thanks
| DS
Quotes are used for literal text: SELECT FName FROM Table1 WHERE FName
= "Smith"
Underscores _ are used as part of a field name. It helps breakup the
words. First_Name vs. FirstName. It's just the style of the developer.
Ampersands can also be used in field name (but rarely). More often
they are used to concatenate strings. "abc" & "123" will give you
"abc123". You may want to do this when you need to concatenate first
name with last name.
Square Brackets [] are used to designate a table name or field name.
[First_Name] & " " & [Last_Name].
Periods are used to separate table names from field names.
[Table1].[First_name]
Exclamation Marks ! can also be used the same as periods.
Asterisks and Question Marks * ? are used as a wild cards.
? means any single character. "T?m" will call up Tim, Tom.
* means 1 or more characters. "T?m" will call up Tim, Tom. Thom,
Tearoom
The pound Sign # is used instead of quotes when working with dates.
#2/12/2005#
This should clear things up.
Tom Collins
| that I see are without them.
| Thanks
| DS
Quotes are used for literal text: SELECT FName FROM Table1 WHERE FName
= "Smith"
Underscores _ are used as part of a field name. It helps breakup the
words. First_Name vs. FirstName. It's just the style of the developer.
Ampersands can also be used in field name (but rarely). More often
they are used to concatenate strings. "abc" & "123" will give you
"abc123". You may want to do this when you need to concatenate first
name with last name.
Square Brackets [] are used to designate a table name or field name.
[First_Name] & " " & [Last_Name].
Periods are used to separate table names from field names.
[Table1].[First_name]
Exclamation Marks ! can also be used the same as periods.
Asterisks and Question Marks * ? are used as a wild cards.
? means any single character. "T?m" will call up Tim, Tom.
* means 1 or more characters. "T?m" will call up Tim, Tom. Thom,
Tearoom
The pound Sign # is used instead of quotes when working with dates.
#2/12/2005#
This should clear things up.
Tom Collins
M
M.L. Sco Scofield
You're welcome.
The Access 2002 (they said they won't being doing a 2003 version)
Developer's Handbook from Sybex by Ken Getz, et al. has a good chapter on
Access SQL. It also has lots of VBA code throughout the book.
The Access 2003 VBA Programmer's Reference from Wrox is a good VBA book.
Although they say it is a reference, I think it is a better tutorial that
their Beginning Access 2003 VBA book which is supposed to be a tutorial.
There's nothing I know of off the top of my head that really goes into
mixing VBA and SQL. There are a lot of samples in magazine articles. I
highly recommend the Access Advisor magazine.
You might also download the SQL 101 presentation from my website. Although
it is a 97 format database, I've converted it to Access 2000 format many
times with no problems. You might be able to get a little from the
PowerPoint slides about mixing VBA and SQL.
Sco
M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Denver Area Access Users Group Vice President www.DAAUG.org
MS Colorado Events Administrator www.MSColoradoEvents.com
Useful Metric Conversion #18 of 19: 8 nickels = 2 paradigms (My personal
favorite)
Miscellaneous Access and VB "stuff" at www.ScoBiz.com
The Access 2002 (they said they won't being doing a 2003 version)
Developer's Handbook from Sybex by Ken Getz, et al. has a good chapter on
Access SQL. It also has lots of VBA code throughout the book.
The Access 2003 VBA Programmer's Reference from Wrox is a good VBA book.
Although they say it is a reference, I think it is a better tutorial that
their Beginning Access 2003 VBA book which is supposed to be a tutorial.
There's nothing I know of off the top of my head that really goes into
mixing VBA and SQL. There are a lot of samples in magazine articles. I
highly recommend the Access Advisor magazine.
You might also download the SQL 101 presentation from my website. Although
it is a 97 format database, I've converted it to Access 2000 format many
times with no problems. You might be able to get a little from the
PowerPoint slides about mixing VBA and SQL.
Sco
M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Denver Area Access Users Group Vice President www.DAAUG.org
MS Colorado Events Administrator www.MSColoradoEvents.com
Useful Metric Conversion #18 of 19: 8 nickels = 2 paradigms (My personal
favorite)
Miscellaneous Access and VB "stuff" at www.ScoBiz.com
D
DS
Wow, thanks for the instant primer...I'm printing this out. Saves me aTom said:| Whats with the quotes, ampersands and _ in SQL. All of the examples
| that I see are without them.
| Thanks
| DS
Quotes are used for literal text: SELECT FName FROM Table1 WHERE FName
= "Smith"
Underscores _ are used as part of a field name. It helps breakup the
words. First_Name vs. FirstName. It's just the style of the developer.
Ampersands can also be used in field name (but rarely). More often
they are used to concatenate strings. "abc" & "123" will give you
"abc123". You may want to do this when you need to concatenate first
name with last name.
Square Brackets [] are used to designate a table name or field name.
[First_Name] & " " & [Last_Name].
Periods are used to separate table names from field names.
[Table1].[First_name]
Exclamation Marks ! can also be used the same as periods.
Asterisks and Question Marks * ? are used as a wild cards.
? means any single character. "T?m" will call up Tim, Tom.
* means 1 or more characters. "T?m" will call up Tim, Tom. Thom,
Tearoom
The pound Sign # is used instead of quotes when working with dates.
#2/12/2005#
This should clear things up.
Tom Collins
lot of reading. Once again, Thank You
DS