Invalid Argument

[Sam]

In Access 2000 - I am trying to run a select-query that
has a field that references a text field from a form that
is open. The field is as follows -

PaymentsDue: IIf(IsNull([Forms]![frm ContinueImport]!
[txtPaymentsDue]),"",[Forms]![frm ContinueImport]!
[txtPaymentsDue])

Most of the time, the query runs fine but occassionally if
the text string in the txtPaymentsDue text box is too long
(over 127 characters), the query will not run and instead
return an error stating "invalid Argument".

Any ideas on what causes this? I posted in previous forum
and had a suggestion to use the NZ function or else just
to reference back to JUST the field and I have tried
both. I still get INVALID ARGUMENT when running the query
if the string in [Forms]![frm ContinueImport]!
[txtPaymentsDue] is too long...

Any suggestions would be appreciated!

 

[Jeff Boyce]

Sam

I'm a bit confused ... you appear to be describing a text field that holds
information about "payments due". Could you provide an example of how such
a field could exceed 100 characters?

More info, please...

Jeff Boyce
<Access MVP>

 

[Sam]

Yes, it is a text box on a form. It contains a string
indicating payments due which includes dates and amounts
due. An example of the string that would be entered in
the text box is "May 29, 2003 for $245.00 and June 29,
2003 for $245.00 and July 29, 2003 for $245.00 and August
29, 2003 for $245.00 and September 29, 2003 for $245.00."
(example is 152 characters long)

 

[Jeff Boyce]

Sam

Access is a relational database. "Stuffing" a series of dates and amounts
in a text string to keep track of them is not a well-normalized design. And
in addition to the problem you are already seeing, what will you do when the
total length of your text string exceeds the maximum allowed in a text field
(255 characters)?

A more flexible design would be to build a table something like:
tblPaymentDue
PaymentDueID (probably an Autonumber)
PayorID (a foreign key from your Payor table)
DueDate
Amount

This design would allow you to have any number of payments due per Payor
(including none).

Good luck

Jeff Boyce
<Access MVP>