passing boolean variable value into SQL string

[rocco]

Hello
I have a class code to insert a record into a table by using SQL (a):
CurrentDb.Execute ("INSERT INTO TBLAGENDA (APPOINTMENT,FIRSTVISIT)
VALUES('" & appointment & "'," & m_firstVisit & ")")

status is a variable from one of the class properties. It is settled form
the user and it is boolean.
What is driving me crazy is that if I run the code above it wont work. I
have to change it this way (b):

Dim status As Integer
If m_firstVisit = True Then
status = -1
Else
status = 0
End If
CurrentDb.Execute ("INSERT INTO TBLAGENDA (APPOINTMENT,FIRSTVISIT)
VALUES('" & appointment & "'," & status & ")")

which is fine, not a big deal, but why it shouldn't work?
What I don't understand is that I have tested this:
CurrentDb.Execute ("INSERT INTO TBLAGENDA (APPOINTMENT,FIRSTVISIT)
VALUES('" & appointment & "',TRUE)")
and it works! And i don't see any difference from the code (a) I have wrote.
If I test m_firstVisit value with:
msgbox m_firstVisit
I get True

Thanks,
Rocco

 

[Daryl S]

Rocco -

You could try this, which forces the boolean to be sent as a number:

CurrentDb.Execute ("INSERT INTO TBLAGENDA (APPOINTMENT,FIRSTVISIT)
VALUES('" & appointment & "'," & 0 + m_firstVisit & ")")

 

[ChrisO]

If you are using an English version of Access then m_firstVisit would be
placed in the string as True. But if, for example, you were using the Dutch
version of Access then Waar would be placed in the string. SQL needs to be in
English and so Waar fails. The fix is as suggested, force the Boolean to its
numerical value.

So CInt(m_firstVisit) should also work.

To see how this works, or in this case fails, try putting the entire SQL
statement in a string and MsgBox it. You should see the word as converted by
Access. Then wrap the Boolean in the CInt function and MsgBox it. You should
see the numerical value.

An alternative is to stop declaring variables as Boolean and change them to
Integer. You can still use something like MyVar = True in code but it will
not be converted to True, or whatever, when put in a string.

 

[rocco]

Thank you very much!!
Both methods work just fine!!

Rocco