Form-based parameter query too complex?

[FinChase]

I have a stored query that requires two parameters. I built a form so that users can enter the parameters in text boxes and then run the query with a command button.



The problem I am encountering seems to stem from the type of parameter that we use. We use a "greater than" (>) symbol in front of the parameters. For example Textbox1 = >12/31/2014, and Textbox1 = >999. These parameters work fine when I enter them in Query Designer. The query also runs from the form when I "dumb" the criteria down to be just a straight date and just a single dollar amount, but that's not what I need. Is there a way I can get the form to pass the parameter correctly?

I am using Access 2010.

[ssanfu]

Post the SQL of the query.
What is the form name and control names?

[CJ_London]

Textbox1 = >12/31/2014

not sure of the context in your query but => will be rejected by query engine - you have to use >=

[FinChase]

not sure of the context in your query but => will be rejected by query engine - you have to use >=

Thanks for your reply. I misspoke: The criteria I am using is ">" not "=>"

Basically, I am just trying to figure out how to pass these parameters via form. They work fine when I use them in Query Designer but it appears they aren't passing from the form as I'm getting a run-time error. If I eliminate the extra parameter and use just 12/31/2014, for example, the query runs from the form, but is useless for my purposes.

[Micron]

What's the error message, "Too few parameters, expected: 2" or something like that? You should always post the error, otherwise we are just guessing....
With respect to 2007 that I use, if that's the message, it's because Jet cannot resolve a parameter that a query needs. In other words, you cannot just run a query in code that requires parameters. Only the database container can do that, which is why it works there. You would need to:
a) define a query object and pass parameters to it, or
b) construct the sql in code

but I will end here because I may be barking up the wrong tree, so to speak.

[FinChase]

Post the SQL of the query.
What is the form name and control names?

Here is the SQL:

SELECT DISTINCT Subscriber.Audit, Subscriber.TotalPaidAmt, Subscriber.MinPaidDt, Null AS SubmitDt, Subscriber.PolicyNum, Null AS Limits, Right([Subscriber]![BenSetCd],3) AS [Set], Subscriber.Diag01, Subscriber.State, Subscriber.ProvSt, Subscriber.ClaimBegDos, Subscriber.ClaimEndDos, Subscriber.SSN, Subscriber.MbrFstNm, Subscriber.MbrLstNm, Subscriber.RelCd
FROM Subscriber
WHERE (((Subscriber.TotalPaidAmt)=[Forms]![frmDetails]![txtMinAmt]) AND ((Subscriber.MinPaidDt)=[Forms]![frmDetails]![txtMinPaidDt]) AND ((Subscriber.PolicyNum) Not In ('4Q498')) AND ((Subscriber.Diag01) In ("27801","27800","27803","6492","V4586","V5351")));

[FinChase]

What's the error message, "Too few parameters, expected: 2" or something like that? You should always post the error, otherwise we are just guessing....
With respect to 2007 that I use, if that's the message, it's because Jet cannot resolve a parameter that a query needs. In other words, you cannot just run a query in code that requires parameters. Only the database container can do that, which is why it works there. You would need to:
a) define a query object and pass parameters to it, or
b) construct the sql in code

but I will end here because I may be barking up the wrong tree, so to speak.

This is the error:

Run-time error 3071
This expression is typed incorrectly or it is too complex to be evaluated.

[CJ_London]

this query seems to have changed from your original post - different error and not a hint of > - or is this where you are starting from and you want to include >??

QUOTE]Run-time error 3071
This expression is typed incorrectly or it is too complex to be evaluated.[/QUOTE]you often get this error returned where there are nulls in the data - because of this Right([Subscriber]![BenSetCd],3) I would check that BenSetCd is not null and at least 3 characters in length and that both your form controls are completed and with the correct datatype format.

if MinPaidDt is a date then you need to either use the cDate function

((Subscriber.MinPaidDt)=cDate([Forms]![frmDetails]![txtMinPaidDt]))

or surround with # and format in US style (mm/dd/yyyy)

[FinChase]

this query seems to have changed from your original post - different error and not a hint of > - or is this where you are starting from and you want to include >??

QUOTE]Run-time error 3071
This expression is typed incorrectly or it is too complex to be evaluated.

you often get this error returned where there are nulls in the data - because of this Right([Subscriber]![BenSetCd],3) I would check that BenSetCd is not null and at least 3 characters in length and that both your form controls are completed and with the correct datatype format.

if MinPaidDt is a date then you need to either use the cDate function

((Subscriber.MinPaidDt)=cDate([Forms]![frmDetails]![txtMinPaidDt]))

or surround with # and format in US style (mm/dd/yyyy)[/QUOTE]

Thank you for your response.

This is where I'm starting from, and I want to send parameters such as " >12/31/2014" for the MinPaidDt field and ">999" for the TotalPaidAmt field. I've tried formatting the parameter as >#12/31/2014# and I still get the error.

If I use the CDate function, would it need to be in the sql of the saved query, or do I need to build my entire query in SQL stored in the VBA code for the form so that I can have VBA format the variable correctly?

[CJ_London]

This is where I'm starting from, and I want to send parameters such as " >12/31/2014" for the MinPaidDt field and ">999" for the TotalPaidAmt field. I've tried formatting the parameter as >#12/31/2014# and I still get the error.

you cannot send > as a parameter or part of a parameter to a query. And formatting is just that, formatting, it does not affect the underlying value.

if you want to include the > then you need to build the query in VBA - along these lines

sqlStr="SELECT DISTINCT Subscriber.Audit, ... FROM Subscriber WHERE TotalPaidAmt " & [txtMinAmt] & " AND MinPaidDt " & [txtMinPaidDt] & " AND .... "

for dates you would enter as >#12/31/2014#

whilst debugging, recommend you add a line

debug.print sqlstr

and copy the result into a new query until you are happy it works as required

[FinChase]

you cannot send > as a parameter or part of a parameter to a query. And formatting is just that, formatting, it does not affect the underlying value.

if you want to include the > then you need to build the query in VBA - along these lines

sqlStr="SELECT DISTINCT Subscriber.Audit, ... FROM Subscriber WHERE TotalPaidAmt " & [txtMinAmt] & " AND MinPaidDt " & [txtMinPaidDt] & " AND .... "

for dates you would enter as >#12/31/2014#

whilst debugging, recommend you add a line

debug.print sqlstr

and copy the result into a new query until you are happy it works as required

Thanks for your response. It sounds like I won't be able to do what I hoped.