UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Access Query Using Chr(13) & Chr(10), Access 2010    
 
   
GaryG79
post Sep 13 2017, 04:06 AM
Post#1



Posts: 4
Joined: 13-September 17



I am trying to add data to a field in the main table of my database without over writing what is currently already in that field from another field in a new table that I have created.

My main table is called BUG and the new table I am using is called ZZZZZZZZZZZZZ_Gary.

Within the ZZZZZZZZZZZZZ_Gary table, there is a field called GG and I am wanting to add the data from this field into the details field of the BUG table without over writing what is already within this field.

I currently have the following written in an access update query.

"--------------------------------------------------" & Chr(13) & Chr(10) & Now() & " Blue Prism Read" & Chr(13) & Chr(10) & "--------------------------------------------------" & Chr(13) & Chr(10) & "[tblZZZZZZZZZZZZZ_Gary].[GG]," & Chr(13) & Chr(10) & "--------------------------------------------------" & Chr(13) & Chr(10) & [details]


I know how to do this for individual records however as I have over 2500 records to update, I was hoping to do this in one bulk however is this possible?

Many Thanks
Go to the top of the page
 
Doug Steele
post Sep 13 2017, 05:53 AM
Post#2


UtterAccess VIP
Posts: 21,439
Joined: 8-January 07
From: St. Catharines, ON (Canada)


welcome2UA.gif

Try making a copy of the table and running that SQL against the copy. You should find it will do what you're trying to do.

The only issue I can see would be if you wanted something other than Blue Prism Read on every record. If that's the case, you'll need to provide a little more detail about what you're trying to do.

--------------------
Go to the top of the page
 
penfold098
post Sep 13 2017, 07:39 AM
Post#3



Posts: 127
Joined: 5-March 14



With all due respect, I think you might want to consider moving your details field to a separate table that also includes BugID (FK) and a time stamp field. You have a 1 to many relationship between BUG table and the BugDetail table. Each time you want to add a detail associated with a bug record, create a new record in the BugDetail table.

This way each added detail is in its own record. Mistakes in individual entries can be easily deleted. The query can sort the details for each bug in decending order on the timestamp like you show in your post.

IMHO, trying to add multiple entries into a single field is dangerous.
Go to the top of the page
 
Doug Steele
post Sep 13 2017, 08:22 AM
Post#4


UtterAccess VIP
Posts: 21,439
Joined: 8-January 07
From: St. Catharines, ON (Canada)


penfold098's advice is sound.

BTW, I just noticed that there's an error in the SQL you presented. The quotes around [tblZZZZZZZZZZZZZ_Gary].[GG] are incorrect: they're going to give you that literal text, not the value from the table.

It should be:

CODE
"--------------------------------------------------" & Chr(13) & Chr(10) & Now() & " Blue Prism Read" & Chr(13) & Chr(10) & "--------------------------------------------------" & Chr(13) & Chr(10) & [tblZZZZZZZZZZZZZ_Gary].[GG] & Chr(13) & Chr(10) & "--------------------------------------------------" & Chr(13) & Chr(10) & [details]

--------------------
Go to the top of the page
 
GaryG79
post Sep 14 2017, 02:12 AM
Post#5



Posts: 4
Joined: 13-September 17



Many thanks for the replies, they are appreciated;

When I have tried that new code you have provided, I am getting the following pop up?

Enter Parameter Value

ZZZZZZZZZZZZZ_Gary.GG


I am unable to make any changes to the database as suggested above as this is not my databaase but have been asked to look and see if what I have asked above was possible.

I have ran similar queries before whereby I was adding the same text for each record I wanted updated, the only difference this time was that each record had different information in the GG field that needed to be added to the Details field in the main BUG table.

Thanks
Go to the top of the page
 
Doug Steele
post Sep 14 2017, 07:08 AM
Post#6


UtterAccess VIP
Posts: 21,439
Joined: 8-January 07
From: St. Catharines, ON (Canada)


It would help if you posted the entire SQL statement, not just one field!

I was assuming that your query had a JOIN of some sort between the two tables so that the query knew about table ZZZZZZZZZZZZZ_Gary. Sounds as though that may not be the case.

--------------------
Go to the top of the page
 
GaryG79
post Sep 18 2017, 02:20 AM
Post#7



Posts: 4
Joined: 13-September 17



Hi Doug,

I do have the BUG and ZZZZZZZZZZZZZ_Gary table joined. Within both tables there is a field named MPAN and this is where I have them joined.

I have selected Option 1 from this: Only include rows where the joined fields from both tables are equal.


The full SQL is below as requested.

UPDATE BUG INNER JOIN ZZZZZZZZZZZZZZZZZZZZZ ON BUG.mpan = ZZZZZZZZZZZZZZZZZZZZZ.MPAN SET BUG.date_follow_up = #9/19/2017#, BUG.resolution = "50_BP_Read_Return", BUG.details = "--------------------------------------------------" & Chr(13) & Chr(10) & Now() & Chr(13) & Chr(10) & "--------------------------------------------------" & Chr(13) & Chr(10) & " [tblZZZZZZZZZZZZZ_Gary] . [GG]," & Chr(13) & Chr(10) & "--------------------------------------------------" & Chr(13) & Chr(10) & [details]
WHERE (((BUG.mpan)="1012345932874"));


Many Thanks
Go to the top of the page
 
Doug Steele
post Sep 18 2017, 07:30 AM
Post#8


UtterAccess VIP
Posts: 21,439
Joined: 8-January 07
From: St. Catharines, ON (Canada)


What is [tblZZZZZZZZZZZZZ_Gary] supposed to be? The only tables your query knows about are those in the FROM clause: BUG and ZZZZZZZZZZZZZZZZZZZZZ.

--------------------
Go to the top of the page
 
GaryG79
post Sep 18 2017, 09:18 AM
Post#9



Posts: 4
Joined: 13-September 17



Realised that I have put tblZZZZZZZZZZZZZ_Gary instead of ZZZZZZZZZZZZZZZZZZZZZ within the SQL.

I have changed this now and it has worked.
This post has been edited by GaryG79: Sep 18 2017, 09:22 AM
Go to the top of the page
 
Doug Steele
post Sep 18 2017, 10:25 AM
Post#10


UtterAccess VIP
Posts: 21,439
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Excellent. Glad you got it working!

--------------------
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    18th November 2017 - 07:25 AM