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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" wrote:

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!
.

Relevant Pages

  • Refresh nested subforms
    ... Display Control: Combo Box ... Row Source Type: Table/Query ... Row Source: SELECT CountryCodes.CountryName FROM CountryCodes ORDER BY ... This field joins the tables using the destination name ...
    (microsoft.public.access.formscoding)
  • Re: =Iff(([Me]![Gender])="M","Male","Female")
    ... does it display an error? ... Out of curiousity, why are you using two text boxes? ... Row Source Type: Value List ... as the value of the Gender field. ...
    (comp.databases.ms-access)
  • Re: Dont allow select in combo box
    ... Row Source Type: Table/Query ... Display When: Always ... Tab Stop: Yes ... > The Locked property may be set to Yes. ...
    (microsoft.public.access.forms)
  • Re: Combo goes blank on form requery
    ... There's a valid case where the combo's Bound Column is not its display column, and the RowSource is filtered in such a way that it does not have the record it needs to display. ... If the combo is selected, the selection may even be the width of the characters, but the characters are not visible. ... On a simple bound continuous form I have a combo for filtering the form by customer. ...
    (comp.databases.ms-access)
  • Re: Combo Box problems
    ... the same value as another and have it display the correct item. ... isn't equal to the bound column. ... the price * Qty value and display it in this field. ...
    (microsoft.public.access.forms)