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
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
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
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.
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
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
Thank you
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
SQL:
SELECT tjnRecDhm.RecID, [Daya] & IIf([DayB]=[DayA],"","-" & [DayB]) AS DayABCon, [HrMinOpen] & ("-"+[HrMinClose]) AS HmOpClCon, tjnRecDhm.DayaID
SQL:
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
SQL:
WHERE (((tjnRecDhm.RecID)=1) AND ((tjnRecDhm.SsnID)=1))
SQL:
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
SQL:
SELECT DISTINCT qcnDhmSsn1No1.RecID, qcnDhmSsn1No1.DayABCon, Concatenate("SELECT [HmOpClCon] FROM qcnDhmSsn1No1 WHERE DayABCon =""" & [DayABCon] & """") AS HmCon, qcnDhmSsn1No1.DayaID
SQL:
FROM qcnDhmSsn1No1
SQL:
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.
SQL:
SELECT DISTINCT qcnDhmSsn1No2.RecID, Concatenate("SELECT [DayABCon] FROM qcnDhmSsn1No2 WHERE HmCon =""" & [HmCon] & """") AS DayCon, qcnDhmSsn1No2.HmCon
SQL:
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
SQL:
SELECT qcnDhmSsn1No3.RecID, [DayCon] & ': ' & [HmCon] AS DhmCon
SQL:
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
SQL:
SELECT DISTINCT qcnDhmSsn1No4.RecID, Concatenate("SELECT DhmCon FROM qcnDhmSsn1No4 WHERE RecID =" & Nz([RecID],0)) AS t2hTxt
SQL:
FROM qcnDhmSsn1No4;
Thank you