I have a form asking a user what data they want to find, and they can enter data in only one box or all 4. Based on their selections I want to build a SQL stmt and create a query that will return that info. (I realize I need to put some more If..Else conditions in there to test what the first box is, right now if the person doesnt enter a state my sql will fail because it will already have an AND condition in it. But I was trying to get the simple version to work first.) It creates the query but when it hits the DoCmd.OpenQuery stmt it fails with error 2001 "you cancelled the previous operation". I am at a loss. In the other posts, I see people suggesting that this should be put into the query itself asking for criteria which is fine if it is only 1-3 items, beyond that it is about impossible to test for all the combinations of blank and filled in choices with Ands and Ors in the query criteria. I am open to a better way to do this.
MyRequest = "SELECT State,Vision,Status,Client FROM Joblist WHERE "
'the next 4 lines are from the form asking the user what info they are looking for, eventually this will include another 2 or 3 choices
If [Forms]![AskMeForm]![whichstate] > " " Then MyRequest = MyRequest & "STATE = JState"
If [Forms]![AskMeForm]![whichtype] > " " Then MyRequest = MyRequest & " AND TYPE = Jtype"
If [Forms]![AskMeForm]![eqvalue] > " " Then MyRequest = MyRequest & " AND EQCOST > JValue"
If [Forms]![AskMeForm]![whichclient] > " " Then MyRequest = MyRequest & " AND Client LIKE JClient"
Set qdf = db.CreateQueryDef("MytempQuery", MyRequest)
DoCmd.OpenQuery ("MytempQuery")