Criteria question for a query

[clueless1376]

I am using a form with 2 text boxes (to and from date) and 3 combo boxes to put information in a query that is used to filter a report.

I have two questions.

I am trying to make the to and from date display all dates if there is nothing placed in them. I have been using the below format for 2 of my combo boxes. It deals with the Null values in them.

Like IIf([forms]![frmSelection]![cboPerson] Is Null,"*",[forms]![frmSelection]![cboPerson] & "*")

I was wondering if it is possible to write the criteria in the date box to handle the null in the same way.

My second question is dealing with a chkbox field.

The way I was trying to work this in the form was to create a combo box that has completed and not completed in the list. If they pick completed the filter will only return the records with the check box = True. If it's Not completed it will only return the once with check box = False, and if it's null then it will return all records.

Any help is appreciated.

Clueless1376

 

[GregGood]

Regarding the question you have with your start and end dates; I create a field in the query called UseThisRec. I describe this field as follows: iif([StrtDt] => [ComboStrtDt] and [EndDt] =< [ComboEndDt],yes,iif(ComboStrtDt] is null and [ComboEndDt] is null,yes,no))

Then, in the criteria for this field, I enter Yes.

I hope this helps.

 

[lupins46]

First, you don't need the iif():

Like forms![frmSelection]![cboPerson] & "*"

will be sufficient.

For a date comparison you use something like:

>forms!frmselection!txtmydate OR forms!frmselection!txtmydate is null

This will show dates greater than the input value or all dates if empty

 

[willir]

clueless1376

I find when the variations for the query get complicated, it is easier to take advantage os Microsoft's Visual Basic for Access VBA.

One way to approach this is to have a "Find" button the end user clicks on after they have finished entering their parameters.

The code goes through each field, and then takes appropriate action...

Dim dtEnter As Date, dtStart As Date, dtEnd As Date
Dim strMsg As String, strWhere As String

If IsDate(Me.set_date) Then
    dtEnter = Me.set_date
    dtStart = DateAdd("m", -1, dtEnter)
    dtEnd = DateAdd("m", 1, dtEnter)[COLOR=blue]
    strWhere = "read_date between #" & dtStart & "# and #" & dtEnd & "#"[/color]

' more code...

        If Me.calc_toggle Then
            strFrom = "from (meter as m inner join " & Me.tmpTable & " as r on m.meter_id = r.meter_id)"
            strFrom = strFrom & " inner join get_var as g on m.meter_type = g.get_var_id "
        Else
            strFrom = "from reading as r, meter as m, get_var as g "
            strFrom = "from (meter as m inner join reading as r on m.meter_id = r.meter_id)"
            strFrom = strFrom & " inner join get_var as g on m.meter_type = g.get_var_id "
        End If
        [COLOR=blue]
        strWhere1 = "where " & strWhere2
        strSQL1 = strSelect & strFrom & strWhere1 & strOrderBy[/color]

This code is for a meter reading program, but as you can hopefully see, the SQL statement is parsed on various conditions. For example, a BETWEEN date1 and date2. Finally, at the end, the various parts of the SQL statement are combined to form the final SELECT statement.

Richard