type mismatch errors for date values

[sdorairaj]

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

[Ivan F Moala]

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

[Jay Petrulis]

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