String too long, truncated, and just doesn't work.

  • Thread starter ThomasK via AccessMonster.com
  • Start date
T

ThomasK via AccessMonster.com

I have a subform that I made using the subform wizard. The subform is based
on a query, AllInspections. I added another field to the table and tried to
add it to the form (the query builder in the record source field of the form
properties) and got an error message. "The string returned by the builder was
too long. The result will be truncated". And it was truncated. It cut off the
last few characters. This is what it has now...

SELECT DISTINCTROW QAllInspections.InspectionsID, QAllInspections.AddressID,
QAllInspections.InspectionType, QAllInspections.InspectionDate,
QAllInspections.CompletionDate, QAllInspections.InspectionNotes,
QAllInspections.InspectorName, QAllInspections.PermitsPlusNumber,
QAllInspections.ComplainantName, QAllInspections.MethodReceived,
QAllInspections.InspectorAssigned, QAllInspections.Description,
QAllInspections.Findings, QAllInspections.Justified, QAllInspections.
DateAssigned, QAllInspections.DateRecieved, QAllInspections.
SupervisorAssigned, QAllInspections.Business, QAllInspections.Residence,
QAllInspections.ComplainantPhone, QAllInspections.Status, QAllInspections.
EvtNotes, QAllInspections.EvtContactFirstName, QAllInspections.
EvtContactLastName, QAllInspections.EvtStartDate, QAllInspections.EvtEndDate,
QAllInspections.EvtName, QAllInspections.Tents, QAllInspections.Cooking,
QAllInspections.BusinessName, QAllInspections.OccupancyClass, QAllInspections.
RepFirstName, QAllInspections.RepLastName, QAllInspections.BusOwnerFirstName,
QAllInspections.BusOwnerLastName, QAllInspections.BuildOwnerFirstName,
QAllInspections.BuildOwnerLastName, QAllInspections.PhoneNumber,
QAllInspections.FaxNumber, QAllInspections.HomeStatus, QAllInspections.
InspectionCycle, QAllInspections.CofO, QAllInspections.CofOIssueDate,
QAllInspections.CofORefferalDate, QAllInspections.Sorority, QAllInspections.
Fraternity, QAllInspections.ADHSLicenseType, QAllInspections.ADHSLicenseDate,
QAllInspections.NumberOfResidents, QAllInspections.NumberLicensed,
QAllInspections.NumberOfBeds, QAllInspections.ResponsiblePartyName,
QAllInspections.ResponsiblePartyPhone, QAllInspections.
ComplainantStreetNumber, QAllInspections.ComplainantDirection,
QAllInspections.ComplainantStreetName, QAllInspections.ComplainantSuite,
QAllInspections.ComplainantBuildingNumber, QAllInspections.
ComplainantLocation, QAllInspections.ReferralType, QAllInspections.
ComplainantCity, QAllInspections.ComplainantState, QAllInspections.
ComplainantZip, QAllInspections.EvtContactPhone FRO

The FROM is truncated. Is there a way to use this query, or fix it? Are some
queries just too long to use? I know I have a lot of fields, but it seems
like I should be able to do this. Would it change anything if I were to just
use the "*" for all fields? I still want my subform to work, and only show
records matching the main forms primary key.

Thanks for the help.
 
A

Arvin Meyer MVP

I think you are only allowed 1024 characters in an SQL statement written in
code. A saved query however allows 64K, so you might try saving it in a
query and using that as a recordsource.
 
D

Dirk Goldgar

ThomasK via AccessMonster.com said:
I have a subform that I made using the subform wizard. The subform is based
on a query, AllInspections. I added another field to the table and tried
to
add it to the form (the query builder in the record source field of the
form
properties) and got an error message. "The string returned by the builder
was
too long. The result will be truncated". And it was truncated. It cut off
the
last few characters. This is what it has now...

SELECT DISTINCTROW QAllInspections.InspectionsID,
QAllInspections.AddressID,
QAllInspections.InspectionType, QAllInspections.InspectionDate,
QAllInspections.CompletionDate, QAllInspections.InspectionNotes,
QAllInspections.InspectorName, QAllInspections.PermitsPlusNumber,
QAllInspections.ComplainantName, QAllInspections.MethodReceived,
QAllInspections.InspectorAssigned, QAllInspections.Description,
QAllInspections.Findings, QAllInspections.Justified, QAllInspections.
DateAssigned, QAllInspections.DateRecieved, QAllInspections.
SupervisorAssigned, QAllInspections.Business, QAllInspections.Residence,
QAllInspections.ComplainantPhone, QAllInspections.Status, QAllInspections.
EvtNotes, QAllInspections.EvtContactFirstName, QAllInspections.
EvtContactLastName, QAllInspections.EvtStartDate,
QAllInspections.EvtEndDate,
QAllInspections.EvtName, QAllInspections.Tents, QAllInspections.Cooking,
QAllInspections.BusinessName, QAllInspections.OccupancyClass,
QAllInspections.
RepFirstName, QAllInspections.RepLastName,
QAllInspections.BusOwnerFirstName,
QAllInspections.BusOwnerLastName, QAllInspections.BuildOwnerFirstName,
QAllInspections.BuildOwnerLastName, QAllInspections.PhoneNumber,
QAllInspections.FaxNumber, QAllInspections.HomeStatus, QAllInspections.
InspectionCycle, QAllInspections.CofO, QAllInspections.CofOIssueDate,
QAllInspections.CofORefferalDate, QAllInspections.Sorority,
QAllInspections.
Fraternity, QAllInspections.ADHSLicenseType,
QAllInspections.ADHSLicenseDate,
QAllInspections.NumberOfResidents, QAllInspections.NumberLicensed,
QAllInspections.NumberOfBeds, QAllInspections.ResponsiblePartyName,
QAllInspections.ResponsiblePartyPhone, QAllInspections.
ComplainantStreetNumber, QAllInspections.ComplainantDirection,
QAllInspections.ComplainantStreetName, QAllInspections.ComplainantSuite,
QAllInspections.ComplainantBuildingNumber, QAllInspections.
ComplainantLocation, QAllInspections.ReferralType, QAllInspections.
ComplainantCity, QAllInspections.ComplainantState, QAllInspections.
ComplainantZip, QAllInspections.EvtContactPhone FRO

The FROM is truncated. Is there a way to use this query, or fix it? Are
some
queries just too long to use? I know I have a lot of fields, but it seems
like I should be able to do this. Would it change anything if I were to
just
use the "*" for all fields? I still want my subform to work, and only show
records matching the main forms primary key.
Click to expand...


If you want all fields from the query, just use "SELECT * FROM
QAllInspections". If you really want to itemize the fields individually,
flip the query into SQL View and remove all the "QAllInspections."
qualifiers from the listed fields, and save it like that.
 
T

ThomasK via AccessMonster.com

Thanks for responding. This is what I did, and maybe it's the complete wrong
way, I'm not sure now.

After I built my tables, (tblAddresses, tblInspections), I made two queries
(QAllAddresses, QAllInspections) Then I made my main form using the form
wizard and used QAllAddresses as the record source. tblAddresses and
tblInspections have a one to many relationship respectivly. Then I made my
subform using the subform control on the main form, also using the wizard. I
used QAllInspections as the record source and used the field AddressID as the
parent child connecting field for the forms.

When I set up the queries QAllAddresses and QAllInspections I used the "*" to
include all the fields in the tables tblAddresses and tblInspections.

I'm starting to think that maybe my procedure was wrong. Should I have not
made queries before I made the forms, and then just based the forms on the
tables? Is that why I have run into this problem? Is this a problem that is
caused by something I did wrong, or just because the table has a lot of
fields?

Thanks again.
 
B

Bob Quintal

I have a subform that I made using the subform wizard. The subform
is based on a query, AllInspections. I added another field to the
table and tried to add it to the form (the query builder in the
record source field of the form properties) and got an error
message. "The string returned by the builder was too long. The
result will be truncated". And it was truncated. It cut off the
last few characters. This is what it has now...

SELECT DISTINCTROW QAllInspections.InspectionsID,
QAllInspections.AddressID, QAllInspections.InspectionType,
QAllInspections.InspectionDate, QAllInspections.CompletionDate,
QAllInspections.InspectionNotes, QAllInspections.InspectorName,
QAllInspections.PermitsPlusNumber,
QAllInspections.ComplainantName, QAllInspections.MethodReceived,
QAllInspections.InspectorAssigned, QAllInspections.Description,
QAllInspections.Findings, QAllInspections.Justified,
QAllInspections. DateAssigned, QAllInspections.DateRecieved,
QAllInspections. SupervisorAssigned, QAllInspections.Business,
QAllInspections.Residence, QAllInspections.ComplainantPhone,
QAllInspections.Status, QAllInspections. EvtNotes,
QAllInspections.EvtContactFirstName, QAllInspections.
EvtContactLastName, QAllInspections.EvtStartDate,
QAllInspections.EvtEndDate, QAllInspections.EvtName,
QAllInspections.Tents, QAllInspections.Cooking,
QAllInspections.BusinessName, QAllInspections.OccupancyClass,
QAllInspections. RepFirstName, QAllInspections.RepLastName,
QAllInspections.BusOwnerFirstName,
QAllInspections.BusOwnerLastName,
QAllInspections.BuildOwnerFirstName,
QAllInspections.BuildOwnerLastName, QAllInspections.PhoneNumber,
QAllInspections.FaxNumber, QAllInspections.HomeStatus,
QAllInspections. InspectionCycle, QAllInspections.CofO,
QAllInspections.CofOIssueDate, QAllInspections.CofORefferalDate,
QAllInspections.Sorority, QAllInspections. Fraternity,
QAllInspections.ADHSLicenseType, QAllInspections.ADHSLicenseDate,
QAllInspections.NumberOfResidents, QAllInspections.NumberLicensed,
QAllInspections.NumberOfBeds,
QAllInspections.ResponsiblePartyName,
QAllInspections.ResponsiblePartyPhone, QAllInspections.
ComplainantStreetNumber, QAllInspections.ComplainantDirection,
QAllInspections.ComplainantStreetName,
QAllInspections.ComplainantSuite,
QAllInspections.ComplainantBuildingNumber, QAllInspections.
ComplainantLocation, QAllInspections.ReferralType,
QAllInspections. ComplainantCity,
QAllInspections.ComplainantState, QAllInspections. ComplainantZip,
QAllInspections.EvtContactPhone FRO

The FROM is truncated. Is there a way to use this query, or fix
it? Are some queries just too long to use? I know I have a lot of
fields, but it seems like I should be able to do this. Would it
change anything if I were to just use the "*" for all fields? I
still want my subform to work, and only show records matching the
main forms primary key.

Thanks for the help.
Click to expand...
You could try to alias the queryname.
In the query builder tool click on any field list in the upper
section, and open the properties popup. There is an alias field in
the general tab. If you set it to A, you will recover lots of space.

In the SQL view, it'll show
SELECT DISTINCTROW A.InspectionsID,
A.AddressID, A.InspectionType,
A.InspectionDate, ....
Click to expand...
FROM QAllInspections Alias A ....
 
Top