Running a report to select specific date

[CuteGeek]

Hello Everyone,

I have a rather deep question

Having a getdate in the where statement to pull data of all files associated with the month of saledate.

Field is dfeSaledate (pulls date of every 1st Tuesday of the month)

Example:
November 18th
Saledate December 3rd (1st Tuesday of the month)
December 18th
saledate is January 7th (1st Tuesday of the month)

Need report to change according to current date and saledate.

How do I create the sql statement or have crystal reports automate this function. At present I would have to go into the where statement of report and change the saledate for each month...

(THIS IS THE GETDATE STATEMENT)
dbo.tblFCTitleEvents.dFTExamReceived > DATEADD(d, - 1, CAST(CAST(DATEPART(month, GETDATE()) AS varchar(20)) + '/' + CAST(DATEPART(day,
GETDATE()) AS varchar(2)) + '/' + CAST(DATEPART(year, GETDATE()) AS varchar(4)) + CAST('16:00:00.000' AS datetime) AS datetime))) AND
(dbo.tblFCTitleEvents.dFTExamReceived < CAST(CAST(DATEPART(month, GETDATE()) AS varchar(20)) + '/' + CAST(DATEPART(day, GETDATE())
AS varchar(2)) + '/' + CAST(DATEPART(year, GETDATE()) AS varchar(4)) + CAST('16:00:00.000' AS datetime) AS datetime))

where tblFCEventdates.dfeSaleDate = '01/07/03'

Hopefully I have made some sense of what I am trying to say...

Thank you all

 

[goranm]

Hello !

Well, I don´t know if I have understod your problem right.

Is it, that you always want to know what date the first Tuesday of next month is ?

And your dfeSaleDate field, is it stored as a character string or is it a date field (it seems to be a string ) ?

Please let me know , and we will see if we can help you.

/Goran

 

[CuteGeek]

Thank you for responding....

dfESaledate is a date field and yes I want this report to pull every Tuesday of the month....

Sale Date for this month Dec is Jan. 07
after Jan. 07 than the new sale date is Feb. 05.

I need the report to automatically change according to the sale date and current date.

I hope this makes sense..It seems to be hard for me to explain.

Thank you
for your time and help
cutegeek

 

[goranm]

Hi !

Well, I don´t know if it is the best way, but here is a formula that will give you the next coming month´ first Tuesday.

Date(year(CurrentDate),Month(CurrentDate),1)+35 - DayOfWeek(Date(year(CurrentDate),Month(CurrentDate),1)+35)+3

So I think you can use it in your record selection like this:
tblFCEventdates.dfeSaleDate = Date(year(CurrentDate),Month(CurrentDate),1)+35 - DayOfWeek(Date(year(CurrentDate),Month(CurrentDate),1)+35)+3

/Goran

 

[CuteGeek]

Happy New Year!!!

Thank you guys I will try what has been suggested. Will let you know if this helps....

Thanks again
CuteGeek