Type Mismatch Error in Access 2000/XP

[csmager]

This SQL works fine in Access 97, but when I use it in Access 2000 or XP, I get a 'type mismatch error'. Any Ideas?

:
SELECT Data.[Date Of Booking] AS BookDate, Allocation.[Room Allocated (Day)] AS BookRoom
FROM Data INNER JOIN Allocation ON Data.ID = Allocation.ID
WHERE (((Data.[Date Of Booking])=[Forms]![Main]![Date Of Booking]) AND ((Allocation.[Room Allocated (Day)])=[Forms]![Main]![Room Allocated (Day)]));

Thanks

 

[cmmrfrds]

The type mismatch is more than likely coming from how you are setting up the select i.e. querydef or other DAO syntax. Access 2000 defaults to ADO syntax so any DAO object need to be identified as such.

For example
DAO.Recordset

 

[csmager]

Where would I put something similar in relation to my query? Sorry to appear completely stupid!

Thanks

 

[cmmrfrds]

Okay, maybe it is the definition of the select.

Is this a field name in your table? The syntax does not look right - Day is a reserved word in Access 2000. Maybe Access is confusing the reserved word.

[Room Allocated (Day)]

DAY
Returns the day of the month (a number from 1 to 31) that corresponds to a serial number.
Syntax
DAY(serial_number)
Serial_number is a number that represents the date or text in date format, such as "4-1-1999" or "31-Oct-2000".

 

[csmager]

It is a field name - should I rename it?

 

[zambezibill]

I'll concur to the previous comments about the use of DAY, however you should open the reference library and make sure Microsoft DAO 3.6 Object Library is checked.

Cheers, Bill