-
March 10th, 2013, 10:39 AM
#1
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
-
March 10th, 2013, 11:04 AM
#2
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.
-
March 10th, 2013, 11:08 AM
#3
Re: Generate Daily Weekly Monthly and Yearly Report using MS ACCESS 2003
Originally Posted by DataMiser
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..
-
March 10th, 2013, 12:33 PM
#4
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.
-
March 10th, 2013, 05:27 PM
#5
Re: Generate Daily Weekly Monthly and Yearly Report using MS ACCESS 2003
Originally Posted by 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?
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.
-
March 10th, 2013, 06:29 PM
#6
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.
-
March 10th, 2013, 07:28 PM
#7
Re: Generate Daily Weekly Monthly and Yearly Report using MS ACCESS 2003
Originally Posted by 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.
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! /..
-
March 11th, 2013, 01:56 AM
#8
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
Is much cleaner
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|