ASP code for monthly report of particular year.

[hemmca]

I am using the ASP code to view the monthly report of particular year.

But I am getting the error as "Incorrect syntax near '#'." ,for the below code.

strsql = "select * from domestic where city='" & city & "' and Date between #" & month1 &

"/01/" & year1 & "# and #" & month1 & "/31/" & year1 & "#;"

Please give me the correct code.

Thank you in advance,

[betheball]

As I see it, you have two problems. First, Date is a reserved word. You should not use it as a column name, but if you must, then surround it in brackets in your SQL. Having fixed that, I believe your code will work as long as the month in the query has 31 days. If not, you will get the error above.

To avoid that, you need to use a variable instead of 31. The following code should calculate the last day of whatever month1 is:


If month1 = 1 OR month1 = 3 OR month1 = 5 OR month1 = 7 OR month1 = 8 OR month1 = 10 OR month1 = 12 Then
day1 = 31
ElseIf month1 = 4 OR month1 = 6 OR month1 = 9 OR month1 = 11 Then
day1 = 30
ElseIf month1 = 2 Then

' Leap year calculations:
' All years that are evenly divisible by 400 or are evenly
' divisible by 4 and NOT divisible by 100 are leap years

test1 = year1 Mod 400
If test1 = 0 Then
day1 = 29
Else
test2 = year1 Mod 4
test3 = year1 Mod 100
If test2 = 0 And test3 > 0 Then
day1 = 29
Else
day1 = 28
End If
End If
End If

Then your SQL would become:

strsql = "select * from domestic where city='" & city & "' and [Date] between #" & month1 &

"/01/" & year1 & "# and #" & month1 & "/" & day1 & "/" & year1 & "#;"

There may be a better way to do it, but the above seems to work.

Good luck.

Note: I borrowed the leap year calculation from here:

http://www.netwood.net/~kosenko/vbcalendar.html

[betheball]

Here's a reply to my reply.

If you are always dealing with a monthly report, you could simply change your SQL to this:

strsql = "select * from domestic where city='" & city & "' and (DatePart('m',[Date])="&month1&" and DatePart('yyyy',[Date])="&year1&")"

[hazee]

I am using the ASP code to view the monthly report of particular year.



Can I have your complete code for the report ? I need to generate reports but stuck :confused: . Will be thankful If u can help.

Regards,
Hazee.