Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-10-2010, 09:33 AM   #1
Moe23
Registered User
 
Join Date: Jun 2010
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Moe23 is on a distinguished road
Creating a report from a form based on multiple criteria

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 ?

Moe23 is offline   Reply With Quote
Old 06-10-2010, 09:45 AM   #2
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
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?
vbaInet is offline   Reply With Quote
Old 06-10-2010, 09:55 AM   #3
Moe23
Registered User
 
Join Date: Jun 2010
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Moe23 is on a distinguished road
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!

Moe23 is offline   Reply With Quote
Old 06-10-2010, 10:08 AM   #4
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
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?
vbaInet is offline   Reply With Quote
Old 06-10-2010, 10:11 AM   #5
Moe23
Registered User
 
Join Date: Jun 2010
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Moe23 is on a distinguished road
Re: Creting a report from a form based on mutliple criteria

yes exactly how should i write that ??
Moe23 is offline   Reply With Quote
Old 06-10-2010, 10:14 AM   #6
Moe23
Registered User
 
Join Date: Jun 2010
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Moe23 is on a distinguished road
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
Moe23 is offline   Reply With Quote
Old 06-10-2010, 10:20 AM   #7
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: Creating a report from a form based on multiple criteria

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.

vbaInet is offline   Reply With Quote
Old 06-10-2010, 10:29 AM   #8
Moe23
Registered User
 
Join Date: Jun 2010
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Moe23 is on a distinguished road
Re: Creating a report from a form based on multiple criteria

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
Moe23 is offline   Reply With Quote
Old 06-10-2010, 10:37 AM   #9
vbaInet
AWF VIP
 
Join Date: Jan 2010
Location: U.K.
Posts: 26,374
Thanks: 0
Thanked 2,423 Times in 2,389 Posts
vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all vbaInet is a name known to all
Re: Creating a report from a form based on multiple criteria

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.

vbaInet is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Report on form that contains multiple Sub Forms scouser Reports 0 09-04-2008 07:28 AM
Error - "The object doesn't contain the Automation object "ExperationDialog" molnerr Reports 6 09-13-2007 01:39 PM
Creating Report based upon multiple fields orbic1 Modules & VBA 6 03-15-2004 03:14 AM
Not sure if problem is report or form huertalm Reports 1 04-18-2003 11:31 AM
Not sure if problem is report or form huertalm Forms 3 03-06-2003 01:06 PM




All times are GMT -8. The time now is 02:49 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World