Calling form value from a query: "expression is too complex"

[mcucino]

Hi all,



I am running into an issue calling a parameter value from an open form from a query. It works for single values like #7/15/2017# but if I create a string like #4/29/2017# Or #5/27/2017# Or #7/1/2017# Or #7/22/2017# it will not work and I get the "expression to complex" error. However, if I copy and paste the string value into the query criteria, it works fine. Any suggestions?

[aytee111]

Can you explain further? "calling a parameter value from an open form from a query" doesn't really explain it. Are you typing that string into an input box or a textbox on the form or what? Post the SQL for the query too.

[June7]

I expect it doesn't work because you have input a single string, not multiple discrete values. The query dynamic parameter is a single input and what it receives from the textbox is a single string.

I NEVER use dynamic parameterized queries. I prefer VBA to construct WHERE CONDITION of OpenForm/OpenReport command or set form Filter property.

The simplest approach for user is probably a multi-select listbox. Review http://allenbrowne.com/ser-50.html

[mcucino]

Sure. I have a main menu form. There is a field called "current week" with a default value of the most recent saturday. Based on this date, when the form is opened, the form puts together a string of dates that include the last saturday of each month (according to this company's fiscal calendar) and the current week concatenated with "# or #".

Main Menu (Menuf) looks like:
Customer: Customer
Currentweek: 7/22/2017
FY: FY18
DateString: #4/29/2017# Or #5/27/2017# Or #7/1/2017# Or #7/22/2017#
Code that generates date string by looping through a list of customer specific pre-entered dates:
Private Sub UpdateString()
DoCmd.SetWarnings False
DoCmd.OpenQuery "qrydates2mjc"
DoCmd.SetWarnings True
Dim db As Database
Dim rs As Recordset
Dim s As String


Set db = CurrentDb
Set rs = db.OpenRecordset("tblcurrentDatesMJC")


rs.MoveFirst
s = "#" & rs!calenddate
rs.MoveNext

While Not rs.EOF
rs.Edit
s = s & "# Or #" & rs!calenddate
rs.Update
rs.MoveNext
Wend
s = s & "# Or #" & Me.CurrentWeek & "#"
Me.DateString = s

rs.Close
db.Close

Set db = Nothing
Set rs = Nothing
End Sub

Query SQL (i put the functional parameters in green and the problem one in red):
SELECT dbo_tblRASdata.sku, dbo_tblRASdata.Loc, dbo_tblRASdata.CustomerID, dbo_tblRASdata.FY, Max(dbo_tblRASdata.retail) AS MaxOfretail, Sum(dbo_tblRASdata.mtd_sales) AS SumOfmtd_sales
FROM dbo_tblRASdata
WHERE (((dbo_tblRASdata.wk_ending)=[forms]![MenuF]![DateString]))
GROUP BY dbo_tblRASdata.sku, dbo_tblRASdata.Loc, dbo_tblRASdata.CustomerID, dbo_tblRASdata.FY
HAVING (((dbo_tblRASdata.CustomerID)=[forms]![menuf]![customer]) AND ((dbo_tblRASdata.FY)=[forms]![menuf]![CurrentFiscalYear]))
ORDER BY dbo_tblRASdata.sku, dbo_tblRASdata.Loc;

[aytee111]

You copy and paste that string into query design, not into the SQL. Once you have copied it to the query design, go into the SQL view and see what is actually there, is will be very different.

The SQL that you posted, is that for query "qrydates2mjc"? If so, you will need to recreate the WHERE portion of it in SQL, using the same logic that is used when those dates are created. You can either make it into a string and use DoCmd.RunSQL "...." or you could change the actual query itself with a new SQL string.

[mcucino]

It's not every saturday, it's a specific set of saturdays designated in another table based on their fiscal calendar. It changes depending on the week (it looks for saturdays that are the last saturday of their fiscal month as designated on the table and only uses those with dates less than currentweek).

I'm not following you... Are you saying I should copy the reference into the SQL as opposed to design view? I don't understand how that would affect the outcome. All I know is that if I copy the actual text into either the SQL or the query design view criteria, it works fine. It just doesn't work as a variable referencing the form text.

[mcucino]

I just don't understand why it works fine when I type it in to the query design view criteria exactly letter for letter the same as in the text box (as a string, not discrete values).

[June7]

As I said, the textbox is populated with a single long string which is then passed to the query dynamic parameter. Won't do what you want.

In query design view you are not typing into a single parameter. You are typing discrete criteria with OR operators. Type and save query object then reopen and see how Access rearranges all the OR criteria onto separate lines.

[mcucino]

Gotcha. So what's the best solution? Can I run an on load event that preselects items from a multiselect listbox?

I am just trying to make this more user friendly. Whoever made this database told the end user they have to manually open each query in design view, look up the dates in the table, and type them in with the proper syntax. As far as I'm concerned, that's like having my mechanic telling me "i fixed your car- if you want to start it, just open the hood and touch these two wires together"!

[Micron]

you cannot say Where someField = theDate OR thisDate OR thatDate OR...

It has to be like Where someField = theDate OR someField = thisDate OR someField = thatDate OR ... - get it?
Not likely that you will want to build a string like this in some control. Even if it was properly constructed, it probably wouldn't work as June7 says (though it would be interesting to know if it would work if built properly). If the dates are in a table that someone can peek at, why isn't that table part of the query? If it's because there are many more dates than you want (i.e. it's not a table of last Saturday dates) then one way would be to build a query that returns only those dates, then make that query part of the main query's table structure. Then you will only get records for the dates that apply. Or have a table that you wipe clear then append the dates to. However, since I could also construct the sql in code as June7 suggested, that would be my preferred method, based on my understanding of the situation. One reason might be that as the year progresses, I imagine the date list gets ever longer and such a query might be too complex for you to create and maintain, and I have no idea how you're generating Saturday only dates.

FWIW - please use code tags around your code and indent as necessary. It alleviates problems and makes code easier to read. This s = "#" & rs!calenddate probably should be s = "#" & rs!calenddate & "#"

Note:
- air code - use on a copy of your db first.
- I might have dropped a bracket or similar defining character. I presume the dates are valid (e.g. 01/01/2017) and can be wrapped in date delimiters (#) in code
- you can use line continuation characters to build sql if you prefer - I rarely use them

Private Sub UpdateString()
'dimension your variables first and specify the type library unless you know it is better not to do so
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strWherePart As String, strSql As String, strEnd As String, strFY As String
Dim lngCust As Long

On Error GoTo errHandler
lngCust = Me.customer
dteFY = Me.CurrentFiscalYear

Set db = CurrentDb
Set rs = db.OpenRecordset("tblcurrentDatesMJC")
strWherePart = "WHERE (((dbo_tblRASdata.wk_ending)=#"

'if this is an action query...
db.Execute "qrydates2mjc", dbFailOnError

If Not rs.EOF And Not rs.BOF Then 'ensure the rs has records before trying to movefirst
  rst.MoveFirst
  Do While Not rs.EOF
    strWherePart = strWherePart & rs!calenddate & "# OR (dbo_tblRASdata.wk_ending) = "
    rs.MoveNext
  Loop
End If

strSql = "SELECT dbo_tblRASdata.sku, dbo_tblRASdata.Loc, dbo_tblRASdata.CustomerID, dbo_tblRASdata.FY, "
strSql = strSql & "Max(dbo_tblRASdata.retail) AS MaxOfretail, Sum(dbo_tblRASdata.mtd_sales) AS SumOfmtd_sales "
strSql = strSql & "FROM dbo_tblRASdata "
strWherePart = Left(strWherePart(Len strWherePart-32)) & "))" '-32 to strip all from the last OR & add closing brackets
strEnd = "GROUP BY dbo_tblRASdata.sku, dbo_tblRASdata.Loc, dbo_tblRASdata.CustomerID, dbo_tblRASdata.FY "
strEnd = strEnd & "HAVING (((dbo_tblRASdata.CustomerID)=" & lngCust & ") AND ((dbo_tblRASdata.FY)=#" & strFY & "#)) "
strEnd = strEnd & "ORDER BY dbo_tblRASdata.sku, dbo_tblRASdata.Loc;"

strSql = strSql & strWherePart & strEnd
debug.print strSql 'check sql in Immediate Window; copy/paste to new query to test; comment this line out if OK
'what now? Open a report or form based on this sql?

exitHere:
rs.Close
db.Close
Set db = Nothing
Set rs = Nothing
Exit Sub

errHandler:
msgbox "Error " & err.Number & ": " & err.Description
Resume exitHere

End Sub

[mcucino]

Thank you all for the help! I got it working by stringing the full where clause together in a hidden field on the form and using docmd.runsql to make another table from the query. Definitely not the most glamorous solution but it's still a hell of a lot better than telling the user to manually edit each query

[June7]

Do not recommend repeatedly making and deleting table. This is a db design change. If you want to use a 'temp' table to hold records only for the duration of procedure, make the table permanent but the records are temporary.

Did you consider advice in post 3 for using listbox and review the link referenced?