Concatenate Problems

[AccessIM]

Hello Everyone-

I am having problems figuring out how to use concatenate. I read and tried
to use Duane Hookum's sample but I'm afraid I continue to get hung up on the
code and am obviously doing something wrong.

I have a table (tblRouteLoad) that contains records of customer orders and
includes Customer, Date and Route as fields. This table currently has around
50,000 records. I also have a second table (tblRouteType) that includes
RoutePrefix and RouteDay as fields.

I have created one query based on tblRouteLoad that pulls all fields plus
creates a RoutePrefix field using the Mid function on the Route field. I
created a second query using the query 1 and tblRouteType with a relationship
between the RoutePrefix fields that returns the data below:

Customer Route RoutePrefix RouteDay
610017 4159 4 Wed
610017 6159 6 Fri

I would like to get to the final output being:

Customer RouteDay
610017 Weds Fri

If anyone could shed some light on using concatenate I would greatly
appreciate it.

Thank You.

 

[John Spencer]

Assumption Customer is a text field

SELECT Distinct Customer
, Concatenate("SELECT DISTINCT RouteDay FROM theQuery WHERE Customer='" &
[Customer] & "'"," ") as RouteDay
FROM TheQuery

You will probably have a problem with the days being in order, but unless you
have a day of the week number (1-7) you will have to live with that problem.

Of course,

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Software-Matters via AccessMonster.com]

Hi,
I think you will need a function for this to work. So, in a module write a
public function that concatenates the days and then pull that in to a query
of customers. The function will be something like this:

Public Function ConcatDays(Customer) As String
dim rs as recordset
ConcatDays = ""
Set rs = db.OpenRecordset("SELECT RouteDay FROM Customers WHERE Customer = '"
& Customer & "'"
if not rs.eof then
rs.movefirst
do until rs.eof
ConcatDays = ConcatDays & rs!RouteDay & " " &
rs.movenext
loop
end if

End Function

Now start a new query with the Customer field in it and then call the
function in a new field.

 

[AccessIM]

Hi John-

Thank you for the quick response. I copied your suggestion and added the
query name where needed so that it looks like this:

SELECT Distinct Customer, Concatenate("SELECT DISTINCT RouteDay FROM
qrySelectionDays2 WHERE Customer='" & [Customer] & "'"," ") as RouteDay
FROM qrySelectionDays2

However, when I run the query, I receive a run-time error that says "No
value given for one or more required parameters". When I click debug, it
brings up the basConcatenate module from Duane's sample with the following
area highlighted:

rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic


Thank you.

Assumption Customer is a text field

SELECT Distinct Customer
, Concatenate("SELECT DISTINCT RouteDay FROM theQuery WHERE Customer='" &
[Customer] & "'"," ") as RouteDay
FROM TheQuery

You will probably have a problem with the days being in order, but unless you
have a day of the week number (1-7) you will have to live with that problem.

Of course,

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hello Everyone-

I am having problems figuring out how to use concatenate. I read and tried
to use Duane Hookum's sample but I'm afraid I continue to get hung up on the
code and am obviously doing something wrong.

I have a table (tblRouteLoad) that contains records of customer orders and
includes Customer, Date and Route as fields. This table currently has around
50,000 records. I also have a second table (tblRouteType) that includes
RoutePrefix and RouteDay as fields.

I have created one query based on tblRouteLoad that pulls all fields plus
creates a RoutePrefix field using the Mid function on the Route field. I
created a second query using the query 1 and tblRouteType with a relationship
between the RoutePrefix fields that returns the data below:

Customer Route RoutePrefix RouteDay
610017 4159 4 Wed
610017 6159 6 Fri

I would like to get to the final output being:

Customer RouteDay
610017 Weds Fri

If anyone could shed some light on using concatenate I would greatly
appreciate it.

Thank You.

 

[Duane Hookom]

Does qrySelectionDays2 have a criteria from a form or parameter?
What is the SQL of qrySelectionDays2?

--
Duane Hookom
Microsoft Access MVP

Hi John-

Thank you for the quick response. I copied your suggestion and added the
query name where needed so that it looks like this:

SELECT Distinct Customer, Concatenate("SELECT DISTINCT RouteDay FROM
qrySelectionDays2 WHERE Customer='" & [Customer] & "'"," ") as RouteDay
FROM qrySelectionDays2

However, when I run the query, I receive a run-time error that says "No
value given for one or more required parameters". When I click debug, it
brings up the basConcatenate module from Duane's sample with the following
area highlighted:

rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic


Thank you.

Assumption Customer is a text field

SELECT Distinct Customer
, Concatenate("SELECT DISTINCT RouteDay FROM theQuery WHERE Customer='" &
[Customer] & "'"," ") as RouteDay
FROM TheQuery

You will probably have a problem with the days being in order, but unless you
have a day of the week number (1-7) you will have to live with that problem.

Of course,

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Hello Everyone-

I am having problems figuring out how to use concatenate. I read and tried
to use Duane Hookum's sample but I'm afraid I continue to get hung up on the
code and am obviously doing something wrong.

I have a table (tblRouteLoad) that contains records of customer orders and
includes Customer, Date and Route as fields. This table currently has around
50,000 records. I also have a second table (tblRouteType) that includes
RoutePrefix and RouteDay as fields.

I have created one query based on tblRouteLoad that pulls all fields plus
creates a RoutePrefix field using the Mid function on the Route field. I
created a second query using the query 1 and tblRouteType with a relationship
between the RoutePrefix fields that returns the data below:

Customer Route RoutePrefix RouteDay
610017 4159 4 Wed
610017 6159 6 Fri

I would like to get to the final output being:

Customer RouteDay
610017 Weds Fri

If anyone could shed some light on using concatenate I would greatly
appreciate it.

Thank You.

 

[Duane Hookom]

The OP is using a function. There shouldn't be a need to write a new function
every time you need to concatenate some child values.

 

[John Spencer]

As a guess the OP is using an MDB and did not uncomment the code section for
mdb and comment out the section of ADPs.

AccessIM,
Read through the concatenate code and note the lines you need to comment out -
add a ' in front of the line(s) - and which lines you need to uncomment -
remove the ' at the beginning of the line.

I believe that you want the lines for DAO and not the lines for ADO

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[AccessIM]

I tried this and now I am getting a compile error (user-defined type not
defined) and it stops at the line below:

Dim db As DAO.Database

 

[AccessIM]

I tried this and now I am getting a compile error (User-defined type not
defined) and it stops at the following line: Dim db As DAO.Database

 

[AccessIM]

No. qrySelectionDays2 itself does not but the query that it is based on
(qrySelectionDays1) does get criteria from a form. Here is the SQL for
qrySelectionDays1:

SELECT tblRouteLoad.Customer, tblRouteLoad.Date, tblRouteLoad.Route,
Mid([Route],1,1) AS RoutePrefix
FROM tblRouteLoad
GROUP BY tblRouteLoad.Customer, tblRouteLoad.Date, tblRouteLoad.Route,
Mid([Route],1,1)
HAVING
(((tblRouteLoad.Customer)=[forms]![frmReportCriteriaMenu]![EnterCustomers])
AND ((tblRouteLoad.Date) Between
[forms]![frmReportCriteriaMenu]![BeginningDate] And
[forms]![frmReportCriteriaMenu]![EndingDate]))
ORDER BY tblRouteLoad.Customer;

Does qrySelectionDays2 have a criteria from a form or parameter?
What is the SQL of qrySelectionDays2?

--
Duane Hookom
Microsoft Access MVP

Hi John-

Thank you for the quick response. I copied your suggestion and added the
query name where needed so that it looks like this:

SELECT Distinct Customer, Concatenate("SELECT DISTINCT RouteDay FROM
qrySelectionDays2 WHERE Customer='" & [Customer] & "'"," ") as RouteDay
FROM qrySelectionDays2

However, when I run the query, I receive a run-time error that says "No
value given for one or more required parameters". When I click debug, it
brings up the basConcatenate module from Duane's sample with the following
area highlighted:

rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic


Thank you.

Assumption Customer is a text field

SELECT Distinct Customer
, Concatenate("SELECT DISTINCT RouteDay FROM theQuery WHERE Customer='" &
[Customer] & "'"," ") as RouteDay
FROM TheQuery

You will probably have a problem with the days being in order, but unless you
have a day of the week number (1-7) you will have to live with that problem.

Of course,

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

AccessIM wrote:
Hello Everyone-

I am having problems figuring out how to use concatenate. I read and tried
to use Duane Hookum's sample but I'm afraid I continue to get hung up on the
code and am obviously doing something wrong.

I have a table (tblRouteLoad) that contains records of customer orders and
includes Customer, Date and Route as fields. This table currently has around
50,000 records. I also have a second table (tblRouteType) that includes
RoutePrefix and RouteDay as fields.

I have created one query based on tblRouteLoad that pulls all fields plus
creates a RoutePrefix field using the Mid function on the Route field. I
created a second query using the query 1 and tblRouteType with a relationship
between the RoutePrefix fields that returns the data below:

Customer Route RoutePrefix RouteDay
610017 4159 4 Wed
610017 6159 6 Fri

I would like to get to the final output being:

Customer RouteDay
610017 Weds Fri

If anyone could shed some light on using concatenate I would greatly
appreciate it.

Thank You.

 

[John Spencer]

Ok, it sounds as if you have not attached the reference library.

Open the VBA modules
== Select Tools: References
== Find Microsoft DAO 3.6 Object Library and check it
== Click the OK button
== Select Debug: Compile ... from the menu
If there are no errors in the code, the menu item will grey out and you should
be good to go. If there are errors in the code, then you should fix them if
you can.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[AccessIM]

Hi John-

You were right on with the suggestion about the reference library. However,
I am receiving a new error - Run Time Error 3061 Too Few Parameters Expected
3. When I click debug, it stops on the following line:

Set rs = db.OpenRecordset(pstrSQL)

When I googled this error, I read that it was the result of the parameters
in the query.

 

[Duane Hookom]

I expect your query parameters are the issue. You might want to look at the
modification from another thread at tek-tips
http://www.tek-tips.com/viewthread.cfm?qid=1548511&page=1.