Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

PeterMAS

Technical User
Apr 3, 2012
18
GT
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
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
 
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
 
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.
Code:
    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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top