Using Form to Enter Report Criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the gist of it figured out, but I'm having trouble with one small
part. How do I handle fields without data?

My situation is this: I have built an invoice database and a report that
asks the user to enter start/end dates and to select an invoice approver. (I
have tried dropdowns based on a table and value lists) If any fields are
skipped, the report returns an error and no records. What I want is for any
of the following scenarios to occur with filtering:
1) If no start date is entered, all records up to the end date will be
returned
2) If no end date is entered, all records from start date to present will be
returned
3) If no approver is entered, all records between the given dates will be
returned

Any help is much appreciated!

-- Karen
 
karenatodd said:
I have the gist of it figured out, but I'm having trouble with one small
part. How do I handle fields without data?

My situation is this: I have built an invoice database and a report that
asks the user to enter start/end dates and to select an invoice approver. (I
have tried dropdowns based on a table and value lists) If any fields are
skipped, the report returns an error and no records. What I want is for any
of the following scenarios to occur with filtering:
1) If no start date is entered, all records up to the end date will be
returned
2) If no end date is entered, all records from start date to present will be
returned
3) If no approver is entered, all records between the given dates will be
returned
Click to expand...


Try something like this air code:

If IsNull(Me.txtstartdate) _
And IsNull(Me..txtstartdate) Then
ElseIf IsNull(Me.txtstartdate)
stCriteria = stCriteria & " AND Invdate <= " _
& Format(Me.txtenddate, "\#m\/d\/yyyy\#")
ElseIf IsNull(Me.txtenddate)
stCriteria = stCriteria & " AND Invdate >= " _
& Format(Me.txtstartdate, "\#m\/d\/yyyy\#")
Else
stCriteria = stCriteria & " AND Invdate Between " _
& Format(Me.txtstartdate, "\#m\/d\/yyyy\#") _
& " And " & Format(Me.txtenddate, "\#m\/d\/yyyy\#")
End If
If Not IsNull(Me.txtapprove) Then
stCriteria = stCriteria & " AND approver = " _
& Me.txtapprove
End If
DoCmd.OpenReport "reportname", _
WhereCondition:= stCriteria
 
Back
Top