Yes/No Feilds Display In Numeric Format When In Union Query

Hi all,

I have looked everywhere for a solution to this and found none. Its an annoying issue which I would like to get around.

Here is what happens.

I have a union query that gathers rows with different criteria together. The query works execept that the yes/no feild which is set to display as "Yes" or "No" is displayes as 0 for No and -1 for Yes.

I understand that the numeric values are the underlying values of yes/no feilds in access, however they are displayed as yes/no in all my queries and reports execept my union query.

I will include the union query here in case.

The following code is a function that I use to build the union query on the fly based on some parameters. If you are asking why I am doing it like this is becasue I need the rows that have a value for the feild schFeilds(0) to be on top of the final results followed by the rows that don't have any values for the above mentioned feild, and I want them sorted. I found this method to be the only one that gives me the result in the order I need.

' ----------------------------------------------------------

Public Function makeMainPanelQry()
On Error GoTo Err_makeMainPanelQry

Dim db As Database
Dim qd_window As QueryDef
Dim qd_nowindow As QueryDef
Dim strSQL As String, sql_window As String, sql_nowindow As String


Set db = CurrentDb
Set qd_window = db.QueryDefs("Q1")
Set qd_nowindow = db.QueryDefs("Q2")

strStatus = cmbStatus.Value
strReboot = cmbARStatus.Value

strFilter = makeHostFilter(strStatus, strReboot)

schFeilds = Split(getRSchechuleFeild(), ",")

sql_window = "SELECT " & pbl_ReleaseTable & ".Hostname, " & pbl_ReleaseTable & ".Status, " & pbl_ReleaseTable & ".Excluded, " & _
pbl_ReleaseTable & ".AutoReboot as Auto, " & HEAT_PROFILE_NODECOM & "." & schFeilds(0) & ", " & _
HEAT_PROFILE_NODECOM & "." & schFeilds(1) & _
", Technician.FirstName as Assigned, 1 as Priority FROM (" & pbl_ReleaseTable & " LEFT JOIN " & HEAT_PROFILE_NODECOM & " ON " & _
pbl_ReleaseTable & ".Hostname = " & HEAT_PROFILE_NODECOM & ".DeviceName) " & _
"LEFT JOIN Technician ON " & pbl_ReleaseTable & ".TechID = Technician.TechID " & _
"WHERE ((" & pbl_ReleaseTable & ".Excluded) = False) " & _
strFilter & "and " & HEAT_PROFILE_NODECOM & "." & schFeilds(0) & " Not Like """" "
'" ORDER BY " & HEAT_PROFILE_NODECOM & "." & schFeilds(0) & ", " & pbl_ReleaseTable & ".Status"

qd_window.sql = sql_window

sql_nowindow = "SELECT " & pbl_ReleaseTable & ".Hostname, " & pbl_ReleaseTable & ".Status, " & pbl_ReleaseTable & ".Excluded, " & _
pbl_ReleaseTable & ".AutoReboot as Auto, " & HEAT_PROFILE_NODECOM & "." & schFeilds(0) & ", " & _
HEAT_PROFILE_NODECOM & "." & schFeilds(1) & _
", Technician.FirstName as Assigned, 2 as Priority FROM (" & pbl_ReleaseTable & " LEFT JOIN " & HEAT_PROFILE_NODECOM & " ON " & _
pbl_ReleaseTable & ".Hostname = " & HEAT_PROFILE_NODECOM & ".DeviceName) " & _
"LEFT JOIN Technician ON " & pbl_ReleaseTable & ".TechID = Technician.TechID " & _
"WHERE ((" & pbl_ReleaseTable & ".Excluded) = False)" & _
strFilter & "and " & HEAT_PROFILE_NODECOM & "." & schFeilds(0) & " Like """" or " & _
HEAT_PROFILE_NODECOM & "." & schFeilds(0) & " is NULL"
'" ORDER BY Priority, " & HEAT_PROFILE_NODECOM & "." & schFeilds(0) & _
'", " & HEAT_PROFILE_NODECOM & ".Status;"

qd_nowindow.sql = sql_nowindow


strSQL = "SELECT * FROM Q1 " & _
"UNION ALL SELECT * FROM Q2 " & _
"order by Priority, " & schFeilds(0)


Set qd_window = Nothing
Set qd_nowindow = Nothing
Set db = Nothing

makeMainPanelQry = strSQL

Exit_makeMainPanelQry:
Exit Function

Err_makeMainPanelQry:
MsgBox Err.Description
Resume Exit_makeMainPanelQry

End Function

' ----------------------------------------------------------

I woudl really appreciate any assitance as its my last hope for a solution.

Thanks and regards
Sep