True, "Wahr" and -1 / 0

[asearle]

Hi Everyone,

I am using VBA to build an INSERT statement which I then execute and which works fine under English ...

INSERT INTO tbl_Registration (MyTrueFalseField)
VALUES (" & Me.TrueFalseField & ")"

The SQL-String writes True into the table.

But if I run this under German MS-Access, it tries to write Wahr , the German word for True and so falls over.

I would therefore like to get the system to write -1 or 0 instead of True/False and so am wondering how I can easily do this.

Of course I can use an Iif or if-clause to flip this manually but that seems very clunky.

I've googled and searched on the format() function but cannot find anything on True/False. My hope here would be to find syntax to easily switch the values True/False to -1 or 0.

Many thanks on any tips that you can give me.



Regards,
Alan

[NoellaG]

Hi,

I suppose the TrueFalseField on the form is an unbound control, so the application reads it as a textfield. You could try to use the conversion function CBool.

like
strSQL = "INSERT INTO tbl_Registration (MyTrueFalseField) VALUES (" & CBool(Me.TrueFalseField) & ")"

If that doesn't work you can try to force the value to -1/0 by the IIf function:
Iif(Me.TrueFalseField = 'Wahr',-1,0)

[asearle]

Many thanks for this tip and, yes, I gave CBool a try and it worked fine under English returning the value True. But under German it returned the value Wahr and fell over again.

I have tried experimenting with formatting (e.g. format(Me.MyTrueFalseField, "#") ) but these experiments didn't bear fruit.

So, yes, it looks like I will have to resort to an if or an iif clause which is a bit irritating because, if at any point the system is used under another language version (e.g. Spanish), then the problem will reoccur with whatever word is Spanish for True.

I'll continue investigating and will give feedback if I find a solution.

But many thanks for the tips.

Regards,
Alan

[NoellaG]

Hi,

maybe you can try CInt to force it to a number format?

grNG

[asearle]

Hi Noella,

Fantastic, thanks. It worked fine.

But I'm a bit ashamed that I didn't think of that :-/

Many thanks for your lateral thinking :-)

Regards,
Alan

[weekend00]

The value save in the Yes/No field in the table is only 0 or -1, there is no "YES", "NO", "TRUE", "FALSE" or "Warh" save in the table, you see these words because 0 or -1 were presented as these words.