Function

[gblack]

Is there any function that can convert either a True, False, "Yes", "No" or Null value.... to a yes/no (check box) data type?

What I want to do is create a make table query. I would like to add a checkbox field from the query. So if there was a function that could convert a data type to a Yes/No checkbox, this would be very helpful...

To give you and idea of what I'm talking about...and how I want to use this... If I am making a table from a query and I want to add a number field, but leave it empty for the time being, what I can do is make a new field in the query maybe call it "Number1". What I would do is Add this as a field in my make table query:

Number1: Cint("")


This will add a blank field that is formatted as a number field when I run the make table query.

So using the same logic, I'd like to make a blank (or unchecked) Yes/No check box field when I run my make table query.

Anyway...that's what I'm hoping for.... I hope someone can help!

Gary

 

[WayneRyan]

Gary,

How about an IIf?

Number1: IIf(SomeBoolean, 1, 0)

If True = 1
If False/Null = 0

Wayne

 

[gblack]

I don't think that's gonna work for me

First off I really just want a yes/no place holder for later use. I don't actually have a value for the table right off the bat.

If you use the IIF(Boolean, 1, 0), then Access will hold those values as Integers... thus making the field a number field.

Its funny because I can make the field a Date field by using the CDate() funtion.... Or a string, by using CStr()... Or and Integer by using CInt()...

Maybe CBool... hmm... lemme try that...

 

[gblack]

ratz

...that just made it a number field as well... I find it amazing that Access doesn't have a function to do this... UG!

 

[The_Doc_Man]

Tech-check:

There IS no such beast as you request.

The Yes/No field is a variant interpretation of a BYTE field in its simplest form. When you look at the field as a byte, you see -1 (TRUE) or 0 (FALSE). Just like the SINGLE and DOUBLE are variant interpretations of 32-bit or 64-bit quantities. Just like Currency is a scaled integer interpretation of a 64-bit quantity. Just like Date is a variant interpretation of a 64-bit (DOUBLE) quantity.

The ONLY true YES/NO field for which you can say such a thing is a PACKED field of length 1 bit, and Access don't play that game.

So I think you are asking for something and forgetting what you are saying when you ask for it.

 

[gblack]

Ok

But the issue is this.

When you make a table in Access (using the GUI design view). You must name each field and then choose a corresponding data type.

Somehow Access differentiates between a number field, a text field, a date field, an auto number field, and a few more… one of which being a yes/no field.

However, when you make a table using a make-table-action-query, Access automatically assigns the type of field based upon the data type within each field in the query.

So if you have a “yes” it will assign the field to be a text field. If you have a 0, 1 or -1… it will automatically assign the field as a number. If you have “7/7/2005” it will assign it to be a text field unless you use the CDate() function, then it will assign the field to be a date field.

I often use make table queries as a preliminary temp table and add data to it as I go along. I often need certain fields from an established table or query, but I often will need an extra (meaning one not bound to the query or table that the make table query is bound to) date field, or a text field, or number field… These three are not a problem because I can always use the CDate, CStr, and Cint or Cdbl functions to force access to make the table setting these data types in my “extra” fields… However… I can’t seem to tell access to create my table with a yes/no field…

I could hold data as True & False or “yes” & “no”…but this will not help me if at a later point I want to use that data in a form at a later point as a check box…or even a yes/no field as it would have been formatted in a table that I manually created.

If I make an extra field in a make table query, I can set the field up to be a number field, or a Text Field or a Date field… but I can’t seem to find a way to set it up to be an Auto Number field… or a yes/no field, like you can do manually if you design the table by hand…

My question really is: is there a way to use these features in access other than via the GUI?

Thanks,
Gary

 

[Exodus]

If you don't have any data right now the whats the problem just go into the table after it is created and add the field yeah it's through the GUI but at least you are not adding all the fields that way.

 

[The_Doc_Man]

My question really is: is there a way to use these features in access other than via the GUI?

Probably not. First, Access is DESIGNED to be a GUI-oriented product. So you are asking already to take a different path than the primary design of the product.

Then, you have to realize that a Yes/No field, being an ALTERNATE INTERPRETATION of a BYTE field (as opposed to a primary one), is not going to be the Make-Table routine's first choice. Its first choice will be text or numeric field types.

Does this mean Access is STUPID? Well.... DUH! Of COURSE it is! It's a Bill Gates product, after all.... And it was designed as a SMALL SYSTEM database management tool. Small... as in "take shortcuts to keep it simple and easy to use."

But that shouldn't stop you. Access doesn't automatically KNOW that you want to display a thing as a Yes/No field - because the possible alternate interpretations take the form of other data types like text, 0/1, etc. Just as a date field using the slash notation could be text for display purposes. Dates are just another alternate notation issue. Access has to be telepathic to know you meant an alternate interpretation to apply. Or.... you COULD just tell it.

So the solution is if you have a table you want to create with specific interpretations (autonumber & yes/no are two that you mentioned), don't trust Access to read your mind.

For what it is worth, you can't do this Yes/No type of field in many other data base types, either. On the old ShareBase servers, you couldn't. Unless it has recently been changed, ORACLE stores Booleans as BYTES and lets the calling program decide what it means. Ditto MySQL and SQL Server. But then again, even the PROGRAMMING LANGUAGES do that. If you don't use a PACK or PACKED keyword/pragma (depending on the language you chose), the languages such as FORTRAN, Ada, BASIC, Pascal, C++, etc. all give you a BYTE integer for each Boolean entity. It is up to you to interpret it correctly.

Follow that logic, if you will. Surely Access is written in one or more of the languages I named. If the underlying language wants a BYTE for a Boolean, did you really think you would get something else in Access itself? The guys who wrote Access were under deadlines, too. They took easy ways to get jobs done. They allowed the source language to dictate the format for any alternate interpretations. I don't know that because of having been a part of the Access developer team; it is a matter of human nature and common developer practice on every team of which I have been a member.

Also, consider this: You are putting the cart before the horse here, BIG-time. A check-box is NOT a table concept. It is a FORM/REPORT concept. You need the table to exist FIRST in order to build the FORM/REPORT. Because it is in the FORM/REPORT that a checkbox exists. Even in a data table, Access shows a Yes/No field as True or False, not as a checkbox. It is the visual formatting style you choose for the field that determines whether it is a button-box, check-box, or an animated flaming letter T or F on your form. The same field would drive all three possibilities - and quite a few others, for that matter.

So my advice is to select your battles more carefully by realizing when you are asking for the unreasonable. And in this case, I think you are. One man's opinion and not intended to be demeaning, but this is not a productive direction for you to pursue.

 

[gblack]

Ah HA!!!

OK… I’ve been fretting over this problem for a while… and I know that there probably isn’t a yes/no function for the above issue that I posted about…however… I have come up with a work around solution for anyone trying to do what I am doing with make table queries.

Here’s the trick… You make a dummy table with one record using the GUI. You make a field in it that has the Access “data type” Yes/No.

Then when you create your Make Table Action Query; choose the table (or query) you wish to use, in order to make your new table, as well as your dummy table.

You don’t need to link them since the dummy table has only one record. You drag all the necessary fields from your main table or query and then drag the Yes/No field down from your dummy table…run your make table query and voila … Access will now make a table with all the necessary fields plus an extra Yes/No field… (i.e. a field that Access understands as Yes/No)

I am now attempting to do the same with the Auto Number “Data Type”… If I can get that to work… my life will be beautiful (thus far I am running into the linking issue... but I think I might be able to make it work)!!!

HTH
Gary

 

[gblack]

Exodus

Because the idea behind what I am doing is to make this an automated process... if every time I run this process I haveto go in and manually add a Yes/No field... well... that would defeat the purpose of automation... right?

Anyway... no worries though, because I solved my problem with the aforementioned solution

Happy Me! LOL