Search results

CJ I think I read somewhere that HTML formatting was incompatible with the current VBA method I am using, is this the case? I'd really like to format the result of the script / query into a more tidy email. At the moment, even with your SPACE function and with the pipes the table is broken as...

Hello CJ, I tried adding the amended query and access just issues a vague syntax error message. I still haven't added the second part to bodystring yet, is that integral to the query working? Set rs = db.OpenRecordset("SELECT ID, bfast,lunch,dwine,bar,desert,cellar,twine,tdate, (Select...

Thanks for the help with this project BTW! I know I have asked this before but is it difficult to get the members' first and surnames from the same recordset and add them to each email with dear....... ........ here is your monthly statement?

Hello CJ I elected to change all of the table's fields default value to 0 instead of blank / null and this seemed to work. Furthermore it seemed to work with SPACE and I managed to format my email better. The problem is now that when I change the date parameters it throws up an incorrect us of...

Hmmm, i removed the SPACE formatting and it the sum worked even when I removed values from two of the fields. So it looks as though NZ and SPACE don't play well together for some reason. EDIT: I tried the first field in the below line of the script |BodyStr = BodyStr & Space(50 -...

(Select sum(NZ(bfast)+NZ(lunch)+NZ(dwine)+NZ(bar)+NZ(desert)+NZ(cellar)+NZ(twine)) This is still returning invalid use of NULL, am I doing something else wrong? The SPACE formatting works btw, thanks. NB There isn't a space in the word desert, in case you think it's that. I don't know why the...

Hello CJ, I tried the formatting as above and it keeps on giving a syntax error message BodyStr = BodyStr & space(10-len(rs.Fields(0)) & rs.Fields(0) & "|" & rs.Fields(1) & "| " & rs.Fields(2) & "| " & rs.Fields(5) & "| " & rs.Fields(3) & "|...

Brilliant....I'll have a shot at this. Also, I didn't know there was the ability to do simple formatting using this method. It'll be great if this helps tidy the output a bit. At the moment it's not terribly presentable :)

CJ below is the current script which works brilliantly but I just need to be able to output the summed total outside the table, just once. I don't care which method i.e. the 2nd recordset or the string concatentation method. At the bottom I have printed the script's output as well, although this...

I think what you were suggesting was to change this part of the script While Not rs.EOF BodyStr = BodyStr & rs.Fields(0) & "| " & rs.Fields(1) & "| " & rs.Fields(2) & "| " & rs.Fields(3) & "| " & rs.Fields(4) & "| " &...

CJ I know there has been a massive break in this but is there any way you can explain the above in simpler terms. I have tried to implement your suggestions and it doesn't work.

Thanks for the clarification on the sub. When you say modify the script is it possible to create another string like BodyStr (BodyStrII?) and have it's data (created by another loop through rs function) inserted into the docmd.sendobject function's messagetext argument.

Yes - just need to copy and paste the subquery where you want it in the order then modify it and give it another name (e.g. BarTtl, RestTtl) I don't get this, when you say subquery do you mean everything between Public Function emailbodfunc(MemID As Long) As String and End function or is it...

CJ it works without any errors but how to I add the total (MthTtl) to the email body. Do I have to add it here, or do I have to declare some kind of variable to store it and act as a placeholder? If BodyStr <> "" Then BodyStr = "Please find below your monthly statement" & vbCrLf & vbCrLf &...

CJ that's brilliant that it's possible to sum within the original query, I'll try it out. How flexible is this method? Can I for example, sum and output say wine, dwine,bar and cellar then do the same with bfast,lunch,dinner and desert for a separate total?

I have realised that there is some missing functionality in the script, is it possible to sum the fields from the various Sales fields and output the total to the email's body concatenated like "your monthly total is" = monthtotal. Would this require another function with another query or could...

Brilliant, the script works a treat. Thanks CJ and pbaldy for your help. By the looks of the numbers viewing this thread I think it may prove useful to others to boot.

Set rs = db.OpenRecordset("SELECT ID,bfast,lunch,dwine,bar,desert,cellar,twine,tdate FROM Sales WHERE MembersID=" & MemID & AND " tdate Between #11/1/2012# And #11/30/2012#) I spotted the omitted quote mark and added it but it still gave an error message.

Set rs = db.OpenRecordset("SELECT ID,bfast,lunch,dwine,bar,desert,cellar,twine,tdate FROM Sales WHERE MembersID=" & MemID & AND tdate Between #11/1/2012# And #11/30/2012#) This gives an opaque syntax error, have I done it right?

pbaldy that's a really good way to avoid typo problems. Cheers