Running parameter queries from VBA

[John Southern]

After much help from 2 Forum members I succeeded in running a query with parameters supplied by VBA.
The code I used is:-
Sub Comparisons()
Dim FROM1, FROM2, TO1, TO2 As Date
Dim strSQL As String
FROM1 = InputBox("Date from", "Input")

TO1 = InputBox("Date to", "Input")
FROM2 = DateAdd("yyyy", -1, FROM1)
TO2 = DateAdd("yyyy", -1, TO1)
FROM2 = DateValue(FROM2) ' needed to ensure variable is a date
TO2 = DateValue(TO2)
FROM1 = DateValue(FROM1)
TO1 = DateValue(TO1)
strSQL = "SELECT SalesComp.Expr4, SalesComp.WT_NOMCC, Sum(SalesComp.WT_NET_TOTAL) AS SumOfWT_NET_TOTAL INTO SumComp FROM SalesComp WHERE (((SalesComp.WM_INVOICE_DATE) > # " & FROM1 & " # AND(SalesComp.WM_INVOICE_DATE) < # " & TO1 & "#) OR (( SalesComp.WM_INVOICE_DATE) > #" & FROM2 & " # AND (SalesComp.WM_INVOICE_DATE) < # " & TO2 & " #)) GROUP BY SalesComp.Expr4, SalesComp.WT_NOMCC;"


CurrentDb.QueryDefs("SUMSalesComp").SQL = strSQL
DoCmd.OpenQuery "SUMSalesComp"

End Sub
This produced eg. this SQL string
SELECT SalesComp.Expr4, SalesComp.WT_NOMCC, Sum(SalesComp.WT_NET_TOTAL) AS SumOfWT_NET_TOTAL INTO SumComp
FROM SalesComp
WHERE (((SalesComp.WM_INVOICE_DATE) > # 01/09/2009 # AND(SalesComp.WM_INVOICE_DATE) < # 30/09/2009#) OR (( SalesComp.WM_INVOICE_DATE) > #01/09/2008 # AND (SalesComp.WM_INVOICE_DATE) < # 30/09/2008 #))
GROUP BY SalesComp.Expr4, SalesComp.WT_NOMCC;

I started to find anamalous results caused, I found, by the Query grid transposing some of the dates.
The SQL view was the same as the VBA SQL string but the grid transposed the day and month of the first and third date in the string to 09/01/2009 , and it was this version that produced the anomalous results.

How can I stop this from happening

[c_smithwick]

You need to explicitly declare each of your variables as a date. Your first line of code

Dim FROM1, FROM2, TO1, TO2 As Date

is only declaring TO2 as a date, all the rest are declared as variants

Declare them this way instead:

Dim FROM1 As Date
Dim FROM2 As Date
Dim TO1 As Date
Dim TO2 As Date

This is the preferred way of declaring variables to explicitly define their data types. It saves Visual Basic from having to force convert them into the needed type when they are first used. Since you are making calls to the DateValue function, this could be causing your problem.

[John Southern]

With high hopes I changed the Dim statement as you suggested, but to no avail. The inversion of day and month continued.
The template query design grid used actual dates, (for September) not parameters
After running code the SQL view showed :-
WHERE (((SalesComp.WM_INVOICE_DATE)>#1/3/2009# And (SalesComp.WM_INVOICE_DATE)<#3/31/2009#)) OR (((SalesComp.WM_INVOICE_DATE)>#1/3/2008# And (SalesComp.WM_INVOICE_DATE)<#3/31/2008#))

Which should have given the desired result but:-
The Query grid showed:-
>#03/01/2009# And <#31/03/2009#
OR >#03/01/2008# And <#31/03/2008#

The table gave results consistent with this grid showing results from January to March for both years not for the month of March that I had wanted.

The dates I had input with the code included leading zeros ie 01/03/09
I am in the UK so computer is set up to use this day month year format

[Gerry]

Can you clarify what anomalies you are seeing in the results. Your last statement about the results said it included "from January to March for both years not for the month of March that I had wanted." I'm not clear what you mean by this.

[c_smithwick]

Try the following and see if it cures things:

Dim FROM1 As String
Dim FROM2 As String
Dim TO1 As String
Dim TO2 As String
FROM1 = Format(CDate(InputBox("Date from", "Input")), "mm/dd/yy")
TO1 = Format(CDate(InputBox("Date to", "Input")), "mm/dd/yy")
FROM2 = DateAdd("yyyy", -1, CDate(FROM1))
TO2 = DateAdd("yyyy", -1, CDate(TO1))
strSQL = "SELECT SalesComp.Expr4, SalesComp.WT_NOMCC, Sum(SalesComp.WT_NET_TOTAL) AS SumOfWT_NET_TOTAL INTO SumComp FROM SalesComp WHERE (((SalesComp.WM_INVOICE_DATE) > # " & FROM1 & " # AND(SalesComp.WM_INVOICE_DATE) < # " & TO1 & "#) OR (( SalesComp.WM_INVOICE_DATE) > #" & FROM2 & " # AND (SalesComp.WM_INVOICE_DATE) < # " & TO2 & " #)) GROUP BY SalesComp.Expr4, SalesComp.WT_NOMCC;"
CurrentDb.QueryDefs("SUMSalesComp").SQL = strSQL
DoCmd.OpenQuery "SUMSalesComp"

[John Southern]

Gerry,
The purpose of the project is to provide a comparison of sales for a period in one year , against the same period of a previous year. Originally I did this by using four parameters (Between FROM1 and TO1 OR Between FROM2 and TO2). As FROM2 and TO2 can be derived from FROM1 and TO1 I am trying to take advantage of this to only enter 2 "parameters" in VBA
Unfortunately my efforts so far have been dogged by the transpositions I have described in earlier posts.
So I enter eg FROM1 as 01/03/10 to get sales from 1st March this is transposed to 03/01/09 giving sales from 3rd January not at all what I want!
I hope this makes things clearer
C Smithwick
Thanks for that, I am just about to try it out.

[John Southern]

C Smithwick thank you very much. I have spent the morning testing and the code works every time. I have had problems with date formats on other projects. I shall remember CDate.
John