Stop Parameter Value Box

[sunny8760]

Hi Everyone,

I'm a beginner Access user and have built a small database to track small projects and issues I need to manage at work.

I have a query that retrieves a list of all "Open" issues/projects that I'm working on. I've added a couple of columns in the query to calculate the age of each project (comparing Now() to Start Date), and using a comparison between the last time I updated the project to Now().

The problem I'm having is that the second calculation is based on the first calculation. As a result, the second calculation causes prompt box "Enter Value Parameter" to pop open whenever I open the form based on this query.

I would like to know how can I open this form without the prompt box popping up?

 

[vbaInet]

Welcome to the forum sunny8760!

Copy and paste the SQL of your query here.

 

[CJ_London]

suggest you also post any relevant vba code around opening the queries

 

[sunny8760]

Hi Everyone,

Thanks for welcoming me. I have learned a lot from reading previous posts on this form and am exciting about using access .

I have addressed the problem I posted in this thread by splitting the query into two separate queries. The first query provides a value that the second query uses to create the final result that I was after.

For those who are interested, here is what I was trying to do:
As mentioned before, I manage several issues/projects that involve various teams in my organization and in some cases customers and vendors.

In some cases these projects become complex and can drag out for several months without reaching a conclusion. I created this database to track various actions taken along the way to resolving/completing the projects. To ensure things stay on track I made sure that among other things, 3 important pieces of information were being tracked:
1. total life span since start of issue.
2. date and time of when the last action was taken,
3. using #2, I calculated how many days had elapsed.

I now have one query calculating item 1 and 2, and a second query that uses the first query and calculates item 3. This has stopped the query from showing the prompt box I was complaining about.

Unfortunately, this calculation doesnt calculate based on weekdays. The value in item 3 includes weekends, but that's another problem I will now work toward solving.

Thanks,

 

[CJ_London]

Unfortunately, this calculation doesnt calculate based on weekdays. The value in item 3 includes weekends, but that's another problem I will now work toward solving

an approximation can be determined by having a calculation along these lines

NoOfWeekDaysateDiff("d",1,[Last UpDate],[Date])/7*5

If you need to know exactly, you'll need to write a function like one that can be found here

https://support.microsoft.com/en-us/kb/210562

or here

http://bytes.com/topic/access/answers/945020-there-datediff-function-counts-business-days

benefit of the latter example is that you can include holidays etc if you include a suitable table

 

[sunny8760]

CJ_London, thank you for the suggestion. I will try the date formulas and if I encounter any problems I'll start a new thread. Thanks