Problem with Duane Hookom's Concatenate function on [Forms]![frmRec]!... 1

[PeterMAS]

I am concatenating Opening & closing times by Day of the week by Season into a text string using Duane Hookom's function: How to concatenate multiple child records into a single value

http://www.tek-tips.com/faqs.cfm?fid=4233

The requirements are rather complex so I need 5 queries to manipulate the data. In the WHERE clause of the first query I need to use:
WHERE (((tjnRecDhm.RecID)=[Forms]![frmRec]![RecID])
In the second and subsequent queries, this brings up the message boxes "Runtime error '3061': Too few parameters. Expected 1" Followed by "This expression is typed incorrectly or it is too complex to be evaluated......"

The queries are written correctly because if I change the where clause to: "WHERE (((tjnRecDhm.RecID)=1)" it works.

I need to concatenate only the current record. Is there a solution?

Here is the table:
RecID
SsnID = Season
DayAID = Day of the week (joined to Mon, etc.)
HmOpID = Opening time in hrs & mins (joined to 00:00, 00:05, etc.)
DayBID = Day of the week (joined to Mon, etc.)
HmClID = Closing time (joined to 00:05, 00:10, etc.)

The first query, qcnDhmSsn1No1, joins the start and finish days and the opening and closing times and inserts hyphens, e.g.:
RecID DayABCon HmOpClCon DayaID
1   Mon-Wed  10:00-15:00 1
1   Mon-Wed  16:00-23:00 1
1   Thu    Closed    4
1   Fri-Sun   10:00-15:00 6
1   Fri-Sun   16:00-23:00 6

SELECT tjnRecDhm.RecID, [Daya] & IIf([DayB]=[DayA],"","-" & [DayB]) AS DayABCon, [HrMinOpen] & ("-"+[HrMinClose]) AS HmOpClCon, tjnRecDhm.DayaID

FROM tlkHmOp INNER JOIN (tlkHmCl RIGHT JOIN (tlkDayA INNER JOIN (tlkDayB INNER JOIN tjnRecDhm ON tlkDayB.DaybID = tjnRecDhm.DaybID) ON tlkDayA.DayaID = tjnRecDhm.DayaID) ON tlkHmCl.HmClID = tjnRecDhm.HmClID) ON tlkHmOp.HmOpID = tjnRecDhm.HmOpID

WHERE (((tjnRecDhm.RecID)=1) AND ((tjnRecDhm.SsnID)=1))

ORDER BY tjnRecDhm.DayaID;

The second query, qcnDhmSsn1No2, concatenates the opening and closing times by days, e.g.:
RecID DayABCon HmCon         DayaID
1   Mon-Wed 10:00-15:00, 16:00-23:00 1
1   Thu    Closed          4
1   Fri-Sun  10:00-15:00, 16:00-23:00 5

SELECT DISTINCT qcnDhmSsn1No1.RecID, qcnDhmSsn1No1.DayABCon, Concatenate("SELECT [HmOpClCon] FROM qcnDhmSsn1No1 WHERE DayABCon =""" & [DayABCon] & """") AS HmCon, qcnDhmSsn1No1.DayaID

FROM qcnDhmSsn1No1

ORDER BY qcnDhmSsn1No1.DayaID;

The third query, qcnDhmSsn1No3, concatenates the start and finish days by the opening and closing times, e.g.:
RecID DayCon      HmCon
1   Mon-Wed, Fri-Sun 10:00-15:00, 16:00-23:00
1   Thu        Closed
Because this query concatenates on the opening and closing times, I can't put "WHERE (((tjnRecDhm.RecID)=[Forms]![frmRec]![RecID])" in the fifth/final query.

SELECT DISTINCT qcnDhmSsn1No2.RecID, Concatenate("SELECT [DayABCon] FROM qcnDhmSsn1No2 WHERE HmCon =""" & [HmCon] & """") AS DayCon, qcnDhmSsn1No2.HmCon

FROM qcnDhmSsn1No2;

The fourth query, qcnDhmSsn1No4, assembles the Days and Times into text strings, e.g.:
RecID DhmCon
1   Mon-Wed, Fri-Sun: 10:00-15:00, 16:00-23:00
1   Thu: Closed

SELECT qcnDhmSsn1No3.RecID, [DayCon] & ': ' & [HmCon] AS DhmCon

FROM qcnDhmSsn1No3;

The fifth query, qcnDhmSsn1No5, concatenates the Days/Times strings into a single string, e.g.:
RecID t2hTxt
1   Mon-Wed, Fri-Sun: 19:00-20:00, 10:00-18:00, Thu: Closed

SELECT DISTINCT qcnDhmSsn1No4.RecID, Concatenate("SELECT DhmCon FROM qcnDhmSsn1No4 WHERE RecID =" & Nz([RecID],0)) AS t2hTxt

FROM qcnDhmSsn1No4;

Thank you

 

[dhookom]

You could first try to set the data type of your parameter in the first query. If that doesn't resolve the issue, I would use a little DAO code to change the SQL property of the query to replace the reference to the control on the form with the actual value.

Duane
Hook'D on Access
MS Access MVP

 

[PeterMAS]

Thank you Duane.

Setting the data type of the parameter in the first query didn't work. So I took your second suggestion.

This thread had several suggestions: Changing Query Criteria via Code

I wrote a new query, qcnDhm, with no WHERE clause and then modified it with the following code and all the queries now function.

    Dim strSQL As String
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Set db = Application.CurrentDb
    Set qdf = db.QueryDefs("qcnDhmSsn1No1")

    strSQL = "SELECT * FROM qcnDhm WHERE (((tjnRecDhm.SsnID)=1) AND ((tjnRecDhm.RecID)=" & !! & "));"
    qdf.SQL = strSQL
    Set db = Nothing
    Set qdf = Nothing

The code I use will be slightly more involved than this because I have more than one set of queries. Thanks again.

 

[dhookom]

You might want to consider using the function at faq701-7433.

Duane
Hook'D on Access
MS Access MVP