Replies

Maximizing and then doing a “shift-close” did not help at all, but stretching the window to nearly-maximized size and then closing it did help.

Would it make any difference if the database is a base .mdb file or if it has been converted to a .mde file?

What I usually do if there are a lot of columns to be included in the formula is to set up the first couple of columns, and then copy and paste in the formula bar, changing column letters as necessary. Not as fancy as your idea, but for someone who is not used to functions and such it is a bit of an easier solution.

Given that your data might lie in individual cells, one row of data to one row of CSV output, you could try the following:

=CHAR(34)&A1&CHAR(34)&”,”&CHAR(34)&B1&CHAR(34)&”,”&CHAR(34)&C1&CHAR(34)

This would give you: “Alpha”,”Beta”,”Gamma”

I use something like this routinely to convert table-based data to formatted text data to upload into our mainframe for additional processing. I create a new sheet in the workbook, expand the above formula to account for all of the fields in each record, and then copy the formula down to encompass all of the data. I then save this second sheet as a CSV text file.

Slightly off-topic here but… Word has an option to Embed True Type fonts that allows a file to be opened up on another computer and retain the fonts used in the document, even if they are not resident on the other computer. The entire font can be embedded, or only those characters actually used in the document.

Is there an add-in or VBA module that can accomplish this for Excel?

Looking at the solutions offered so far, I’m surprised I havn’t seen anyone mention countif. This, and sumif, are a couple of my favourite ways of grouping data without having to sort or subtotal.

For the simplest counts, assuming cell B32, =countif(b1.b30,”name”) will give you a count of all items in the range matching criteria “name”. For a slightly more flexible count, you could substitute A32 for “name”, and then just type in the new criteria, rather than having to edit the formula every time you want to look at a different subset of data.

Each PC holds all the data for its user (no Exchange server involved), and there are no multiple users set up on the PCs, so it should be very easy to search for these files and have them included in the backups. Thanks.

You can have the report title be a formula that can list one or all of the counties selected. I’d use something similar to this:

“Report for Doctors in the following counties: “&[County.Select1]&Nz(“, “&[County.Select2])&Nz(“, “[County.Select3])…..

You will always have at least one county in the report. Since you can have anywhere up to 15 counties, expand this formula to the full 15 selections. The Nz function will return “, County#” if that county was selected, or a zero-length string if that county was not selected. You will end up with one continuous string that will only show the selected counties.

Now, how to get the counties selected? You could use a form with a checkbox beside each county’s name, to save a “Yes/No” choice to a table, and use this table as the source for your report title.

Clear as mud?

Switched to internet mode, and things are now the way I’m used to using them.

Thanks.

I would be tempted to have it “all in one”, except that compacting the database once it gets up to its full operating size becomes a real time-consuming chore. Either I do not use “compact on close”, or I would have to expect the database to never be compacted.

I have set up some databases that were “all in one”, since they were so small (and were not ever expected to get to an unmanageable size) that splitting was actually a less efficient way of working.

I would use a query. For the simplest solution, you could build a query with the state as the first field, then your desired “other” field/s next. Using totals, use “Group By” for the state, and “Count” for your other fields.

If this isn’t exactly what you need, the Crosstab Query Wizard is good at building similar types of tables.

To answer your questions, and perhaps add some additional clarification:

We are using Jet (4.0 I believe).

The database is split into a back-end (containing the main data table and a couple of supporting tables, and the macros necessary to load and update the data), and a front-end, which contains all the report-generating forms, queries, and reports. Front-to-back access is via linked tables, and both of the databases are located in the same directory.

To get the data into my file, I created a temp file on the originating machine, imported the data table into this temp file, and then sent the file to my PC across the office intranet. Once the file was on my drive, I imported the data table into my back-end file, replacing my test data.

I ran my first test at this point and got my first 90 second timing. The database was then compacted and retested, yielding another 90 second timing. To help my computer along, I rebooted it while preventing the network load up. Running only Explorer, Systray, and Access, I continued to get the poor timings.

Today we tried the same thing, but in reverse. We took my newer test data and sent it to my co-worker’s PC… and the timings were consistently in the 8 to 10 second range, just as they had been on my PC. Because of this, my first instinct is to say was that something became corrupted in the data table when it was transferred to my PC.

Instead of using the “Exit Application” switchboard command, I set the command to run a “quitter” macro instead. This macro contains one command only – Quit, with the option set to Exit. Click the button, and Access will close completely.

The printer that is being used now is not the same one that the reports were originally developed with, but all of the reports were reformatted once the database was copied to its new location. I havn’t updated my Office suite for any service releases, especially since I don’t have ready internet access at the office… I’ll have to see what our IT people can do.

Try: Tools / Options / Transition / Transition Formula Entry

Using the help button on the Options form states that this option:
“Converts formulas entered in Lotus 1-2-3 release 2.2 syntax to Microsoft Excel syntax, and makes names defined in Microsoft Excel behave like names defined in Lotus 1-2-3.”

Check the option box and type 1+2+3 into a cell and hit Enter, and Excel automatically insert a = into the formula and return a value of 6, instead of returning the undesired text string.

This is good for entering simple formulas, but it can also cause some undesired results. This is particularly true when keying in dates or other numerical-looking data that contain operator-type characters. Keying 3/7/01 results in 0.428571 instead of the date; 800-555-1212 yields -967.