Errors when trying to execute query

[frustrating]

I know I'm doing something wrong, but I'm trying to insert the results of a query, where the value of the query comes from a listbox into an email. To first test my query I am using this line:

thepercent = db.OpenRecordset("SELECT converttopercent.[Dept Root Source] FROM converttopercent WHERE [converttopercent].[Dept Root Source] = " & List20.Value)

I keep getting a "type mismatch" error. Any ideas? Also converttopercent is another query.

 

[pr2-eugin]

Hello frustrating..

What is the Data Type for the [converttopercent].[Dept Root Source] field in the table?

What is that you are assigning it to? I mean what is the Type of thepercent? I hope it is a recordset.

A little bit of coding will help us identify the problem a bit more.. as the line you have is totally out of context..

 

[frustrating]

Hello frustrating..

What is the Data Type for the [converttopercent].[Dept Root Source] field in the table?

What is that you are assigning it to? I mean what is the Type of thepercent? I hope it is a recordset.

A little bit of coding will help us identify the problem a bit more.. as the line you have is totally out of context..

The data type for converttopercent.[dept root source] is text. I want to query that field with the value of what's in the listbox and then display the results in an email.

I want to break down certain departments and then display the information in a query when the user selects which department to email.

So for example: User selects "pharmacy department" it opens up an email, and displays information queried from another table and displays the information. Right now, I can't seem to display information from my query.

 

[pr2-eugin]

Since the field [converttopercent].[Dept Root Source] is a Text, you have to enclose the value of the listbox inside single quotes.. Like..

db.OpenRecordset("SELECT converttopercent.[Dept Root Source] FROM converttopercent WHERE [converttopercent].[Dept Root Source] =[B][COLOR=Red] '[/COLOR][/B]" & Me.List20 & "[COLOR=Red][B]'[/B][/COLOR]")

Try this and see what happens..

 

[frustrating]

Since the field [converttopercent].[Dept Root Source] is a Text, you have to enclose the value of the listbox inside single quotes.. Like..

db.OpenRecordset("SELECT converttopercent.[Dept Root Source] FROM converttopercent WHERE [converttopercent].[Dept Root Source] =[B][COLOR=Red] '[/COLOR][/B]" & Me.List20 & "[COLOR=Red][B]'[/B][/COLOR]")

Try this and see what happens..

Now I'm getting an object required error

Here's the code I'm calling in my email body:

Function thepercent() As String
Dim DB As DAO.Recordset
Set DB = CurrentDb
Set thepercent = DB.OpenRecordset("SELECT converttopercent.[Dept Root Source] FROM converttopercent WHERE [converttopercent].[Dept Root Source] = '" & Me.List20 & "'")
End Function

 

[pr2-eugin]

Okay the db.OpenRecordset returns a RecordSet object not a String.. So my question is.. "Why is that you are troubling with recordsets?" Will a simple DLookUp not work? You do not even need to call this in a separate function.. All you have to do is..

Nz(DLookUp("[Dept Root Source]","converttopercent","[Dept Root Source] = '" & Me.List20 & "'"),"No Info Available")

The use of Nz() function is just in case if the value returned is Null..

 

[frustrating]

Okay the db.OpenRecordset returns a RecordSet object not a String.. So my question is.. "Why is that you are troubling with recordsets?" Will a simple DLookUp not work? You do not even need to call this in a separate function.. All you have to do is..

Nz(DLookUp("[Dept Root Source]","converttopercent","[Dept Root Source] = '" & Me.List20 & "'"),"No Info Available")

The use of Nz() function is just in case if the value returned is Null..

To answer your question: Because I'm very new to all of this, and I started out believing that I would need to use a function to return a value. Let me try what you're doing, and I'll report back. Thanks for all your help so far!

 

[frustrating]

So with Dlookup, is it possible to display all the rows being called? I think it just displays one, no?

Here is what I'm using it for: Each department in my group has made billing errors, and I've ran queries that have broken down what each problem was in the department by percentage. I've done the query that does the division/ % work, so using a function to do that isn't necessary.

For example:
The pharmacy department has made $XXX.00 in errors this month.
40% is because you can't do math.
60% because blah blah blah

So there are two fields belonging to the pharmacy department that need to get inserted into the email body. I have the total, and department name working...just not this.

 

[pr2-eugin]

DLookUp's can be also used on Inbuilt Queries.. So in your case, this is already a Query.. So you all you have to do is change the Criteria of the Query as the one below..

[COLOR=Blue]Forms![[COLOR=Red][B]yourFormName[/B][/COLOR]]![/COLOR]List20

So whatever fields you want from the Query you can use DLookUp to obtain the value..

 

[frustrating]

But the problem is I'll need to return more than one field per record because I'm posting a break down of all the the percentages. What you're suggesting will only display one return. I need all the returns associated with the variable.

So say there are 4 different records.
Department name1 with x reason for making mistakes, 25 %
Department name1 with y reason for making mistakes, 25 %
Department name 1 with z reason for making mistakes, 25%
Department name 1 with n reason for making mistakes, 25%

A dlookup seems to only be returning one, when I need all 4.

 

[pr2-eugin]

Okay in that case you will need a RecordSet.. The following (untested) code is how you ould go around getting the values..

Public xStr As String, yStr As String, zStr As String, nStr As String
Sub thepercent()
    Dim DB As DAO.Recordset
    Dim rst As DAO.Recordset
    Set DB = CurrentDb
    
    Set rst = DB.OpenRecordset("SELECT * FROM converttopercent WHERE [converttopercent].[Dept Root Source] = '" & Me.List20 & "'")
    
    Do while Not rst.EOF 
        xStr = rst!Fields([COLOR=Blue]reasonXFieldName[/COLOR])
        yStr = rst!Fields([COLOR=Blue]reasonYFieldName[/COLOR])
        zStr = rst!Fields([COLOR=Blue]reasonZFieldName[/COLOR])
        nStr = rst!Fields([COLOR=Blue]reasonNFieldName[/COLOR])
        rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing   
    Set db = Nothing
End Sub

Please change all the blue bits to match your table/query field names..

 

[frustrating]

I made all the changes, but am getting a type mismatch error. I researched this, and it seems as though it's because I'm not referencing the DAO library, but when I go to add the reference, it's saying I can't because it conflicts with another.

Any ideas on why I'd get this error?

Edit: It's not the reference issue, apparently. I was able to get it on, and I'm getting the same error.

 

[pr2-eugin]

Which line of code is the type mismatch error occurring? I just gave an example declaring them all strings.. if something is returning a number make sure it is declared as number and not string.. For example, if reason X is returning number change the declaration as..

Dim xNum As Long, yStr As String, zInt As Integer

 

[frustrating]

Which line of code is the type mismatch error occurring? I just gave an example declaring them all strings.. if something is returning a number make sure it is declared as number and not string.. For example, if reason X is returning number change the declaration as..

Dim xNum As Long, yStr As String, zInt As Integer

The error occurs on:

 Set DB = CurrentDb

They all will be strings. It's only text being sent.

 

[pr2-eugin]

Okay I am so sorry.. I made a mistake.. Please try the following..

Public xStr As String, yStr As String, zStr As String, nStr As String
Sub thepercent()
    Dim DB As [COLOR=Red][B]Database[/B][/COLOR] [COLOR=Green]'this was wrong in my previous code.. [/COLOR]
    Dim rst As DAO.Recordset
    Set DB = CurrentDb
    
    Set rst = DB.OpenRecordset("SELECT * FROM converttopercent WHERE [converttopercent].[Dept Root Source] = '" & Me.List20 & "'")
    
    Do while Not rst.EOF 
        xStr = rst!Fields(reasonXFieldName)
        yStr = rst!Fields(reasonYFieldName)
        zStr = rst!Fields(reasonZFieldName)
        nStr = rst!Fields(reasonNFieldName)
        rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing   
    Set db = Nothing
End Sub

 

[frustrating]

So this may be working once I tweak all the little stupid mistakes I'm making.

As a test, I used this return field:

xstr = rst!Fields([Dept Root Source])

Which gives me an error of "Item not found"

Also, this is a 101 question, but when I call the sub, I call Call Thepercent, correct?

 

[pr2-eugin]

Can you show the complete procedure you have implemented?

Also, this is a 101 question, but when I call the sub, I call Call Thepercent, correct?

Yes.. That is how you call procedures..

 

[frustrating]

Can you show the complete procedure you have implemented?
Yes.. That is how you call procedures..

These are the two procedures that work with each other. The values for list20.value obviously come from the value of the listbox which is referencing information from a different table. That part was way easier! here we go:

Private Sub Command22_Click()
Dim strEmail, strBody As String
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem
Set objOutlook = CreateObject("Outlook.Application")
Set objEmail = objOutlook.CreateItem(olMailItem)
With objEmail
    .To = "email@whatever.com"
    .Subject = "Error Analysis for the" & " " & List20.Value & " " & "Department"
    .Body = "Greetings [Applicable name field can be here]:" & vbCrLf & vbCrLf & "Your department has made " & List20.Column(1) & " in errors. The example email is pulling from the" & " " & List20.Value & " " & "department." & vbCrLf &
    "The errors have been broken down into the following categories:" & vbCrLf 
 Call thepercent
    .Display
End With
Set objEmail = Nothing
Exit Sub
End Sub

Public Sub thepercent()
Dim xstr As String
    Dim DB As Database
    Dim rst As DAO.Recordset
    Set DB = CurrentDb
    Set rst = DB.OpenRecordset("SELECT * FROM converttopercent WHERE [converttopercent].[Dept Root Source] = '" & Me.List20 & "'")
    Do While Not rst.EOF
        xStr = rst!Fields([Dept Root Source])
        rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing
    Set DB = Nothing
End Sub

 

[pr2-eugin]

The usage of SELECT * should not be a problem.. but just in case.. try including the fields that you wish to use.. Like..

DB.OpenRecordset("SELECT [COLOR=Red][B][Dept Root Source][/B][/COLOR] FROM converttopercent WHERE [converttopercent].[Dept Root Source] = '" & Me.List20 & "'")

And when accessing use..

rst!Fields([COLOR=Red][B]"[/B][/COLOR][Dept Root Source][COLOR=Red][B]"[/B][/COLOR])

Also you are assigning the values to String variables, so you need to use them in the Email Body.. like..

Call thepercent
.Body = "Greetings [Applicable name field can be here]:" & vbCrLf & vbCrLf & "Your department has made " & List20.Column(1) & " in errors. The example email is pulling from the" & " " & List20.Value & " " & "department." & vbCrLf & "The errors have been broken down into the following categories:" & vbCrLf &"Reason A : " & [COLOR=Red][B]xStr[/B][/COLOR]

 

[frustrating]

I just tried using your fixes and got the same error. "Item not found in this collection" and it occurs on line:

xstr = rst!Fields("[Dept Root Source]")

I swear this field exists!