SQL Challenge

[AnthonyJ20]

Hello everyone. What I'm trying to do is use one SQL Statement to pull not only the current month's data but also that of the previous month. Really is it possible, and if so, What in the world would it look like. Below is the SQL statement that I want to transform. I currently have two variables called MyDate and MyDate2 which control what the output will be. Anyone with an idea?

Sql = "SELECT * from CustomerBoxes,tblDataTableNew " _
& "Where CustomerBoxes.CustomerID = tblDataTableNew.DataTable AND VolDate >= #" & MyDate & "# AND VolDate <= #" & MyDate2 & "# AND FullPay + ImageItems > 0 " _
& "Order By CustomerBoxes.CustomerName,tblDataTableNew.CustomerName,tblDataTableNew.VolDate "


currentArtist = ""
set rs = Cnt.execute(sql)

 

[AnthonyJ20]

Don't give up just yet. I tried what you suggested, commenting out the line. But still the same error. Can this thing be beaten?

Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/NewPercentSpreadSheet.asp, line 47, column 97
& "Where ((CustomerBoxes.CustomerID = tblDataTableNew.DataTable) AND (VolDate BETWEEN #&DateAdd("m",-1,#" & MyDate & "#)&# AND #&DateAdd("m",-1,#" & MyDate2 & "#)&#) AND (FullPay + ImageItems > 0)) "_
------------------------------------------------------------------------------------------------^

 

[SatishPutcha]

Your last error was because of double quotes.

Try:

& "Where ((CustomerBoxes.CustomerID = tblDataTableNew.DataTable) AND (VolDate BETWEEN #" & DateAdd("m",-1,MyDate)& "# AND #" & DateAdd("m",-1,MyDate2) & "#) AND (FullPay + ImageItems > 0)) "_

 

[AnthonyJ20]

10091976, Thank you!! Man you saved the day. My hat off to you as well DotNetGnat for sticking with this as long as you did in your efforts to assist. You guys are awesome!

 

[AnthonyJ20]

Just one more question 10091976. The SQL is working fine, but it's showing only the previous month's data. Can the function be switched so that the Current month is displayed while still containing the data from the previous month?

 

[SatishPutcha]

You would need to change the date range to something like earlier date to today's date. That should do it. Hope this helps.

Regards
Satish

 

[AnthonyJ20]

Just a bit more info. The variables MyDate and MyDate2 are request.form objects that get their values from dates I pick from a calendar. If I want to see data for Oct 1-17 I pick 10/1/2005 as my start date and 10/17/2005 as my end date. You mentioned changing the date range to something earlier. What does that mean?

 

[SatishPutcha]

If you have 10-Oct-05 and 15-Oct-2005, take the earlier of the two i.e. 10-Oct-05 as MyDate and make MyDate2 equal today's date.

Regards
Satish

 

[AnthonyJ20]

Got it 10091976. I'll give it a shot.

 

[Genimuse]

Also, for reference, you can see the sql with a write PLUS a flush, like

Response.Write("<h3>" & sql & "</h3>")
Response.Flush()

And for additional reference, the SQL Server forum (or the "Microsoft Access Queries and Jet SQL" forum) is a really great place to go for SQL stuff.

Not that the guys here aren't great, too.

Also, you should seriously consider awarding these guys stars, in my opinion. They've really stuck with you.