By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,498 Members | 1,564 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,498 IT Pros & Developers. It's quick & easy.

Referring to a variable field using SQL

P: n/a
I have built a SQL statement that is trying to loop through the fields of a
table that was built from a spreadsheet and hence is "short and fat". So
rather than hard-coding, I have a loop from field #6 to the last field
(#63). When I use the fld variable (type integer) in the loop, I have no
problem with this line which is near the end of the SQL statement:

strMySql = strMySql & "Accts.[Rev], Accts.Manager, " & "[" &
tdf.Fields(fld).Name & "],"

But when I try to indicate that I only want results for non-null values, I
am getting stuck. I have tried to add a "Where" clause after the From:

"strMySql = strMySql & " AS Attribute FROM ChaseAccts WHERE [" &
tdf.Fields(fld).Value & "]" > 0

If I use the property tdf.Fields(fld).Name, I get a "Type Mismatch" error,
though using the name of the field (as opposed to a variable for the name)
spelled out in SQL works. So I tried indicating the Value, but even in the
watch window, it says this is an <invalid operation>. It is not critical, I
can delete the nulls in a second query, but it is bugging me (3 hours worth
of irritation).

What am I doing wrong?

Thanks
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
The problem is that yo improperly mixed the levels of abstraction in the
second case. The higher level of abstraction is where you are using VB to
build a string which must be a valid SQL statement that the JET SQL engine
will interpret. When you refer to the .Value property in the VB code that
builds the string, you are inserting the field's value into the string instead
of its name. SQL will just want the name of the field that contains the value
to check - the SQL will retrieve the value from the field, not VB.

On the other side of the coin, you can't compare with zero in the VB code
itself because VB is not running the query, but building the query command to
be executed. You get the type mismatch because you are telling the code to
build a string, then compare that string with a number. Incidentally, you
can't check for Null by comparing with zero. You have to use the Is Null
operator (or the IsNull function when you need to check for Null in VB code,
but that's not this case).

Your second case should read like this...

strMySql = strMySql & " AS Attribute FROM ChaseAccts WHERE [" &
tdf.Fields(fld).Name & "] Is Not Null"

On Sat, 13 Dec 2003 05:49:11 GMT, "Colleyville Alan"
<ae***********@nospam.comcast.net> wrote:
I have built a SQL statement that is trying to loop through the fields of a
table that was built from a spreadsheet and hence is "short and fat". So
rather than hard-coding, I have a loop from field #6 to the last field
(#63). When I use the fld variable (type integer) in the loop, I have no
problem with this line which is near the end of the SQL statement:

strMySql = strMySql & "Accts.[Rev], Accts.Manager, " & "[" &
tdf.Fields(fld).Name & "],"

But when I try to indicate that I only want results for non-null values, I
am getting stuck. I have tried to add a "Where" clause after the From:

"strMySql = strMySql & " AS Attribute FROM ChaseAccts WHERE [" &
tdf.Fields(fld).Value & "]" > 0

If I use the property tdf.Fields(fld).Name, I get a "Type Mismatch" error,
though using the name of the field (as opposed to a variable for the name)
spelled out in SQL works. So I tried indicating the Value, but even in the
watch window, it says this is an <invalid operation>. It is not critical, I
can delete the nulls in a second query, but it is bugging me (3 hours worth
of irritation).

What am I doing wrong?

Thanks


Nov 12 '05 #2

P: n/a
Thanks for both the answer and especially the explanation.

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:ig********************************@4ax.com...
The problem is that yo improperly mixed the levels of abstraction in the
second case. The higher level of abstraction is where you are using VB to
build a string which must be a valid SQL statement that the JET SQL engine
will interpret. When you refer to the .Value property in the VB code that
builds the string, you are inserting the field's value into the string instead of its name. SQL will just want the name of the field that contains the value to check - the SQL will retrieve the value from the field, not VB.

On the other side of the coin, you can't compare with zero in the VB code
itself because VB is not running the query, but building the query command to be executed. You get the type mismatch because you are telling the code to build a string, then compare that string with a number. Incidentally, you
can't check for Null by comparing with zero. You have to use the Is Null
operator (or the IsNull function when you need to check for Null in VB code, but that's not this case).

Your second case should read like this...

strMySql = strMySql & " AS Attribute FROM ChaseAccts WHERE [" &
tdf.Fields(fld).Name & "] Is Not Null"

On Sat, 13 Dec 2003 05:49:11 GMT, "Colleyville Alan"
<ae***********@nospam.comcast.net> wrote:
I have built a SQL statement that is trying to loop through the fields of atable that was built from a spreadsheet and hence is "short and fat". Sorather than hard-coding, I have a loop from field #6 to the last field
(#63). When I use the fld variable (type integer) in the loop, I have no
problem with this line which is near the end of the SQL statement:

strMySql = strMySql & "Accts.[Rev], Accts.Manager, " & "[" &
tdf.Fields(fld).Name & "],"

But when I try to indicate that I only want results for non-null values, Iam getting stuck. I have tried to add a "Where" clause after the From:

"strMySql = strMySql & " AS Attribute FROM ChaseAccts WHERE [" &
tdf.Fields(fld).Value & "]" > 0

If I use the property tdf.Fields(fld).Name, I get a "Type Mismatch" error,though using the name of the field (as opposed to a variable for the name)spelled out in SQL works. So I tried indicating the Value, but even in thewatch window, it says this is an <invalid operation>. It is not critical, Ican delete the nulls in a second query, but it is bugging me (3 hours worthof irritation).

What am I doing wrong?

Thanks

Nov 12 '05 #3

P: n/a
Your explanation helped and the code works, but while I had mostly nulls
that needed to be eliminated, there were some zeroes as well. I finally ran
a query to change the zeroes to null and then this sql statement and it all
worked, but still the question remains: how do I say show only values >0 in
this field? If I had wanted to show values >50 and <75, there should be a
way to do it.

In the interactive mode, a >0 works in the criteria field. It also works
when I change to SQL and have the name spelled out, in this case the field
was the name of a mutual fund and the value was the $amt invested in it. So
if I had [Janus Worldwide}>0 in my sql statement, it works. But there must
be a way to do this in code.

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:ig********************************@4ax.com...
The problem is that yo improperly mixed the levels of abstraction in the
second case. The higher level of abstraction is where you are using VB to
build a string which must be a valid SQL statement that the JET SQL engine
will interpret. When you refer to the .Value property in the VB code that
builds the string, you are inserting the field's value into the string instead of its name. SQL will just want the name of the field that contains the value to check - the SQL will retrieve the value from the field, not VB.

On the other side of the coin, you can't compare with zero in the VB code
itself because VB is not running the query, but building the query command to be executed. You get the type mismatch because you are telling the code to build a string, then compare that string with a number. Incidentally, you
can't check for Null by comparing with zero. You have to use the Is Null
operator (or the IsNull function when you need to check for Null in VB code, but that's not this case).

Your second case should read like this...

strMySql = strMySql & " AS Attribute FROM ChaseAccts WHERE [" &
tdf.Fields(fld).Name & "] Is Not Null"

On Sat, 13 Dec 2003 05:49:11 GMT, "Colleyville Alan"
<ae***********@nospam.comcast.net> wrote:
I have built a SQL statement that is trying to loop through the fields of atable that was built from a spreadsheet and hence is "short and fat". Sorather than hard-coding, I have a loop from field #6 to the last field
(#63). When I use the fld variable (type integer) in the loop, I have no
problem with this line which is near the end of the SQL statement:

strMySql = strMySql & "Accts.[Rev], Accts.Manager, " & "[" &
tdf.Fields(fld).Name & "],"

But when I try to indicate that I only want results for non-null values, Iam getting stuck. I have tried to add a "Where" clause after the From:

"strMySql = strMySql & " AS Attribute FROM ChaseAccts WHERE [" &
tdf.Fields(fld).Value & "]" > 0

If I use the property tdf.Fields(fld).Name, I get a "Type Mismatch" error,though using the name of the field (as opposed to a variable for the name)spelled out in SQL works. So I tried indicating the Value, but even in thewatch window, it says this is an <invalid operation>. It is not critical, Ican delete the nulls in a second query, but it is bugging me (3 hours worthof irritation).

What am I doing wrong?

Thanks

Nov 12 '05 #4

P: n/a
Since, as you noted, >0 works to check for non-null positive numbers in SQL,
it also works when you generate the same kind of SQL in VB code. That means,
you could change the VB code back to something closer to your original
statement, but with the ">0" part inside the quotes, not outside. Just say
">0" instead of " Not Null". The reason this works is that comparing a Null
with anything using any comparison operator does not give True or False, but
Null as a result. When the Where clause is evaluated, Null is treated the
same as False.

On Sat, 13 Dec 2003 14:10:10 GMT, "Colleyville Alan"
<ae***********@nospam.comcast.net> wrote:
Your explanation helped and the code works, but while I had mostly nulls
that needed to be eliminated, there were some zeroes as well. I finally ran
a query to change the zeroes to null and then this sql statement and it all
worked, but still the question remains: how do I say show only values >0 in
this field? If I had wanted to show values >50 and <75, there should be a
way to do it.

In the interactive mode, a >0 works in the criteria field. It also works
when I change to SQL and have the name spelled out, in this case the field
was the name of a mutual fund and the value was the $amt invested in it. So
if I had [Janus Worldwide}>0 in my sql statement, it works. But there must
be a way to do this in code.

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:ig********************************@4ax.com.. .
The problem is that yo improperly mixed the levels of abstraction in the
second case. The higher level of abstraction is where you are using VB to
build a string which must be a valid SQL statement that the JET SQL engine
will interpret. When you refer to the .Value property in the VB code that
builds the string, you are inserting the field's value into the string

instead
of its name. SQL will just want the name of the field that contains the

value
to check - the SQL will retrieve the value from the field, not VB.

On the other side of the coin, you can't compare with zero in the VB code
itself because VB is not running the query, but building the query command

to
be executed. You get the type mismatch because you are telling the code

to
build a string, then compare that string with a number. Incidentally, you
can't check for Null by comparing with zero. You have to use the Is Null
operator (or the IsNull function when you need to check for Null in VB

code,
but that's not this case).

Your second case should read like this...

strMySql = strMySql & " AS Attribute FROM ChaseAccts WHERE [" &
tdf.Fields(fld).Name & "] Is Not Null"

On Sat, 13 Dec 2003 05:49:11 GMT, "Colleyville Alan"
<ae***********@nospam.comcast.net> wrote:
>I have built a SQL statement that is trying to loop through the fields ofa >table that was built from a spreadsheet and hence is "short and fat".So >rather than hard-coding, I have a loop from field #6 to the last field
>(#63). When I use the fld variable (type integer) in the loop, I have no
>problem with this line which is near the end of the SQL statement:
>
> strMySql = strMySql & "Accts.[Rev], Accts.Manager, " & "[" &
>tdf.Fields(fld).Name & "],"
>
>But when I try to indicate that I only want results for non-null values,I >am getting stuck. I have tried to add a "Where" clause after the From:
>
>"strMySql = strMySql & " AS Attribute FROM ChaseAccts WHERE [" &
>tdf.Fields(fld).Value & "]" > 0
>
>If I use the property tdf.Fields(fld).Name, I get a "Type Mismatch"error, >though using the name of the field (as opposed to a variable for thename) >spelled out in SQL works. So I tried indicating the Value, but even inthe >watch window, it says this is an <invalid operation>. It is notcritical, I >can delete the nulls in a second query, but it is bugging me (3 hoursworth >of irritation).
>
>What am I doing wrong?
>
>Thanks
>


Nov 12 '05 #5

P: n/a
Thanks
"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:3u********************************@4ax.com...
Since, as you noted, >0 works to check for non-null positive numbers in SQL, it also works when you generate the same kind of SQL in VB code. That means, you could change the VB code back to something closer to your original
statement, but with the ">0" part inside the quotes, not outside. Just say ">0" instead of " Not Null". The reason this works is that comparing a Null with anything using any comparison operator does not give True or False, but Null as a result. When the Where clause is evaluated, Null is treated the
same as False.

On Sat, 13 Dec 2003 14:10:10 GMT, "Colleyville Alan"
<ae***********@nospam.comcast.net> wrote:
Your explanation helped and the code works, but while I had mostly nulls
that needed to be eliminated, there were some zeroes as well. I finally rana query to change the zeroes to null and then this sql statement and it allworked, but still the question remains: how do I say show only values >0 inthis field? If I had wanted to show values >50 and <75, there should be away to do it.

In the interactive mode, a >0 works in the criteria field. It also works
when I change to SQL and have the name spelled out, in this case the fieldwas the name of a mutual fund and the value was the $amt invested in it. Soif I had [Janus Worldwide}>0 in my sql statement, it works. But there mustbe a way to do this in code.

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:ig********************************@4ax.com.. .
The problem is that yo improperly mixed the levels of abstraction in the second case. The higher level of abstraction is where you are using VB to build a string which must be a valid SQL statement that the JET SQL engine will interpret. When you refer to the .Value property in the VB code that builds the string, you are inserting the field's value into the string

instead
of its name. SQL will just want the name of the field that contains the
value
to check - the SQL will retrieve the value from the field, not VB.

On the other side of the coin, you can't compare with zero in the VB
code itself because VB is not running the query, but building the query commandto
be executed. You get the type mismatch because you are telling the
codeto
build a string, then compare that string with a number. Incidentally,
you can't check for Null by comparing with zero. You have to use the Is Null operator (or the IsNull function when you need to check for Null in VB

code,
but that's not this case).

Your second case should read like this...

strMySql = strMySql & " AS Attribute FROM ChaseAccts WHERE [" &
tdf.Fields(fld).Name & "] Is Not Null"

On Sat, 13 Dec 2003 05:49:11 GMT, "Colleyville Alan"
<ae***********@nospam.comcast.net> wrote:

>I have built a SQL statement that is trying to loop through the fields ofa
>table that was built from a spreadsheet and hence is "short and fat".

So
>rather than hard-coding, I have a loop from field #6 to the last field
>(#63). When I use the fld variable (type integer) in the loop, I have
no >problem with this line which is near the end of the SQL statement:
>
> strMySql = strMySql & "Accts.[Rev], Accts.Manager, " & "[" &
>tdf.Fields(fld).Name & "],"
>
>But when I try to indicate that I only want results for non-null values,I
>am getting stuck. I have tried to add a "Where" clause after the
From: >
>"strMySql = strMySql & " AS Attribute FROM ChaseAccts WHERE [" &
>tdf.Fields(fld).Value & "]" > 0
>
>If I use the property tdf.Fields(fld).Name, I get a "Type Mismatch"

error,
>though using the name of the field (as opposed to a variable for the

name)
>spelled out in SQL works. So I tried indicating the Value, but even

inthe
>watch window, it says this is an <invalid operation>. It is not

critical, I
>can delete the nulls in a second query, but it is bugging me (3 hours

worth
>of irritation).
>
>What am I doing wrong?
>
>Thanks
>

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.