Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004

    Unanswered: ListBox Query not working fully

    I have a listbox that populated results (ID, Name) from a qry. when the user leaves the box empty it should send all the results from the query that match the criterias to the report and disply them. i get an error message "Join Expression not supported"

    ***** code******
    sSQLBegin = "SELECT"
    sSQLItems = " mytable.FacilityID, mytable.Facility_Name"
    sSQLEnd = " FROM mytable INNER JOIN yourtable ON mytable.FacilityID = yourtable.FacilityID"

    If Form_submenu_FacilitySearch.lbCov_facility.Value > 0 Then
    sSQLEnd = sSQLEnd & " WHERE mytable.FacilityID = '" & Form_submenu_FacilitySearch.lbCov_facility.Value & "'"
    If Form_submenu_FacilitySearch.lbCov_facility.Value = Null Then
    sSQLEnd = sSQLEnd & " WHERE mytable.FacilityID=""'"
    End If
    End If
    If Form_submenu_FacilitySearch.checkboxShowDueDate.Va lue = -1 Then
    sSQLItems = sSQLItems & ", yourtable.DueDate"
    sSQLEnd = sSQLEnd & " AND (((yourtable.DueDate) Between #" & globjEndBox & "# And #" & globjStartBox & "#))"

    Report_ComplianceReport.Item3.ControlSource = "DueDate"
    End If
    I know that the problem is in this part of the code somewhere.
    because the qry works Perfect if something is selected in the listbox, but not if its empty.
    I tried to add all to the top of the listbox as an option and give it a NULL value, I couldnt get that to work right.
    I'm open for suggestions

  2. #2
    Join Date
    Jun 2004
    Florida, US
    Form_submenu_FacilitySearch.lbCov_facility.Value = Null
    should be
    If IsNull(Form_submenu_FacilitySearch.lbCov_facility. Value) = true Then
    sSQLEnd = sSQLEnd & " WHERE mytable.FacilityID<>0"

    with mytable.FacilityID<>0 criteria I suppose you don't have ID number as zero which will bring all non zero ID records. I also highly recommend using IsNull() function and test null before others.

Posting Permissions

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