Ctrl+Enter in a text field

[gblack]

OK I have three separate fields call them [Field1] [Field2] and [Field3]

I want to concatenate them in such a way that Field1 is above Field2 and Feild2 is Above Field3 (for formatting reasons).

So instead of 1, 2, 3 it would look like
1
2
3

In MS Access you can hit Ctrl+Enter whilst in a text field to drop down one line and insert another string in the same field.

Access interprets Ctrl+Enter as a small square box when it pulls this up in a query. But I was wondering how I might add these three fields together in a memo field and format it so 3 is under 2 and 2 is under 1. (i.e. in similar fashion to using Ctrl+Enter)

Does anyone have any ideas?

 

[Jack Cowley]

I am not sure why you want to concatenate. Can't you just put your fields like this in your form or report:

Field1
Field2
Field3

Jack

 

[gblack]

Here's why

NO, I can't...

Access Reports do not work with anything else on the planet, so they are in a sense useless.

What I want to do is transfer the data from these three fields into one cell in an Excel spread sheet, formatted so each of these fields are laying on top of eachother. WHY?

Well, for several reasons. But I'll just tell you the first one:

I have three fields which break up an address:

Field1 Street number and Name
Field 2 Suite (if any)
Field 3 City, State and Zip.

Now if I only needed to use MS Access I would be fine. I could throw each field in the propper format using a form or report and that would be the end of it.

BUT...I need a bit more than that... which is why I'm writting this posts.

What I need... is to throw all of these fields together and have them stack on top of eachother in such a way that I can Export them to excel and not have to go into each individual cell in the spreadsheet and reformat the address.

This is why I need to do this.

Thanks,
Gary

 

[Rich]

 

[Jack Cowley]

I don't do Excel, but this function will 'stack' the fields:

Public Function StackFields(M, N, O) As String
StackFields = M & vbCrLf & N & vbCrLf & O
End Function

Jack

 

[gblack]

YEP

Yep thats more what I'm looking for...thanks!

 

[mission2java_78]

That was hillarious Pat.

BTW I happen to like using the access reporting tools...so they don't suck..you can whip up some pretty nifty reports in a matter of minutes. I've never come across a reporting tool that was that simple.

Jon

 

[R. Hicks]

In defense of Jack ......

If you would have only used the phrase "Export to Excel" in your original post you may have gotten the correct reply sooner.

RDH

 

[gblack]

Sorry

Sorry guys and Gals -about the attitude. I'll try not to let it happen again.

I was under a deadline (hence my weekend question) and was stressing hard. But no excuse, I shouldn't have been that way.

I already tried to post this but hit newtopic instead, so I apologized to the everyone on these boards as well (inadvertantly

Thanks for the help.
Gary

 

[Jack Cowley]

Gary -

Thanks for taking the time to send the apology. Your apology is accepted.

Jack

 

[mission2java_78]

VB front end to SQL Server back end.

I have no problem with access reporting tool...I think its very easy to use.

Jon

 

[gblack]

Ya the reporting tool is fine for certain things... if you don't need the report in any kind of usable format (beyond read only), or need to use the data from the report in any other way than the report is in.

The problem with Access Reports is that the data from the report is ALWAYS in final/read only format. So, no one can do anything but read and print an access report.

Often with certain types of data, analysts need to take bits and pieces of a report and throw them into a database of their own. In this case (which is the context I made the statement about Access Reports being worthless), Access Reports are worthless.

You can’t use an access report like you can an excel spreadsheet. At least I have never seen it used this way (and I have been using Access reporting tool for since 2.0).

So in cases where the data will need to be reused, Access reports have no functionality (even with excel, which I find silly).

BTW: Ease of use, does not always equate to great functionality. There are plenty of other reporting tools out there that maybe harder to use than Access (Actuate, Cognos, Humming Bird etc…), but that have far better usefulness from a business perspective.

I don't mean to get down on Access, I think its a great tool! But, it does have its limitations and I wouldn't go so far as to say the reporting aspect of it is fantastic. If I were to grade it, I'd give it a B.

Later,
G

 

[mission2java_78]

Agree....however nothing is perfect.

So I dont expect access or any tool for that matter to do everything we expect it to do. If you want to modify reports (which I have too to)..you may have to use crystal reports.

I know what you mean by users taking bits and pieces and access doesnt allow this unless you export to silly ol rtf format and play around with it from there. Doing this isn't so great either since you lose many things from the original access report.

Jon

 

[gblack]

Yep

I agree.

I wish MS would enable Access Reports to Transfer to Excel Spreadsheets (or even better, allow reports to transfer to excel with its grouping and formatting in tact).

If they did this Access would rule over almost anything.

 

[mission2java_78]

Re: Yep

They do...its called the excel object model..and its got the greatest flexibility.

Theres only one problem...you have to code it .

Jon

 

[gblack]

Coding every report kinda defeats the point, doesn't it?

 

[mission2java_78]

From my perspective no I make money that way...

Jon

 

[JimAhl]

Sorry if I am misunderstanding what you guys are trying to do, but there is an export button from the access report preview screen that will dump the report into excel. I am assuming you guys must know that, and you are trying to do something else.

Hope this helps...

 

[mission2java_78]

Yes we realize that...however the report isnt preformatted like it should be.

That is why we are speaking of the excel object model.

Jon

 

[JimAhl]

Why not just format the report as you need it in the spreadsheet? I do this all the time. I just leave the data on the report with basically no formatting, or space between your controls on the report. If you also need a formatted report, just make two, one with the formats for printing, and one without for exporting into excel.