Generate Daily Weekly Monthly and Yearly Report using MS ACCESS 2003

[jovsjovel]

Hello Guys.. I need Your Help.. To generate the report of the daily weekly monthly and yearly report of my database.

Can You Give me Example or Idea to Solve my Problem,

Thanks Guys

[DataMiser]

Use a select query with a where clause and give the date range as part of the where clause

[jovsjovel]

Use a select query with a where clause and give the date range as part of the where clause

i am using date picker to make a report in weekly sir. but i don't know to how to code for the weekly report.. help me sir iam trying to understand but i can't.. more example sir..

[DataMiser]

You have not provided any details so I can't tell you much more than I have already. Do you know how to use a select query? Do you know how to construct a where clause? Do you know how to use ADO? If not have you searched for examples?

[jovsjovel]

You have not provided any details so I can't tell you much more than I have already. Do you know how to use a select query? Do you know how to construct a where clause? Do you know how to use ADO? If not have you searched for examples?

Yes Sir I know how ton query and i know how to where clause..i know how to ado.

But i don't know how to determine the date in weekly monthly and yearly..

Dim listx As ListItem
Dim rs As New ADODB.Recordset

If DTPicker1.Value = "" Then
    MsgBox "The Date Field is Empty.", vbExclamation, "Details"
      Else
    rs.Open "Select * from Dailyrecord where purchase_date = " & DTPicker1.Value & "", acd, adOpenForwardOnly
If rs.RecordCount = 0 Then
        MsgBox "The Date you Select Does Not Exist!", vbExclamation, "Details"

Else
ListView1.ListItems.clear
While rs.EOF <> True
Set listx = ListView1.ListItems.Add(, , rs.Fields("Receipt"))

listx.SubItems(1) = rs.Fields("BarCode")
listx.SubItems(2) = rs.Fields("ProductName")
listx.SubItems(3) = rs.Fields("Quantity")
listx.SubItems(4) = rs.Fields("Price")
listx.SubItems(5) = rs.Fields("TotalPrice")
listx.SubItems(6) = rs.Fields("purchase_date")
rs.MoveNext

Wend
End If
ListView1.Enabled = True
End If

this is my code the error is always popup The Date you Select Does Not Exist!" .. even though the date already in db.

[DataMiser]

Is the field a date field in the database? Does it also include the time?

You need to use delimiters around a date value if it is a date field in Access that would be # i.e. DateField=#2/3/2013# or whatever
If your date field has a time included you will never get a match when using = date because the time will not match.

What you would need to do is use a date range meaning a starting and ending date/time then either use >= and <= or the between clause to get the data you seek.

[jovsjovel]

Is the field a date field in the database? Does it also include the time?

You need to use delimiters around a date value if it is a date field in Access that would be # i.e. DateField=#2/3/2013# or whatever
If your date field has a time included you will never get a match when using = date because the time will not match.

What you would need to do is use a date range meaning a starting and ending date/time then either use >= and <= or the between clause to get the data you seek.

sir this is my code for weekly
Dim listx As ListItem
Dim rs As New ADODB.Recordset

rs.Open "Select * from Dailyrecord where purchase_date Between " & DTPicker2.Value & " And " & DTPicker3.Value & " ", acd, adOpenForwardOnly
If rs.RecordCount = 0 Then
MsgBox "The Date you Select Does Not Exist!", vbExclamation, "Details"

Else
ListView1.ListItems.clear
While rs.EOF <> True
Set listx = ListView1.ListItems.Add(, , rs.Fields("Receipt"))

listx.SubItems(1) = rs.Fields("BarCode")
listx.SubItems(2) = rs.Fields("ProductName")
listx.SubItems(3) = rs.Fields("Quantity")
listx.SubItems(4) = rs.Fields("Price")
listx.SubItems(5) = rs.Fields("TotalPrice")
listx.SubItems(6) = rs.Fields("purchase_date")
rs.MoveNext

Wend
End If

but the error is always The Date you Select Does Not Exist! /..

[DataMiser]

You do not have any delimiters in your sql string

you must use the # character around dates in a query for access mdbs assuming that it is a date field in the data base. If it is a string then you must use ' and if it is numeric then you don;t use a delimiter but your date values are not going to work.

On another note why did you do this

While rs.EOF <> True

While Not Rs.EOF

Is much cleaner

Do Until Rs.EOF

Is even better