get a report based on a date : vba access

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
gtmathewDallasAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
PatHartmanConnect With a Mentor Commented:
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
 
gtmathewDallasAuthor Commented:
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
 
PatHartmanCommented:
Select ...
From ...
Where Forms!YourForm!SomeDate Between [Start Date] AND [End Date];
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
gtmathewDallasAuthor Commented:
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
 
PatHartmanCommented:
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
 
gtmathewDallasAuthor Commented:
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
 
PatHartmanCommented:
Are you sure there are records that satisfy the criteria?
0
 
gtmathewDallasAuthor Commented:
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
 
PatHartmanCommented:
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
 
gtmathewDallasAuthor Commented:
Please kindly check it, the db with just the test table,
Thanks
test-db.accdb
0
 
PatHartmanCommented:
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
 
gtmathewDallasAuthor Commented:
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
 
gtmathewDallasAuthor Commented:
Looks like the following query satisfy the requirement :
SELECT  *  FROM test WHERE   test.date2 >= #12/13/2013#
0
 
gtmathewDallasAuthor Commented:
No I am sorry, It will not
0
 
gtmathewDallasAuthor Commented:
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
 
PatHartmanCommented:
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
All Courses

From novice to tech pro — start learning today.