469,338 Members | 8,430 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,338 developers. It's quick & easy.

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

167 100+
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
Nov 5 '10 #1

✓ answered by mshmyob

Try

Expand|Select|Wrap|Line Numbers
  1. iif(UCase(rsTemp!new_asset_flag)="Y",-1,0)
  2.  
cheers,

12 10478
mshmyob
904 Expert 512MB
Try

Expand|Select|Wrap|Line Numbers
  1. iif(UCase(rsTemp!new_asset_flag)="Y",-1,0)
  2.  
cheers,
Nov 5 '10 #2
NeoPa
32,182 Expert Mod 16PB
I would suggest :
Expand|Select|Wrap|Line Numbers
  1. IIf(UCase(rsTemp!new_asset_flag) = 'Y', True, False)
Nov 6 '10 #3
mshmyob
904 Expert 512MB
You say 'potato' I say 'pawtato'

cheers,
Nov 6 '10 #4
NeoPa
32,182 Expert Mod 16PB
Good analogy!

'Potato's easier to read though ;-)
Nov 6 '10 #5
mshmyob
904 Expert 512MB
'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,
Nov 6 '10 #6
NeoPa
32,182 Expert Mod 16PB
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
Nov 6 '10 #7
OldBirdman
675 512MB
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?
Nov 6 '10 #8
ADezii
8,800 Expert 8TB
I'm still having problems trying to understand wht a BOOLEAN occupies 2 Bytes (16 Bits)!
Nov 6 '10 #9
NeoPa
32,182 Expert Mod 16PB
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).
Expand|Select|Wrap|Line Numbers
  1. If 329 Then
  2.     MsgBox "Not FALSE"
  3. Else
  4.     MsgBox "FALSE"
  5. End If
This shows Not FALSE, but :
Expand|Select|Wrap|Line Numbers
  1. If 329 = True Then
  2.     MsgBox "Not FALSE"
  3. Else
  4.     MsgBox "FALSE"
  5. End If
shows FALSE.
Nov 7 '10 #10
OldBirdman
675 512MB
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
Expand|Select|Wrap|Line Numbers
  1. Not IsNull(rsTemp!new_asset_flag)
might be faster? Simplier? Better?
Nov 7 '10 #11
ADezii
8,800 Expert 8TB
What is with this 'po-tah-to' thing? (LOL).
Nov 7 '10 #12
NeoPa
32,182 Expert Mod 16PB
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.
Nov 8 '10 #13

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Zack Sessions | last post: by
1 post views Thread by Fernando Flores Prior | last post: by
1 post views Thread by voidfill3d | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.