3464 Data Type mismatch

[Marianne]

Hello!

I have a form with a command button that opens a report based on the CLNum
field. It works fine if the underlying query that run the report has "payment
records" for the invoices. if no payment records exist for an invoice then
the error 3464 - "Data Type mismatch" encountered. CLNum field is a text
field containing alpha numeric characters, ie. ABC123.

Code to open report:

DoCmd.OpenReport "rptInvoiceSummaryByClient", acViewPreview, , "[CLNum]="""
& Me.CLNum & """", acWindowNormal

 

[Beetle]

Try using the Nz function to trap for Null;

DoCmd.OpenReport "rptInvoiceSummaryByClient", acViewPreview, , "[CLNum]="""
& Nz(Me.CLNum, vbNullString) & """", acWindowNormal

 

[Dirk Goldgar]

Hello!

I have a form with a command button that opens a report based on the CLNum
field. It works fine if the underlying query that run the report has
"payment
records" for the invoices. if no payment records exist for an invoice then
the error 3464 - "Data Type mismatch" encountered. CLNum field is a text
field containing alpha numeric characters, ie. ABC123.

Code to open report:

DoCmd.OpenReport "rptInvoiceSummaryByClient", acViewPreview, ,
"[CLNum]="""
& Me.CLNum & """", acWindowNormal

I don't think it's this statement that is causing the trouble, if CLNum is
defined everywhere as a text field. Is there code behind the report that
might raising the error? Does the error message give you a Debug option to
click, which might show you the statement causing the error?

 

[Marianne]

Hi Dirk!

I do get the Debug option, and it takes me to the line of code as posted.

The field is defined as a text field in the table.

I do not have any code in the open event or within the report.

I am baffled as to why the report opens correctly on some records and not on
others?

I do have this expression in the query for the report, the only distinctsion
I see is it appears to happen on records that do not always have payments for
the invoices.

PymtTotal: (FormatNumber(DSum("nz([Pymt])","PaymentDetail","[PymtInvID]= "
& [InvID]),2,-1,-1))

Hello!

I have a form with a command button that opens a report based on the CLNum
field. It works fine if the underlying query that run the report has
"payment
records" for the invoices. if no payment records exist for an invoice then
the error 3464 - "Data Type mismatch" encountered. CLNum field is a text
field containing alpha numeric characters, ie. ABC123.

Code to open report:

DoCmd.OpenReport "rptInvoiceSummaryByClient", acViewPreview, ,
"[CLNum]="""
& Me.CLNum & """", acWindowNormal

I don't think it's this statement that is causing the trouble, if CLNum is
defined everywhere as a text field. Is there code behind the report that
might raising the error? Does the error message give you a Debug option to
click, which might show you the statement causing the error?

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

 

[J_Goddard via AccessMonster.com]

Hi -

If there are no records that meet the criteria for DSum, it return a Null,
not a zero. Try Putting the entire DSum inside another Nz function:

PymtTotal: (FormatNumber(nz(DSum("nz([Pymt])","PaymentDetail","[PymtInvID]= "
& [InvID]),0),2,-1,-1))

This will give you 0 if there are no records that meet the criteria.

John

Hi Dirk!

I do get the Debug option, and it takes me to the line of code as posted.

The field is defined as a text field in the table.

I do not have any code in the open event or within the report.

I am baffled as to why the report opens correctly on some records and not on
others?

I do have this expression in the query for the report, the only distinctsion
I see is it appears to happen on records that do not always have payments for
the invoices.

PymtTotal: (FormatNumber(DSum("nz([Pymt])","PaymentDetail","[PymtInvID]= "
& [InvID]),2,-1,-1))
[quoted text clipped - 15 lines]

might raising the error? Does the error message give you a Debug option to
click, which might show you the statement causing the error?

 

[Marianne]

That fixed it! Thanks so much for your help!

Merry Christmas!

Hi -

If there are no records that meet the criteria for DSum, it return a Null,
not a zero. Try Putting the entire DSum inside another Nz function:

PymtTotal: (FormatNumber(nz(DSum("nz([Pymt])","PaymentDetail","[PymtInvID]= "
& [InvID]),0),2,-1,-1))

This will give you 0 if there are no records that meet the criteria.

John

Hi Dirk!

I do get the Debug option, and it takes me to the line of code as posted.

The field is defined as a text field in the table.

I do not have any code in the open event or within the report.

I am baffled as to why the report opens correctly on some records and not on
others?

I do have this expression in the query for the report, the only distinctsion
I see is it appears to happen on records that do not always have payments for
the invoices.

PymtTotal: (FormatNumber(DSum("nz([Pymt])","PaymentDetail","[PymtInvID]= "
& [InvID]),2,-1,-1))

Hello!

[quoted text clipped - 15 lines]

might raising the error? Does the error message give you a Debug option to
click, which might show you the statement causing the error?

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca



.