How to convert a datatype of text to a datatype of 'yes/no'

[ncsthbell]

I am importing a file that has a text column and the value is either null or 'Y'. I am inserting them into a table that has the column defined as a 'Yes/No' datatype. So in my logic I need to convert all values of "Y" to be 'True'.
I am trying to accomplish this by using the following conditional statements as part of my insert statement:

IIf(UCase(rsTemp!new_asset_flag), "Y", 0) and I also tried, IIf(UCase(rsTemp!new_asset_flag), "Y", True)

Neither one of these will work, I get an error stating "datatype mismatch". Any suggestions would be appreciated!!
Thanks

[mshmyob]

Try

iif(UCase(rsTemp!new_asset_flag)="Y",-1,0)
 

cheers,

[NeoPa]

I would suggest :

IIf(UCase(rsTemp!new_asset_flag) = 'Y', True, False)

[mshmyob]

You say 'potato' I say 'pawtato'

cheers,

[NeoPa]

Good analogy!

'Potato's easier to read though ;-)

[mshmyob]

'Potato's easier to read though ;-)

Maybe so, but I was just helping Access from having to convert the Boolean True/False to their respective values that are actually stored in the database.

I think I increased his application speed by .0000001 of a sec (lol).

cheers,

[NeoPa]

Mshmyob:
I think I increased his application speed by .0000001 of a sec (lol).

Darn it!! I've been trumped by a a mere 100 nanoseconds :-D

[OldBirdman]

If Access has to convert boolean values to numeric to use them, why have any boolean fields or variables?
Instead of using boolean, I might use byte or integer, and then could use 0 or 1, avoiding the -1, which isn't exactly intuitive. I figured out long ago that boolean isn't a bit, but a numeric field. But with -1 as true, I figured that Access looked at the first bit only. -1=11111111B or 1111111111111111B in storage. There might be a time/hardware advantage to informing Access to work in boolean and only examine the 1st bit. True? Or Not?

[ADezii]

I'm still having problems trying to understand wht a BOOLEAN occupies 2 Bytes (16 Bits)!

[NeoPa]

Ah. This concept often confuses.

-1 is used because it is a TRUE state for all the available bits. Work is required to determine the value of an individual bit, hence it is quicker and easier to use the whole register, which in 32-bit intel compatible processors means 8-, 16- or 32-bit. It's been so long since I did assembly work, so I don't know why it would default to 16-bit values in the current processors (I would expect 32-bit, but I would guess the 16-bit selection is merely a hang-over from years gone by.), but the concept of why it's not a single bit is still the same. It's simply more work. The processor has a zero flag which is set after every instruction that is relevant, which is how it determines if a value is FALSE (0) or not.

To get around the logical problem of so many possible values in a field of that size, every value that is not zero, is considered to be equivalent to TRUE, but be very careful, as non-zero values are not equal to TRUE. If statements will always execute based on non-FALSE though, as opposed to simply TRUE (-1) and FALSE (0).

If 329 Then
    MsgBox "Not FALSE"
Else
    MsgBox "FALSE"
End If

This shows Not FALSE, but :

If 329 = True Then
    MsgBox "Not FALSE"
Else
    MsgBox "FALSE"
End If

shows FALSE.

[OldBirdman]

Thank you NeoPa for the examples using 329 to illustrate the problems that may arrise when treating a boolean value as a number. I see that when using boolean values, they should always be assigned values of True or False, and tested against the same True or False.
Knowing that Access stores a number in the field/variable just makes for ambiguity.
A parallel might be drawn between Boolean and Date/Time. Knowing how Access stores the values allows a developer to do arithmetic on the value by using it as a number. dteEnd = dteStart + 23 will do as intended, and add 23 days to the field. But dteEnd = DateAdd('d', 23, dteStart) would be better, as it does not assume knowledge of the way Access stores the value. For all I know, future versions of Access will store Date/Time as integers, in nanoseconds, so adding 23 will add 23 nanoseconds to the date.
I say 'po-tah-to', and wonder if

Not IsNull(rsTemp!new_asset_flag)

might be faster? Simplier? Better?

[ADezii]

What is with this 'po-tah-to' thing? (LOL).

[NeoPa]

OldBirdman:
I see that when using boolean values, they should always be assigned values of True or False, and tested against the same True or False.

I'm not sure how you came to these conclusions.

Always assigning True or False would restrict the power of logic in your programs. That said, to be absolutely true to the boolean concept I would have to agree with you. A bit of extra labour - but will always yield correct and consistently interpreted results.

As for only testing against True or False, this is an error that many make, which results in unexpected results. Comparing against False would always be reliable, but comparing one TRUE value with any other (EG. True) numerically is likely to give a result of False when in boolean terms the result should be True. An important point to understand though, is that a boolean value is the expected result in most cases (the cases we're discussing particularly), so comparing them with anything is entirely redundant. The fact that it is also likely to confuse and give invalid results simply adds to the reasons why a boolean result should not be further processed.