Access97 Query expression truncated by Access2000

G

Guest

I wrote a database for a customer in Access97, and it works fine.
The customer upgraded to Access2000 and I converted the Access97 database to
Access2000 format.
It continued to work fine.

However, after adding some functionality to one of the forms, the customer
discovered a bug in a seemingly unrelated function.

This bug turned out to be related to a wording change in the updated form.

In order to resolve the bug, I had the customer open the effected query in
design view and change the criteria to relect the new wording used in the new
form.

However, when closing the design view we received an error indicating a
required character was missing from an expression, but the referenced
expression had not been edited by us!

Going back into design view we discovered an expression had been truncated
without us touching it at all. The only way to close the query was to
discard all changes, including the required change of criteria.

We tried several different methods, and views to make the changes, all
unsuccessful until I broke down and wrote the query in Access97 and emailed
it to him in an otherwise blank database. He was able to import the query to
his Access2000 version (without opening in design view), and now the query
works fine (complete with the corrected criteria, AND the expression which is
too for design view.

I even tried opening the query in SQL view, but it would not allow it with
the truncated expression.

PLEASE HELP ME!

Was there some limitation added in the change from Access97 to Access2000?
If yes, does the limitation carry over to newer versions such as AccessXP or
Access2003?
Is there another work around so I can avoid the need for two duplicate
databases?

Thanks in advance!
 
G

Guest

Having read several other posts, I see it helps to post the actual
expression/code.

Here is the version which works fine in either version until opened in
design view

Customer: IIf([Customer_Info]![Owner_Co_1st] Is Not
Null,[Customer_Info]![Owner_1st] & " " & IIf([Customer_Info]![Owner_Middle]
Is Null,"",[Customer_Info]![Owner_Middle] & " ") &
[Customer_Info]![Owner_Last] & " and " & [Customer_Info]![Owner_Co_1st] & " "
& IIf([Customer_Info]![Owner_Co_Middle] Is
Null,"",[Customer_Info]![Owner_Co_Middle] & " ") &
[Customer_Info]![Owner_Co_Last],[Customer_Info]![Owner_1st] & " " &
IIf([Customer_Info]![Owner_Middle] Is Null,"",[Customer_Info]![Owner_Middle]
& " ") & [Customer_Info]![Owner_Last])

Word tells me this is 488 characters (534 with spaces)

Once opened in Access2000-design view it is truncated as follows:
Customer: IIf([Customer_Info]![Owner_Co_1st] Is Not
Null,[Customer_Info]![Owner_1st] & " " & IIf([Customer_Info]![Owner_Middle]
Is Null,"",[Customer_Info]![Owner_Middle] & " ") &
[Customer_Info]![Owner_Last] & " and " & [Customer_Info]![Owner_Co_1st] & " "
& IIf([Customer_Info]![Owner_Co_Middle] Is
Null,"",[Customer_Info]![Owner_Co_Middle] & " ") &
[Customer_Info]![Owner_Co_Last],[Customer_Info]![Owner_1st] & " " &
IIf([Customer_Info]![Owner_Middle] Is Null,"",[Customer_Info]![Owner_Middle]
& " ") & [Custom

Word tells me this is 466 characters (512 with spaces)

Here is the who;e SQL view
INSERT INTO Payment_Property_Select ( Property_Number, Location,
Customer_Number, Customer, Property_Status )
SELECT Property_Status.Property_Number, Property_Master.Location,
Customer_Info.Customer_Number, IIf([Customer_Info]![Owner_Co_1st] Is Not
Null,[Customer_Info]![Owner_1st] & " " & IIf([Customer_Info]![Owner_Middle]
Is Null,"",[Customer_Info]![Owner_Middle] & " ") &
[Customer_Info]![Owner_Last] & " and " & [Customer_Info]![Owner_Co_1st] & " "
& IIf([Customer_Info]![Owner_Co_Middle] Is
Null,"",[Customer_Info]![Owner_Co_Middle] & " ") &
[Customer_Info]![Owner_Co_Last],[Customer_Info]![Owner_1st] & " " &
IIf([Customer_Info]![Owner_Middle] Is Null,"",[Customer_Info]![Owner_Middle]
& " ") & [Customer_Info]![Owner_Last]) AS Customer, Property_Status.Status
FROM (Property_Status INNER JOIN Property_Master ON
Property_Status.Property_Number = Property_Master.Property_Number) INNER JOIN
Customer_Info ON Property_Status.Customer_Number =
Customer_Info.Customer_Number
WHERE (((Property_Status.Status) Like "Selling*" Or
(Property_Status.Status)="Renting"))
ORDER BY Property_Status.Property_Number;

Word tells me this is 1022 characters (1105 with spaces)
Interestingly, I note the truncation is removing 22 characters which drops
the total of the SQL view to 1000 (without spaces). Is this the key?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top