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
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
You want a textbox to show you a total of a field on a report?
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
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])
Not sure I understand but there were errors in your code. Try this
Code:=DSum("DelTo","tblEdit","ShipmentDate= #" & Me.ShipmentDate & "# And PostCode = '" & Me.PostCode & "'")
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
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
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
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
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:
....except I would still use vbCrLf or vbNewLine as a line breakCode: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>"
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
Hi ridders52, it has given me red text in the code, it doesn't appear to like the apostrophes
Code:mailto:" & '" & EMAdd & "'</P>"
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.