[AccessD] How to control an Update Query

Hi Borge

That solution looks fine to me.

/gustav

>>> pcs at azizaz.com 22-12-2004 01:36:06 >>>
Hi Gustav,
Thanks for showing interest in this minor problem here, and also for
your
responses to my date field questions some weeks past....

Well, it was about how do you control the sorting when updating a
memofield
with several records from a source table, one line in the memofield for
each
record.

I gave it another thought and found a solution.

What I first did was :

UPDATE ztblMerge INNER JOIN qztblSource ON ztblMerge.UFN =
qztblSource.UFN
SET ztblMerge.MergeField =
IIf(Len([ztblMerge].[MergeField])>0,[ztblMerge].[MergeField] & "
" & [qztblSource].[TextField],[qztblSource].[TextField]),
qztblSource.SelectFlag = Yes
WHERE (((qztblSource.SelectFlag)=No));

...thinking that since the query qztblSource was sorted the way I
wanted,
then the records would be written into the merge memofield in the same
sorted way.
They don't!

Creating another select query based on the inner join above and doing
the
sort on the fields as required and then using this new select query as
source for the update does the trick:

qzUpdateztblMergeVersion2_Source:
SELECT ztblMerge.UFN, ztblMerge.MergeField, qztblSource.UFN,
qztblSource.TextField, qztblSource.SelectFlag
FROM qztblSource INNER JOIN ztblMerge ON qztblSource.UFN =
ztblMerge.UFN
ORDER BY qztblSource.UFN DESC , qztblSource.TextField DESC;

The final update query:
UPDATE qzUpdateztblMergeVersion2_Source AS Q SET Q.MergeField =
IIf(Len([Q].[MergeField])>0,[Q].[MergeField] & "
" & [Q].[TextField],[Q].[TextField]), Q.SelectFlag = Yes
WHERE (((Q.SelectFlag)=No));

Perhaps there is a simpler way?

Borge


----- Original Message ----- 
From: "Gustav Brock" <Gustav at cactus.dk>
To: <accessd at databaseadvisors.com>
Sent: Tuesday, December 21, 2004 8:36 PM
Subject: Re: [AccessD] How to control an Update Query


> Hi Borge
> Oh, they completely confused me.
> Now, what was your question?
> /gustav

> Gustav, they are just visual delimeters added to the data placed in
the
> memo
> field... pretty ugly ehh?
> Borge

> > Hi Borge
> > What are the ">>" signs supposed to do?
> > /gustav