Set rs = db.OpenRecordset("SELECT ID,bfast,lunch,dwine,bar,desert,cellar,table,tdate FROM sales WHERE MembersID=" & MemID [COLOR=red]& " AND tdate Between #11/1/2012# And #11/30/2012#[/COLOR])
don't miss the space before the AND!Code:Set rs = db.OpenRecordset("SELECT ID,bfast,lunch,dwine,bar,desert,cellar,table,tdate FROM sales WHERE MembersID=" & MemID [COLOR=red]& " AND tdate Between #11/1/2012# And #11/30/2012#[/COLOR])
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#)
SELECT ID,bfast,lunch,dwine,bar,desert,cellar,twine,tdate[COLOR=red], [/COLOR][COLOR=red](Select sum(bfast+lunch+dwine+bar+desert+cellar+twine) From Sales as tmp WHERE MembersID=Sales.MembersID AND tdate Between #11/1/2012# And #11/30/2012#) as MthTtl[/COLOR] FROM Sales WHERE MembersID=" & MemID & AND " tdate Between #11/1/2012# And #11/30/2012#
SELECT ID,bfast,lunch,dwine,bar,desert,cellar,twine,tdate[COLOR=red], [/COLOR][COLOR=red](Select sum(bfast+lunch+dwine+bar+desert+cellar+twine) From Sales as tmp WHERE MembersID=Sales.MembersID AND tdate >=#11/1/2012# And TDate<=Sales.tdate) as MthToDate[/COLOR] FROM Sales WHERE MembersID=" & MemID & AND " tdate Between #11/1/2012# And #11/30/2012#
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?
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 & "ID " & "" & "|" & "Breakfast" & "|" & "Lunch" & "|" & "Desert" & "|" & "Desert Wine" & "|" & "Table Wine" & "|" & "Bar " & "|" & "Cellar " & "|" & "Date"[COLOR=red] & "Month To Date"[/COLOR] & vbCrLf & BodyStr 'add a header line to the body
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 part of the sql query.
SELECT ID,bfast,lunch,dwine,bar,desert,cellar,twine,tdate[COLOR=red], [/COLOR][COLOR=red](Select sum(bfast+lunch+dwine+bar+desert+cellar+twine) From Sales as tmp WHERE MembersID=Sales.MembersID AND tdate Between #11/1/2012# And #11/30/2012#) as MthTtl[/COLOR] FROM Sales WHERE MembersID=" & MemID & AND " tdate Between #11/1/2012# And #11/30/2012#
I have managed to get the sum to output as a field in the email's body which is good but it just repeats the same total for every line. Ideally I'd like it to output to another line in the body just once
while not rst.eof [COLOR=green]'existing[/COLOR]
body=body & "....[COLOR=green]'existing[/COLOR]
rst.movenext [COLOR=green]'existing[/COLOR]
[COLOR=red]if rst.eof then body=body & vbcrlf & vbcrlf & "Total for Month: " & rst.fields("MthTtl")[/COLOR]
wend [COLOR=green]'existing[/COLOR]
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 part of the sql query.
SELECT ID,bfast,lunch,dwine,bar,desert,cellar,twine,tdate[COLOR=red], [/COLOR][COLOR=red](Select sum(bfast+lunch+dwine+bar+desert+cellar+twine) From Sales as tmp WHERE MembersID=Sales.MembersID AND tdate Between #11/1/2012# And #11/30/2012#) as MthTtl[/COLOR] FROM Sales WHERE MembersID=" & MemID & AND " tdate Between #11/1/2012# And #11/30/2012#
I have managed to get the sum to output as a field in the email's body which is good but it just repeats the same total for every line. Ideally I'd like it to output to another line in the body just once
while not rst.eof 'existing
body=body & "....'existing
[COLOR=red]body2=body2 & ".... [/COLOR]
rst.movenext 'existing
if rst.eof then body=body & vbcrlf & vbcrlf & "Total for Month: " & rst.fields("MthTtl")
wend 'existing
[COLOR=red]bodystr=body & body2[/COLOR]
docmd.sendobject....body1 & body2