Auto Insert- i can't solve this problem.

[hyousef]

Some one help me please with this challenge.

Let me first give you a short summary about my DB, Lets take an example, there is budgetRef(ProjectNumber), each budgetRef has 7 phases(Activities) these phases are standard, each phase has a number of SubActivities. For example, P30158 has all the 7 phases such as (Intiation,Pre bidding....etc) the initiation phase has TOR, and bid SubActivities.

oK, I have 3 subForms (sfrmProjectRef, sfrmBudgetActivityDates, sfrmSubActivity). I placed these subform in "ProjectOverAll" form.

First a user will start enterindg dates from the lowest level which is "sfrmSubActivity".
Lets take P30158, Initiation, TOR, plannedStartDate
then P30158, Initiation, Bid, plannedStartDate.

then i need to loop through these 2 records and find out the lowest plannedstartdate and enter it automatically into the plannedStartDate in initiation phase for P30158 in "sfrmBudgetActivityDates"

then in "sfrmSubActivity" i should loop through all plannedStartDates records for all phases for a particular project lets say P30158 in this case and find out the lowest plannedStartDates and enter it automatically into plannedstartdate textbox for P30158 in "sfrmProjectRef"

For plannedEndDate, i need to do the same thing but find the bigger or the latest date. also, i need to do the same for Actual and forecast dates. i am still struggling...can't make any progress. What do you think about this problem...Any help.

It might be difficult to understand what i want without having a look at my DB.
I can email my db, if some one is willing to help. here is my email: hyousef4@yahoo.com

 

[hyousef]

I think i wrote too much in the previous message, so may be you gus got confused.

Ok,here is the first step that i need to do:

I have a number of text boxes in a column named txtplannedStartDate.
How to loop through a column(all the text boxes) and find out the lowest or the earlier date?

Thanks in advance.

 

[David R]

Create a new query. Include ONLY the date field you're interested in. Click the Totals button (sigma) on the taskbar. Change "Group By" to "Max" or "Min". Run the query and see what you get. You can include the ProjectNumber to get a min/max for each project, of course.

Does that help get you rolling? You can also use DMax/DMin outside of queries, but they are kind of resource-intensive.

HTH,
David R

 

[hyousef]

Thank you Dave,
It works but there is one problem...

There are some blank fields...For the "Min", it will count the blank and insert the blank field as the minimum...but how can i avoid counting blank fields???

 

[Rich]

Not Is Null in the fields criteria.

 

[hyousef]

Rich,
no it didn't do it, For example, lets say i have a column with 4 rows...one of the rows is blank and the other 3 filled with dates, if i use "not is null" then it will not count the whole column...but what i want is to skip the blank and find the minimum for the other 3 dates.

 

[David R]

I don't get it. I just tested it, because I didn't have time to Friday. A date field Min() is the same regardless of whether there are blanks in it or not.
Are you sure you formatted the field as Date/Time in the underlying table?

 

[hyousef]

Thanks Dave and Rich, its working.