Passing Complex? Criteria From A Form Variable to a Query

[RacerX2000]

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..

[MMcCarthy]

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.

[RacerX2000]

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]));

[MMcCarthy]

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:

Private Sub VarCustomGrp_AfterUpdate()
Dim db As DAO.database
Dim qdf As QueryDef
Dim strQuery As String
 
 
    Set db = CurrentDb
    Set qdf = db.QueryDefs("Query10")
 
    strQuery = "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')"
 
    strQuery = strQuery & " AND ((tlkpDrMaster.type)"
 
    If Me!VarCustomGrp.Value = "1" Then
        strQuery = strQuery & "=10"
    ElseIf VarCustomGrp.Value = "2" Then
        strQuery = strQuery & " Between 14 And 30"
    Else
        strQuery = strQuery & " Between 0 And 30"
    End If
 
    strQuery = strQuery & "))"
    Debug.Print strQuery
    qdf.SQL = strQuery
 
    Set qdf = Nothing
    Set db = Nothing
 
End Sub
 

[RacerX2000]

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.

[MMcCarthy]

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