type mismatch errors for date values

sdorairaj

New Member
Joined
Apr 2, 2002
Messages
2
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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
I think your start and end date values should
be strings and NOt values....
try converting to strings
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top