SQL & VBA

[bebelino]

Hello,
I've asked this same question two weeks ago or so. So, this is my last
shot in hoping there's one person with an answer.
I use a lot of SQL statements in my VBA-code. I do it like this: make
the query, go the SQL code, copy and paste this in my VBA-code.
Now here's my problem: each time I have to make the same sort of
corrections in VBA in order to make the SQL work:
e.g. "SELECT tlbA.a & " " & tblB.b as ab" --> has to be changed in
"SELECT tblA.a & " & chr$(34) & " " & chr$(34) & " & tblB.b as ab"
Does anyone know of a plugin or a function that can do these sort of
corrections automaticaly? I could write one myself, but I think it's
gonna be a tough one.
Thanks,
--
bebelino

[Bruce Rusk]

It's easier to use single quotes (apostrophes) rather than chr$(34) in VBA.
That way you just have a continuous string with no interruption:

"SELECT tblA.a & ' ' & tblB.b as ab"

Should work in A2K or 2K2, dunno about A97.

"bebelino" <a.*@c.d> wrote in message
news:ie********************************@4ax.com...

Hello,
I've asked this same question two weeks ago or so. So, this is my last
shot in hoping there's one person with an answer.
I use a lot of SQL statements in my VBA-code. I do it like this: make
the query, go the SQL code, copy and paste this in my VBA-code.
Now here's my problem: each time I have to make the same sort of
corrections in VBA in order to make the SQL work:
e.g. "SELECT tlbA.a & " " & tblB.b as ab" --> has to be changed in
"SELECT tblA.a & " & chr$(34) & " " & chr$(34) & " & tblB.b as ab"
Does anyone know of a plugin or a function that can do these sort of
corrections automaticaly? I could write one myself, but I think it's
gonna be a tough one.
Thanks,
--
bebelino

[Rich P]

The answer to your problem is in the problem. If you can state what the
problem is that you are having then someone will be able to suggest how
to solve the problem.

Now here's my problem: each time I have to make the same sort of
corrections in VBA in order to make the SQL work:
e.g. "SELECT tlbA.a & " " & tblB.b as ab" --> has to be changed in
"SELECT tblA.a & " & chr$(34) & " " & chr$(34) & " & tblB.b as ab"
<<

Here you are showing a correction that you make manually, but you are
not stating what the problem is which you are correcting.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Bob Quintal]

bebelino <a.*@c.d> wrote in
news:ie********************************@4ax.com:

Hello,
I've asked this same question two weeks ago or so. So, this is
my last shot in hoping there's one person with an answer.
I use a lot of SQL statements in my VBA-code. I do it like
this: make the query, go the SQL code, copy and paste this in
my VBA-code. Now here's my problem: each time I have to make
the same sort of corrections in VBA in order to make the SQL
work: e.g. "SELECT tlbA.a & " " & tblB.b as ab" --> has to be
changed in "SELECT tblA.a & " & chr$(34) & " " & chr$(34) & "
& tblB.b as ab" Does anyone know of a plugin or a function
that can do these sort of corrections automaticaly? I could
write one myself, but I think it's gonna be a tough one.
Thanks,

I suspect that you are trying to concatenate two fields with a
space between them. It might be a lot simpler to write your query
as "SELECT tblA.a & space$(1) & tblB.b as ab". If you do htis in
the query builder, you don't have to change it to make it work in
VBA.

Bob Q

[Albert D. Kallal]

Hum, as others have mentioned, we are not really seeing your problem.

Why not in the query builder just go:
Ab:(a & ' ' & b)

That way, you don't have to change any code at all. Further, I often don't
even paste the sql into the code anyway. You can go:
dim strSql as string
dim rstRecs as dao.RecordSet

strSql = currentdb.querydefs("queryname").sql
strSql = Left(strSql, InStr(strSql, ";") - 1)
The above little trick means you don't have this big ugly mess of sql pasted
into your code, but can continue to use the query builder.

And, for sure, if you don't actually need to modify the sql, then you can
use:

set rstRecs = currentdb.OpenRecordSet("queryName")

Again, I not really sure what your problem is, but in the thousands and
thousands of posts here I have not seen your question, nor your problem.
Clearly either we here in the group are totally missing something, or you
are missing something?

Feel free to ask more questions, or perhaps you might post the 2 or 3 lines
of vba code that uses the sql text to give us an idea of what you are trying
to accomplish.

However, I see no reason as to why you don't just create the expression in
the query grid builder as:

Ab:(a & ' ' & b)
--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
ka****@msn.com
http://www.attcanada.net/~kallal.msn

[bebelino]

On Mon, 6 Oct 2003 17:10:48 -0400, "Bruce Rusk"
<ho**********@spamless.ucla.edu> wrote:

It's easier to use single quotes (apostrophes) rather than chr$(34) in VBA.
That way you just have a continuous string with no interruption:

"SELECT tblA.a & ' ' & tblB.b as ab"

Okay, thanks, didn't know that,
--
bebelino

[bebelino]

On 06 Oct 2003 21:22:19 GMT, Rich P <rp*****@aol.com> wrote:

The answer to your problem is in the problem. If you can state what the
problem is that you are having then someone will be able to suggest how
to solve the problem.
Off course.
Here you are showing a correction that you make manually, but you are
not stating what the problem is which you are correcting.

It's just the problem that I need to manually correct all over again
SQL-statements in VBA. Not only those " character but the format of a
date etc. But I begin to relealize that I'm doing it all wrond,
thanks,
--
bebelino

[bebelino]

On Mon, 06 Oct 2003 22:50:59 GMT, Bob Quintal
<bq******@generation.net> wrote:

I suspect that you are trying to concatenate two fields with a
space between them. It might be a lot simpler to write your query
as "SELECT tblA.a & space$(1) & tblB.b as ab". If you do htis in
the query builder, you don't have to change it to make it work in
VBA.

I've learned that it's even possible in a shorter way. Just ' '
instead of chr$(34) or space$(1).
regards,
--
bebelino

[bebelino]

On Tue, 07 Oct 2003 02:46:47 GMT, "Albert D. Kallal" <ka****@msn.com>
wrote:

That way, you don't have to change any code at all. Further, I often don't
even paste the sql into the code anyway. You can go:
You're right. I've spent a lot of time over the last years to edit
queries in VBA where it could be a whole lot easier.
dim strSql as string
dim rstRecs as dao.RecordSet

strSql = currentdb.querydefs("queryname").sql
strSql = Left(strSql, InStr(strSql, ";") - 1)

The above little trick means you don't have this big ugly mess of sql pasted
into your code, but can continue to use the query builder.
Wonderful tip, thanks.
And, for sure, if you don't actually need to modify the sql, then you can
use:

set rstRecs = currentdb.OpenRecordSet("queryName")
Did know that, but since I use a lot of SQL in code I found it not
clear to have so many queries in my querytab, and each time find an
explanatory name for it. Is there some good way to divide those
queries into groups? For instance those you only use in code etc. Or
am I missing again something?
Again, I not really sure what your problem is, but in the thousands and
thousands of posts here I have not seen your question, nor your problem.
Clearly either we here in the group are totally missing something, or you
are missing something?
I think it's the last ;-)
The way I builded queries with the query-editor forced me to make the
same corrections over and over again when I copied the SQL into code.
And then offcourse I've always used the chr$(34) and it became very
messy.
Feel free to ask more questions, or perhaps you might post the 2 or 3 lines
of vba code that uses the sql text to give us an idea of what you are trying
to accomplish.

Thanks, if I run into other query-problems I will ask them.
Many thanks for the great tips. I wonder how it's possible that I've
never seen a solution to my problem.
regards,
--
bebelino

[Albert D. Kallal]

"bebelino" <a.*@c.d> wrote in message
news:jk********************************@4ax.com...

Again, I not really sure what your problem is, but in the thousands and
thousands of posts here I have not seen your question, nor your problem.
Clearly either we here in the group are totally missing something, or you
are missing something?
I think it's the last ;-)

Nah, don't let me discourage you in anyway. As mentioned, it is only that
myself was not quite grasping the problem here! (this is not your fault!).
I think in reading the above...I was being a bit harsh. I am sorry about
that!
My apologies.
The way I builded queries with the query-editor forced me to make the
same corrections over and over again when I copied the SQL into code.
And then offcourse I've always used the chr$(34) and it became very
messy.

yes, I hoping that little trick of:

ab:(a & ' ' & b)

Will solve your problem. (as mentioned, I might be slight missing your
point).

The query builder will turn the above into:

select (a & ' ' & b) as ab

And, I will say for sure, I use the query builder just like, and then paste
in the resulting code. In many cases, I wish the query builder did NOT place
the table name in front of each field.

I would say that I am also near lost with out the query builder. I also
OFTEN have to modify the resulting text after I paste the sql into VBA. So,
I don't want in any way to suggest that the resulting text never needs
modifying (it certainly does!). However, I am suggesting the above little
expression should fix your concatenation problem.

And yes, I wish all of the forms, queries etc could be organized by folders.

--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
ka****@msn.com
http://www.attcanada.net/~kallal.msn

[bebelino]

On Tue, 07 Oct 2003 02:46:47 GMT, "Albert D. Kallal" <ka****@msn.com>
wrote:

That way, you don't have to change any code at all. Further, I often don't
even paste the sql into the code anyway. You can go:
You're right. I've spent a lot of time over the last years to edit
queries in VBA where it could be a whole lot easier.
dim strSql as string
dim rstRecs as dao.RecordSet

strSql = currentdb.querydefs("queryname").sql
strSql = Left(strSql, InStr(strSql, ";") - 1)

The above little trick means you don't have this big ugly mess of sql pasted
into your code, but can continue to use the query builder.
Wonderful tip, thanks.
And, for sure, if you don't actually need to modify the sql, then you can
use:

set rstRecs = currentdb.OpenRecordSet("queryName")
Did know that, but since I use a lot of SQL in code I found it not
clear to have so many queries in my querytab, and each time find an
explanatory name for it. Is there some good way to divide those
queries into groups? For instance those you only use in code etc. Or
am I missing again something?
Again, I not really sure what your problem is, but in the thousands and
thousands of posts here I have not seen your question, nor your problem.
Clearly either we here in the group are totally missing something, or you
are missing something?
I think it's the last ;-)
The way I builded queries with the query-editor forced me to make the
same corrections over and over again when I copied the SQL into code.
And then offcourse I've always used the chr$(34) and it became very
messy.
Feel free to ask more questions, or perhaps you might post the 2 or 3 lines
of vba code that uses the sql text to give us an idea of what you are trying
to accomplish.

Thanks, if I run into other query-problems I will ask them.
Many thanks for the great tips. I wonder how it's possible that I've
never seen a solution to my problem.
regards,
--
bebelino

[bebelino]

On Wed, 08 Oct 2003 03:02:10 GMT, "Albert D. Kallal" <ka****@msn.com>
wrote:

Nah, don't let me discourage you in anyway. As mentioned, it is only that
myself was not quite grasping the problem here! (this is not your fault!).
I think in reading the above...I was being a bit harsh. I am sorry about
that!
My apologies.

Really, no problem.

The way I builded queries with the query-editor forced me to make the
same corrections over and over again when I copied the SQL into code.
And then offcourse I've always used the chr$(34) and it became very
messy.

I would say that I am also near lost with out the query builder. I also
OFTEN have to modify the resulting text after I paste the sql into VBA. So,
I don't want in any way to suggest that the resulting text never needs
modifying (it certainly does!). However, I am suggesting the above little
expression should fix your concatenation problem.
It was this I tried to explain. Since modifications of SQL-code in VBA
often need the same modifications (dates and so on) I was wondering
if someone had a module or whatever where you passed the sqlstring in
and would return a modified sql-string, ready to paste into code. But
with ' ', I'm a whole lot further. And when I think about it now, I
will have less more work to do.
And yes, I wish all of the forms, queries etc could be organized by folders.

Just like me indeed.
best regards,
--
bebelino

[Bruce Rusk]

Did know that, but since I use a lot of SQL in code I found it not
clear to have so many queries in my querytab, and each time find an
explanatory name for it. Is there some good way to divide those
queries into groups? For instance those you only use in code etc. Or
am I missing again something?

There are a couple of ways to do this; one is by a naming convention
(something like qvbaxxx for any query that will be used in code only). One
advantage is that it will group queries in alphabetical order.

I use a simple, quick-and-dirty approach: make all the queries the user
shouldn't be seeing (those used by modules and/or forms and reports) hidden
and turning View Hidden on (they become visible but greyed out in the
database window), and when you give the mdb to users you just turn View
Hidden off. There are also ways to make groups, but probably not worth it.

[bebelino]

On Wed, 8 Oct 2003 11:08:05 -0400, "Bruce Rusk"
<ho**********@spamless.ucla.edu> wrote:

There are a couple of ways to do this; one is by a naming convention
(something like qvbaxxx for any query that will be used in code only). One
advantage is that it will group queries in alphabetical order.

I use a simple, quick-and-dirty approach: make all the queries the user
shouldn't be seeing (those used by modules and/or forms and reports) hidden
and turning View Hidden on (they become visible but greyed out in the
database window), and when you give the mdb to users you just turn View
Hidden off. There are also ways to make groups, but probably not worth it.

Thanks, qvbaxxx --> off course, simple.
I found those grouping possibilities with the favoritesfolder. Have to
check it further
regards,
--
bebelino

[Tony Toews]

bebelino <a.*@c.d> wrote:

It was this I tried to explain. Since modifications of SQL-code in VBA
often need the same modifications (dates and so on) I was wondering
if someone had a module or whatever where you passed the sqlstring in
and would return a modified sql-string, ready to paste into code.

What I prefer to do is the following from my page "One method of using SQL queries in
code as parameter queries can be difficult to work with in code"
http://www.granite.ab.ca/access/sqlqueries.htm

The problem is that you need to use a query in code such as looping through a
recordset or running an action query of some sort but you want to limit the number of
records via some criteria.

However when you run the query in the query window you have to enter appropriate
parameter values. Locating an appropriate value can be a pain in the ...

Actually what I always do when action or recordset queries get the slightest bit ugly
I make a SELECT query with the required fields. By ugly I mean one join to another
table, a lot of fields or whatever. I get this query working nicely showing all the
records I want. But with no selection criteria. Now I can quickly see if the query
looks proper now or six months from now when I forget what appropriate values were
for the criteria.

I make a SELECT query with the required fields. I get this query working nicely
showing all the records I want. But with no selection criteria. Now I can quickly
see if the query looks proper now or six months from now when my memory is a bit hazy
on the specifics.

I then create the action or SELECT query in the QBE grid with dummy selection
criteria. I do NOT save it. Instead I click to the SQL code view and copy and Ctrl+C
to copy the code to the clipboard. Now I go into my VBA module and Ctrl+V to paste
the SQL code into the VBA code.. I then setup the strings with the double quotes and
line continuation for readability along the proper WHERE clause criteria from my
calling form or my code.

' decrement the QOH MatItemSizeQty record, ie change 5 to 3
strSQL = "UPDATE [Mat Rcvg - Update MRR - Update Qtys] " & _
"SET miqQtyOnHand = [miqQtyOnHand]-" & sngInventoryQty & " " & _
"WHERE itdID=" & me!InvTransDetailID & ";"
CurrentDb.Execute strSQL, dbFailOnError

where [Mat Rcvg - Update MRR - Update Qtys] is the ugly basic query.

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

[bebelino]

On Thu, 09 Oct 2003 18:27:59 GMT, Tony Toews <tt****@telusplanet.net>
wrote:

What I prefer to do is the following from my page "One method of using SQL queries in
code as parameter queries can be difficult to work with in code"
http://www.granite.ab.ca/access/sqlqueries.htm

The problem is that you need to use a query in code such as looping through a
recordset or running an action query of some sort but you want to limit the number of
records via some criteria.

However when you run the query in the query window you have to enter appropriate
parameter values. Locating an appropriate value can be a pain in the ...

Actually what I always do when action or recordset queries get the slightest bit ugly
I make a SELECT query with the required fields. By ugly I mean one join to another
table, a lot of fields or whatever. I get this query working nicely showing all the
records I want. But with no selection criteria. Now I can quickly see if the query
looks proper now or six months from now when I forget what appropriate values were
for the criteria.

I make a SELECT query with the required fields. I get this query working nicely
showing all the records I want. But with no selection criteria. Now I can quickly
see if the query looks proper now or six months from now when my memory is a bit hazy
on the specifics.

I then create the action or SELECT query in the QBE grid with dummy selection
criteria. I do NOT save it. Instead I click to the SQL code view and copy and Ctrl+C
to copy the code to the clipboard. Now I go into my VBA module and Ctrl+V to paste
the SQL code into the VBA code.. I then setup the strings with the double quotes and
line continuation for readability along the proper WHERE clause criteria from my
calling form or my code.

' decrement the QOH MatItemSizeQty record, ie change 5 to 3
strSQL = "UPDATE [Mat Rcvg - Update MRR - Update Qtys] " & _
"SET miqQtyOnHand = [miqQtyOnHand]-" & sngInventoryQty & " " & _
"WHERE itdID=" & me!InvTransDetailID & ";"
CurrentDb.Execute strSQL, dbFailOnError

where [Mat Rcvg - Update MRR - Update Qtys] is the ugly basic query.

Thanks for your reply tony,
regards,
--
bebelino