YTD total sales

[dmccallum]

I have a query that SUMs total sales but I need to check the delivery date against the current date and make sure the SUM I display is YTD. How can I get the current date and what calculation could I perform to make sure the data is not more than one year from today? I was thinking of:

SELECT SUM(Tot_Sales) AS Expr1, Del_Date
FROM JobCost
WHERE (Del_Date < 'getdate + 1')
GROUP BY Del_Date

 

[tomschafer]

To return the current date use the Date() function. To make sure the date is within the last year use Date()-365.

 

[dmccallum]

I received the error message:

Cannot be converted to date/time

 

[tomschafer]

Is the field set as Date/Time in the table??? If not and you do not want to change it you can use this in your where clause...

CDate(Del_Date)<Date()+1

 

[dmccallum]

My Del_Date is an 8 character string and when I try your suggestion I still get the same error message:

Your entry cannot be converted to a valid date time value.

 

[tomschafer]

What is the format of your quazi date entry??

 

[dmccallum]

Del_Date is the only field I'm working with and it is:

yyyymmdd

and then I've tried comparing it to DATE()

I've even tried ToNumeric(Left(Del_Date,4)<DATE()+1

 

[tomschafer]

Is there a reason you can not format the field as Date/Time instead of char???

 

[dmccallum]

I sometimes get invalid date.

 

[tomschafer]

Are you entering the slashes between dateparts when you enter the data?? i.e. 2001/12/16 or are you merely entering 20011216, the previous will not work.

 

[tomschafer]

Also if you set the field to Date/Time and add a user defined format of yyyymmdd it will not store the slashes, but they must be there at entry.

 

[dmccallum]

When the data in the database is exists as 20011216.

 

[tomschafer]

Ok paste this function into a module and save it. Then use it in your query.

Function ConvertToDate(strData As String) As Date
' strData is a character string 8 characters long

Dim strDay As String
Dim strMonth As String
Dim strYear As String

strDay = Right(strData, 2)
strMonth = Mid(strData, 5, 2)
strYear = Left(strData, 4)

ConvertToDate = strDay & &quot;/&quot; & strMonth & &quot;/&quot; & strYear

End Function

 

[dmccallum]

I created the module named ConvertToDate and changed one line:

ConvertToDate = strDay & &quot;/&quot; & strMonth & &quot;/&quot; & strYear

to

ConvertToDate = strMonth & &quot;/&quot; & strDay & &quot;/&quot; & strYear

But I still get no results. This how I did it:

SELECT SUM(Tot_Sales) AS YTD
FROM JobCost
GROUP BY Del_Date
HAVING (del_date)
= '(ConvertToDate({jobcost&quot;.Del_Date})IN YearToDate'

Do you see what I did wrong?

 

[tomschafer]

I would change the function back to what it was originally, note- if you want to change the displayed format of the date do it in the query. On the query try this..
SELECT SUM(Tot_Sales) AS Expr1, Del_Date
FROM JobCost
WHERE (ConvertToDate(Del_Date)>Date()-365)
GROUP BY Del_Date

 

[dmccallum]

Now I get:

Invalid scalar function: ConvertToDate

 

[tomschafer]

OK let's forget about the function. This is the same thing just hardcoded into the SQL. Try using this query...

SELECT
CDate(Right([Del_Date],2) & &quot;/&quot; & Mid([Del_Date],5,2) & &quot;/&quot; & Left([Del_Date],4)) AS Expr1,
Sum(Tot_Sales) AS SumOfTot_Sales
FROM
Table1
GROUP BY
CDate(Right([Del_Date],2) & &quot;/&quot; & Mid([Del_Date],5,2) & &quot;/&quot; & Left([Del_Date],4))
HAVING
(((CDate(Right([Del_Date],2) & &quot;/&quot; & Mid([Del_Date],5,2) & &quot;/&quot; & Left([Del_Date],4)))>=Date()-365));

 

[dmccallum]

Syntax error: SELECT
CDate(Right

I think the problem is actually with Pervasive. When I checked their website concerning the Invalid scalar erorr message they acknowledged the bug and are considering fixing it in the next version.