Make Table Help

[Guest]

Dear All

I am trying to create a Make Table query that will create a True/False
datatype field in the underlying table that is created. The user creates the
table and views the contents in a form, so I don't want them to alter the
table design to use it.

The code I use at the moment is:

SELECT DISTINCT
Query1.Gross, Query1.MaxBookees, Query1.SalesInvoices.ID, "" AS Contact INTO
tblDailyNonAttendees IN 'K:\Attendees.mdb'
FROM Query1 LEFT JOIN qryAccessNLSubscriptionsL2b ON Query1.MemberID =
Query2.MemberID;

If I declare the field as: "" AS Contact, how can I ensure that when the
user sees the Contact field on the form what they see is the square box field
data type that they can tick. Is it only acheivable through DAO?

Alastair MacFarlane

 

[Guest]

Try decalaring it as False instead of ""

.... Query1.SalesInvoices.ID, False AS Contact INTO ....

 

[Guest]

Thanks for the reply Denis.

I tried that but unfortunately that makes the field an integer data type
rather that a TRUE/False datatype.

Any other thoughts. Thanks again and I appreciate your comments.

Alastair MacFarlane

 

[Guest]

I've not been able to find a way to force it to define itself as a checkbox.
Is it possible for you to change your procedure to a Delete query and then an
Append query so that the table fields are already defined ?

 

[Guest]

Dennis,

It's a good workaround and one that I should have thought off. The purist in
me thinks that there must be a way to do it the other way as well.

Thanks again,

Alastair

 

[Guest]

Dennis,

I've struck another problem with this. If I create the table with the
original query and then change the data type to TRUE/FALSE and then append
the data to an empty table I get a violations error and none of the records
are inserted. The TRUE/FALSE field has its Required set to No. It makes no
difference whether I add or don't add the field to the grid of the Append
query or whether I set the default value to FALSE for the append, I still get
the violations error.

If I remove the TRUE/FALSE field from the table and run the append it works
fine. There is obviously a difference between Contact: FALSE and the field
data type being TRUE/FALSE. The append query seems to read the data type as
an integer and says that this cannot be appended into a TRUE/FALSE field.

However this seems to be further confused because it converts the field from
an integer (with all values set at 0) to a TRUE/FALSE field with little
difficulty.

I am confused!

Alastair

 

[Guest]

I've just tested on a test table and it works fine with Contact set to True
or False
Can't really help you any more.

 

[Brendan Reynolds]

Re changing a field type to Boolean (Yes/No) see replies by myself and Allen
Browne in your other thread. Re getting the field to display as a check
box - you can do that via DAO ...

CurrentDb.TableDefs("tblTest").Fields("TestInt").Properties("DisplayControl")
= 106

 

[Chris2]

"Alastair MacFarlane" <[email protected]>
wrote in message

Dear All

I am trying to create a Make Table query that will create a True/False
datatype field in the underlying table that is created. The user creates the
table and views the contents in a form, so I don't want them to alter the
table design to use it.

The code I use at the moment is:

SELECT DISTINCT
Query1.Gross, Query1.MaxBookees, Query1.SalesInvoices.ID, "" AS Contact INTO
tblDailyNonAttendees IN 'K:\Attendees.mdb'
FROM Query1 LEFT JOIN qryAccessNLSubscriptionsL2b ON Query1.MemberID =
Query2.MemberID;

If I declare the field as: "" AS Contact, how can I ensure that when the
user sees the Contact field on the form what they see is the square box field
data type that they can tick. Is it only acheivable through DAO?

Alastair MacFarlane

Alastair MacFarlane,

Try adding an all-No Yes/No column to one of the base tables the
queries on the FROM clause above runs against, and then naming that
column in the SELECT clause.

SELECT...INTO uses the datatype of the column in the base table in
order to set the datatype of the new columns.

For columns that are created in the query itself, Access decides on
its own what the output column datatype is (I can't locate
documentation on this, but I only searched for 10 minutes).

(I seem to vaguely recall this question coming up before on the
newsgroup. There did not appear to be a way around the problem short
of having a correct datatype column in a source table.)


Sincerely,

Chris O.