DCount, A97 to A2K difference? (A2K)

Topic

New Reply

I have a couple of

Reply | Quote

Replies

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….)

Reply | Quote

I had a similar problem in A97 with a long text name I was using in an index, it would seemingly lose records from the subform (the text field was part of a main form). I found that users were putting carriage returns in the text field and this seemed to be causing all sorts of problems. Also my problem was with text strings approaching 255 characters, not the 128 (magic number) as you have.

What I did in the end was to use a autonumber field to identify the text field and to use this in the index instead, and all my problems went away.

Another problem I had with using a text field in an index (so long) was that I had about 4 tables that were using this as part of the index thereby making the index size very large indeed. It seems that one should try and keep the index size as small as you can.

HTH
Pat.

Reply | Quote

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…

Reply | Quote

If all you want to do is display the number of records in the subform, why are you using a DCount in the first place? You can open a recordsetclone on the subform, movelast, and get the recordcount. I use something like that in the Current event of subforms when I want to present that information but I don’t want to use the built in navigation tools.

Reply | Quote

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….

Reply | Quote

Yesterday it seems to me that somewhere I read that if you index a text field an index will only use the first 128 characters, but I’ve just spent half an hour looking for it and didn’t find it. In any event, that sounds like a possible link to the problem you are having. Rereading your post, it would probably be a better design to put some sort of numerical key and use that as your lookup, rather than using the text string as your key. Or have I misunderstood your post? One other question – what Service Release of Access 2000 are you using? I ask because if you don’t have at least SR1, there were numberous bugs in 2000. OK, I guess there is another question too. Did you have Access 2000 convert the 97 database, or did you do an import into a clean A2k database? Converted databases have a checkered history at best.

Reply | Quote

That’s one of the nice things about the recordsetclone–it reflects the available records in the recordset. You have to set the recordsetclone on the *subform*, then it will reflect only the records that are allowed by the master/child links.

I haven’t had any problems with the SP6 update to Jet 4.

I agree with Wendell though on that *long* string. It’s much more efficient to search on a numeric index.

Reply | Quote

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.

Reply | Quote