Creating a report from a form based on multiple criteria (1 Viewer)

Hello, what i am tryin to do is generate this report from a form for this telephone company where i input the customers phone no. AND the beginning and end dates to view his billing history ,, what i have now is that it is getting billing history for the specified dates but NOT for a specified customer phone no. here is the VB code :

Private Sub Command4_Click()
Dim strReport As String
Dim strDateField As String
Dim strWhere As String
Dim lngView As Long


Const strcJetDate = "\#mm\/dd\/yyyy\#"


strReport = "CallCostQuery"
strDateField = "[Date]"
lngView = acViewPreview


If Len(Me.Text5 & "") > 0 Then
strWhere = strWhere & " AND [CustomerPhoneno] = '" & Me.Text5 & "'"
End If


If IsDate(Me.txtStartDate) Then
strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
End If
If IsDate(Me.txtEndDate) Then
If strWhere <> vbNullString Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtEndDate + 1, strcJetDate) & ")"
End If


If CurrentProject.AllReports(strReport).IsLoaded Then
DoCmd.close acReport, strReport
End If


DoCmd.OpenReport strReport, lngView, , strWhere
exit_Handler:
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then
MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Cannot open report"
End If
Resume exit_Handler
End Sub

can anyone please help , am i doing something wrong ?

Re: Creting a report from a form based on mutliple criteria

Welcome to AWF!!

In your first two IF blocks, you do realise that if the first IF is greater than 5 strWhere is overwritten in the second IF block if it the condition is ALSO satisfied? I'm talking about these two IF blocks:

If Len(Me.Text5 & "") > 0 Then
strWhere = strWhere & " AND [CustomerPhoneno] = '" & Me.Text5 & "'"
End If

If IsDate(Me.txtStartDate) Then
strWhere = "(" & strDateField & " >= " & Format(Me.txtStartDate, strcJetDate) & ")"
End If

Also, why are you closing and opening the report?

Re: Creting a report from a form based on mutliple criteria

oh ok then i must create another varibale thanks , closing incase i regenerate the report again if the old one before is still open it closes ,,, anyways can u tell me whether the first first if is right ,, what i am trying to do here is filter the report based on what the user enters in that textbox, so is the first IF the correct code ?? thanks for your help anyways!

Re: Creting a report from a form based on mutliple criteria

You could write it like this too:

If Len$(Me.Text5 & "") <> 0 Then

You also don't need to concat strWhere in that block as well because it hasn't been assigned anything yet nor is it in a loop:

strWhere = "[CustomerPhoneno] = '" & Me.Text5 & "'"

I can see you're testing for three conditions? Is it going to be an AND condition if all three conditions are met?

Re: Creting a report from a form based on mutliple criteria

yes exactly how should i write that ??

Re: Creting a report from a form based on mutliple criteria

and btw [customerphoneno] is the field that will be looked up in the query to generate the report

I will give you the idea and see how you get on:

Initialise strWHERE at the start:
strWhere = ""

You are going to use 3 main IF blocks.

1. Test for the first condition using:
If Len$(Me.Text5 & "") <> 0 Then

2. Test for the second condition:
If IsDate(Me.txtStartDate) Then

However, inside that you will need to test whether strWhere has been assigned a value as well:
If Len$(strWhere) <> 0 then
' Concatenate strWHERE
else
' Don't concatenate StrWHERE
end if

3. Perform the same check for the EndDate and use the inner IF for strWhere check.

sorry for all the trouble but i am not that good with VB syntax so if u could please tell me how i should write that ,, sorry,, appreciate it

I believe you wrote the code you're having problems with? From what I explained you should have sufficient vba skills (from what I've seen) to execute it. Read it properly, try it, post your results and I'll have a look.