Replies

Are companyA and CompanyAA and/or CompanyAAA related somehow? Are they branches of the same organization in different cities?

If not, you need to create (or re-create) the table in the form:

City / Company
Atlanta, CompanyA
Denver, CompanyAA
Miami, CompanyAAA

etc

Then

SELECT DISTINCT tblCityTable.City, tblCityTable.Company
FROM tblCityTable;

Will extract only the unique combinations of City and Company. As a shortcut, if you define the table with City and Company combined to form the Primary Key (before you start adding records to the file) then Access will not add duplicate city/company combinations – this means that the table will already be in the output form you need, if that helps.

If the companyA, CompanyAA, CompanyAAA are related, then

SELECT DISTINCT tblCityTable.Miami, tblCityTable.Atlanta, tblCityTable.Denver
FROM tblCityTable;

will extract unique combinations of CoA, CoAA, CoAAA – but if you have ‘missing’ entries (a null for ‘Miami’ in one row, and the right names for Denver and Atlanta, for instance) you will extract the Denver and Atlanta information twice – once with Miami, and once without. It will get you part of the way there, anyway. Again, if you define the combined Miami/Atlanta/Denver fields as a Primary key before you add data, Access will eliminate any duplicate entries on its own

Alan – as a general comment, Access will be a much better data repository than Excel. You will be able to produce different reports (manager, salesman, customer, etc) with come information highlighted or omitted much more easily, simply by defining different report layouts. You are also right that the data security and validation will be better. Using Excel as a database (even a flat-file) is a lot of work – it’s tough to coerce Excel to do the job. Access is built to do this kind of work – it may not be as immediately accessible as Excel, but within a short period of time the Access solution will be much more robust. Have you ever had somebody “sort” your Excel data, but not include the entire data range? I rest my case!

I am not sure what you are trying to do with the Word template – if it cannot be replaced by an Access report there are a couple of approaches that I have used (I am an Access newbie – but the loungers over there are just as helpful as the folks in this corner). If the data you want fits nicely into a query, you can set up a Word mail merge main document and specify the Access query as the data source – this works very well if you are using the same subset of data repeatedly, and you want better control of the formatting and document layout than you can get in Access – a periodic form letter is a good example. I have a database where the required subset of the data changes often – this could be set up as a parameter query in Access, but I decided to do it a little differently. I use some list boxes and option buttons to allow users to select the subset of the whole database as they require. I have a command button to export the query results to Word (or to Excel, in some cases), which creates a Word table that is useable as a mail merge data source in its own right. I do this instead of a direct link to a query to “freeze” the data so that a mistake in selecting the sub-categories of output doesn’t screw up a live mail merge. In addition, most of the people who use the data are (reasonably) familiar with Word, but are intimidated by Access (there seems to be an inverse relationship at work there ). The export to Excel works the same way – I can manipulate -say- lists of suppliers in Excel representing a sub-set of our whole database and then e-mail the file to someone who does not have security rights to the Access database. Again, the users are more familiar with Excel, and if they screw things up after it leaves me it’s their problem not mine (they just ask for another copy, anyway)!

I can’t help if you are committed to using “countif” – but if you are willing to use an array function, this will work:

Assuming your column of dates is in column A, extending from A2 to A78, and you want to be able to count the number that occur in May, June, November, etc – place a number from 1-12, representing the month (January – December) in cell A80. In cell B80, enter the formula:

=SUM(IF(MONTH(A$2:A$78) = A80,1,0))

This will be an array formula, and must be entered by hitting ctrl-shift-enter. Excel will insert braces around the formula to indicate that it is an array formula. If you need to see the count for more than one month at a time, drag to copy the formula down the B column, and then put in the months you need. If required, you can have twelve copies of the formula and show the count for all twelve months at once. The referenced range for the formula (A2:A78) can be changed either by editing the formula bar or by dragging an outline – just remember to enter the formula by ctrl-shift-enter.

An alternate formula woudl be:

=SUM((MONTH(A$2:A$78) = A81)*1)

This might evaluate faster, but I doubt that the difference would be noticeable

If you are dealing with more than one year you can edit the criteria to use an “AND” function to test for the value in the range to be more than a particular starting date and less than an ending date, as well – unless you want to aggregate May 02 with May 01

The easy way is to use two cells, putting the text in one and the linked value in the other, and then format the text as right-justified and the value as left-justified. When printed or displayed it will look “pretty close” to a single caption for a report.

The alternative that you were actually asking for is to concatenate two text strings, one with the phrase you want (“Sales Report as of…”) and the other with the text value corresponding to the date recorded in the other file. This uses the TEXT function to convert a value to a text string, in the required format. Try the formula below:

=”Monthly Report of Sales, as at “&TEXT([Book2]Sheet1!$B$3,”dd mmmm, yyyy”).

The on-line help (or the formula builder) can give you some insight into how the TEXT function works, or post back here for more discussion.

Might the difference be in the unicode character set and data storage in A2K and up, compared to ascii in A97 and below?

Louise

I think I understand what you are saying. In any database in Inner Join is where a query has to find a match in both tables to return a record. An Outer Join allows records to be returned from one table or the other without a matching record in the other table. Your situation is one of these – you want to return a “Studies” record even when there is no matching Payment record (perhaps even particularly when there is no matching payment record ). Don’t feel bad about not knowing this, by the way – it is part of the Access learning curve

I have made up a little pretend database that includes similar tables and information, just so I could get a screenshot of what you have to do (I hope it attaches and shows up properly).

In access, go to the query grid where you created the join between your “Studies” table and your “Payments” table. If you right-click on the join line between them you will get a small dialog giving you the choice of “Join Properties” or “Delete” (don’t pick delete). when you select Join Proerties it will bring up the dialog box pictured (this is in A2K – the details will be different depending on what version you are in.

There will be three choices for the type of join – the top is the most common – an Inner Join where you need a match in both tables. The bottom two choices are the two Outer Joins. The ‘left’ and ‘right’ refers to which table the query will take records from without a match in the other one – fortunately, Access plops the table names in the description so you don’t have to worry about the left and right distinctions. Note on the query grid the join has an arrow pointing towards the table that must be matched – that’s because I took this screen shot after I had set the join up as an Outer Join, and then went back and did it again, to take the purty picture!

The query will now return a record for all studies, showing blanks (nulls) for payment amount and date for any “Study” where there is no payment record, instead of just ignoring them.

I hope that helps!

Louise – I don’t quite understand when you say that ‘most of the information comes from a table, but two columns come from a query.’ I assume that you mean a query takes most of its data from a single table, and adds two more fields from another table(s).

In any event, I would look at the query you are using if you only return records when there is data in those fields joined from other tables. This sounds like an “Inner Join” in Access – where a record is only returned if there is matching data in both tables. If you want to include records where “table A” has a value and there is no corresponding value in “table B” you should set this as an “Outer Join” (either left or right) – look at the ‘join properties’ on the query grid.

Inserting ‘dummy information’ is a pain – you have to remember to do it, which implies that YOU have to be on top of what information should / should not be available (that’s the computer’s job, not yours) and then you have to remember to delete it from the database, and probably from the Excel file you are generating. I would strongly recommend against it.

Wendell and Charlotte – thank you both for your comments. The “Company” table is set up with an autonumber key field – but through inexperience I sometimes used the numeric key field and sometimes the text ‘name’ field for my links to other tables or in queries

I am not really excited about going back and changing all the links to be consistent – but that’s probably the right way to correct this. I am hopeful that it will improve performance overall – at least that way I can rationalize it as ‘an improvement’ rather than ‘a correction’ – but that’s just a little mind-game!

Wendell asked about the file conversion. This file is a front end converted from A97 to A2K – the office is standardized on A97, but my home computer is on A2K. In order to work on the file at home I convert between the two, and periodically copy and convert the back end just so I have up-to-date data to test it against, although the back-end file now includes examples of just about every type of data element I am likely to encounter so keeping my converted back end up to date is no longer as big an issue.

The short answer is that I didn’t know how to do that until I read your message!

Will the recordsetclone reflect the master-child link between the form and the subform? I’ll have a whack at it over the weekend – thanks for the tip.

I had a workaround of including a =count(*) textbox on the subform header, and *that* works, but I think there is a more fundamental problem: it seems that Access is very skittish about indexing (I think that is were the problem lies) fields with (text) data elements longer than 128 characters.

Everything works in my application, if I construct reports to not include this particular record (I have a bunch of pre-set reports that show companies by location, etc – by reporting on locations that don’t include this “long name” outfit I can see that everything else works). As soon as I include them on a report (which is sorted by company name, Access freezes up.

Coincident with inputting this long company name, I upgraded Jet 4.0 with sp6 – I don’t know if the problems occurred before or after I upgraded, and can’t recall whether I had this company entered before the upgrade, and if I ran reports that would include them at that time. I am quite mystified, since the whole thing works properly in A97….

Why not store the relevent customer numbers in a table, then count the number of records returned when that table is queried with the active customer number as a criterion? if no records are returned, then the active customer is not one of the specified customers where special action must be taken. If the query returns a result, then the active customer is in the set of ‘special customers’ and you can display the massage and take appropriate actions.

This should also make it easier to maintain the special customer numbers, I would think.

If you want to hard-code the numbers into VBA as you are showing, I think the syntax for the “OR” operator is to make disjunctions between two expressions – you would have to use something like:

If(((((custID=123 OR custID=9000) OR custID=2700) OR custID=6300) OR _
custID=5000)  OR custID=770) then...

(I may have the brackets wrong – this is on-the-fly) The intent is to chain the “OR” operators so that “(test1 OR test2) is presented as a single truth value (call it R1) to the next “OR” as “(R1 OR test3)” – call that R2 – and present it to the next “OR” as “(R2 OR test4)” and so on…

It would probably be easier to set this up as a CASE statement:

Select Case CustID
Case 123
   Msg routine
Case 9000
   Msg routine
...
Case Else
  continue
End Select

Ken – you must have something in the database to indicate the expiration of memberships already – otherwise you wouldn’t know when to delete those non-renewals (and send out the reminders in advance, etc). Just include a criterion in your label-printing routine that “LapseDate >= Date().” If you only know that someone’s membership is expiring because you have a start date and a term then the expression has to change a bit, but not the underlying idea. Then when they renew, you just have to update your expiry date (or start date + term data)

Hi Pat – thanks for your comments.

I have moved things around a bit on the form and subform and included a textbox with an “=count(*)” data source in the subform header. Fortunately the two other places in the database where I indicate the number of subform records are not running into trouble because the criteria fields they refer to are much shorter – 20-30 characters at most, although the field is set as maximum 255 characters – I still don’t think it will be a problem since this was a special case due to the legal description of the joint venture we were dealing with.

I had thought of going back and using an autonumber field for the criteria – but changing the form and subform layout was simpler than tearing up the existing database to put in a new key field…

Well – I’ve narrowed down the problem – although that isn’t getting me any closer to the solution. The problem is definitely in the text box that uses the DCount function to determine how many proposals have been presented by each company (or their affiliates). Everything works without a hitch if I remove that text box.

With the text box (and the DCount) in place the problem seems to be related to the length of the data in the company name field (used in the DCount criteria) for that record. Yesterday (when I was having problems) it was 134 characters long. The function works properly when I edit the company name down to 125 characters – but that doesn’t match the name we are using on the contract – and it would be kind of nice if our records matched our contracts!

As I noted yesterday, this problem only shows up in A2K: A97 seems fine with the long(er) name. Does anyone know if there is a limit on how long the criteria string can be in DCount, and if this limit was reduced between A97 and A2K?

Does anyone have any ideas for a workaround? Right now I am thinking of writing a new query based on the query that supplies data to the subform, with the criteria to be matched in the query (which would be the same as the master link field for the subform), and then just displaying a text box with a DCount of all the records returned in that query. Is there a way to count the number of records on a subform directly, other than by including a count function in a subform header or footer (I don’t really want to waste the space on the subform, although that is another option if I can juggle the layout a bit….)

If 123.45 should format as 0012345, what should 123.00 format as?

If it should be displayed as 0012300, then the solution is just to multiply your values by 100 and display the result, with the format set to “0000000”.

If it should display as 0000123 then you will probably have to write a little vba function to return only the digits. To do this you could either multiply by 10 until the current value of the variable equals the truncated value of the variable, or treat the variable as a string and extract the characters one at a time, ignoring the “.” character. I can’t imagine why this would be the desired outcome, so I can’t say which is preferable.

In addition to the “Step by Step” Access book, I would suggest “Running Microsoft Access 2000” (I know you asked about XP – I don’t know if the XP version is out yet) by John Viescas. It is very well presented and thorough. I have been looking for Helen’s book, as she does a good job on the Woody’s Access Watch page, but I have not yet found a copy.

Based on my experience with “Access 2000 Unleashed,” I would recommend against it – it may serve a particular need, but I have not been impressed: far too much time and attention to Access as a front end to SQL Server or Oracle, and too little on using Access for a personal or departmental / work group database system – but perhaps that just reflects my bias.