Yes/No Question

[Guest]

I have set up a union query that displays a yes/no field. On the original
table the results show as a yes or a no, but in the query they show as a -1
or a 0. The best i have come up with is:

& Format ([UnitPass - Yes/No?],("Yes/No"))

but this shows as -1Yes and 0No.

In the same query there is a field that in the original table has a format
of "CD23-"# so CD23 goes infront of the number. This also messes up when
displayed in the query and just shows the number. I have tried:

& Format ([CD23TestNo],("&CD23-&"))

but this does the same: 1CD23-1, 2CD23-2 and so on.

Any help much appreciated.

Thanks

 

[John Vinson]

I have set up a union query that displays a yes/no field. On the original
table the results show as a yes or a no, but in the query they show as a -1
or a 0.

Exactly. That's what a yes/no field IS: Yes is stored as an integer
-1, No as an integer 0.

The best i have come up with is:

& Format ([UnitPass - Yes/No?],("Yes/No"))

but this shows as -1Yes and 0No.

In the same query there is a field that in the original table has a format
of "CD23-"# so CD23 goes infront of the number. This also messes up when
displayed in the query and just shows the number. I have tried:

& Format ([CD23TestNo],("&CD23-&"))

but this does the same: 1CD23-1, 2CD23-2 and so on.

Solution? Don't use the query datasheet for display purposes. That's
not what it's for! Create a Form (for onscreen display) or a Report
(for printing) based on the Query; set the Format property of the form
or report control to display the -1 as Yes and the other field as you
want to see it.

If you really want to use the union query datasheet, simply put the
Format() function call into the query as a calculated field. Don't
append it to the existing field, use it INSTEAD OF the calculated
field.

John W. Vinson[MVP]