How to put Check marks in query (1 Viewer)

[ellenr]

I have a query in which a field is -1 or 0. Is there a way to display a check mark for the -1? This query is constructed to export as an Excel file. The original field is defined as "number" and not yes/no. Thanks for your help.

 

[Eugene-LS]

Is there a way to display a check mark for the -1?

Ye-ee-e-s-ss!

 

[ellenr]

How?

 

[MajP]

@Eugene-LS. WWW---TTT---FF?

@ellenr. Not sure how this will help in your export.

Public Sub ChangeDisplayControl(QueryName As String, FieldName As String)
  Dim qdf As QueryDef
  Dim db As DAO.Database
  Dim newProp As Property
  Dim fld As DAO.Field
  Const conPropNotFound As Integer = 3270

  On Error GoTo Proc_Err
  Set db = CurrentDb
  Set qdf = db.QueryDefs(QueryName)
  Set fld = qdf.Fields(FieldName)
  fld.Properties("DisplayControl").Value = acCheckBox
  GoTo Proc_Exit
Proc_Err:
    If Err = conPropNotFound Then
        Set newProp = fld.CreateProperty("DisplayControl", dbInteger, acCheckBox)
        fld.Properties.Append newProp
        fld.Properties.Refresh
    Else
        MsgBox "Some other error occured"
    End If
Proc_Exit:
  
End Sub

Public Sub Test()
  ChangeDisplayControl "Query1", "FldOne"
End Sub

 

[ellenr]

MajP, thank you. I am running out of time on this project so I may have to settle for converting the -1 to "Yes". Just not as pretty! Not sure your solution would export to Excel, but if I get a chance I will give it a try. I was hoping for an easy solution!

 

[MajP]

I am running out of time on this project so I may have to settle for converting the -1 to "Yes

???. It would take about a minute to open your database. paste the code into a standard module, change the field name and table name in sub "Test", and run the code. But no it will not export since in Excel you would have to add a control not formatting.

 

[ellenr]

Got cha! Thanks so much for the input.

 

[Pat Hartman]

The export to Excel is data, NOT formatting. You can open the spreadsheet after the TransferSpreadshet method creates it and change the data type of the column in question.

I can't offer the code but when I want to automate Excel, I use the macro recorder. I open the spreadsheet, turn on the macro recorder and try to achieve the format I want. Then I close the recorder and look at the code it generated.

 

[isladogs]

One of the options available when exporting to Excel using the wizard is to export data with formatting and layout

 

[MajP]

One of the options available when exporting to Excel using the wizard is to export data with formatting and layout

That is still not going to build a checkbox control within an Excel Cell AFAIK.

 

[isladogs]

That is still not going to build a checkbox control within an Excel Cell AFAIK.

Agreed - it was meant as a response to post #8