VBA SQL Question

graviz · Dec 14, 2009

I know I'm missing something pretty basic but I cannot get this to work:

Public Function test2()

Dim OL As Object, MailSendItem As Object
Dim CompM As String
Set OL = CreateObject("Outlook.Application")
Set MailSendItem = OL.CreateItem(olMailItem)

CompM = "Select 30_Day_Latest_Week.Comp%" & _
"From 30_Day_Latest_Week"

With MailSendItem
.Subject = "Completion Rate"
.HTMLBody = .HTMLBody & "<body>"
.HTMLBody = .HTMLBody & "Completion %: " & CompM & ""
.HTMLBody = .HTMLBody & "</body>"

.To = ""
.CC = ""
.Importance = 2 ' olImportanceHigh
.Display
.Send
End With

End Function

The thing I can't figure out is what it won't give the output of my SQL code in the e-mail. When I run this it just e-mails the SQL statement and not the value. What am I missing?

boblarson · Dec 14, 2009

The reason it is sending the SQL Statement and not the value is because you are just setting a string to a SQL Statement and a string is going to send what you set. If you want a value you will need to assign to a recordset or use a DLookup.

Something like:

Code:

CompM = Nz(DLookup("Comp%", "30_Day_Latest_Week"),0)

graviz · Dec 14, 2009

boblarson said:
The reason it is sending the SQL Statement and not the value is because you are just setting a string to a SQL Statement and a string is going to send what you set. If you want a value you will need to assign to a recordset or use a DLookup.

Something like:

Code:

CompM = Nz(DLookup("Comp%", "30_Day_Latest_Week"),0)

When I change it to that I receive Run-time error 3075 Syntax error in query expression 'Comp%'.

Public Function test2()

Dim OL As Object, MailSendItem As Object
Dim CompM As String
Set OL = CreateObject("Outlook.Application")
Set MailSendItem = OL.CreateItem(olMailItem)
todaysdate = Date

CompM = Nz(DLookup("Comp%", "30_Day_Latest_Week"), 0)

With MailSendItem
.Subject = "Completion Rate"
.HTMLBody = .HTMLBody & "<body>"
.HTMLBody = .HTMLBody & "Completion %: " & CompM & ""
.HTMLBody = .HTMLBody & "</body>"

.To = ""
.CC = ""
.Importance = 2 ' olImportanceHigh
.Display
.Send
End With

End Function

boblarson · Dec 14, 2009

Sorry, forgot with the special character in there we need square brackets (again another reason why to NOT use spaces or special characters in your field or object names).

Change to:

CompM = Nz(DLookup("[Comp%]", "30_Day_Latest_Week"), 0)

graviz · Dec 14, 2009

Bob-

Thank you for always helping me out! That worked perfectly! For learning purposes what am I doing wrong on the recordset way of doing things:

Public Function test2()

Dim OL As Object, MailSendItem As Object
Dim CompM As String
Dim db As DAO.Database
Dim compp As DAO.Recordset
Set OL = CreateObject("Outlook.Application")
Set MailSendItem = OL.CreateItem(olMailItem)
todaysdate = Date

CompM = "Select Comp% " & _
"From 30_Day_Latest_Week "

Set db = CurrentDb
Set compp = db.OpenRecordset(CompM)

With MailSendItem
.Subject = "Completion Rate"
.HTMLBody = .HTMLBody & "<body>"
.HTMLBody = .HTMLBody & "Completion %: " & compp & ""
.HTMLBody = .HTMLBody & "</body>"

.To = ""
.CC = ""
.Importance = 2 ' olImportanceHigh
.Display
.Send
End With

End Function

boblarson · Dec 14, 2009

You would need to set the results of a field:

So, you would need to use something like:

.HTMLBody = .HTMLBody & "Completion %: " & compp("Comp%").Value & ""

VBA SQL Question

graviz

Registered User.

boblarson

Smeghead

graviz

Registered User.

boblarson

Smeghead

graviz

Registered User.

boblarson

Smeghead

Similar threads

Users who are viewing this thread