Formatting dates

[John Southern]

I have the following code. It gets two dates using input boxes, calculates the corresponding dates for the previous year and then uses these dates as parameters in a series of Queries.

 
Sub Comparisons()
Dim FROM1 As Date
Dim FROM2 As Date
Dim TO1 As Date
Dim TO2 As Date
Dim strSQL As String
Dim strSQL2 As String
FROM1 = Format(InputBox("Enter Date from", "Input"), "mm/dd/yy")
 
TO1 = Format(InputBox("Enter Date to", "Input"), "mm/dd/yy")
FROM2 = DateAdd("yyyy", -1, CDate(FROM1))
TO2 = DateAdd("yyyy", -1, CDate(TO1))
Call addBudget(FROM1)
strSQL = "SELECT SalesComp.Expr4, SalesComp.WT_NOMCC, Sum(SalesComp.WT_NET_TOTAL) AS SumOfWT_NET_TOTAL, First(SalesComp.Expr1) As FirstOfExpr1 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"
strSQL2 = "SELECT EQryCredits.NOMCC, EQryCredits.Expr4, Sum(EQrycredits.Amount) as SumOfAmount, [Expr4] & [NOMCC] AS Expr1,First(EQryCredits.Expr3) as FirstOfExpr3 INTO SelCredits FROM EQrycredits WHERE(((EQryCredits.Date)># " & FROM1 & " # AND (EQryCredits.Date) < #" & TO1 & "#) OR (( EQryCredits.Date) >#" & FROM2 & " # AND (EQryCredits.Date )< # " & TO2 & " #)) GROUP BY EQryCredits.Expr4,EQryCredits.NOMCC;"
CurrentDb.QueryDefs("FQrySumCredits").SQL = strSQL2
DoCmd.OpenQuery "FQrySumCredits"
End Sub

The dates are entered in the English way dd-mm-yyyy and should be reformatted into mm-dd-yyyy for MS Query to work

My problem is that the dates FROM1 and FROM2 appear to be formated differently from the dates TO1 and TO2, and this means that the queries give anomalous results.
As far as I can see both dates are treated in the same way it seems very strange that they should appear differently in the SQL statements of the queries.
Any help would be much appreciated.


John

[llkhoutx]

A date is a date is a date. Formating a date is to display a date in a specific format.

It's possible that your date in you table is actually a string, not a date. Check that out.

[pbaldy]

Actually dates in VBA/SQL need to be in US format. I use a variation of this:

http://www.mvps.org/access/datetime/date0005.htm

More info and an alternate method here:

http://support.microsoft.com/default...b;en-us;210069