Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

get a report based on a date : vba access

Posted on 2014-02-11
16
Medium Priority
?
463 Views
Last Modified: 2014-02-17
I have a table with room details including room user(employee) info. Users can be change at any time, new users can come in, room functions can be change, etc. Here I want to get the status(users-function-ant other room info) of a particular room or all rooms status in the building as of a single date. for example, get a report like who was using the room and what was the function of that room in 1/12/2013. There is a date field in the table to show the last updated date of that room.Shall we need to add more fields(date) to the table to generate  this kind of reports.
I just want to collect the better options from you experts that will help me to go right direction in development.Please help
0
Comment
Question by:gtmathewDallas
  • 9
  • 7
16 Comments
 
LVL 40

Accepted Solution

by:
PatHartman earned 1500 total points
ID: 39851886
If you need to track history, you need a history table, you can't just write over the current data.

The simplest solution is to run an append query in the BeforeUpdate event of the form you use to update the table.  This will copy the current record -before changes and append it to history.  All other solutions require a lot more code.
0
 

Author Comment

by:gtmathewDallas
ID: 39860586
Yes I could create history table, but to get the report based on a date i request your help to create a query,  For example with an input date criteria- 12/11/2013 we have to get the report of employees those who were in the department during that time. With the below sample data answer should be Bob-Paul-Laura who were in the department on 12/11/2013 Here is the table(Space_Occupants) sample structure. Please help
Thanks

ID    room id   Employee_name       Start date                  End date
 1    101                Scott                   01/02/2012              10/13/2013
 2    101                Bob                     10/14/2013              12/12/2013
 3    300                Paul                    01/01/2012               02/14/2014
 4    101                Mark                   12/13/2013               02/14/2014
 5    200                Jim                      01/10/2012               09/09/2012
 6    200                Laura                   09/102012                02/14/2014
0
 
LVL 40

Expert Comment

by:PatHartman
ID: 39860858
Select ...
From ...
Where Forms!YourForm!SomeDate Between [Start Date] AND [End Date];
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Author Comment

by:gtmathewDallas
ID: 39864946
Unfortunately it is not returning any value, example - for an input date 12/11/2013 - it should return bob Paul and Laura, ie, who were there on that specific date in the department(table).
I am trying with different queries but its not helping out, Please help
Thanks
0
 
LVL 40

Expert Comment

by:PatHartman
ID: 39865051
Please post your query.

Is the column in the table defined as a date?
Is the control on the form defined as a date?
Did you Format() either field (that would have turned it from a date into a string so it wouldn't act like a date)?
0
 

Author Comment

by:gtmathewDallas
ID: 39865141
Yes both date fields are in Date/Time format. query with this format is working(SELECT  *  FROM test WHERE  test.date2 >= #12/13/2013#;) but not getting the required result with my different query trials.
I tried with this query(SELECT  *  FROM test WHERE   test.date1 >= #12/13/2013#   and  test.date2 <= #12/13/2013#  ), but no results
0
 
LVL 40

Expert Comment

by:PatHartman
ID: 39865244
Are you sure there are records that satisfy the criteria?
0
 

Author Comment

by:gtmathewDallas
ID: 39865265
Yes, I used this following data to test

ID      room      name        date1                       date2
1      200              paul                10/2/2012               9/2/2013
2      200              mathew        9/2/2013              12/12/2013
3      200              george        12/13/2013              2/14/2014
4      100              scott        1/1/2012              10/12/2013
5      100              sam                10/13/2013               2/14/2014
6      300              jino                1/1/2012              2/14/2014
0
 
LVL 40

Expert Comment

by:PatHartman
ID: 39865309
The only thing that makes any sense is that the dates in the table are defined as text.  Can you post a database with just the table and your query.
0
 

Author Comment

by:gtmathewDallas
ID: 39865328
Please kindly check it, the db with just the test table,
Thanks
test-db.accdb
0
 
LVL 40

Expert Comment

by:PatHartman
ID: 39865454
SELECT  *  FROM test WHERE   test.date1 >= #12/13/2013#   and  test.date2 <= #12/13/2013#

There are no rows that match the criteria.

But there are rows that match this criteria:

SELECT  *  FROM test WHERE   test.date1 >= #12/13/2013#   OR  test.date2 <= #12/13/2013#
0
 

Author Comment

by:gtmathewDallas
ID: 39865492
the requirement is to get the employees who where in the department on a date 12/13/2013. With our sample table, the result should be  George - Sam -  Jino with the date 12/13/1013, Sorry if i made any confusions,
Thanks
0
 

Author Comment

by:gtmathewDallas
ID: 39865511
Looks like the following query satisfy the requirement :
SELECT  *  FROM test WHERE   test.date2 >= #12/13/2013#
0
 

Author Comment

by:gtmathewDallas
ID: 39865517
No I am sorry, It will not
0
 

Author Comment

by:gtmathewDallas
ID: 39865580
Yes this one is working PatHartman, Thanks a lot be with me to help me out,
This query is very simple but took lot of time to get it.

SELECT  *  FROM test WHERE   test.date2 >= #12/13/2013#  and  test.date1 <= #12/13/2013#

Thanks
0
 
LVL 40

Expert Comment

by:PatHartman
ID: 39865698
Glad you got it working.

This is a good example of why it makes sense to give fields meaningful names.  I had no idea what date1 and date2 were supposed to be so I couldn't comment on the validity of the expression.  All I  could tell you was the expression as written was correct but didn't select any records because there were none that matched.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question