Error Checking in Excel
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
    Location
    Water Research Laboratory
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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)
    Else
    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 datatable.date 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,

    Thanks,

    SD

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

    Default

    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
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

    HTH,
    Jay

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
  •