Replies

I would add that, in addition to the other suggestions, one of the fields that you are crashing in the midst of updating is a rather large memo fld — that might do it. At least, that’s the sum total similarity of the repairs I’ve had to do so far —

> I would just like to say that this pleasant exchange of info sums up my conception of Woody

> One last thing: You’re getting a “1” from the code example because …

Thanks — I’ll try the information from this most recent post later, but right now, I can’t let the day end before making this confession:

Guess what I found in the “onformat” of the report pagefooter:

If is_form_open(“BouncePopReportLimits”) Then
nfo.Visible = True
Else
nfo.Visible = False
End If

IOW, the ” = iif()” controlsource that I was using was _still_ broken, it had nothing to do with whether I’d bogged down the memory, or I’d cured it by compacting the database — I had simply and merely, out of frustration, “invisibled” my error to the outside world — and even myself, the next morning, when I forgot all about it.
Aaauuugghh!!! — I hope I haven’t led people down the primrose path.
Sorry for all this, but thanks to you and Mark for all your enlightening information!

Pat

> ?IIf(True, MsgBox(“True”), MsgBox(“False”))

LOL! I see! It _does_ go through both actions. No “instant evaluations.”
So, as Mark Liquorman says, Access must resolve everything, first before the information is passed to the iif() function — your line of code above sure illustrates that!
It also demonstrated why — even though I tried a variation of
“iif(is_form_open(),” / “iif(is_form_open() = True,” / “iif(is_form_open() = -1”, that the last variation might not have worked, anyway — since the true from the iif() seems to return a “1” rather than a “-1”, which I’ve noticed happening before (I don’t know if was another “iif()” function that returned the “1”)
Still, it seems to be able to “self-recover” from the error condition if there are enough resources or the database is nicely compacted? because, what was breaking last night, is no longer breaking “publicly” like it was last night …
Thanks for all your input, I enjoyed running that code sample!
Pat

> To resolve this, you will need to create a custom function to essentially do what you want IIF to do.

Yes, I have a “flexi-title” function with a static that I’ve used in many places, and I’ve decided to just use that rather than picking up the information from a possibly open (or not) form.
Thanks very much for the interesting information!

Pat

> (2) A periodic repair and compact seems to do wonders in squashing flaky behavior.

I’m putting this first, because that was probably it, and not the memory usage. The very last thing I did last night was compact the database, from 15 megs to 2.5 approx. megs — I had been testing my “archive this table” part of the software

> (1) I believe both the true and false portions of an iif() statement are evaluated, regardless of the result of the logic evaluation. Therefore, if the form isn’t open, you may still get the #NAME? error because Access still tries to resolve [Forms]![FormName]![FieldName].

Hmm, I thought Access and other programs were optimized to skip over those conditions it does not meet — but, thinking along similar lines as yours, although not the same, I did try to reverse the order (iif “not” my condition). I also tried to specify the result, and just not assume the “true:” — like, “iif(is_form_open = true .. ” etc.
But I’m sure, now, that it was that it needed compacting, and maybe the memory also played a part.
Thanks for your answer! I remembered the 15 megs, and went, “duh” ..

Pat W.

DrkRealm,
I don’t know if this is good programming practice, or if it saves time as opposed to the dcount (which would work swell), but — often I do the following: Say you have three status. I’ll create a query off of the original data with two fields — one, status “grouped” for status = “Complete”, the 2nd field I might call “StatCompleteCount”, and I’ll “count” that. So, you have one aggregate query for one status. I’ll do the same for each status — ending up w/ 3 queries of two fields each, limited by different statuses. I’ll then create my report query, and include the three aggregate queries _but_ do not join. I’ll drag in the three count fields. This will cause the same data to replicate in each row, but it will not duplicate the row even though a cross-join, because each query returns a single row only. Will this work? — Pat
> I have several different queries in my database. How would I go about pulling data from all of these queries? ….. the complete entries, “Complete” being an entry under [Status]. In my ‘Complete’ report, I can do ‘=Count([status]) ….. How can I add conditions to my =Count function?

Your form is too big for your screen — that is all!
thx
Pat

I just recently got a variety of error messages including the “Expression is typed incorectly or is too complex ” message involving a date and a parameter (in Access 97). After tracking down a couple of KB that might possibly relate to the problem and tearing my hair out, I finally discovered the problem — BAD DATA. I had tested my query with my test data, but as soon as I imported the _real_ data, the holes in the data appeared. You might look for null data in the field that you are creating the calculated field in your query.
Pat

Select count(YourTable.PersonID) from YourTable
Where YourTable.PersonID not in
(Select subTable.PersonID
from YourTable subTable
where subTable.AlphaField in (‘A’, ‘B’, ‘C’))

— I don’t know if this will be helpful or not, but I always use the character combination of chr(13) and chr(10). One is the line break, and one is the carriage return, I believe. — Pat

I didn’t even know about Jetcomp until I began reading about it in the Lounge a while back. I postponed getting it down — and then, three weeks ago, got a database that had 40 corrupted records, that were impossible to delete — we couldn’t even land on them — and the regular compact repair didn’t work. So, I either pulled Jetcomp down from the web or found it on one of our utility CD’s — and, voila! the corrupted records (memo fld only) were written to an “I couldn’t do anything with this record” file, and the rest of the database brought back to “smooth”. The 40+ records were able to be re-entered easily, because all the data except the memo fields were in the “problem” table. Thank you Lounge, for making me aware of Jetcomp! I would recommend that people download it ahead of time, you’ll never know when you might need it most!
thx
Pat