Solved

get a report based on a date : vba access

Posted on 2014-02-11
16
448 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 34

Accepted Solution

by:
PatHartman earned 500 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 34

Expert Comment

by:PatHartman
ID: 39860858
Select ...
From ...
Where Forms!YourForm!SomeDate Between [Start Date] AND [End Date];
0
 

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 34

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 34

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 34

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 34

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 34

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now