Access Report with VB

	Home Register Search Help Login

Sponsors

Shopping Cart Software
Ecommerce software integrated into Frontpage, Dreamweaver and Golive templates. No monthly fees and available in ASP and PHP versions.

Website Templates
We also have a wide selection of Dreamweaver, Expression Web and Frontpage templates as well as webmaster tools and CSS layouts.

Frontpage website templates
Creative Website Templates for FrontPage, Dreamweaver, Flash, SwishMax

Search Forums

Recent Posts

Todays Posts
Most Active posts
Posts since last visit
My Recent Posts
Mark posts read

Access Report with VB

View related threads: (in this forum | in all forums)

Logged in as: Guest

Users viewing this topic: none

Printable Version

All Forums >> Web Development >> ASP and Database >> Access Report with VB

Page: [1]

Shashant

Posts: 1
Joined: 9/28/2004
Status: offline

Access Report with VB - 9/28/2004 10:42:45

Hey Everyone,

I was wondering fi anyone can help with this code. I have a report which will total up all teh different categories. The first thing the code does is figure out which field to use, either estimate or final budget category, such as if there is a printing total that is an estimate and there is also one that is a final, it will grab the final. That part works fine. I can get it to total up for the group of Client. Now I would like to have a Report total for all the clients. I would assume to place that in teh report footer, but the problem i am getting is that the totals are not adding up at all. I have been working on this for days now trying to get the numbers to add up but they don't want to :( .

Here is my code, please take a look to see if you see anything obviously wrong or something that you think could fix the problem. Thank you.


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim strConstant As String
strConstant = "N/A"


[Design] = 0
[Photo] = 0
[Printing] = 0
[Mailhouse] = 0
[Postage] = 0
[Other] = 0





'Projects
If Not IsNull([ProjectName]) Then
TotalProjectCount = TotalProjectCount + 1
End If




'Estimate

If IsNull([qryAllProjectEstimateData.Total]) Then
    [Estimate] = strConstant
Else
    [Estimate] = [qryAllProjectEstimateData.Total]
    TotalEstimate = TotalEstimate + [Estimate]
End If

'Final

If IsNull([qryAllProjectFinalData.Total]) Then
    [Final] = strConstant
Else
    [Final] = [qryAllProjectFinalData.Total]
    TotalFinal = TotalFinal + [Final]
End If

'Balance

If [Estimate] = strConstant Or [Final] = strConstant Then
    [Balance] = strConstant
Else
    [Balance] = ([Estimate] - [Final])
    TotalBalance = TotalBalance + [Balance]
End If

'Design

If IsNull([qryAllProjectEstimateData.Design]) And IsNull([qryAllProjectFinalData.Design]) Then
        [Design] = strConstant
        If [Design] <> strConstant Then TotalDesign = TotalDesign + [Design]
       

    ElseIf Not IsNull([qryAllProjectEstimateData.Design]) And IsNull([qryAllProjectFinalData.Design]) Then
        [Design] = [qryAllProjectEstimateData.Design]
        If [Design] <> strConstant Then TotalDesign = TotalDesign + [Design]
  
        
    ElseIf Not IsNull([qryAllProjectFinalData.Design]) Then
        [Design] = [qryAllProjectFinalData.Design]
        If [Design] <> strConstant Then TotalDesign = TotalDesign + [Design]
  
End If


'Photo

If IsNull([qryAllProjectEstimateData.Photography]) And IsNull([qryAllProjectFinalData.Photography]) Then
        [Photo] = strConstant
        If [Photo] <> strConstant Then TotalPhoto = TotalPhoto + [Photo]
      
        
    ElseIf Not IsNull([qryAllProjectEstimateData.Photography]) And IsNull([qryAllProjectFinalData.Photography]) Then
        [Photo] = [qryAllProjectEstimateData.Photography]
         If [Photo] <> strConstant Then TotalPhoto = TotalPhoto + [Photo]
      
    
    ElseIf Not IsNull([qryAllProjectFinalData.Photography]) Then
        [Photo] = [qryAllProjectFinalData.Photography]
         If [Photo] <> strConstant Then TotalPhoto = TotalPhoto + [Photo]
        
End If



'Printing

If IsNull([qryAllProjectEstimateData.Print]) And IsNull([qryAllProjectFinalData.Print]) Then
        [Printing] = strConstant
         If [Printing] <> strConstant Then
            TotalPrinting = TotalPrinting + [Printing]
      
         End If
         
    ElseIf Not IsNull([qryAllProjectEstimateData.Print]) And IsNull([qryAllProjectFinalData.Print]) Then
        [Printing] = [qryAllProjectEstimateData.Print]
         If [Printing] <> strConstant Then
            TotalPrinting = TotalPrinting + [Printing]
           
         End If
        
    ElseIf Not IsNull([qryAllProjectFinalData.Print]) Then
        [Printing] = [qryAllProjectFinalData.Print]
         If [Printing] <> strConstant Then
            TotalPrinting = TotalPrinting + [Printing]
         End If
         
End If


'MailHouse

If IsNull([qryAllProjectEstimateData.Mail]) And IsNull([qryAllProjectFinalData.Mail]) Then
        [Mailhouse] = strConstant
         If [Mailhouse] <> strConstant Then TotalMailHouse = TotalMailHouse + [Mailhouse]
        
    ElseIf Not IsNull([qryAllProjectEstimateData.Mail]) And IsNull([qryAllProjectFinalData.Mail]) Then
        [Mailhouse] = [qryAllProjectEstimateData.Mail]
         If [Mailhouse] <> strConstant Then TotalMailHouse = TotalMailHouse + [Mailhouse]
        
    ElseIf Not IsNull([qryAllProjectFinalData.Mail]) Then
        [Mailhouse] = [qryAllProjectFinalData.Mail]
         If [Mailhouse] <> strConstant Then TotalMailHouse = TotalMailHouse + [Mailhouse]
End If

'Postage

If IsNull([qryAllProjectEstimateData.Postage]) And IsNull([qryAllProjectFinalData.Postage]) Then
        [Postage] = strConstant
        If [Postage] <> strConstant Then TotalPostage = TotalPostage + [Postage]
        
    ElseIf Not IsNull([qryAllProjectEstimateData.Postage]) And IsNull([qryAllProjectFinalData.Postage]) Then
        [Postage] = [qryAllProjectEstimateData.Postage]
        If [Postage] <> strConstant Then TotalPostage = TotalPostage + [Postage]
        
    ElseIf Not IsNull([qryAllProjectFinalData.Postage]) Then
        [Postage] = [qryAllProjectFinalData.Postage]
        If [Postage] <> strConstant Then TotalPostage = TotalPostage + [Postage]
End If

'Other

If IsNull([qryAllProjectEstimateData.Other]) And IsNull([qryAllProjectFinalData.Other]) Then
        [Other] = strConstant
        If [Other] <> strConstant Then TotalOther = TotalOther + [Other]
        
    ElseIf Not IsNull([qryAllProjectEstimateData.Other]) And IsNull([qryAllProjectFinalData.Other]) Then
        [Other] = [qryAllProjectEstimateData.Other]
        If [Other] <> strConstant Then TotalOther = TotalOther + [Other]
        
    ElseIf Not IsNull([qryAllProjectFinalData.Other]) Then
        [Other] = [qryAllProjectFinalData.Other]
        If [Other] <> strConstant Then TotalOther = TotalOther + [Other]
End If

[TotalDesign1] = TotalDesign
[TotalPhoto1] = TotalPhoto
[TotalPrinting1] = TotalPrinting
[TotalMailHouse1] = TotalMailHouse
[TotalPostage1] = TotalPostage
[TotalOther1] = TotalOther
[TotalEstimate1] = TotalEstimate
[TotalFinal1] = TotalFinal
[TotalBalance1] = TotalBalance
[TotalProjectCount1] = TotalProjectCount



End Sub



Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)


ReportDesign = ReportDesign + TotalDesign
ReportPhoto = ReportPhoto + TotalPhoto
ReportPrinting = ReportPrinting + TotalPrinting
ReportMailHouse = ReportMailHouse + TotalMailHouse
ReportPostage = ReportPostage + TotalPostage
ReportOther = ReportOther + TotalOther
ReportEstimate = ReportEstimate + TotalEstimate
ReportFinal = ReportFinal + TotalFinal
ReportBalance = ReportBalance + TotalBalance
ReportProjectCount = ReportProjectCount + TotalProjectCount

End Sub

Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)

TotalDesign = 0
TotalPhoto = 0
TotalPrinting = 0
TotalMailHouse = 0
TotalPostage = 0
TotalOther = 0
TotalEstimate = 0
TotalFinal = 0
TotalBalance = 0
TotalProjectCount = 0


End Sub



Private Sub ReportFooter_Format(Cancel As Integer, FormatCount As Integer)
[ReportDesign1] = ReportDesign
[ReportPhoto1] = ReportPhoto
[ReportPrinting1] = ReportPrinting
[ReportMailHouse1] = ReportMailHouse
[ReportPostage1] = ReportPostage
[ReportOther1] = ReportOther
[ReportEstimate1] = ReportEstimate
[ReportFinal1] = ReportFinal
[ReportBalance1] = ReportBalance
[ReportProjectCount1] = ReportProjectCount
End Sub

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)

ReportDesign = 0
ReportPhoto = 0
ReportPrinting = 0
ReportMailHouse = 0
ReportPostage = 0
ReportOther = 0
ReportEstimate = 0
ReportFinal = 0
ReportBalance = 0
ReportProjectCount = 0

End Sub