I'm using the BuildCriteria function to develop an sql to filter a query on a subform:
Code:
for each ctrl1 in Screen.ActiveForm
....
sWhereClause1 = sWhereClause1 & " or " & BuildCriteria("Project_Scope", dbText, ctrl1.Name)
....
This will produce "Projectscope = 'REG' or Projectscope= 'SCR'..."
My problem is that I have a ctrl named "RND" and when it creates a 'criteria' for it , it does: "Projectscope = Rnd()" thinking that it is the Round function and not a name, I'm guessing. What I need is: "Projectscope = 'RND'", and it is not possible to change the name of project scope
I'm using the BuildCriteria function to develop an sql to filter a query on a subform:
Code:
for each ctrl1 in Screen.ActiveForm
....
sWhereClause1 = sWhereClause1 & " or " & BuildCriteria("Project_Scope", dbText, ctrl1.Name)
....
This will produce "Projectscope = 'REG' or Projectscope= 'SCR'..."
My problem is that I have a ctrl named "RND" and when it creates a 'criteria' for it , it does: "Projectscope = Rnd()" thinking that it is the Round function and not a name, I'm guessing. What I need is: "Projectscope = 'RND'", and it is not possible to change the name of project scope
Public Function projectyear()
'for rptProjectYear
sWhereClause1 = "Where "
ssql1 = "SELECT * From qryprojectyear "
For Each ctrl1 In Screen.ActiveForm 'this function will loop through each control that is a checkbox except for me.all
If TypeName(ctrl1) = "CheckBox" Then 'and if it's = to true build on the Where statement
If ctrl1.Name <> "all" Then 'the BuildCriteria() function will display "Project_scope = [control name]" in Where statement
If ctrl1 = True Then ' this is why the name of the checkbox MUST be the same as the name of the Project Scope
Screen.ActiveControl.SetFocus
If sWhereClause1 = "Where " Then
sWhereClause1 = sWhereClause1 & BuildCriteria("Project_Scope", dbText, ctrl1.Name)
criteria1 = BuildCriteria("Project_Scope", dbText, ctrl1.Name)
Else
sWhereClause1 = sWhereClause1 & " or " & BuildCriteria("Project_Scope", dbText, ctrl1.Name)
criteria1 = criteria1 & "or " & BuildCriteria("Project_Scope", dbText, ctrl1.Name)
End If
End If
End If
End If
Next ctrl1
MsgBox sWhereClause1
End Function
The problem is with the BuildCriteria() line, it works with all my other Project_Scopes (SCR,REG, ENG, LPA....) just not RND because its coming back as a function. Can I use a string function around "ctrl.name" to let it know that it is a string and not a function??
Public Function projectyear()
'for rptProjectYear
sWhereClause1 = "Where "
ssql1 = "SELECT * From qryprojectyear "
For Each ctrl1 In Screen.ActiveForm 'this function will loop through each control that is a checkbox except for me.all
If TypeName(ctrl1) = "CheckBox" Then 'and if it's = to true build on the Where statement
If ctrl1.Name <> "all" Then 'the BuildCriteria() function will display "Project_scope = [control name]" in Where statement
If ctrl1 = True Then ' this is why the name of the checkbox MUST be the same as the name of the Project Scope
Screen.ActiveControl.SetFocus
If sWhereClause1 = "Where " Then
sWhereClause1 = sWhereClause1 & BuildCriteria("Project_Scope", dbText, ctrl1.Name)
criteria1 = BuildCriteria("Project_Scope", dbText, ctrl1.Name)
Else
sWhereClause1 = sWhereClause1 & " or " & BuildCriteria("Project_Scope", dbText, ctrl1.Name)
criteria1 = criteria1 & "or " & BuildCriteria("Project_Scope", dbText, ctrl1.Name)
End If
End If
End If
End If
Next ctrl1
MsgBox sWhereClause1
End Function
The problem is with the BuildCriteria() line, it works with all my other Project_Scopes (SCR,REG, ENG, LPA....) just not RND because its coming back as a function. Can I use a string function around "ctrl.name" to let it know that it is a string and not a function??
I do not think that problem is not with ctrl.name. It is already a string data type. No need to convert it.
I still am thinking the problem is in BuildCriteria() and the way it handles this. without seeing the VBA code this function, I can not see what it is expecting as parameters.
From what you have posted, everything seams to point to BuildCriteria() as the place where RND gets converted to Rnd().
Without being able to verify that the VBA code in BuildCriteria() is correct, it will be difficult to properly debug this issue and help you any further.
The control name that is having problems is "RND". I have other controls named "SCR, REG, ENG..." It works fine for them. Doesn't work for RND. It definitely is within the BuildCriteria() function because everything else works. What I get when it runs "RND" is "Project_scope = Rnd()" which doesn't filter correctly, what I want is "Project_scope = 'RND'". I may just change the name of the control though to avoid this problem.
What HiTech Coach is asking for is a copy of the code in the function BuildCriteria() so that he can assess what the problem with that function is that is resulting in the error.
Likely, it is not delimiting the input data in a way that avoids the string 'Rnd' being interpreted as the vba function Rnd(). There may well be ways to fix this unintended situation (and others like it) by tweaking the function (it may be as simple as declaring an input variable type). However, it is usually a good practice to avoid naming controls etc identically to built-in functions that exist in vba, such as Rnd, Date, Now, etc unless you have a solid reason for doing so.
However, he can't help you if you refuse to provide him the information he has asked for. Which is, the code for the function BuildCriteria(). That is, everything from the line:
Code:
Public Function BuildCriteria([COLOR="Red"]some variable declarations here[/COLOR]) as String
wtrimble,
BuildCriteria is not a standard VBA or Access function. It is a custom function someone has written and should exist in a Module for your database. That is the code Hi Tech Coach is asking for. Check your Modules. You will surely find it there.
Buildcriteria() is a built in function and not a function I created, so it's not calling a public function. The variables used are the ones listed in the function. An example of me calling on Function projectyear() is
Code:
Private Sub AIG_Click()
If Me.AIG = False Then
Me.all = False
End If
Call projectyear
If sWhereClause1 = "Where " Then
Call projectyearnull
Else
Me.Text6 = Format(DSum("Proposal_Price", "qryprojectyear", criteria1), "Currency")
Me.Text9 = Format(DSum("PO_Value", "qryprojectyear", criteria1), "Currency")
Me.qryProjectYear_subform.Form.RecordSource = ssql1 & sWhereClause1
End If
End Sub
Where AIG is the name of the control that needs to be filtered.
I'll leave it to others to respond further. Not sure I can see much that can be done with it since we can't change a built-in function's code. Renaming might be your best bet unless you write a custom function to do the same job but that can handle this type of situation.
I'll leave it to others to respond further. Not sure I can see much that can be done with it since we can't change a built-in function's code. Renaming might be your best bet unless you write a custom function to do the same job but that can handle this type of situation.
Buildcriteria() is a built in function and not a function I created, so it's not calling a public function. The variables used are the ones listed in the function. An example of me calling on Function projectyear() is
Code:
Private Sub AIG_Click()
If Me.AIG = False Then
Me.all = False
End If
Call projectyear
If sWhereClause1 = "Where " Then
Call projectyearnull
Else
Me.Text6 = Format(DSum("Proposal_Price", "qryprojectyear", criteria1), "Currency")
Me.Text9 = Format(DSum("PO_Value", "qryprojectyear", criteria1), "Currency")
Me.qryProjectYear_subform.Form.RecordSource = ssql1 & sWhereClause1
End If
End Sub
Where AIG is the name of the control that needs to be filtered.
Access Developer Reference
Application.BuildCriteria Method
Show All
Hide All
The BuildCriteria method returns a parsed criteria string as it would appear in the query design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.), in Filter By Form (Filter By Form: A technique for filtering data that uses a version of the current form or datasheet with empty fields in which you can type the values that you want the filtered records to contain.) or Server Filter By Form (Server Filter By Form: A technique that uses a version of the current form or datasheet with empty fields in which you can type values you want the filtered records to contain. The data is filtered by the server before being retrieved from the database.) mode. For example, you may want to set a form's Filter or ServerFilter property based on varying criteria from the user. You can use the BuildCriteria method to construct the string expression argument for the Filter or ServerFilter property. String.
Syntax
expression A variable that represents an Application object.
Parameters
Name Required/Optional Data Type Description
Field Required String The field for which you wish to define criteria.
FieldType Required Integer An intrinsic constant (intrinsic constant: A constant that is supplied by Access, VBA, ADO, or DAO. These constants are available in the Object Browser by clicking globals in each of these libraries.) denoting the data type of the field. Can be set to one of the DAO DataTypeEnum values.
Expression Required String A string expression identifying the criteria to be parsed.
Return Value
String
Remarks
The BuildCriteria method enables you to easily construct criteria for a filter based on user input. It parses the expression argument in the same way that the expression would be parsed had it been entered in the query design grid, in Filter By Form or Server Filter By Form mode.
For example, a user creating a query on an Orders table might restrict the result set (result set: The set of records that results from running a query or applying a filter.) to orders placed after January 1, 1995, by setting criteria on an OrderDate field. The user might enter an expression such as the following one in the Criteria row beneath the OrderDate field:
>1-1-95
Microsoft Access automatically parses this expression and returns the following expression:
>#1/1/95#
The BuildCriteria method provides the same parsing from Visual Basic code. For example, to return the preceding correctly parsed string, you can supply the following arguments to the BuildCriteria method:
Visual Basic for Applications
Dim strCriteria As String
strCriteria = BuildCriteria("OrderDate", dbDate, ">1-1-95")
Since you need to supply criteria for the Filter property in correctly parsed form, you can use the BuildCriteria method to construct a correctly parsed string.
You can use the BuildCriteria method to construct a string with multiple criteria if those criteria refer to the same field. For example, you can use the BuildCriteria method with the following arguments to construct a string with multiple criteria relating to the OrderDate field:
Visual Basic for Applications
strCriteria = BuildCriteria("OrderDate", dbDate, ">1-1-95 and <5-1-95")
This example returns the following criteria string:
OrderDate>#1/1/95# And OrderDate<#5/1/95#
However, if you wish to construct a criteria string that refers to multiple fields, you must create the strings and concatenate them yourself. For example, if you wish to construct criteria for a filter to show records for orders placed after 1-1-95 and for which freight is less than $50, you would need to use the BuildCriteria method twice and concatenate the resulting strings.
Example
The following example prompts the user to enter the first few letters of a product's name and then uses the BuildCriteria method to construct a criteria string based on the user's input. Next, the procedure provides this string as an argument to the Filter property of a Products form. Finally, the FilterOn property is set to apply the filter.
Code:
Visual Basic for Applications
Sub SetFilter()
Dim frm As Form, strMsg As String
Dim strInput As String, strFilter As String
' Open Products form in Form view.
DoCmd.OpenForm "Products"
' Return Form object variable pointing to Products form.
Set frm = Forms!Products
strMsg = "Enter one or more letters of product name " _
& "followed by an asterisk."
' Prompt user for input.
strInput = InputBox(strMsg)
' Build criteria string.
strFilter = BuildCriteria("ProductName", dbText, strInput)
' Set Filter property to apply filter.
frm.Filter = strFilter
' Set FilterOn property; form now shows filtered records.
frm.FilterOn = True
End Sub
