VBA query change criteria

[Coleman984]

See the below query, how would I write this into vba and have the criteria be set by cboNumber?

If anyone could help that would be greatly appreciated. I got this code from the query designer generated code.

I highlighted in red where I suspect I would change this to the combo box. Which would be me.cboNumber.value i believe.
But the problem still remains that I do not know how to get vba to change the query which is named Individual Employee Points to use this select statement from vba.

SELECT [tblEmployee.txtFName] & " " & [tblemployee.txtLName] AS [Full Name], [tblEmployee.empidno] AS [Employee ID #], Sum(IIf([fkAttendancetypeID]=2,1,IIf([fkAttendancetypeID]=3,0.5,IIf([fkAttendancetypeID]=4,0.5,IIf([fkAttendancetypeID]=5,1,0))))) AS [Total Points]
FROM (tblEmployee INNER JOIN tblEmployeeAttendance ON tblEmployee.pkEmployeeID = tblEmployeeAttendance.fkEmployeeID) INNER JOIN tblEmployeeJobDescriptions ON tblEmployee.pkEmployeeID = tblEmployeeJobDescriptions.fkEmployeeID
WHERE (((tblEmployee.dteInactive) Is Null))
GROUP BY [tblEmployee.txtFName] & " " & [tblemployee.txtLName], [tblEmployee.empidno]
HAVING ((([tblEmployee.empidno])=[COLOR="Red"]124102[/COLOR]) AND ((Sum(IIf([fkAttendancetypeID]=2,1,IIf([fkAttendancetypeID]=3,0.5,IIf([fkAttendancetypeID]=4,0.5,IIf([fkAttendancetypeID]=5,1,0))))))>0));

 

[spikepl]

In the query designer, in the relevant Criteria field, right-click and select Build. There, via Forms->Open Forms, navigate to the form in question, select the desired control and paste it in.

 

[Coleman984]

In the query designer, in the relevant Criteria field, right-click and select Build. There, via Forms->Open Forms, navigate to the form in question, select the desired control and paste it in.

Um...not sure if you read the question. That isn't a vba solution. That is already what I did.

 

[John Big Booty]

I think it will look something like;

"SELECT [tblEmployee.txtFName] & CHR(34) CHR(34) & [tblemployee.txtLName] AS [Full Name], [tblEmployee.empidno] AS [Employee ID #], Sum(IIf([fkAttendancetypeID]=2,1,IIf([fkAttendancetypeID]=3,0.5,IIf([fkAttendancetypeID]=4,0.5,IIf([fkAttendancetypeID]=5,1,0))))) AS [Total Points] " & _
"FROM (tblEmployee INNER JOIN tblEmployeeAttendance ON tblEmployee.pkEmployeeID = tblEmployeeAttendance.fkEmployeeID) INNER JOIN tblEmployeeJobDescriptions ON tblEmployee.pkEmployeeID = tblEmployeeJobDescriptions.fkEmployeeID " & _
"WHERE (((tblEmployee.dteInactive) Is Null)) " & _
"GROUP BY [tblEmployee.txtFName] & CHR(34) CHR(34) & [tblemployee.txtLName], [tblEmployee.empidno] " & _
"HAVING ((([tblEmployee.empidno])= " & Me.cboNumber & ") AND ((Sum(IIf([fkAttendancetypeID]=2,1,IIf([fkAttendancetypeID]=3,0.5,IIf([fkAttendancetypeID]=4,0.5,IIf([fkAttendancetypeID]=5,1,0))))))>0));"

Note; in the above that the SQL needs to be in a continuous string enclosed by double quotes. However for ease of reading it's been broken down to a number of lines, each of the lines, except the last, is terminated by a space a double quote a space and ampersand (&) another space and an underscore. Additionally where you need to enclose a portion of the SQL with double quote, such as the spaces in your code, you can replace the double quote with CHR(34) the ASCII code for a double quote. You may also see them replaced with the arrangement of a double quote to close the string an ampersand followed by a single quote enclosed in double quote an ampersand and then another double quote to continue the SQL string.

You could also us Forms!YourFormName!cboNumber as the criteria in your query.

 

[Coleman984]

thanks John, but how do I pass that into the query from VBA?

That is the part I'm really clueless on. Thanks for the tips though.

Also I tried the query you posted and get a data mis-match error when I try to open the query. Not sure why with the exception of char(34) it looks the same.

 

[John Big Booty]

The code presented in my response, would be run using DoCmd.RunSQL, which is what I thought you where looking to do. However it would seem I have misunderstood what it is you are trying to achieve.

If what you are after is to dynamically change the red highlighted portion of your SQL based on the contents of the combo cboNumber, then simply create a query and insert Forms!YourFormName!cboNumber in the criteria row of the appropriate field.

 

[Coleman984]

no that is the what i wanted, just didn't know the command. how will that command know the query name?

 

[Coleman984]

Not sure if you have any idea why it appears to be doubling some results. If there an error in one of pieces of code?

 

[Coleman984]

Should be noted that this issue was already present before your help. Just had not yet discovered it.

 

[John Big Booty]

Not sure if you have any idea why it appears to be doubling some results. If there an error in one of pieces of code?

Couldn't say without seeing the actual data

 

[Coleman984]

As I suspected it was a problem with the employee in the database, don't know what caused the issue. But I deleted the employee and reentered all the data for the employee and now it is giving the correct results. Is there a way to check for hidden duplicate data? I believe there was hidden data or something of the sort as I couldn't find anywhere where it was doubling this employees entries.

 

[John Big Booty]

What do you mean by hidden? As far as user input data goes what you see is what you've got. The problem could also arise as the unforeseen result of either table structure or query structure.