Type Mismatch in Expression

[tjnichols]

I have a query that I want to actually populate a table with information. This database is a contact database that has classes that the contacts / students will attend. I think the AttendanceID should be a new record because each contact can attend a class. Each contact can attend many classes and each class can have many students.

Initially, I tried to do this with a DLookup function but someone (on here) told me to change it a query. I did this but when I run the query (QRYAttendance) it give me a "Type mismatch in expression".

I really don't care how I do this - I just want it to work reliably.

Thanks in advance!

 

[John Big Booty]

Type mismatch in expression error tends to indicate that you have an internal join in your query on say a string (text) and a number field, or you are trying to compare a string and a number field.

Check that the fields that you are trying to compare or join on are formatted the same. It is very easy to have one field formatted as a number and another formatted as a string and it will not be immediately obvious.

 

[vbaInet]

... why not show us the SQL statement of the query?

 

[tjnichols]

As requested vbaInet - here is the SQL. I will look at the formatting. I have looked at this but I am unsure of whether or not this is a problem...

My Attendance table has an autonumber for the Attendance ID, my classes table also has an auto number for the classes ID but the field that connects the two (ClassesID) in the attendance table is a number field. Is this the way it should be done?

Thanks for your help!!!

SELECT [Attendance].[AttendanceID], [Attendance].[ChildID], [Attendance].[ClassID], [Attendance].[Teacher or Contact], [Attendance].[Event or Subject], [Attendance].[ClassName], [Attendance].[ClassDate], [Attendance].[Location], [Attendance].[Notes], [Classes].[FiscalYear]
FROM Classes INNER JOIN Attendance ON [Classes].[ClassID]=[Attendance].[ClassID];

 

[vbaInet]

Did you build this using the Query Builder?

 

[tjnichols]

Yes - but I went back and looked closely at the formatting - IT'S FIXED!!!

 

[vbaInet]

Good job!