Yesno Data Type From Calculated Field in Query - Access Developers


	Back to Access Developers Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Yesno Data Type From Calculated Field in Query

Richard Scheid

14 months ago

I have a complex query (7 tables) for ordering ingredients in a restaurant management system. I would like to be able to update this query but cannot due to the complexity. The updates are only needed to create shopping lists (with no persistence requirements) for both inventoried and non-inventoried items, so using a Make Table query works fine as the table is updatable.  The query has a couple of YES/NO fields and a calculated field to sort out whether the ingredients are also inventory items and whether those inventory items are actually stocked. This involves the query of a query. This second query is the Make Table query.

The calculated field of the MT query is an IFf function with a DLookup function as the "true" expression.

Stocked: IIf([NeededIngredients_Q]![isInventoryItem]=True,DLookUp("[Active]","InventoryItem_T","[ingredientID]=" & [ingredientID]),0)

The [Active] field in the InventoryItem_T table is a YES/NO datatype.

Examining the new table, YES/NO fields in the query all end up as YES/NO data types in the table, EXCEPT for the calculated field. This field is a NUMBER data type (double), with the values of either -1 or 0. But these are not Booleans, as I can changed their values to any integer.  I thought perhaps this was because I had used a 0 in the IIf statement, so I tried converting the Stocked expression to a Boolean:

Stocked: CBool(IIf([NeededIngredients_Q]![isInventoryItem]=True,DLookUp("[Active]","InventoryItem_T","[ingredientID]=" & [ingredientID]),0))

Examining this new table, the field is still a NUMBER data type but now an integer.  Again, not Boolean, as I can change them to values other than -1 or 0.

So to be consistent with my data type usage, I would like to understand how to create a YES/NO data type from a calculated field in a query.

Donald Blackwell @Reply

14 months ago

Hi Richard,

Unfortunately, Queries created in access via the QBE Designer will always make Yes/No fields as number of type Integer in a Make Table Query.

To have a Query that actually creates a Yes/No field, you would have to write the SQL manually in SQL View and even then Access is finicky.

Richard has many videos on SQL (see below) but Make-Table will always set a boolean to an Integer. You could do 2 queries, 1 to Create Table, and then one to append records but again, to make a Create Table Query, you have to manually write it in SQL View or using VBA.

Direct SQL View
SQL Syntax
SQL with Access
Access SQL Seminars
Quickly Run SQL

To Learn about Create Table, you'd need to watch Access SQL Seminar # 3 (but if you're unfamiliar with SQL, I would recommend watching 1 and 2 first)

	Richard Scheid OP @Reply 14 months ago
There are other fields in the Make Table query that are YES/No data types, and all of these fields end up as YES/NO data types in the resulting table when the query is run. It's only the calculated field that returns the NUMBER data type. That's why I'd hoped that the CBool() function would return a YES/NO data type, but it didn't.

	Kevin Robertson @Reply 14 months ago
If you add a Check Box to your Form or Report and bind it to the calculated field it should inherit that value (0 or -1) and display the correct value for the Check Box (True or False).

Donald Blackwell @Reply

14 months ago

Richard

Sorry, I didn't say that right. The Make Table Query essentially copies the datatype from the table it gets data from. However, with a field calculated in a query, it only recognizes booleans as integers. Unfortunately, there is no way in the QBE or directly in SQL in the Query editor to have a Make Table create a checkbox field.

As Kevin said, you can have a form or report create a check box which will limit the values to True or False, or, you can edit the table after it's created and change the datatype to Yes/No. Or you could create a second query in SQL view to "Alter" the table"

ALTER TABLE tablename
ALTER COLUMN [Stocked] YESNO;

Running that will turn it into a checkbox field in the table.

Again, sorry I didn't say it right the first time.

	Richard Scheid OP @Reply 14 months ago
Thanks. I took the ALTER TABLE/ALTER COLUMN route. Works just fine.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developers.

Next Unseen

New Feature: Comment Live View