By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,001 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Passing Complex? Criteria From A Form Variable to a Query

P: 3
I have an MS access Database (2000) and Have created a form that sets a variable to a value I would like (Based on other selections in the form) to pass to my query criteria and I get the following results.

If the Form Variable = "10" - everything works fine
If the Form Variable = "Between 10 And 12" - it tells me the criteria is too complex
If I paste the exact text from the variable ie "Between 10 And 12" into the query it works fine

Is this a limitation that means I need to create the whole SQL select statement from code ?

Thanks in advance.
If I need to create the SQL code I will use MS Examples http://support.microsoft.com/kb/136062 to save anyone retyping how to do it here....unless you can advise of a better method..
Sep 11 '07 #1
Share this Question
Share on Google+
5 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Two things ...

First is the variable a string or a number data type and is the field its checking a text or a number?

Secondly can we see how you are passing this value to the query. If you are using VBA can we see the code.
Sep 13 '07 #2

P: 3
Thanks for the response
Following are the details of my code etc.

NB: The criteria is being used on a number field of the query

I have a form with an option group that updates the value of a text box on change of selection via VBA code as follows:
Private Sub VarCustomGrp_AfterUpdate()
If VarCustomGrp.Value = "1" Then
VarCustFilter = "10"
Else:
If VarCustomGrp.Value = "2" Then
VarCustFilter = "Between 14 And 30"
Else:
VarCustFilter = "Between 0 And 30"
End If
End If
End Sub
I then use the following detail in the query criteria to use th value of the text box:
[Forms]![MainReport]![VarCustFilter]
If I select option 1 and the Text Box value is 10 my query qorks
If I select option 2 or 3 I get the following error:
"The expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may containtoo many complicated elements. Try simplifying the expression by assigning parts of the expressions to variablee"
If I copy and paste the value from the text box in question when option 2 or 3 are selected and past that value into the query instead of using the pointer to the text box it works fine.

Therefore It seems that using a variable in the criteria of the query cannot contain anything but the value to be used as part of an expression and passing an expression (even a simple one) is not possible via this method.

I realise I could assign the value of 2 variables and create an expression in the criteria that uses the 2 variables (as basic values only) (eg "Between [Forms]![MainReport]![VarCustFilter1] And [Forms]![MainReport]![VarCustFilter2]") but I need to use the same method for another selection group that is more complex and may contain multiple between statements.

What I cant understand is why the more complex value is not accepted..it does not make logical sense..(to me...anyway)

I am hoping that I may have done something wrong, or there is another simple method to do what I desire but I have a feeling it is a software limitation that means I need to write the selest SQL statment myself, which currently I have no experience with and will make it hader for me to experiment so I would like to avoid this for the timebeing.

Again thanks for the response. I look forward to futher advice if possible.

PS Incase it helps here is the SQL of my Query. The criteria causing the problem is at the end in bold.
SELECT tlkpStkPurchHist.tryear, tlkpStkPurchHist.trmonth, tlkpStkPurchHist.trdate, tlkpStkPurchHist.ref, tlkpStkPurchHist.type, tlkpDrMaster.name, tlkpDrMaster.type, tlkpStkPurchHist.areaCode, tlkpStkPurchHist.stkcode, tlkpStkPurchHist.stkDesc, tlkpStkPurchHist.per, tlkpStkMaster.Category, tlkpStkMaster.catDesc, tlkpStkPurchHist.qty, tlkpStkPurchHist.netCost, tlkpStkPurchHist.sellPrice, tlkpStkPurchHist.netVal, tlkpStkPurchHist.Desc2, tlkpStkPurchHist.CustRef, tlkpStkPurchHist.OrderNo, tlkpStkPurchHist.LastCost, tlkpStkPurchHist.AveCost, tlkpStkPurchHist.drmst, tlkpStkPurchHist.crmst, tlkpStkPurchHist.drCode, tlkpStkPurchHist.crCode, tlkpStkPurchHist.netCost_signed, tlkpStkPurchHist.qty_moved
FROM tlkpStkMaster INNER JOIN (tlkpDrMaster INNER JOIN tlkpStkPurchHist ON tlkpDrMaster.drcode = tlkpStkPurchHist.drCode) ON tlkpStkMaster.stkcode = tlkpStkPurchHist.stkcode
WHERE (((tlkpStkPurchHist.tryear)=[Forms]![MainReport]![VarYear]) AND ((tlkpStkPurchHist.trmonth)=[Forms]![MainReport]![VarMonth]) AND ((tlkpStkPurchHist.trdate)>#1/1/2003#) AND ((tlkpStkPurchHist.type)="i" Or (tlkpStkPurchHist.type)="c") AND ((tlkpDrMaster.type)=[Forms]![MainReport]![VarCustFilter]));
Sep 14 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
OK your query won't work on "Between 14 And 30" because it would equate to "=Between 14 And 30" which doesn't make any sense. Try this instead.

Create a query or use your existing one. The contents won't matter as the following code will replace them. For this exercise I will call it query1 but change that to the name of your query.

Now change your code as follows:

Expand|Select|Wrap|Line Numbers
  1. Private Sub VarCustomGrp_AfterUpdate()
  2. Dim db As DAO.database
  3. Dim qdf As QueryDef
  4. Dim strQuery As String
  5.  
  6.  
  7.     Set db = CurrentDb
  8.     Set qdf = db.QueryDefs("Query10")
  9.  
  10.     strQuery = "SELECT tlkpStkPurchHist.tryear, tlkpStkPurchHist.trmonth, " & _
  11.         "tlkpStkPurchHist.trdate, tlkpStkPurchHist.ref, tlkpStkPurchHist.type, " & _
  12.         "tlkpDrMaster.name, tlkpDrMaster.type, tlkpStkPurchHist.areaCode, " & _
  13.         "tlkpStkPurchHist.stkcode, tlkpStkPurchHist.stkDesc, tlkpStkPurchHist.per, " & _
  14.         "tlkpStkMaster.Category, tlkpStkMaster.catDesc, tlkpStkPurchHist.qty, " & _
  15.         "tlkpStkPurchHist.netCost, tlkpStkPurchHist.sellPrice, tlkpStkPurchHist.netVal, " & _
  16.         "tlkpStkPurchHist.Desc2, tlkpStkPurchHist.CustRef, tlkpStkPurchHist.OrderNo, " & _
  17.         "tlkpStkPurchHist.LastCost, tlkpStkPurchHist.AveCost, tlkpStkPurchHist.drmst, " & _
  18.         "tlkpStkPurchHist.crmst, tlkpStkPurchHist.drCode, tlkpStkPurchHist.crCode, " & _
  19.         "tlkpStkPurchHist.netCost_signed, tlkpStkPurchHist.qty_moved " & _
  20.         "FROM tlkpStkMaster INNER JOIN (tlkpDrMaster INNER JOIN tlkpStkPurchHist " & _
  21.         "ON tlkpDrMaster.drcode = tlkpStkPurchHist.drCode) " & _
  22.         "ON tlkpStkMaster.stkcode = tlkpStkPurchHist.stkcode " & _
  23.         "WHERE (((tlkpStkPurchHist.tryear)=[Forms]![MainReport]![VarYear]) " & _
  24.         "AND ((tlkpStkPurchHist.trmonth)=[Forms]![MainReport]![VarMonth]) " & _
  25.         "AND ((tlkpStkPurchHist.trdate)>#1/1/2003#) " & _
  26.         "AND ((tlkpStkPurchHist.type)='i' Or (tlkpStkPurchHist.type)='c')"
  27.  
  28.     strQuery = strQuery & " AND ((tlkpDrMaster.type)"
  29.  
  30.     If Me!VarCustomGrp.Value = "1" Then
  31.         strQuery = strQuery & "=10"
  32.     ElseIf VarCustomGrp.Value = "2" Then
  33.         strQuery = strQuery & " Between 14 And 30"
  34.     Else
  35.         strQuery = strQuery & " Between 0 And 30"
  36.     End If
  37.  
  38.     strQuery = strQuery & "))"
  39.     Debug.Print strQuery
  40.     qdf.SQL = strQuery
  41.  
  42.     Set qdf = Nothing
  43.     Set db = Nothing
  44.  
  45. End Sub
  46.  
Sep 14 '07 #4

P: 3
Great Help.

Works perfectly except for the typo on making a query called Query1 when you call it Query10 in the code.

I will use this to consolidate several option group choices when I click on the report button rather than updating a vaue with each change of an option group.


Thank you very much for your assitance.
Sep 19 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Great Help.

Works perfectly except for the typo on making a query called Query1 when you call it Query10 in the code.

I will use this to consolidate several option group choices when I click on the report button rather than updating a vaue with each change of an option group.


Thank you very much for your assitance.
Glad its working for you. Sorry for the typo.

Mary
Sep 19 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.