3464 Data Type mismatch

M

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
 
B

Beetle

Try using the Nz function to trap for Null;

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

Dirk Goldgar

Marianne said:
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?
 
M

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))

Dirk Goldgar said:
Marianne said:
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

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?
 
M

Marianne

That fixed it! Thanks so much for your help!

Merry Christmas!

J_Goddard via AccessMonster.com said:
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?

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



.
 

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