Easily Copy & Paste query results (1 Viewer)

Gasman · Apr 13, 2019

Good morning all,

I am looking for advice so as to be able to copy and paste (as text if possible) the results of a query (shown below) into an email.

If it were Excel I could copy the relevant cells into the email, but do not want to transfer to Excel unless that is the only way.

I could do it via Debug.Print, but is there a tidier way please.?

I only need the last 3 columns as well.

TIA

essaytee · Apr 13, 2019

I assume you are not sending an Outlook email from within Access.

I've had a quick play around and can't copy multiple fields to the clipboard.

Edit: I could copy an entire row or entire column but not specific fields across rows and columns.

isladogs · Apr 13, 2019

You can definitely copy and paste query results into an email.
The attached screenshot shows both a complete query and then just two fields being pasted. The query name is included by default but can be deleted

I did both of these manually but I doubt code would be difficult.
To simplify the code you could make another query with just the columns required

essaytee · Apr 13, 2019

Yes, it's all about positioning of the mouse. Yes, I got it to work.

Gasman · Apr 13, 2019

Hi Colin & Steve,

As the site has been down (AFAIWA) I've been experimenting further.
I got as far as writing the query data to a text file, opening the text file and then substituting " for nothing manually.

Now I can see that a more judicious use of the mouse, will select all columns and rows.

However I still have a small problem.?
A field in the table is named ThirdParty as it can hold a Donor name or a Recipient Name.
I've tried renaming it in the query

Code:

SELECT Emails.CMS, Emails.Client, Emails.ThirdParty AS Donor, Sum(Emails.Amount) AS Donated, Calcrefund([CMS],[ThirdParty]) AS Refund
FROM Emails
WHERE (((Emails.Amount)>0))
GROUP BY Emails.CMS, Emails.Client, Emails.ThirdParty
HAVING (((Emails.CMS)=[Tempvars].[CMS]));

but it still shows up with a heading of 3rdParty

I even tried a query based on that query. in the QBE window, I see the field Donor, but when selected it still shows 3rdParty.:banghead:

If I can get the heading correct I can go with the simple query and mouse selection, else I'll stick with the textfile approach, as there I can write a line with whatever headings I want.

I just want to be able to highlight and copy and paste with minimum editing.

The paste is into the charity web system notes screen, not a standard email.

I would want something along the lines of

Code:

3rd Party	Donated	Refund
ABF	£500.00	£15.38
HFH	£139.97	£4.31
RBL	£487.98	£15.01
WGC	£400.00	£12.30

but with a heading of Donor instead of 3rdparty which is the caption for the field in the table as they will always be Donors.
TIA

isladogs · Apr 13, 2019

I can't replicate your issue with aliased query fields.
When I copy and paste into Outlook, the alias name is displayed.

Gasman · Apr 14, 2019

isladogs said:
I can't replicate your issue with aliased query fields.
When I copy and paste into Outlook, the alias name is displayed.

Morning Colin,

It appears I cannot override captioned fields.?

Not to worry, I'll use the text file approach now I have written it, and remember the copy and paste for the future.

Feel a little embarrassed to be honest, where the solution was just placing the mouse in the correct location.

Micron · Apr 14, 2019

It appears I cannot override captioned fields.?

Not if the alias/caption is at the table field level, AFAIK. Check the table field for a caption property. Wouldn't removing a table field caption solve the emailing issue?

If we're not supposed to be working in tables, does a table field caption serve any real purpose?

Gasman · Apr 14, 2019

They are. I do not want to remove them for the reason below.
I have a tendency to caption the fields to make form creation easier.
It has not come back to bite me, well at least up until now.

I found a reasonable workaround with the text file approach.
One simple substitute and I can copy and paste into the web page.

Micron said:
Not if the alias/caption is at the table field level, AFAIK. Check the table field for a caption property. Wouldn't removing a table field caption solve the emailing issue?

If we're not supposed to be working in tables, does a table field caption serve any real purpose?

isladogs · Apr 14, 2019

I hadn't realised you were using field captions at table level.
Now I'm somewhat perplexed.
Why set a caption if you later want to overwrite it?

Gasman · Apr 15, 2019

isladogs said:
I hadn't realised you were using field captions at table level.
Now I'm somewhat perplexed.
Why set a caption if you later want to overwrite it?

I have always set captions just so as to be able to create forms more easily, as that becomes the label for the control. TBH I *thought* this was good practice. I was not aware I could not override them.

I have also named fields in queries (usually fomulae/function) in the past.

I have not come upon this problem before, and just wanted "Donor" and not ThirdParty for this particular issue, as in this case it will always be Donors.

The textfile approach allows me this option, and having now written the code is quick to use. Learnt something new again.

isladogs · Apr 15, 2019

No idea if its good practice. Not something I've ever done to the best of my knowledge

Uncle Gizmo · Apr 15, 2019

I've noticed that later versions of MS Access have some new "helpful not" features when you create forms and things, where it grabs the caption for the label from all sorts of places, depending.

Some code I wrote to shift data from a datasheet into Excel, had to have three different ways of pulling the possible column names from the datasheet!

Sent from my SM-G925F using Tapatalk

Easily Copy & Paste query results (1 Viewer)

Gasman

Enthusiastic Amateur

Attachments

essaytee

Need a good one-liner.

isladogs

MVP / VIP

Attachments

essaytee

Need a good one-liner.

Gasman

Enthusiastic Amateur

isladogs

MVP / VIP

Gasman

Enthusiastic Amateur

Micron

AWF VIP

Gasman

Enthusiastic Amateur

isladogs

MVP / VIP

Gasman

Enthusiastic Amateur

isladogs

MVP / VIP

Uncle Gizmo

Nifty Access Guy

Similar threads

Users who are viewing this thread