Query criteria from Combo box

[cbende2]

Hello all,

I have a problem with what I think will be a simple fix.


In one of my forms, I have an two unbound text boxes, txtStartDate and txtEndDate, and I also have an unbound combobox, cboAdminName.

I have several forms that will be referring to these unbound text boxes and this combo box.

The point of these unbound text boxes and combo box is to refine the Select query for the different forms that are calling on these unbound text boxes and combobox in their query criteria.

The problem I'm having is that if I don't select txtStartDate And txtEndDate And cboAdminName, then my query throws will return blank.

In my query, lets say MyDateField has the criteria of "Between Forms!MyForm!txtStartDate And Forms!MyForm!txtEndDate." My other field MyAdminField has the criteria "Forms!MyForms!cboAdminName."

I'd like for it to basically ignore the criteria if I don't select a StartDate, EndDate, or AdminName, instead of putting a "" in the criteria.


I'm thinking this will have a pretty simple solution.

Thanks

[lfpm062010]

This is one way to solve your issue.

Since you use between, it is easier just choose a date like "1/1/1970" for start date when is null and date like "12/31/2099" for the end date is null. I think something like this should work (not tested).

BETWEEN IIF(ISNULL(Forms!MyForm!txtStartDate), #1/1/1970#, Forms!MyForm!txtStartDate) AND
IIF(ISNULL(Forms!MyForm!txtEndDate), "12/31/2099", Forms!MyForm!txtEndDate)

Same apply for the cobmo box, except that you use like and append a "*" as part of the criteria. Again, code is not tested.

LIKE IIF(ISNULL(Forms!MyForms!cboAdminName), "", Forms!MyForms!cboAdminName) & "*"

[ranman256]

You cant use form boxes in a query if there's nothing in them..so..
Test all controls for a possible filter then build the where clause.

if not isnull(cboState) then   sWhere = sWhere & " and [state]='" & cboState & "'"
if not IsNull(txtName) then    sWhere = sWhere & " and [Name]='" & txtName & "'"
if not IsNull(chkContact) then sWhere = sWhere & " and [Contact]=" & chkContact.value

    'remove 1st And
sWhere= mid(sWhere,5)
  'just use the filter
me.filter = sWhere
me.filterOn = true
   'OR   
   'apply the sql to the form
sSql = "SELECT * FROM tblCompany WHERE " & sWhere

[cbende2]

Thanks for the replies guys, I'll test them out.