Replies

Ah! I just figured it out!

The shorter codes had two blank spaces at the end. I thought I had originally checked this, but evidently I didn’t do it twice 😉

Thanks for reading my hopeless pleas and for your advice 😉

Cecilia 🙂

Cool, most definitely worth a try–Can you give me an idea which event or object to put this code under? I’m not at all an Excel developper…. 😉

Hi All!

I am hoping someone else has run into this at some time or another and can verify with me that either there is a good solution or it’s just a MS feature that I need to find some way to build around. I’m usually in the Access or Word forums, so I’m a bit new to Excel, so please be gentle 😉

What I have is a set of Excel workbooks that someone else built, and they have lots of nice VLookups in them. The last sheet in the workbook is always a data sheet. What these guys are doing is getting the data from an Access query, putting it in Excel (save to Excel), then manually copying it over (after manipulating it a bit). I’d like to have a more automated way of doing this, but here’s my problem:
The workbooks work just fine if you copy data into the worksheet, but if you use either MS query or a linked table, all of a sudden the workbooks randomly don’t work. I can assure you that it’s the exact same data whether you copy it or query it. What is the strangest part of it is the randomness with which the Vlookup doesn’t work.

I have a series of codes that are being looked up:
Code Hours
Adm01 12
Adm03 15
AdmLv01 42
App02 7
Here is the lookup code:
+IF(ISERROR(VLOOKUP(‘Exams’!B8,’Access Data’!A:C,3,FALSE)),”0.00″,VLOOKUP(‘Exams’!B8,’Access Data’!A:C,3,FALSE))

When I use MS Query and return the data, the lookups for Adm03 and AdmLv01 work just fine. Adm01 and App02 do not work at all (they return 0.00 instead of the hours). If I go in and re-type Adm01, all of a sudden it works. (There are about 142 codes; about 70% work just fine, about 30% don’t work until they’re re-typed.) If the whole thing didn’t work, I could say it might be a format problem, but it’s so random that it can’t be that.

Ideally I would like to link from Excel to Access so that the data is live, but the link randomly doesn’t work in the same way. I’ve already checked to make sure the sort is correct, so it’s not that.

Is this a MS feature, or is there something I can do to fix it?

TIA!

Cecilia 🙂

LOL! Don’t I know it. Luckily most of my stuff is compiled, so it won’t even open. But I need to convert everything to have an option for the XP users.

Unfortunately, I’m only a cog in the wheel. (Part of me thinks that they _want_ my stuff to die, so they can point fingers, but bottom line my stuff is needed by the people I report to. Uggggh.) My making noise does nothing. In the gov’t, it all falls on deaf ears.

This is going to be _so_ much fun.

Cecilia

Thanks Charlotte, that makes sense.

Can you point to any references that will make it “easier” for me to convert from DAO to ADO when the time comes?

Unfortunately I also don’t have the luxury of time. My users (or at least half of them–ugggh) will be getting XP at the exact same time I do, so I will have no time to do all this conversion, or to even learn how to do it. Uggggh.

Cecilia 🙂

LOL! I guess your client DOES have better access than I do. I can’t do anything server side at all. I’m not even allowed to install software on my pc, regardless of its use, and apparently under XP they’re trying to strip away my ability to write code. They’ve already destroyed our ability to set references or use ActiveX. The chances that they’ll let me try anything like what you’re suggesting are less than zero.

Basically, I’m trying to give the guys here in budgeting a button to press to produce their reports, so I don’t think MDSE is going to cut it. I’m trying to sum my rows now, so I can reduce them a bit. If so, I should be able to get them below 100,000 per quarter, therefore 400,000 per year.

Thanks again, Wendell, for your advice! I have so much work to do….hee hee….

Cecilia 🙂

Hi–

I haven’t actually done this myself, but there is a query property for TopValues. You can set the property in the design view of the query, then base your report on the query. Check it out in the help.

You can use the TopValues property to return a specified number of records or a percentage of records that meet the criteria you specify. For example, you might want to return the top 10 values or the top 25 percent of all values in a field.

Note The TopValues property applies only to append, make-table, and select queries.

Setting

The TopValues property setting is an Integer value representing the exact number of values to return or a number followed by a percent sign (%) representing the percentage of records to return. For example, to return the top 10 values, set the TopValues property to 10; to return the top 10 percent of values, set the TopValues property to 10%.

THere’s also an example in there.

Good luck!

Cecilia 🙂

Hi Wendell,

Basically what your client does is what I have to do. Security, security, security! LOL

I’m taking your advice at this moment, but I’m not very hopeful. I’ve sent out a request for a few stored procedures (this is a transactional db, so I’m thinking that if I can just pass it a start and end date & therefore only hit records, say, for the current quarter or full year (the db contains 5 or 6 years at this point), it should speed things up? More info: when I pulled down a quarter’s worth of data, I got about 350,000 records.). We’ll see if anyone wants to help me. _Then_ I’ll worry about learning how to do this 😉

My user population for this db is small, it’s only to build reports for budgeting, so even if potentially there could be 5 or more using it, probably never more than 1 or 2 at a time. What is the MSDE? (Aside from another acronym I need to learn 😉

Thank you so much for all of your advice here!

Cecilia 🙂

Hi Wendell!

Thanks for the resource list! I think I’m going to put off the XP issues for a bit and worry about them after I’ve had time to look at some of those books.

I had thought that you could set a reference in 97 to allow you to use ADO, but I guess this is not true? Hmmmm.

So I will focus on the problem of efficiently transferring the data from SQL-Server to Access 97.

I wish I could do stored procedures, but unfortnately, I only have read access to the tables and it would take a tornado to get those others to do anything with the server at all. So basically, I’m stuck with what I’ve got, transferring the data from SQL Server to Access.

I had been told at one point that Passthru queries were more efficient than linked tables, due to interference with the Jet engine. Is this not still a belief? If there’s any truth in it, is there a preferred way of doing it?

Thanks!

Thanks for the advice, Charlotte 🙂

I’m not afraid of writing code, but bottom line is that I’ll never know how many columns I need. I supposed I could just populate a datasheet with the maximum number of columns and in code have them display or not display, but I think the code there would not be worth all the effort expended.

Format won’t work, because it makes my numeric fields into strings. I need them to stay numbered, and to have them aligned as numbers. I was hoping I could somehow modify the format property of the query (or qdf in this case), but I can’t seem to find anything.

Ah, well. Back to thinking up more things that are just impossible 😉

Cecilia 🙂

Normally, I’d agree. But in this case the user is going to use the data, probably cut & paste a bit, even put it in Excel. And there are several queries (all crosstabs that may have different numbers of columns based on the options that the user picks) that I’m creating on the fly, which is why I didn’t go with forms (datasheets) or reports (which don’t work well for what I’m doing anyway).

It really isn’t horribly important, since he’s probably going to export to excel anyway, he can format it there, but my job is to make his life easier, so I wanted to have it all formatted. If it can’t be done, that’s fine 😉 If you have any suggestions for displaying crosstabs that are created on the fly, I’d love to hear! 🙂

Thanks!

Cecilia 🙂

Use the Before Update event of the first combo to set the recordsource of the second combo.

Hey, now THAT’s a cool idea!

Here’s what I did:

I created a batch file in a temp location. The batch file copies my db to the temp location with another name. Then using FileCopy, I am able to copy the copy of hte db from the temp location to the actual location.

The reason I did it this way is that the batch file runs on its own, so there’s no error handling and such. I error handled the temp location as much as I could, so I hope there are no errors. But for the user save location, I needed the error handling on the FileCopy command, as well as its ability to use long file names (and names like “My Documents” which aren’t read properly in a batch file.

You get some awesome cool points for that creative solution!

Thanks again,

Cecilia 🙂

Thanks, but this db was supposed to simplify a multi-file process (which included batch files) into a single-file distribution. If I could write the batch command _within_ the db, then it would work, but as long as I need a separate batch file, I’m better off without it.

Cecilia 🙂

Wise words. But unfortunately, it was my fault: I thought, if I can attach it, why can’t I save it? Still doesn’t make sense to me, but he _does_ understand the meaning of “It’s a Microsoft Feature.” I’ve taught him well enough 🙂

Thanks for all of your help!!!

Cecilia 🙂