CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2013
    Posts
    34

    Generate Daily Weekly Monthly and Yearly Report using MS ACCESS 2003

    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

  2. #2
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    Re: Generate Daily Weekly Monthly and Yearly Report using MS ACCESS 2003

    Use a select query with a where clause and give the date range as part of the where clause
    Always use [code][/code] tags when posting code.

  3. #3
    Join Date
    Feb 2013
    Posts
    34

    Re: Generate Daily Weekly Monthly and Yearly Report using MS ACCESS 2003

    Quote Originally Posted by DataMiser View Post
    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..

  4. #4
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    Re: Generate Daily Weekly Monthly and Yearly Report using MS ACCESS 2003

    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?
    Always use [code][/code] tags when posting code.

  5. #5
    Join Date
    Feb 2013
    Posts
    34

    Re: Generate Daily Weekly Monthly and Yearly Report using MS ACCESS 2003

    Quote Originally Posted by DataMiser View Post
    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..

    Code:
    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.

  6. #6
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    Re: Generate Daily Weekly Monthly and Yearly Report using MS ACCESS 2003

    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.
    Always use [code][/code] tags when posting code.

  7. #7
    Join Date
    Feb 2013
    Posts
    34

    Re: Generate Daily Weekly Monthly and Yearly Report using MS ACCESS 2003

    Quote Originally Posted by DataMiser View Post
    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! /..

  8. #8
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    Re: Generate Daily Weekly Monthly and Yearly Report using MS ACCESS 2003

    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
    Code:
    While Not Rs.EOF
    Is much cleaner

    Code:
    Do Until Rs.EOF
    Is even better
    Always use [code][/code] tags when posting code.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured