[AccessD] How to control an Update Query

Borge Hansen pcs at azizaz.com
Mon Dec 20 09:34:51 CST 2004


I have a table and a query :

tblContactSource_Merged  ::1 to many::  qsystblContactSource

I am updating a memo field in the table with data from the query - one line
in the memofield for each related record in the query.
The query is sorted ascending on the UFN (Unique File Number) and the SCtgy
(text field).

This is how the update query is structured :

********
UPDATE tblContactSource_Merged INNER JOIN qsystblContactSource ON
tblContactSource_Merged.UFN = qsystblContactSource.UFN SET
tblContactSource_Merged.Source_Merged =
IIf(Len([tblContactSource_Merged].[Source_Merged])>0,[tblContactSource_Merge
d].[Source_Merged] & "
" & [qsystblContactSource].[SCtgy] & "  >>  " &
[qsystblContactSource].[SCtgySpecific] & "  >>  " &
[qsystblContactSource].[SCtgyDegree] & "  >>  " &
[qsystblContactSource].[SCtgyDegreeYear],[qsystblContactSource].[SCtgy] & "
>>  " & [qsystblContactSource].[SCtgySpecific] & "  >>  " &
[qsystblContactSource].[SCtgyDegree] & "  >>  " &
[qsystblContactSource].[SCtgyDegreeYear]), qsystblContactSource.SelectFlag =
Yes
WHERE (((qsystblContactSource.SelectFlag)=No));
********

It's being run as an action query (docmd.openquery "queryname")

I expected that the data from the query would be written into the memo field
in the order the query is sorted.

However, this is not happening.

So my question is : Is it possible, and if so hoouuuwwww?


PS
... the

& "
" &

holds the linebreak in the memofield being updated.

(As an aside, I've only found an action query to work when it comes to
inserting a linebreak like this.
I have yet to find a way to structure a linebreak in an SQL string in vba
code.
Is that possible?
I've tried vbCRLF - doesn't seem to work)


Thanks,
Borge




---
Outgoing mail is certified Virus Free by AVG Anti Virus System.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.799 / Virus Database: 543 - Release Date: 20/11/2004




More information about the AccessD mailing list