Max fields in a query for access97 2

[hwkranger]

i have a form based on a query, with 15 fields pulled from 4 tables - I tried to add a 16th field but after saving the query it lasts for about 10 seconds, then the 16th field vanishes off the query. Any hints as to why? Is there a max number of fields for a query? does this mean I have to base my form on 2 queries instead of one?

Any help is appreciated. i know this is the "forms" forum, but this does concern my form (a little) Cruz'n and Booz'n always.
This post shows what little I do at work.

 

[JerichoJ]

Queries and Tables have a maximum of 255 fields, so that isn't your problem.

How are you editing this query? Is it a saved query? Are you opening it from the Queries tab of the database window? Or are you clicking on the elipses (...) next to the recordsource property in form design view?

If you are doing it from the Queries tab of the database window and it truly does not save the changes you are making, I would suspect some .mdb corruption. Try repairing and compacting your database.

 

[MichaelRed]

Hmmmmmmmmmm,

the "absoloute" value of 255 is 'correct', but only in the narrow sense of Ms. Speak, where the definition of 'field' is not the normal english language useage, but includes additional 'fields' for the indicies and other esoteria. Still, it is unlikely that the 16 (actual) fields have included 240 'carry-on' fields, so JerichoJ's suggestions are not inappropiate, just not technically accurate.

It coule help to have a more complete (detailed) discription of "last about ten seconds", including a detaild sequence of keyboard activity and screen response during hte fabeled '10 seconds' - and perhaps some of the preceeding activity.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[hwkranger]

I do believe that I did read before that 255 was the max. Me being on the lower end of the mental retentive curve I had forgotten.

The following doesn't have 100% Kosher naming qualities, but as time goes on, I am fixing what I can.

Thankyou for attempting to answer my question, I will elaborate more:

this is the query:

SELECT DISTINCTROW USYSAccountsWithProblems.[Vendor Name], USYSAccountsWithProblems.[Account Number], USYSAccountsWithProblems.[Acct Contact], USYSAccountsWithProblems.[Contact Number], USYSAccountsWithProblems.[Contact Address], USYSCheckInformation.[Chk #], USYSLetterInformation.Opening, USYSCheckInformation.[Invoice Number], USYSCheckInformation.[Chk Amnt], USYSCheckInformation.[Inv Amnt], USYSLetterInformation.Closing, USYSLetterInformation.Signature, USYSCheckInformation.[Chk Date], USYSLetterInformation.Date, USYSCheckInformation.VID
FROM USYSAccountsWithProblems INNER JOIN (USYSLetterInformation RIGHT JOIN USYSCheckInformation ON USYSLetterInformation.[Letter Number] = USYSCheckInformation.[Invoice Number]) ON USYSAccountsWithProblems.VID = USYSCheckInformation.VID
WHERE (((USYSCheckInformation.VID)=2707));

The query definition changes based on the following on click event (which opens a form based on current record)

With myquery

.SQL = "SELECT DISTINCTROW [USYSAccountswithProblems].[Vendor Name], [USYSAccountswithProblems].[Account Number], [USYSAccountswithProblems].[Acct Contact], [USYSAccountswithProblems].[Contact Number], [USYSAccountswithProblems].[Contact Address], [USYSCheckInformation].[Chk #], [USYSLetterInformation].Opening, [USYSCheckInformation].[Invoice Number], [USYSCheckInformation].[Chk Amnt], [USYSCheckInformation].[Inv Amnt], [USYSLetterInformation].Closing, [USYSLetterInformation].Signature, [USYSCheckInformation].[Chk Date], [USYSLetterInformation].Date, [USYSCheckInformation].VID FROM [USYSAccountsWithProblems] INNER JOIN ([USYSLetterInformation] RIGHT JOIN [USYSCheckInformation] ON [USYSLetterInformation].[Letter Number] = [USYSCheckInformation].[Invoice Number]) ON [USYSAccountsWithProblems].VID = [USYSCheckInformation].VID Where [USYSCheckInformation].VID =" & Me.VID

End With

All I want to do, Is add USYSCheckInformation.[Wlkr #] to the select. Which works but when I add it to the query def for the onclick event it doesn't.. It doesn't include that part of the query at all.

This isn't really a big ordeal, since I can pull the information I want from a separate form.

I'm sure to figure it out sooner or later..

Thankyou for the help (already given) and in advance!

Cruz'n and Booz'n always.
This post shows what little I do at work.

 

[hwkranger]

Well, Happy New Years..

Today I did what I had done in the past (just added the extra selection) and now it works... Always before when using the forms, part of the query vanished, so i got frustrated, left the question and left the query/form alone.

Today it works..

Although in the past week or so I have both repaired and compacted the db.

kudo's to you guys.. had you never responded I wouldn't have looked at this issue for a while longer, and I may never have even bothered to fix it. (since the information can be retrieved just as easy elsewhere)


Cruz'n and Booz'n always.
This post shows what little I do at work.