Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    TransferSpreadsheet/Apostrophies (2000 )

    I (access knowledge = 0) have a database originally written in 97 and converted a week ago to 2k (the os went 95 to 2k at the same time) that picks up data from .txt files into linked tables, runs select queries on them and transfers the data out to spreadsheets when I run a macro. All very simple. All calculations were done in the spreadsheets.

    I needed to duplicate one of the reports for a different region so I copied the spreadsheet, copied all the related queries and the macro, and then changed all the old links/references to the new set.

    and it worked. I changed my regional criteria in the queries and I was off running. Everything has been working fine for a couple of days. And then I thought it was about time I started doing more of the manipulation in access so I did some concatenation in a new field within one of the queries and tried exporting it and it had worked - or so I thought. All my text data is now turning up with apostrophies in front of it. I'm assuming this is some kind of data-type problem - numbers are coming through okay, it's just the text strings.

    but the problem is that I can't go back: I've rebuilt the original query from scratch and the apostrophies are there: I've tried rebuilding the queries and using different names for them in case it was an export specification thing. no go. I've even opened a new database and rebuilt the query. I get apostrophies. The only thing I haven't done is re-run the reports that the new one was based on - and that's only because I daren't loose their functionality.

    How do I get rid of these pesky apostrophies? Is this something simple that being a non-access person I have overlooked? Or am I further up the creek than any sane person would want to be?

    Please just tell me:

    a) that I'm a dunce
    [img]/forums/images/smilies/cool.gif[/img] that I should really learn how to use the search facility

    and c) what to do to get rid of those apostrophies.

    TIA
    Brooke

  2. #2
    Star Lounger
    Join Date
    Oct 2001
    Location
    Western Massachusetts, USA
    Posts
    99
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TransferSpreadsheet/Apostrophies (2000 )

    The first question would be whether your underlying data in the Table has apostrophies around the text values. If it does, then you need to eliminate it at that level. If that's the case then you could write a small routine that would get rid of them and your data would be fresh again.
    Paul

  3. #3
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TransferSpreadsheet/Apostrophies (2000 )

    no, unfortunately, the underlying data is the same. I call it from a datawarehouse once a day. this mornings reports were fine - it was only when i started playing this afternoon, using the same .txt files as per the morning, that the problem arose.

  4. #4
    Star Lounger
    Join Date
    Oct 2001
    Location
    Western Massachusetts, USA
    Posts
    99
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TransferSpreadsheet/Apostrophies (2000 )

    I'm going to run out of ideas quickly, but would it be possible to test the lengh of a string in the txt file against your data. That is Len(.txt file) compared to actual value. What I'm beating around the bush at is whether it's possible to imbed an apostrophe in the text file that isn't visible. If you can't do that, you can run an update query against your data that will get rid of the offending digits. To do that, you would create a query, add the query that has you data. Add the fields with the apostrophies to the field lines (don't include any fields that are not at issue). Turn the query into an Update query. Then on the Update to line add the following code
    Mid([FieldName],2,Len(FieldName)-2)
    Do this for each field in the query you wnat to change adjusting the FieldName to the appropriate Name. Then run the query. It should return just the values you need. Practice on a small amount of sample data first to make sure you understand the process. Then I would make backup queries of my data until you are satisfield that the process has performed properly. Good luck
    Paul

  5. #5
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TransferSpreadsheet/Apostrophies (2000 )

    I was about to follow your line of thinking and take the steps you advise. But I think you've missed a crucial point - the reports ran fine this morning. This afternoon using the same data source I have apostrophies falling out of every orifice known to medical science.

    I'll try your advice anyway because I'm beginning to panic - but I just wanted to let you know my thoughts so you could keep the grey matter warm.

    many thanks

    Brooke

  6. #6
    Star Lounger
    Join Date
    Oct 2001
    Location
    Western Massachusetts, USA
    Posts
    99
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TransferSpreadsheet/Apostrophies (2000 )

    Queries basically are a mirror of the underlying data. They don't have any permanence in and of themselves. Each time you run a query all the data is reformatted and refreshed. The problem started after you tried concatenating fields together in order to manipulate your data in Access and not in (Excel)? I'm not sure if you are working exclusively between Access and Excel or if the Reports you are referring to are in Access. It might help to know that, but regardless, you've managed to add apostrophies somehow to your data that you can't get rid of. If the Reports are in Excel then the problem may well lie somewhere between Access and Excel. If the Reports are in Access, then I'm more concerned about the condition of your underlying tables. Anyway, keep me posted. I don't know about warm grey matter, but I'm laid up with a pulled muscle in my back that I'm keeping warm(or else I'd be on a golf course some place trying to get a few extra rounds in).
    Paul

  7. #7
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TransferSpreadsheet/Apostrophies (2000 )

    like I said, my knowledge of access is limited. so my use of the term report was a bad idea and a major red herring.

    So here's the story: the data resides in a datawarehouse and we have a frontend to run queries - Business Objects. Unfortunately it's a single global instance so if you're not in the USA it runs exceedingly slowly. So slowly that I devised the Grand Master Plan of calling off several large queries from Business Objects and saving them as text files which are then fed into an access database as linked tables.

    This way I end up running queries on my laptop using Access as opposed to on a remote server using Business Objects which has no interest in talking to anything outside the state of CA, let alone the US, and everything - apart from the initial queries - runs an awful lot quicker.

    So I've got a database that has some linked tables in. I run some select queries via various macros that dump the results into the excel spreadsheets (the same conversion criteria as above apply) then do some calculations to pull the right data into the right places and finally there is some VBA to copy the sheets I really want to a new file that gets posted to a lotus notes database as what the field know as "daily reports" - therein my red herring to access users. sorry.

    So the Business Objects results, saved as text files, haven't changed. The reports aren't reports as anyone in this forum would think of them.

    Sorry to hear about the pulled muscle. I'm missing a few rounds too, albeit of a different nature, so I kind of know how you're feeling!

  8. #8
    Star Lounger
    Join Date
    Oct 2001
    Location
    Western Massachusetts, USA
    Posts
    99
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TransferSpreadsheet/Apostrophies (2000 )

    So it seem that you have, in effect, two problems. First, your text data continues to show up with the unwanted apostrophe's even though you've rebuilt all your queries, and second, you want to do some concatenating of fields before you send them to Excel without the apostrophe's. Some of these answers are probably already obvious but I'll repeat them. When you create a query from your 'linked tables' the apostrophe's show up automatically in each record of each text field? Yes/No If that's 'Yes', then somehow, the text files have added an apostrophe to the value of each record in each field. In MSWord, if you insert a carriage return you don't see the formatting behind the text, you just see the text starting on a new line(it's a small square I think). I'm beginning to think that somehow, you've done the same thing except that when you created the Select queries, that apostrophe that you probably used to concatenate things got written to the text file. That's one of the reasons I was wondering if you could determing the length of a field using the Len() function. If you insert Len("cat ") and the result is 3 then that's not the problem, but if the result is 5 then there are 2 unseen values lurking in the file. A carriage return/line feed will add two unseen characters to a string (MyString) but they will show up in the value for Len() if you run Len(MyString). If you still have the Concatenating string you used to manipulate you data I'd like to see that. I recently ran into the same issue when I was building an Excel report for our CFO. I'm about as literate in Excel as you feel in Access. I was able to get around it by reworking my concatenation, but it would help to see your's to see if I spot the same type of problem.
    Paul

  9. #9
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TransferSpreadsheet/Apostrophies (2000 )

    <hr>First, your text data continues to show up with the unwanted apostrophe's <hr>
    only when it gets to excel
    <hr>When you create a query from your 'linked tables' the apostrophe's show up automatically in each record of each text field? Yes/No <hr>
    no. the queries look fine in access. I'm convinced it's the transfer to excel that's the problem. At your suggestion, I have done the length thing. The strings in the access queries all seem to report the visible length of the string and therefore don't appear to me to hold any hidden characters. but bear in mind that i have stripped the problem fields out of the query and then deleted the query and then rebuilt it in the way i built it in the first case, and i'm still getting apostrophies. this makes me feel it's likely to be something like an export specification and data type problem, but I don't know enough to be sure.

    The bottom line is I'm a spreadsheet jockey, and you should never let a cowboy near something as sophisticated as access!

  10. #10
    Star Lounger
    Join Date
    Oct 2001
    Location
    Western Massachusetts, USA
    Posts
    99
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TransferSpreadsheet/Apostrophies (2000 )

    OK, if it's only when it gets to excel, post the concatentations you are using to manipulate the data. That may be where the problem lies. And it probably makes it less of a problem.
    Paul

  11. #11
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TransferSpreadsheet/Apostrophies (2000 )

    okay. prior to yesterday afternoon I had,in the access query:

    <table border=1><td>region</td><td>product line</td><td>revenue</td><td>uk</td><td>pants</td><td>1234</td></table>

    and in the spreadsheet i had

    <table border=1><td>region</td><td>product line</td><td>revenue</td><td>regmap</td><td>uk</td><td>pants</td><td>1234</td><td>=a1&b1</td></table>
    later, I tried performing the work of the fourth column shown here in the query,

    <table border=1><td>region</td><td>product line</td><td>revenue</td><td>regmap :[table1]![region]&[table1]![productline]</td><td>uk</td><td>pants</td><td>1234</td><td>ukpants</td></table>

    but on TransferSpreadsheet I got

    <table border=1><td>region</td><td>product line</td><td>revenue</td><td>regmap</td><td>'uk</td><td>'pants</td><td>1234</td><td>'ukpants</td></table>
    now, I have reverted to

    <table border=1><td>region</td><td>product line</td><td>revenue</td><td>uk</td><td>pants</td><td>1234</td></table>
    and in the spreadsheet i still get

    <table border=1><td>region</td><td>product line</td><td>revenue</td><td>'uk</td><td>'pants</td><td>1234</td></table>
    it's the fact that I can't get back to where I was that is the real killer.

  12. #12
    Star Lounger
    Join Date
    Oct 2001
    Location
    Western Massachusetts, USA
    Posts
    99
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TransferSpreadsheet/Apostrophies (2000 )

    Ugggg! That's me hitting a blank wall. I've created a query with your data plus some additional and used the Tools....Analyze With Excel, and the DoCmd TransferSpreadsheet Methods to look at the data and nothing added any apostrophe's. It probably won't make any difference, but could you post the SQL from the query. And could you confirm that you are using the DoCmd.TransferSpreedsheet Method to move the information to the spreadsheet. I'm grasping at straws.
    If you name the query something different, and transfer it to the same spreadsheet, it should put it on a new sheet. If you do that, does the new sheet have the same problem as the original?
    One other thing, is it possible to get rid of the apostrophe's in your excel sheet manually Uggg!
    Last question, does the apostrophe have any significance in Excel or Excel formulas?

  13. #13
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TransferSpreadsheet/Apostrophies (2000 )

    This is the original

    SELECT Mainadj.[Product Line Name], Mainadj.[Region Name], Mainadj.[Sales Org Name], Mainadj.[Country Name], Sum(Mainadj.[Order Adjustment Amount]) AS [SumOfOrder Adjustment Amount], Sum(Mainadj.[Sales Adjustment Amount]) AS [SumOfSales Adjustment Amount]
    FROM Mainadj
    GROUP BY Mainadj.[Product Line Name], Mainadj.[Region Name], Mainadj.[Sales Org Name], Mainadj.[Country Name], Mainadj.[Product Business Unit Code], Mainadj.[Global Region Name], Mainadj.[Exec Sales Org Name]
    HAVING (((Mainadj.[Product Business Unit Code])="BNC") AND ((Mainadj.[Global Region Name])="EUROPE")) OR (((Mainadj.[Product Business Unit Code])="BNC") AND ((Mainadj.[Exec Sales Org Name])="EUROPE"));

    this is with the concatenation


    SELECT Mainadj.[Product Line Name], Mainadj.[Region Name], Mainadj.[Sales Org Name], Mainadj.[Country Name], Sum(Mainadj.[Order Adjustment Amount]) AS [SumOfOrder Adjustment Amount], Sum(Mainadj.[Sales Adjustment Amount]) AS [SumOfSales Adjustment Amount], [Mainadj]![Sales Group Name] & [Mainadj]![Product Line Name] AS Expr1
    FROM Mainadj
    GROUP BY Mainadj.[Product Line Name], Mainadj.[Region Name], Mainadj.[Sales Org Name], Mainadj.[Country Name], Mainadj.[Product Business Unit Code], Mainadj.[Global Region Name], Mainadj.[Exec Sales Org Name], [Mainadj]![Sales Group Name] & [Mainadj]![Product Line Name]
    HAVING (((Mainadj.[Product Business Unit Code])="BNC") AND ((Mainadj.[Global Region Name])="EUROPE")) OR (((Mainadj.[Product Business Unit Code])="BNC") AND ((Mainadj.[Exec Sales Org Name])="EUROPE"));

    Nothing complicated there to my knowledge. To transfer the data out I click on macro's, new, design, select the transferspreadsheet action and apply the arguments export, microsoft excel 8-9, aaqryNARadj, c:aaaQ2dailynumbers.xls,yes. aaqryNARadj is the name of the query that the sql above come from.

    renaming the query still gives me apostophies. But I've plucked up the courage and rerun the main suite of queries and they are not displaying this behaviour.

    Yes, I can strip the apostrophies if I have to, but it won't be a pretty job. the apostophies just stop the formulae working. I can rewrite everything. I just don't want to!

  14. #14
    Star Lounger
    Join Date
    Oct 2001
    Location
    Western Massachusetts, USA
    Posts
    99
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TransferSpreadsheet/Apostrophies (2000 )

    Nothing. Nothing. Nothing. The only thing I noticed in playing with this is that when I click on a text cell, the formula bar displays an apostrophe before the text value. If I put an apostrophe in front of the text value in the cell, the formula bar shows a quotation mark. Not likely to make the evening news. I used a TransferSpreadsheet macro but still couldn't get any problems to crop up. Sorry, but I'm a deadend. I'll poke around some other forums and see if I find anything but I wouldn't hold my breath.
    Regards,
    Paul

  15. #15
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: TransferSpreadsheet/Apostrophies (2000 )

    Brooke,

    I'm coming late to this thread, but what exactly is the problem with the apostrophe? That's what is generally used in Excel to denote a string as distinct from a number. You don't actually see the single quote when you look at the sheet, only if you examine the formula bar, and it is ignored for purposed of manipulating the strings. What problem is it causing you?

    If I have two cells with apostrophes denoting text and I have a calculated cell like A1 & B1, *no* apostrophes show up in the result displayed in the calculated cell, so what are you seeing that's different?

    BTW, this isn't really an Access issue, it's an Excel issue. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> Using TransferSpreadsheet just adds the details that native Excel may skate over.
    Charlotte

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •