By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,853 Members | 2,007 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,853 IT Pros & Developers. It's quick & easy.

Using Date Range to generate reports

P: 48
hello again

i have almost finished with my database. i have decided to generate the reports by using a date or date range and i can't get it to work. first i used parameters in a query but its not very user friendly so i decided to make my own date entry form.
the main problem is the actual date in the table. when i try to generate a report the report shows all the records, regardless of which date i input. I've tried all sorts but i'm failing to sort out this part. anyone have an idea of how this can be fixed?
Sep 23 '08 #1
Share this Question
Share on Google+
12 Replies


ADezii
Expert 5K+
P: 8,627
How about providing some more details with all relevant information.
Sep 23 '08 #2

NeoPa
Expert Mod 15k+
P: 31,426
As the man says, you could provide some more detailed information. Access is a technical subject and general descriptions rarely help much. Specifics are required.

In the mean-time, check out Literal DateTimes and Their Delimiters (#) for help on how you should specify dates in literal form.
Sep 23 '08 #3

P: 48
sorry about that. here's the info.

i have a table called tblMain and a form that is connected to the table through DAO. the form is for adding records and these records are saved to tblMain. after each month the users need to generate a report but from a certain date. the problem is that when a user tries to add a record the date displays the current date, which is fine, but if they access the same record the next day, it will have that days date, instead of the date when the record was added. thus defeating the whole purpose of the of generating reports that were added on a specific date. i have code for the date range input for the users and i think its okay, but i can't know for sure until i can actually get working dates.
here's the code for the date range input

Expand|Select|Wrap|Line Numbers
  1. Private Sub Preview_Click()
  2.  If IsNull([Start Date]) Or IsNull([End Date]) Then
  3.  MsgBox "You must enter both beginning and ending dates." DoCmd.GoToControl "Start Date" 
  4. Else If [Start Date] > [End Date] Then 
  5. MsgBox "Ending date must be greater than Beginning date." DoCmd.GoToControl "Start Date" 
  6. Else
  7.  Me.Visible = False
  8.  End If 
  9. End If
  10.  End Sub
thankyou for your help
Sep 23 '08 #4

NeoPa
Expert Mod 15k+
P: 31,426
Your checking code is logically wrong. Check through it to find the error.

Your table [tblMain] is not explained and we can't see what, if anything, you have to store the date in. If this date is only added when the record is added, then you shouldn't have this issue.

When you try to select the records, are you selecting by the field in the record, or by Date? Date is a function returning today's date.
Sep 23 '08 #5

P: 48
when i open the form the first record is displayed and it should display the date it was created in the txtdate textbox. if a user clicks the command button to add a new record, the date should automatically display todays date. the date has to appear automatically because the user will have to add at least 50 records and i don't want them to keep typing in todays date each time they have to add a new record. but once they save the record, the record should be saved with the date from which it was created so that if i navigate through the records the next day or any time after that i will see the records and the date they were created, not todays date, unless im adding a new record. i know to view the current date on the form is date = now(), or something like that, but i can't seem to keep the date as is when the record is saved.

i hope this makes sense i know im not really good at access terminology right now.
Sep 23 '08 #6

ADezii
Expert 5K+
P: 8,627
when i open the form the first record is displayed and it should display the date it was created in the txtdate textbox. if a user clicks the command button to add a new record, the date should automatically display todays date. the date has to appear automatically because the user will have to add at least 50 records and i don't want them to keep typing in todays date each time they have to add a new record. but once they save the record, the record should be saved with the date from which it was created so that if i navigate through the records the next day or any time after that i will see the records and the date they were created, not todays date, unless im adding a new record. i know to view the current date on the form is date = now(), or something like that, but i can't seem to keep the date as is when the record is saved.

i hope this makes sense i know im not really good at access terminology right now.
I'm still not clear as to what exactly is going on here, but it appears as though you have an 'Unbound' Text Box (txtDate) with a Defaut Value set to =Date(). In this case, the Value for each New Record will always be the Current Date, and this Value will not be stored in tblMain. You need to 'Bind' (set the Control Source) txtDate to a Date/Time Field in tblMain. In this manner the Default Date will always be displayed for every New Record, but for previous Records the actual Date that the Record was added will be displayed. Does this make sense to you?
Sep 23 '08 #7

P: 48
I'm still not clear as to what exactly is going on here, but it appears as though you have an 'Unbound' Text Box (txtDate) with a Defaut Value set to =Date(). In this case, the Value for each New Record will always be the Current Date, and this Value will not be stored in tblMain. You need to 'Bind' (set the Control Source) txtDate to a Date/Time Field in tblMain. In this manner the Default Date will always be displayed for every New Record, but for previous Records the actual Date that the Record was added will be displayed. Does this make sense to you?
yes thank you answered my question spot on. it wasn't bound and i'm using DAO so i had to connect it from code and now its working. thank you again
Sep 24 '08 #8

NeoPa
Expert Mod 15k+
P: 31,426
when i open the form the first record is displayed and it should display the date it was created in the txtdate textbox. if a user clicks the command button to add a new record, the date should automatically display todays date. the date has to appear automatically because the user will have to add at least 50 records and i don't want them to keep typing in todays date each time they have to add a new record. but once they save the record, the record should be saved with the date from which it was created so that if i navigate through the records the next day or any time after that i will see the records and the date they were created, not todays date, unless im adding a new record. i know to view the current date on the form is date = now(), or something like that, but i can't seem to keep the date as is when the record is saved.

i hope this makes sense i know im not really good at access terminology right now.
Your code certainly doesn't do that now.

Have you looked at the checking code yet, that I said was wrong in post #5?
Sep 24 '08 #9

P: 48
Your code certainly doesn't do that now.

Have you looked at the checking code yet, that I said was wrong in post #5?
yes I've looked at it and i'm still trying to fix it. the answer i got in the previous post was fine because i can now get the date on the form and its actually saved. but the checking code is a disaster so thats my next dilemma. i was thinking of putting an SQL statement in the code the BETWEEN [start date] and [end date]. is that okay. i just have to figure out how i'm going to phrase it.
Sep 24 '08 #10

NeoPa
Expert Mod 15k+
P: 31,426
Check the logic of it. The code worked, but the items are comparing the wrong way around.

This will work, but it will not do what you want it to do. Is that any clearer?
Sep 24 '08 #11

P: 48
Check the logic of it. The code worked, but the items are comparing the wrong way around.

This will work, but it will not do what you want it to do. Is that any clearer?
yes it finally worked now, the reports are generating properly, according to date range. i had the same name for my end date text box and my end date label, so that was one of the reasons it wasn't working. thank you again.
Sep 25 '08 #12

NeoPa
Expert Mod 15k+
P: 31,426
Pleased to hear it :)

Good luck with your project.
Sep 25 '08 #13

Post your reply

Sign in to post your reply or Sign up for a free account.