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

Why does it matter how the yes/no fields are displayed in the query? Surely you can apply formatting in the form or report.

When I want to customise the sort order, I usually use a calculated field in the query to produce a number that I can sort on. This would be much simpler than using your union query.

Why does it matter how the yes/no fields are displayed in the query? Surely you can apply formatting in the form or report.

When I want to customise the sort order, I usually use a calculated field in the query to produce a number that I can sort on. This would be much simpler than using your union query.
well, im using the union query as the row source of a list box. how would I go about formatting them in this case? lets say I want to reformat 0 to No and -1 to Yes.

I have used a number feild already to sort the order of the two groups of rows in the query, but the sorting order within each group needs to be based on the actual feild values. Did I miss something here?

if you have a way this can be done could you pls be more specific so I understand what you mean.

thanks