Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2004
    Posts
    86

    Unanswered: Make Table query - Checkboxes

    Hi i have a make table query that outputs a normalised database to text format (bringing linked info from another table) but checkboxes that are 1 on the source table are being output on the new table as -1
    anyone know why this is, and how i can fix it?
    thank you

    SQL:
    SELECT [CMS Compliance Table].ID, [CMS Compliance Table].[Policy Number], tblEmployee.EmployeeName, tblDepartment.DepartmentName, tblManager.ManagerName, tblComplianceAgents.ComplianceAgentName, [CMS Compliance Table].Date, [CMS Compliance Table].[Closing Action], [CMS Compliance Table].Nature, [CMS Compliance Table].Specific, [CMS Compliance Table].[Policy Logged Against], [CMS Compliance Table].[Complaint or Enquiry Flag], [CMS Compliance Table].[Justified or Unjustified], [CMS Compliance Table].[Holding Letter], [CMS Compliance Table].Suspended, [CMS Compliance Table].[Process at Fault], [CMS Compliance Table].Comments, [CMS Compliance Table].[Agent ID], [CMS Compliance Table].[Assosciated CMS], [CMS Compliance Table].[Discount Given], [CMS Compliance Table].[Root Cause Failed], [CMS Compliance Table].TotalRecs INTO CMSTableOutput
    FROM tblComplianceAgents INNER JOIN (tblDepartment INNER JOIN (tblEmployee INNER JOIN (tblManager INNER JOIN [CMS Compliance Table] ON tblManager.ManagerID = [CMS Compliance Table].TeamManager) ON tblEmployee.EmployeeID = [CMS Compliance Table].AgentName) ON tblDepartment.DepartmentID = [CMS Compliance Table].Department) ON tblComplianceAgents.ComplianceAgentID = [CMS Compliance Table].ComplianceAgent
    WITH OWNERACCESS OPTION;

  2. #2
    Join Date
    Mar 2004
    Location
    belgium
    Posts
    290
    is the checkbox as checkbox placed in new table, or is the value of the checkbox placed in the new table.
    if the first happens, the the checkbox(field) is set to default value.

  3. #3
    Join Date
    Oct 2004
    Posts
    86
    no the value -1 apears in the new table if clicked and 0 if not clicked

    i would want 1 if clicked and 0 if not clicked
    or if possible i would like the checkbox as a "checkbox" in the new table

    thanks

  4. #4
    Join Date
    Mar 2004
    Location
    belgium
    Posts
    290
    i think it is not possible, because -1 is the vb-equivalant for TRUE, and +1 is not.

  5. #5
    Join Date
    Sep 2004
    Location
    Reston, VA
    Posts
    86
    Actually, I believe Access considers any non-zero value as True, while 0 is always False. So -1 or +1 would represent True. If you want it converted to 1 and still retain a checkbox, you may need to use VBA to convert the value:

    variable = checkbox_name * -1


    But if you want to populate a number field from a checkbox where -1 is converted to 1, then for every checkbox field in the query, use this:

    field_name = name of checkbox field

    IIf([table_name].[field_name]=Yes,1,0)
    Last edited by BummerJeff; 10-28-04 at 15:21.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •