Cast 0 to bit value?

[Cassiopeia]

Hi everybody,

I have this problem: I am using MS Access as a frontend for entering and managing data. The data is stored in a SQL db. In the SQL db i got two separate customer tables which I try to union in a query (view). The problem is that tblCustomer1 has a field called Standard which is of type bit (true/false) and the second table, tblCustomer2 has no such field. What I have done is making a view of tblCustomer2 and there I have added the field like

SELECT .......,CAST(0 AS bit) AS Standard,....
FROM tblCustomer2

Then I try to union this view with tblCustomer1.It works fine within SQL-server but when I link the view to access I get an error message that says:

ODBC-call failed,
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification(#0)

And then it opens the table showing no records at all, only "#Name?" in every field.

The question is: How do I cast this fake-field Standard in the view that includes tblCustomer2 so that MS Access accepts it? Every record should be of type false.

Thanks,

Cassio

 

[osjohnm]

Hi

Try the following in your view:

select col1, col2, col3, Standard
from Customers1
union
select col1, col2, col3, 'false'
from customers2

or maybe leave off the quotes eg: 'false' = false
Don't use bit datatype so not to sure if you need the quotes
or not.

You shouldn't have to use a CAST function. When you do a union you must have the same amount of columns in both SELECTS of the union. If you select 7 cols in from one table then you must select 7 from the other table even if the second table only has 3 cols.
I put in false since you said all records are false but since all that the sql wants is a "dummy" column you can return 'null', 0 or anything you want really.

Hope that helps

John

 

[Cassiopeia]

Thank you, that did the ting. Actually it worked with (if anybody later would be interested):

select col1, col2, col3, Standard
from Customers1
union
select col1, col2, col3, NULL
from customers2

/Cassio