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
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