Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: type mismatch errors for date values

  1. #1
    New Member
    Join Date
    Apr 2002
    Water Research Laboratory
    Post Thanks / Like
    0 Post(s)
    0 Thread(s)


    Hi All,

    I have a query table in an Excel worksheet linked to a table in an Access 97.mdb file.

    I want the query table to show only 2 months worth of data. Date values are stored in one column using a dd/mm/yy format.

    I have set up a macro so the user enters the month and year they are interested in and the query table refreshes to show the data from the user specified month and the previous month.

    My problem:

    I get the user to enter both the month and date as integers i.e. (1=January, 2=February etc)

    Dim month2 As Single
    Dim Year2 As Single
    Dim month1 AS Single
    Dim Year1 As Single
    Dim start As Date
    Dim Finish As Date

    'user inputs month and year of interest
    month2 = InputBox("Which month is to be analysed?")
    Year2= InputBox ("Which year?")

    month1 = month2-1
    Year1 =Year2-1

    'use datevalue to set start and finish periods

    finish = DateValue("31/ & month2 &"/" &year2)

    If month2 = 1 Then
    start = DateValue("01/12/" & year1)
    start = DateValue("01/"& month1 & "/" & year2)

    Fine and dandy until this point

    However when I use the start and finsih values in my SQL that updates the query table I am returned with a type mismatch error, the SQL query I use goes along the lines of:

    "SELECT......." & "WHERE Between #" & start &"# and #" & finish & "#)" ...&("ORDER BY ... ")
    .Refresh False

    Can someone tell me if they can see a gross error in what I have done - should I be using CDate instead of DateValue? Are the litterals the problem?

    Or does anyone have any other ideas? I ahve tried to let the user input the month name (i.e January as aopposed to 1) however I run into problem when I try to define the previous month.

    Any help wouyld be greatly appreciated,



  2. #2
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Auckland, New Zealand
    Post Thanks / Like
    0 Post(s)
    0 Thread(s)


    I think your start and end date values should
    be strings and NOt values....
    try converting to strings

    Kind Regards,
    Ivan F Moala From the City of Sails

  3. #3
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Chicago, IL USA
    Post Thanks / Like
    0 Post(s)
    0 Thread(s)


    Not sure, but try and changing the date format in your DateValue string to mm/dd/yy.

    I think that DateValue in VBA "doesn't play by the rules" in international versions of Excel and is only looking for a US setup (according to Stephen Bullen, but my copy of his book is at work, so I cannot verify this).

    I could be wrong on this, so please test it out.

    Also, you may want to use DateSerial(year, month, day) which is the VBA equivalent to =DATE() in Excel.


Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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