CODEWITHSUNDEEP
excelvbams-access
user2497624
user2497624

Reputation: 159

Convert strings to booleans

I need to import data from excel to access. The importation is now "working" for the obvious types (string, integers). However, In the excel file i have some strings that i need to convert to boolean in my access tables. The strings can take only 2 values "oui" or "non" (yes or no in french). In my vba code i have the following line:

cSQL = "INSERT INTO " & strTable & " ( [N_CLIENT], [NOM_CLI], ) VALUES (" & Chr(34) & .Range("A" & i) & Chr(34) & "," & Chr(34) & .Range("F" & i) & Chr(34) & ");"
        
                DoCmd.RunSQL cSQL

I would liketo know if i can use an if condition to check the value itself inside the cSQL call and replace it with either true or false. like what follows.

cSQL = "INSERT INTO " & strTable & " ( [N_CLIENT], [NOM_CLI], ) VALUES (If(" & Chr(34) & .Range("A" & i) & Chr(34) & " = "oui") then "true" else then "false"," & Chr(34) & .Range("F" & i) & Chr(34) & ");"
        
                DoCmd.RunSQL cSQL

Upvotes: 0

Views: 701

Answers (4)

Elias
Elias

Reputation: 2632

You could also use the IIF statement in SQL to check the value:

http://msdn.microsoft.com/en-us/library/hh213574.aspx

Upvotes: 1

Andy G
Andy G

Reputation: 19367

It is easier to use apostrophes rather than Chr(34), and splitting the statement across lines using the line-continuation character '_' helps to read the statement. The IIf() function is also used in the following.

Also note that you have an extra comma after the term [NOM_CLI] which shouldn't be there.

    cSQL = "INSERT INTO " & strTable & "( [N_CLIENT], [NOM_CLI] ) VALUES ('" _
    & IIf(StrComp(.Range("A" & i), "oui", 1) = 0, "true", "false") _
    & "','" & .Range("F" & i) & "');"

This results in a string like this:

INSERT INTO ( [N_CLIENT], [NOM_CLI] ) VALUES ('true','hello');

To use the Boolean values (0, -1) change to:

    cSQL = "INSERT INTO " & strTable & "( [N_CLIENT], [NOM_CLI] ) VALUES (" _
    & IIf(StrComp(.Range("A" & i), "oui", 1) = 0, -1, 0) _
    & ",'" & .Range("F" & i) & "');"

which yields

INSERT INTO ( [N_CLIENT], [NOM_CLI] ) VALUES (-1,'hello');

Upvotes: 0

majjam
majjam

Reputation: 1326

It looks like you want the Access SQL to evaluate your oui / non into true and false. You could do this using the iif statement http://www.techonthenet.com/access/functions/advanced/iif.php. However, why bother? You could just evaluate the range in vba and pass through the boolean variable. Using something like this:

dim result as boolean
if sheet1.Range("A" & i) = "oui" then
    result = true
else
    result = false
end if

Then just insert that into your SQL:

cSQL = "INSERT INTO " & strTable & " ( [N_CLIENT], [NOM_CLI], ) VALUES (If(" & Chr(34) & result & Chr(34) & , & Chr(34) & .Range("F" & i) & Chr(34) & ");"

Upvotes: 1

AKDADEVIL
AKDADEVIL

Reputation: 206

You can use VBA-Functions in Access-SQL-Queries: 

"[...] VALUES(strcomp('" & Range("A" & i) & "','oui', 1)=0 [...]"

Regards,

AKDA

Upvotes: 1

Related Questions