"Type mismatch in expression" error on Layout View click 1

[Emmali]

This is the most hideous form of Access torture yet.
I created a sample report through a *shudder* wizard to see if I can get the report to pull text in as a result of calculated values, e.g.:
If (value)>(limit) Show Text.rtf
... or some such thing. However, I have an unadulterated magicked report that generates an error when I try to "preview" the report in layout view:
"Type mismatch in expression."
No clues as to what expression or where.
Any ideas?

Center for World Indigenous Studies

http://www.cwis.org

"Always carry a tuna sandwich in case of tigers."

 

[jiqjaq]

Look at the report code in design view. Put a breakpoint at the start of the Detail_Format section and end of the Report_Open. If you make it past the Report_Open, you know you've started reading the data. If you stop rigth after the first cycle of Detail_Format, one of the control's ControlSource doesn't match the the data record for the type of data you have in the reocrd.

 

[Emmali]

Sorry for the lag time, I've been out sick.

I added the breakpoints you specified, and it stops at Report_Open: "End Sub", so its started reading data...


Option Compare Database

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
End Sub

Private Sub Report_Open(Cancel As Integer)
End Sub

Center for World Indigenous Studies

http://www.cwis.org

"Always carry a tuna sandwich in case of tigers."

 

[jiqjaq]

You didn't quite make it past the Report_Open. You have not started to actually read the data into the report. There is either an error in the RecordSource, the Sort/Grouping expressions or on the layout of the report.

Look at the Sort/Grouping expressions. If any of these are wrong the report won't work. Maybe you'll see the problem?
This is usually the most common place for a report to die without specific errors.

You can always create a query from the report's RecordSource. If you then attempt to run that query, you'll see if that part is correct.

 

[Emmali]

jigjag,

I don't know where to find the Sort/Grouping expressions, or how to unmuddle them, or where to go to create a query with the pseudoSQL Access generates. I tried with a"normal" new query design and got the same error message.

Access created all this from its wizard, and the RecordSource is heinous:

SELECT [Client Personal Info].[ClientID] AS [Client Personal Info_ClientID], [Client Personal Info].[Title], [Client Personal Info].[FName], [Client Personal Info].[MI], [Client Personal Info].[LName], [Client Personal Info].[Affil], [Client Personal Info].[Address1], [Client Personal Info].[Address2], [Client Personal Info].[City], [Client Personal Info].[ST], [Client Personal Info].[PostalCode], [Client Personal Info].[Telephone] AS [Client Personal Info_Telephone], [Client Personal Info].[Cell], [Client Personal Info].[Fax], [Client Personal Info]. AS [Client Personal Info_Email], [Client Personal Info].[Birthdate], [Client Personal Info].[Gender], [Client Personal Info].[HtFeet], [Client Personal Info].[HtInches], [Client Personal Info].[Bloodtype], [Client Personal Info].[Bodyfat Estimate], [PractitionerContactInfo].[PractitionerID] AS [Practitioner Contact Info_PracticionerID], [PractitionerContactInfo].[PName], [PractitionerContactInfo].[Telephone] AS [Practitioner Contact Info_Telephone], [PractitionerContactInfo].[Email] AS [Practitioner Contact Info_Email], [Visit Data Subform].[RecNum], [Visit Data Subform].[ClientID] AS [Visit Data Subform_ClientID], [Visit Data Subform].[PracticionerID] AS [Visit Data Subform_PracticionerID], [Visit Data Subform].[Date], [Visit Data Subform].[Complaints], [Visit Data Subform].[Glucose#], [Visit Data Subform].[Potassium#], [Visit Data Subform].[Weight], [Visit Data Subform].[Test Given] FROM PractitionerContactInfo INNER JOIN ([Client Personal Info] INNER JOIN [Visit Data Subform] ON [Client Personal Info].[ClientID]=[Visit Data Subform].[ClientID]) ON [PractitionerContactInfo].[PractitionerID]=[Visit Data Subform].[PracticionerID];

Emm.

 

[jiqjaq]

Since the query from the RecordSource doesn't work, you can forget about any groupings. Try recreating the report using the wizard.

First, rename the forms fields like Glucose #. # is a special character in Access reserved for dates. Check the ClientID and PractionerID fields to make sure they are the same type of data (character, integer, etc.)

 

[Emmali]

Verify data type of fields in tables or in the report?
Can I just change the names and control sources of the # fields (as well as those relevant fields in the tables)? I've done that. Otherwise, the recreation will have to wait until tomorrow, as even the wizard takes 15-20 minutes.
Going home for now, thanks, back tomorrow.

 

[Emmali]

Report recreated. Wow, its faster the 3rd/4th time around.
Still no luck...

Center for World Indigenous Studies

http://www.cwis.org

"Always carry a tuna sandwich in case of tigers."

 

[jiqjaq]

The message you're getting usually refers to the data fields which are being compared against one another. In this case that's the ones with the = in the query. The listing of your query indicates that these are table fields, not form fields.

Are you sure that [PractitionerContactInfo].[PractitionerID]=[Visit Data Subform].[PracticionerID] are both numeric or both character? Even if both are numeric, if one is defined as integer and the other is long with a value > than 65535, you'll get an error trying to compare them.

Same thing for [Client Personal Info].[ClientID]=[Visit Data Subform].[ClientID] .

 

[Emmali]

No, actually the ID fields in the Visit Data Subform table are text since they were intended to display the names on the form but only the ID#s in the table! Duh. I can't believe I did that!
Fixed that, and now it works - I just have to adjust the layout for a standard 8.5x11" sheet, not the 22" wide report Access generated!
Marvelous work, thank you!