Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185

    Sum Of Report Records

    Hi all, is there a method of having a text box on a report ie: txtPostCodeSum = Sum Of Postcode ? The report has got a Post Code Header, this is so that 1 page prints per postcode (Subject to quantity of records per Post Code) Kind Regards Dave

  2. #2
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 10 Access 2016
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    You want a textbox to show you a total of a field on a report?

  3. #3
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hi Perceptus, yes, that's the target, the report maybe 36 pages for 36 clients, there is a postcode header, but on report header, i am looking to do something like, DSum("Client","tblJobs","[PostCode] = " & Me.txtPostCode & " And [Date] = Forms!frmMainMenu!txtDate") or something along these lines!, the Report prints all pages at once and if there is 3 items for Postcode1 and 10 items for postcode2 and 7 items for postcode3 etc, can each postcode have a sum based on the form date, please forgive me if this doesn't make sense!!!! many thanks

  4. #4
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    I have put the following code into a text box on a report page header, it comes up error in the text box, is this correct to calculate how many records are on the report ?

    Code:
    =DSum("DelTo","tblEdit","[SHipmentDate]= #" & [ShipmentDate] & "#" & " And [PostCode] = " & [PostCode])

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Not sure I understand but there were errors in your code. Try this

    Code:
    =DSum("DelTo","tblEdit","ShipmentDate= #" & Me.ShipmentDate & "# And PostCode = '" & Me.PostCode & "'")
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hi ridders52, thank you very much for your reply, no there was no errors in the code, I am placing this code in the control source of a text box so each postcode report has a got a total quantity in the page header, the text box has got #error actually in the text box ?

    There is also one final hurdle going back the email signature, all works perfectly the final bit is, how do i make the email address a "click to email" rather than plain text in the mail body ??

    Code:
    Dim MyApp As Outlook.ApplicationDim MyItem As Outlook.MailItem
    Dim BodyMessage, Orders, SigString, Signature As String
    Dim MySig As String
    Dim EMAdd As String
    
    
    EMAdd = DLookup("EmailAdd", "tblSignatures", "[IDNumber] = " & Me.txtIndexNo)
    
    
    Signature = "<P>Unit 123,</P>" & Chr(10) & _
    "<P>Someplace,</P>" & Chr(10) & _
    "<P>Somewhere</P>" & Chr(10) & _
    "<P>Tel: 1234-56789</P>" & Chr(10) & _
    "E-Mail:" & " " & "#" & EMAdd & "#" ' EMAdd is plain text rather than click to email ??
    
    
    Set MyApp = CreateObject("Outlook.application")
    Set MyItem = MyApp.CreateItem(olMailItem)
    BodyMessage = "THIS IS A TEST EMAIL FROM DM" & Chr(10) & Chr(10) & _
    "<P><img border =0 hspace=0 alt='' src='C:\Users\David\Desktop\DMT\EmailLogo.jpg' align=baseline></P>"
    
    
    With MyItem
    'Signature = MyItem.HTMLBody
    .To = "dave@dmoses.co.uk"
    .Subject = "TEST MAIL FROM DM"
    Debug.Print BodyMessage
    MyItem.HTMLBody = BodyMessage & Chr(10) & _
    Signature
    Debug.Print .HTMLBody
    .Display
    End With

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I corrected your DSum code in my last reply to add text delimiters.
    If your postcode system is just numbers, that's fine but otherwise you need my version

    I also removed superfluous double quotes "
    You have the same issue in the line marked with red text
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Ahh yes, i understand that the hyphen is string delimiters, i will try again with your code yes on the email side, all works perfectly apart from EMAdd is just plain text, it would be good to have as a hyperlink so it can be clicked on to generate email, i tried changing the following Dim EMAdd As String to Dim EMadd as Hyperlink, the Dlookup didn't like that! can the EMAdd be a hyperlink ?? kind regards Dave

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I understand that the hyphen is string delimiters
    Sorry now you've lost me completely
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #10
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    I think 2 subjects on the go! email signature does exactly what it should but the EMAdd is not hyperlink (highlighted red), Subject 2 is the DSum on the report just displays #error, and doesn't display total quantity per postcode, hope this makes sense ?

  11. #11
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by DMT Dave View Post
    I think 2 subjects on the go! email signature does exactly what it should but the EMAdd is not hyperlink (highlighted red), Subject 2 is the DSum on the report just displays #error, and doesn't display total quantity per postcode, hope this makes sense ?
    1. What format do you have for postcodes? UK type format with text & numbers or just text or just numbers?
    What is the DSum formula you are using now?

    2. I'll check & get back to you later re clickable email text if not answered first by someone else
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  12. #12
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I just checked the clickable email code. Its very simple: "mailto:1 2 3 @ x y z. c o m" (spaces added to fool forum software)
    So something like:

    Code:
    Signature = "<P>Unit 123,</P>" & Chr(10) & _"<P>Someplace,</P>" & Chr(10) & _
    "<P>Somewhere</P>" & Chr(10) & _
    "<P>Tel: 1234-56789</P>" & Chr(10) & _
    "<P>E-Mail: mailto:" & '" & EMAdd & "'</P>"
    ....except I would still use vbCrLf or vbNewLine as a line break
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  13. #13
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hi ridders52, I will try both again, postcode is set as short text in the table and yes UK postcodes are use, having said that, we do have some Ireland contacts on the system and because we don't have their postcodes, I would rather add something rather than nothing because if there are any search's on the system, access needs something to search rather than nothing! I will try the email changes tonight as I can do on laptop, the postcode sum on the report I will try tomorrow at work, will leave you a good comment regards D

  14. #14
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,185
    Hi ridders52, it has given me red text in the code, it doesn't appear to like the apostrophes
    Code:
    mailto:" & '" & EMAdd & "'</P>"

  15. #15
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I thought about that after posting. It was a mistake. Try without them.
    If that doesn't work either suggest you create a function GetEMAdd and set its value equal to the DLookup code.
    Then use that in the final line.

    I can confirm that the 'mailto' line worked for me when I tested it.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 02-04-2016, 08:13 AM
  2. Replies: 3
    Last Post: 11-07-2015, 05:08 PM
  3. Replies: 4
    Last Post: 02-12-2015, 04:17 PM
  4. Records w no data on report?
    By wnicole in forum Reports
    Replies: 2
    Last Post: 01-21-2014, 11:13 AM
  5. Replies: 4
    Last Post: 12-13-2010, 05:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums