SQL in a form

[Günter Brandstätter]

Hi all,

I have a tricky problem with an SQL-string in a form. I need to make a query
on a boolean field in a table. What I do is to open a DAO recordset with a
predefined SQL-string.
The String looks like this:
"SELECT blabla FROM table WHERE myBoolean=" & bMyBoolean
set rst = CodeDb.OpenRecordset(strSQL)
should work but it does not, because I work with a german version of
MS-Access the interpreter interprets the variable bMyBoolean as the german
word "Wahr" instead of "True".

I tried the following:

If bMyBoolean = True then
bMyBoolean = "True"
Else
bMyBoolean = "False"
End If

and used the same SQL-String to create a QueryDef and executed this query.
It works when I open the created query by hand. When I try to open a
recordset using this query in code, it does not work

My question: How should I treat my Boolean-variable to make it say "True"
and "False" even in german environment??

Any answer appreciated
Günter

 

[Brendan Reynolds]

It seems there should be a more elegant way of handling this, but if no one
else has a better solution, I believe the following should work ...

"SELECT blabla FROM table WHERE myBoolean=" & IIf(bMyBoolean, -1, 0)

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Günter Brandstätter]

Hi Brendan,
thank you for your answer, but I forgot to say that I tried all this. When I
try your solution I get the error "Type mismatch". even when I write the 0
and the -1 directly in my SQL-String.
What I should have mentioned is that I am using a linked table on an
SQL-Server.

thank you again
Günter

 

[Nieurig]

Hi Günter,
do you have tested converting the value by using

CStr(bMyBoolean) ?

Niels

 

[John Smith]

Have you tried:

"SELECT blabla FROM table WHERE myBoolean=" & Val(bMyBoolean)

Hopefully that should force it to be a number!

 

[Brendan Reynolds]

If the field in the SQL Server table is a BIT field, change -1 to 1.

If that's not it - bMyBoolean *is* declared as a Boolean variable, right?
It's not a Variant?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Günter Brandstätter]

Hi to all who answered me,

thank you for your answers, but all of that did not work. I tried different
approaches and finally solved my problem by doing the following:

First of all, the field in the SQL Server table is of type 'bit', so it can
only manage 0s and 1s.
bMyBoolean is a Variable that is passed by a function.
I dim-ed bMyBoolean to a Variant-type.
then I did the conversion
If bMyBoolean = True then
bMyBoolean = 1
Else
bMyBoolean = 0
End If

then regard my original SQL-string:
"SELECT blabla FROM table WHERE myBoolean=" & bMyBoolean
I replaced with :
"SELECT blabla FROM table WHERE myBoolean = " & bMyBoolean
mention the spaces before and after the equal-sign!!!!
Without these spaces it still did not work, but with the spaces it does.

Just in case that someone faces the same problem again
regards
Günter


Hi Günter,
do you have tested converting the value by using

CStr(bMyBoolean) ?

Niels