hi Jim,
> reason for the 554 ... do not remember the reason
perhaps something is automatically appended at the end? Look at the data ...
is Paragraph2 something contcatenated in a query? Where does it come from? A table? A query? I see the calculated field name is "MemoWithDate" ... but I don't see any date -- where does the date come from?
Anyhow,
to convert the expression you posted to what I would do for that expression: append "" to any field tested with LEN or LEFT (to handle Null cases), and testing length before deciding what to do:
1. show whatever is there if length of Paragraph2 < 554
MemoWithDate: iif( Len(tblMaxCompensatoryMemo.Paragraph2 & "") > 554, Left([tblMaxCompensatoryMemo].[Paragraph2] & "", Len([tblMaxCompensatoryMemo].[Paragraph2] & "")-554, tblMaxCompensatoryMemo.Paragraph2)
2. show nothing if length of Paragraph2 < 554
MemoWithDate: iif( Len( tblMaxCompensatoryMemo.Paragraph2 & "") > 554, Left([tblMaxCompensatoryMemo].[Paragraph2] & "", Len([tblMaxCompensatoryMemo].[Paragraph2] & "")-554, "")
as Duane said, if you send a negative number for the number of characters for the LEFT function, this is the error message:
Run-time Error '5';
Invalid Procedure Call or Argument
either of the examples above should avoid that error by testing the length first -- and ensuring that True and False expressions evaluate successfully� no matter what.
When you concatenate with &, result can be non-null even when one or more of the parts evaluates to Null. Null is ignored when using & ... unless everything is null, in which case, you'll still have Null. That's why using the plus operator ( somefield + whatever ) & ... produces different results.
Null means the ABSENCE of a value. Many say 'Null Value', but to me, Null and Value are mutually exclusive...
kind regards,
crystal
Crystal,
many years ago, the users wanted a report with different paragraphs stating company policy and compliance to those policies on the report with data from the tables for comp time. That would be easy. Just put labels on the report with the company policies and be done. But the VP was very formal and insisted that the paragraphs be justified. Well, that meant that the paragraphs had to be in a table to populate text boxes that were in rich text format. I had to ask for help in this and other Access groups to find a solution. The 554 is one of the things suggested. It has worked for years until yesterday. I am not sure why it stopped�working but the reality was that it did, and my main user was upset and under many deadlines with one of her staff out. So, she took it out on me and the Access database. The report was supposed�to be sent out Monday but that has since gone and past. So, the reason for the 554 is that I do not remember�the reason. There are 3 paragraphs on the report but for some reason this is the only one that decided it was going to act like it did not care about others but itself.�
Sorry for the long response.�
From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of crystal (strive4peace) via groups.io <strive4peace2008=yahoo.com@groups.io>
Sent: Thursday, October 10, 2024 9:50 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Invalid Procedure Call error�hi Jim,
What are you trying to do ? ... just decease size by 554 -- or would you rather set the size to a particular number?
... maybe this? Where MAXNUMBER is the max characters you want to show
MemoWithDate: iif( Len( tblMaxCompensatoryMemo.Paragraph2 & "") > MAXNUMBER,
Left( tblMaxCompensatoryMemo.Paragraph2 & "", MAXNUMBER), tblMaxCompensatoryMemo.Paragraph2)
reason "" is concatenated to field to test is that LEN returns Null if Paragraph2 doesn't have a value.
"" also appended to True part because IIF evaluates both True and False so they both have to work, and LEFT also only works with strings, not Null. Appending "" turns a Null into a zero-length string so LEN and LEFT can both work
What is the reason for 554?
kind regards,
crystal
On 10/10/2024 11:23 AM, Duane Hookom wrote:
I believe if you have less than 554 characters the Left() function will attempt to return 242-554. This should generate an error. For instance,? Left("This is a test of len and left",-3)�
generates the error message:
Run-time Error '5';Invalid Procedure Call or Argument
Duane
From:�MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Jim Wagner <josephwagner2@outlook.com>
Sent:�Thursday, October 10, 2024 10:52 AM
To:�MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject:�Re: [MSAccessProfessionals] Invalid Procedure Call error�Duane,242 characters with spaces
Jim Wagner
From:�MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Jim Wagner <josephwagner2@outlook.com>
Sent:�Thursday, October 10, 2024 8:50 AM
To:�MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject:�Re: [MSAccessProfessionals] Invalid Procedure Call error�Thank You Duane. I will look
Jim Wagner
From:�MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Duane Hookom <duanehookom@hotmail.com>
Sent:�Thursday, October 10, 2024 7:26 AM
To:�MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject:�Re: [MSAccessProfessionals] Invalid Procedure Call error�I would first look at the number of characters in Paragraph2 to make�sure it's more than 554 characters.
Duane
From:�MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Jim Wagner <josephwagner2@outlook.com>
Sent:�Thursday, October 10, 2024 8:40 AM
To:�MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject:�[MSAccessProfessionals] Invalid Procedure Call error�Hello All,�Yesterday I started to get an error message for "Invalid Procedure call" when I tried to open a report. This report has been running for years with no issues. This morning after several hours, I deleted from the query this line��MemoWithDate: Left([tblMaxCompensatoryMemo].[Paragraph2],Len([tblMaxCompensatoryMemo].[Paragraph2])-554)��and the query ran successfully with no issues. So, I am assuming that either an office update or something is haywire with the database has recently occurred. I have checked vba references and there are no missing references.��I am not sure where to go from here. Can someone help me find a solution. My user is to the point of extreme frustration with me.��Thank You�Jim Wagner
Tidak ada komentar:
Posting Komentar