Some code or query expressions that were working have stopped – here are the updated applications.
Recently, I have had to modify several Access 2000 applications that are currently running in Access 2002 or 2003 because code or query expressions that used to work have stopped working. The problems are all data type-related. It appears that Access is getting much stricter about requiring data to be in exactly the correct data type, requiring converting values using CLng, CDbl and the other conversion functions. Where previously a number could be saved to a Currency field, I find that now I have to explicitly convert it to a Currency value with CCur, or I get an error – and similarly for other data types.
Another problem occurred in a query, where I created a DateEquiv expression, using CDate and other functions to create a date from two text fields (Semester and Year), for purposes of filtering by date.
DateEquiv: CDate(Switch([Semester]=”Fall”,”9″,[Semester]=”Spring”,”2″,[Semester]=”Summer”,”6″) & “/1/” & [Year])
This DateEquiv query expression was used for filtering reports by a date range, with filter strings such as
Filter: [DateEquiv] Between #2/1/2003# And #6/1/2004#
This technique worked fine in Access 97 and Access 2000, but now (in Access 2002 or 2003), when used for filtering it causes a “Data Type Mismatch in Criteria Expression” error. To work around the error, I had to create a DateEquiv field in the table, and fill it with a date value from the AfterUpdate event of a control on a form (the date value was created by the same expression that caused an error when used in the filter string). A filter expression using the date field now works just fine.
