Concatenate function from a Query, not a table

[Jared]

Hi,

I am using Duane Duane Hookom's concatenate function with great success when
concatenating data from a table.

I have a requirement to squeeze alot of data onto a single form.

Each time the concatenate function is called (6 times per record), it is
querying the entire table. To improve efficiency, I would like the
concatenate function to query a query. I cannot however overcome an error
when using the function of:

Too few parameters, expected 1.

I suspect I am not referencing a query correctly within the FROM statement
of the SQL code. Any help would be appreciated.

 

[Duane Hookom]

We need more information about the SQL and Concatenate() syntax. This error
is typical when a text field is used in the criteria without delimiting it
with quotes.

The Concatenate() function is bound to slow down your query significantly.
You may want to create a global database object rather than creating it in
each call to the function. Indexing will also be very important.

 

[Jared]

Hi Duane,

Thankyou in advance for your help.

This is one of the subqueries to feed the form's recordsource. The purpose
is to provide the user with vehicle allocation information per vehicle, for a
specific date. For this particular query, to summarise all the drop points
for a vehicle.

SELECT vehicle.vehicle_label,
makeusdate([Forms]![frmAllocation]![AllocationDateField]) AS currDate,
concatenate("
SELECT trips.trip_to AS TripTo
FROM ( vehicle LEFT JOIN trips ON vehicle.vehicle_label = trips.vehicle_fkey
)
WHERE ( vehicle.vehicle_label = '" & [vehicle_label] & "' ) AND (
trips.drop_date = #" & [currDate] & "# )
GROUP BY trips.trip_to, vehicle.vehicle_label ;
") AS Drops
FROM vehicle
ORDER BY vehicle.vehicle_label;

This code works but seems the very long way to do things. My idea was to
speed up the process by selecting FROM a query of the current days' trips
instead of the full trips table. As soon as I modify the FROM to a query
instead of the table "trips" I recieve the error.

I am open to all suggestions for improvement. Thankyou.

jared

 

[Duane Hookom]

Is this the SQL that causes the error? What is MakeUSDate()?

You can't used the derived column name "[CurrDate]" inside the Concatenate().

--
Duane Hookom
Microsoft Access MVP

Hi Duane,

Thankyou in advance for your help.

This is one of the subqueries to feed the form's recordsource. The purpose
is to provide the user with vehicle allocation information per vehicle, for a
specific date. For this particular query, to summarise all the drop points
for a vehicle.

SELECT vehicle.vehicle_label,
makeusdate([Forms]![frmAllocation]![AllocationDateField]) AS currDate,
concatenate("
SELECT trips.trip_to AS TripTo
FROM ( vehicle LEFT JOIN trips ON vehicle.vehicle_label = trips.vehicle_fkey
)
WHERE ( vehicle.vehicle_label = '" & [vehicle_label] & "' ) AND (
trips.drop_date = #" & [currDate] & "# )
GROUP BY trips.trip_to, vehicle.vehicle_label ;
") AS Drops
FROM vehicle
ORDER BY vehicle.vehicle_label;

This code works but seems the very long way to do things. My idea was to
speed up the process by selecting FROM a query of the current days' trips
instead of the full trips table. As soon as I modify the FROM to a query
instead of the table "trips" I recieve the error.

I am open to all suggestions for improvement. Thankyou.

jared

We need more information about the SQL and Concatenate() syntax. This error
is typical when a text field is used in the criteria without delimiting it
with quotes.

The Concatenate() function is bound to slow down your query significantly.
You may want to create a global database object rather than creating it in
each call to the function. Indexing will also be very important.

 

[Jared]

Hi Duane,

This SQL works perfectly - but is slow. It gives the error when I try to
substitue the table "trips" with a query ie "qryTodaysTripsOnly".

MakeUSDate() converts dd-mm-yyyy to mm-dd-yyyy, a problem I've commonly
encountered with pass through queries using Aussie regional settings.

The currDate derived field works in the code posted, but perhaps that's the
issue when trying to substitute a query for the table.
I will hard code the date and then reference the query to see if that is the
cause.

Is this the SQL that causes the error? What is MakeUSDate()?

You can't used the derived column name "[CurrDate]" inside the Concatenate().

--
Duane Hookom
Microsoft Access MVP

Hi Duane,

Thankyou in advance for your help.

This is one of the subqueries to feed the form's recordsource. The purpose
is to provide the user with vehicle allocation information per vehicle, for a
specific date. For this particular query, to summarise all the drop points
for a vehicle.

SELECT vehicle.vehicle_label,
makeusdate([Forms]![frmAllocation]![AllocationDateField]) AS currDate,
concatenate("
SELECT trips.trip_to AS TripTo
FROM ( vehicle LEFT JOIN trips ON vehicle.vehicle_label = trips.vehicle_fkey
)
WHERE ( vehicle.vehicle_label = '" & [vehicle_label] & "' ) AND (
trips.drop_date = #" & [currDate] & "# )
GROUP BY trips.trip_to, vehicle.vehicle_label ;
") AS Drops
FROM vehicle
ORDER BY vehicle.vehicle_label;

This code works but seems the very long way to do things. My idea was to
speed up the process by selecting FROM a query of the current days' trips
instead of the full trips table. As soon as I modify the FROM to a query
instead of the table "trips" I recieve the error.

I am open to all suggestions for improvement. Thankyou.

jared

We need more information about the SQL and Concatenate() syntax. This error
is typical when a text field is used in the criteria without delimiting it
with quotes.

The Concatenate() function is bound to slow down your query significantly.
You may want to create a global database object rather than creating it in
each call to the function. Indexing will also be very important.

--
Duane Hookom
Microsoft Access MVP


:

Hi,

I am using Duane Duane Hookom's concatenate function with great success when
concatenating data from a table.

I have a requirement to squeeze alot of data onto a single form.

Each time the concatenate function is called (6 times per record), it is
querying the entire table. To improve efficiency, I would like the
concatenate function to query a query. I cannot however overcome an error
when using the function of:

Too few parameters, expected 1.

I suspect I am not referencing a query correctly within the FROM statement
of the SQL code. Any help would be appreciated.

 

[Duane Hookom]

If you use the DAO code, you can create a global Database object that can be
used with each call to the function rather than creating and deleting the
object. Indexing would be very important.

--
Duane Hookom
Microsoft Access MVP

Hi Duane,

This SQL works perfectly - but is slow. It gives the error when I try to
substitue the table "trips" with a query ie "qryTodaysTripsOnly".

MakeUSDate() converts dd-mm-yyyy to mm-dd-yyyy, a problem I've commonly
encountered with pass through queries using Aussie regional settings.

The currDate derived field works in the code posted, but perhaps that's the
issue when trying to substitute a query for the table.
I will hard code the date and then reference the query to see if that is the
cause.

Is this the SQL that causes the error? What is MakeUSDate()?

You can't used the derived column name "[CurrDate]" inside the Concatenate().

--
Duane Hookom
Microsoft Access MVP

Hi Duane,

Thankyou in advance for your help.

This is one of the subqueries to feed the form's recordsource. The purpose
is to provide the user with vehicle allocation information per vehicle, for a
specific date. For this particular query, to summarise all the drop points
for a vehicle.

SELECT vehicle.vehicle_label,
makeusdate([Forms]![frmAllocation]![AllocationDateField]) AS currDate,
concatenate("
SELECT trips.trip_to AS TripTo
FROM ( vehicle LEFT JOIN trips ON vehicle.vehicle_label = trips.vehicle_fkey
)
WHERE ( vehicle.vehicle_label = '" & [vehicle_label] & "' ) AND (
trips.drop_date = #" & [currDate] & "# )
GROUP BY trips.trip_to, vehicle.vehicle_label ;
") AS Drops
FROM vehicle
ORDER BY vehicle.vehicle_label;

This code works but seems the very long way to do things. My idea was to
speed up the process by selecting FROM a query of the current days' trips
instead of the full trips table. As soon as I modify the FROM to a query
instead of the table "trips" I recieve the error.

I am open to all suggestions for improvement. Thankyou.

jared

:

We need more information about the SQL and Concatenate() syntax. This error
is typical when a text field is used in the criteria without delimiting it
with quotes.

The Concatenate() function is bound to slow down your query significantly.
You may want to create a global database object rather than creating it in
each call to the function. Indexing will also be very important.

--
Duane Hookom
Microsoft Access MVP


:

Hi,

I am using Duane Duane Hookom's concatenate function with great success when
concatenating data from a table.

I have a requirement to squeeze alot of data onto a single form.

Each time the concatenate function is called (6 times per record), it is
querying the entire table. To improve efficiency, I would like the
concatenate function to query a query. I cannot however overcome an error
when using the function of:

Too few parameters, expected 1.

I suspect I am not referencing a query correctly within the FROM statement
of the SQL code. Any help would be appreciated.