var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Data Type mismatch on criteria
I have converted an Access 95 application to an Access 2000 application. I have a process that is supposed to delete records based on a date range that is entered into a form. The control names in the form are mo_box_1 and yr_box_1. The input into these controls are concatenated in a procedure by the following statements:
mo1 = Forms![Archive_Form]![Mo_Box_1]
yr1 = Forms![Archive_Form]![Yr_Box_1]
month1 = "#" & mo1 & " / 31 / " & yr1 & "#"
Set myDB = DBEngine.Workspaces(0).Databases(0)
Set archiveCC = myDB.QueryDefs("Archive_NMR_Query")
archiveCC!month1 = month1
In the Delete query the criteria is <=[month1]
When I open the form and try to run the application, I get "Data type mismatch on criteria.
I have tried changing everything I know and still can't get it to work.
Not all months have 31 days.
Could be that you're trying to run a delete query on a month with less then 31 day.
at the very top of your module type:
Option Base 1 ' this will make arrays start with 1 not 0
mo1 = ...
yr1 = ...
arrDays = Array(31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31)
'2004 is a leap year (29 days in Feb.) so here's a check for a leap year:
If mo1 = 2 Then
Dim varLeapYear As Variant
varLeapYear = yr1 / 4
varLeapYear = varLeapYear - Int(varLeapYear)
If varLeapYear = 0 Then arrDays(2) = arrDays(2) + 1
month1 = "#" & mo1 & "/ " & arrDays(mo1) & "/ " & yr1 & "#"
if you don't want to check for leap year then just change second value in the array to 29. Feel free to change variable names as you probably have a different naming practice.
Last edited by The Byte; 02-15-04 at
I won't byte... hard!
You were right about the date, that was part of the problem. I finally found the problem and fixed it by changing the following:
in the Append Query Criteria I changed it to <=DateValue([month1])
and in the procedure I changed month1 to
month1 = "12" & "/31/" & yr1
They want to archive by year anyway so I hard coded the month and day into the procedure. They will be able to choose the year.
Thanks for your help.