DefQuery: How to use key word "AS" to create string for sql property

[Hannes S]

Hi Experts,

I have a general question.

I want to create a string (in VBA) for the sql property of a DefQuery
Object. But I don't know how to include expressions like the key word
--> AS <-- and --> " " <--.

I tried it with --> ' ' <--, but without success.

Examples which I would like to integrate:

string = "select [2003] AS ACTUAL, [2002] AS LAST_YEAR ...

.... between "aa" and "ee" ........

Thanks in advance for your help.

Kind regards,
Hannes

[Allen Browne]

There should be no problem with AS within the string.
To embed quote marks in the string, double them up, e.g.:

Dim strSql As String

strSQL = "SELECT [2003] AS Actual FROM MyTable WHERE ([MyCategoryField]
Between ""a"" And ""ezzz"");"

CurrentDb().QueryDefs("MyQuery").SQL = strSQL

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Hannes S" <hs******@gmx.at> wrote in message
news:40**************@news.utanet.at...

Hi Experts,

I have a general question.

I want to create a string (in VBA) for the sql property of a DefQuery
Object. But I don't know how to include expressions like the key word
--> AS <-- and --> " " <--.

I tried it with --> ' ' <--, but without success.

Examples which I would like to integrate:

string = "select [2003] AS ACTUAL, [2002] AS LAST_YEAR ...

.... between "aa" and "ee" ........

Thanks in advance for your help.

Kind regards,
Hannes

[Hannes S]

Hi Allen,

The problem with --> AS <-- was, that VBA always told me, that it
expects an end of the instruction. But perhaps it's something else
(like a missing question mark) I will try it again with a more simple
syntax.

Thanks for the hints regarding the question marks. I will try it
during the day.

Greetings from Austria to Australia
Hannes
On Wed, 28 Jan 2004 09:42:10 +0800, "Allen Browne"
<Al*********@SeeSig.Invalid> wrote:

There should be no problem with AS within the string.
To embed quote marks in the string, double them up, e.g.:

Dim strSql As String

strSQL = "SELECT [2003] AS Actual FROM MyTable WHERE ([MyCategoryField]
Between ""a"" And ""ezzz"");"

CurrentDb().QueryDefs("MyQuery").SQL = strSQL

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Hannes S" <hs******@gmx.at> wrote in message
news:40**************@news.utanet.at...

Hi Experts,

I have a general question.

I want to create a string (in VBA) for the sql property of a DefQuery
Object. But I don't know how to include expressions like the key word
--> AS <-- and --> " " <--.

I tried it with --> ' ' <--, but without success.

Examples which I would like to integrate:

string = "select [2003] AS ACTUAL, [2002] AS LAST_YEAR ...

.... between "aa" and "ee" ........

Thanks in advance for your help.

Kind regards,
Hannes

[Tony Toews]

An****************@See.Comment.Header (Jim's wife) wrote:

Comments: This message did not originate from the above address.
It was remailed by two or more anonymous mail services.

As always if you see a posting which looks somewhat bizarre check the headers.

Please ignore this posting.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm