Make boolean (Yes/No) field with SELECT INTO sql statement

[AOB]

Hi guys,

I've been using a SELECT INTO statement to import data from a linked text file into a temporary table in Access. Something along the lines of :

SELECT [tblLink].[fld1] AS Field1,
[tblLink].[fld2] AS Field2,
[tblLink].[fld3] AS Field3
INTO [tblTemp]
FROM [tblLink]

(There's an INNER JOIN in there and some Nz / CLng functions but just want to keep it simple...)

Now - I've just realised I also need to create a couple of extra 'dummy' fields in my temporary table (for later on in the show) and I need them to be Yes/No format (will set them to False at first, then run some separate queries later to update them)

I tried this :

SELECT [tblLink].[fld1] AS Field1,
[tblLink].[fld2] AS Field2,
[tblLink].[fld3] AS Field3,
False AS Field4,
False AS Field5
INTO [tblTemp]
FROM [tblLink]

But this sets Field4 and Field5 as Number fields, with each record given a value of 0.

Can anybody advise what syntax is required in the SQL to make these fields Yes/No rather than Number?

Thanks as always!!

AOB

 

[CJ_London]

yes/no fields are numeric 0=No/False, -1=Yes/True, they just have different rules around what types of controls you use and the values required.

You could precreate the destination table which will 'force' the issue - either manually, using the SQL Create Table or Access Tabledefs

Our you can change it afterwards, either manually or using SQL Alter Table or Access Tabledefs

 

[AOB]

Thanks CJ

Yeah, I've tried this :

SELECT [tblLink].[fld1] AS Field1,
[tblLink].[fld2] AS Field2,
[tblLink].[fld3] AS Field3
INTO [tblTemp]
FROM [tblLink]

Followed by this :

ALTER TABLE [tblTemp]
ADD COLUMN Field4 YESNO, Field5 YESNO

It does appear to work when I break and check out the temporary table in design view; the new fields are there and set to Yes/No as expected.

However, in datasheet view, they still appear as numbers rather than checkboxes. I'm not overly concerned about the appearance as I'll be dropping this table again later.

However, when I then try to update these fields from another table again :

UPDATE [tblTemp]
INNER JOIN [tblMain] ON [tblTemp].[ID] = [tblMain].[ID]
SET [tblTemp].[Field4] = [tblMain].[fld4],
[tblTemp].[Field5] = [tblMain].[fld5]

I get an error :

Run-time error '3615':
Type mismatch in expression

fld4 and fld5 in tblMain are also set up as Yes/No and appear as checkboxes in design view (I don't know how significant this is...)

Any suggestions?

Thanks

AOB

 

[AOB]

CJ,

Don't waste any time on this - I foolishly assumed the mismatch was in the dummy fields. It wasn't, it was with the ID used for the JOIN (in tblMain it was a long integer but in tblTemp it was defaulting to text, even though the values themselves are actually integers)

Sorted now, thanks for your assistance!

AOB

 

[CJ_London]

The tickbox is specific to Access so you need to use tbledefs to set this.

If you are using a form (which you will be ultimately) then simply use a checkbox control to display the data.

Run-time error '3615' is what is says - a type mismatch. If both sets of fields are now yesno perhaps it is the ID fields which don't match?