Display Yes/No instead of -1, 0 on Search Forms

  • Thread starter Thread starter carriey
  • Start date Start date
C

carriey

I have some checkboxes set up in my tables and data entry forms however, when
searching in my search forms, I would like the user to be able to select
either yes or no from the drop down and have the results also display yes or
no. In both places I can only seem to get -1, 0 to show up.

I am sure this is super easy but I'm totally stuck!

In this instance the field that I want to show as yes/no is Rescinded
(cbo_rescinded for the search). Here's the search code - not sure if it will
help:

StrSQL = "SELECT Subtbl_Obligations_MAIN.Oblig_ID AS [Oblig ID],
Subtbl_Obligations_MAIN.Oblig_Status AS Status,
Subtbl_Obligations_MAIN.Oblig_Date AS [Date],
Subtbl_Obligations_MAIN.Govt_Agency AS Govt,
Subtbl_Obligations_MAIN.Obligation_Type AS Type,
Tbl_Oblig_Subtypes.Obligation_Subtype AS Subtype,
Tbl_Oblig_Subtypes2.Oblig_Subtype2 AS [Oblig Detail],
Subtbl_Obligations_MAIN.Cost_Type AS [Cost Type],
Subtbl_Obligations_MAIN.Rescinded, Subtbl_Obligations_MAIN.Locations AS [No
Of Locations]" _
& " FROM (Tbl_Oblig_Subtypes2 RIGHT JOIN Subtbl_Obligations_MAIN ON
Tbl_Oblig_Subtypes2.ObligSubId = Subtbl_Obligations_MAIN.Oblig_Subtype2) LEFT
JOIN Tbl_Oblig_Subtypes ON Subtbl_Obligations_MAIN.Obligation_Subtype =
Tbl_Oblig_Subtypes.ObligTypeId" _

strWhere = "WHERE"

strOrder = "ORDER BY Subtbl_Obligations_MAIN.[Oblig_ID];"

'Set the WHERE clause for the Listbox RowSource if information has been
entered into a field on the form

If Not IsNull(Me.Oblig_ID) Then '<--If the textbox Oblig_ID contains no
data THEN do nothing
strWhere = strWhere & " (Subtbl_Obligations_MAIN.[Oblig_ID]) Like '*" &
Me.Oblig_ID & "*' AND" '<--otherwise apply the LIKE statement to the QueryDef
End If

If Not IsNull(Me.cbo_status) Then '<--If the combo Oblig_Status contains
no data THEN do nothing
strWhere = strWhere & " (Subtbl_Obligations_MAIN.[Oblig_Status]) Like
'*" & Me.cbo_status & "*' AND" '<--otherwise apply the LIKE statement to the
QueryDef
End If

If Not IsNull(Me.cbo_govt) Then '<--If the combo Oblig_Status contains
no data THEN do nothing
strWhere = strWhere & " (Subtbl_Obligations_MAIN.[Govt_Agency]) Like '*"
& Me.cbo_govt & "*' AND" '<--otherwise apply the LIKE statement to the
QueryDef
End If

If Not IsNull(Me.cbo_rescinded) Then '<--If the combo Oblig_Status
contains no data THEN do nothing
strWhere = strWhere & " (Subtbl_Obligations_MAIN.[Rescinded]) Like '*" &
Me.cbo_rescinded & "*' AND" '<--otherwise apply the LIKE statement to the
QueryDef
End If

Thanks in advance!
 
To get Yes/No displayed in an unbound combo box for search
purposes, use a combo box with properties like;

Row Source Type: Value List
Row Source: -1;Yes;0;No
Bound Column: 1
Column Count: 2
Column Widths: 0", .5"

To get Yes/No to display in your query, use a calculated field like;

Iif([Rescinded] = True, "Yes", "No") As Rescinded

Also, if this is a boolean field, I don't see why you would use the
Like operator in your where clause, it shoul be =;

If Not IsNull(Me.cbo_rescinded) Then '<--If the combo Oblig_Status
contains no data THEN do nothing
strWhere = strWhere & " (Subtbl_Obligations_MAIN.[Rescinded]) = " &
Me.cbo_rescinded & " AND" '<--otherwise apply the LIKE statement to the
QueryDef

--
_________

Sean Bailey


carriey said:
I have some checkboxes set up in my tables and data entry forms however, when
searching in my search forms, I would like the user to be able to select
either yes or no from the drop down and have the results also display yes or
no. In both places I can only seem to get -1, 0 to show up.

I am sure this is super easy but I'm totally stuck!

In this instance the field that I want to show as yes/no is Rescinded
(cbo_rescinded for the search). Here's the search code - not sure if it will
help:

StrSQL = "SELECT Subtbl_Obligations_MAIN.Oblig_ID AS [Oblig ID],
Subtbl_Obligations_MAIN.Oblig_Status AS Status,
Subtbl_Obligations_MAIN.Oblig_Date AS [Date],
Subtbl_Obligations_MAIN.Govt_Agency AS Govt,
Subtbl_Obligations_MAIN.Obligation_Type AS Type,
Tbl_Oblig_Subtypes.Obligation_Subtype AS Subtype,
Tbl_Oblig_Subtypes2.Oblig_Subtype2 AS [Oblig Detail],
Subtbl_Obligations_MAIN.Cost_Type AS [Cost Type],
Subtbl_Obligations_MAIN.Rescinded, Subtbl_Obligations_MAIN.Locations AS [No
Of Locations]" _
& " FROM (Tbl_Oblig_Subtypes2 RIGHT JOIN Subtbl_Obligations_MAIN ON
Tbl_Oblig_Subtypes2.ObligSubId = Subtbl_Obligations_MAIN.Oblig_Subtype2) LEFT
JOIN Tbl_Oblig_Subtypes ON Subtbl_Obligations_MAIN.Obligation_Subtype =
Tbl_Oblig_Subtypes.ObligTypeId" _

strWhere = "WHERE"

strOrder = "ORDER BY Subtbl_Obligations_MAIN.[Oblig_ID];"

'Set the WHERE clause for the Listbox RowSource if information has been
entered into a field on the form

If Not IsNull(Me.Oblig_ID) Then '<--If the textbox Oblig_ID contains no
data THEN do nothing
strWhere = strWhere & " (Subtbl_Obligations_MAIN.[Oblig_ID]) Like '*" &
Me.Oblig_ID & "*' AND" '<--otherwise apply the LIKE statement to the QueryDef
End If

If Not IsNull(Me.cbo_status) Then '<--If the combo Oblig_Status contains
no data THEN do nothing
strWhere = strWhere & " (Subtbl_Obligations_MAIN.[Oblig_Status]) Like
'*" & Me.cbo_status & "*' AND" '<--otherwise apply the LIKE statement to the
QueryDef
End If

If Not IsNull(Me.cbo_govt) Then '<--If the combo Oblig_Status contains
no data THEN do nothing
strWhere = strWhere & " (Subtbl_Obligations_MAIN.[Govt_Agency]) Like '*"
& Me.cbo_govt & "*' AND" '<--otherwise apply the LIKE statement to the
QueryDef
End If

If Not IsNull(Me.cbo_rescinded) Then '<--If the combo Oblig_Status
contains no data THEN do nothing
strWhere = strWhere & " (Subtbl_Obligations_MAIN.[Rescinded]) Like '*" &
Me.cbo_rescinded & "*' AND" '<--otherwise apply the LIKE statement to the
QueryDef
End If

Thanks in advance!
Click to expand...
 
Back
Top