RE: Concatenate function from a Query, not a table
- From: Jared <Jared@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Sat, 30 May 2009 20:52:01 -0700
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" wrote:
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
"Jared" wrote:
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.
- Follow-Ups:
- RE: Concatenate function from a Query, not a table
- From: Duane Hookom
- RE: Concatenate function from a Query, not a table
- Prev by Date: Re: Using the OR criteria. Am I doing it right?
- Next by Date: RE: Can I split a string or use the split function in design view?
- Previous by thread: Can I split a string or use the split function in design view?
- Next by thread: RE: Concatenate function from a Query, not a table
- Index(es):
Relevant Pages
|
