Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Location
    Walnut Cove, NC
    Posts
    101

    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

    archiveCC.Execute



    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.

    Please help!

    Thanks,

    SBR

  2. #2
    Join Date
    Feb 2004
    Location
    Binary Universe
    Posts
    57
    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.

    Try this:

    at the very top of your module type:
    Option Base 1 ' this will make arrays start with 1 not 0

    then after:
    mo1 = ...
    yr1 = ...

    Dim arrDays
    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
    End If

    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 07:00.
    I won't byte... hard!

  3. #3
    Join Date
    Jan 2004
    Location
    Walnut Cove, NC
    Posts
    101
    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.

    SBR

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •