Coding help!!!!

[deepaksharma]

My greetings to all of you.


I need help from all of you with coding.

I have created a program which is made up of main form (offerentry) and sub form (greyshade). This is a program for a textile merchant to take orders for clothes. Customer related information is entered in the main form and shed and meter related to the goods are entered in the sub form. I have created seven blue colored boxes on the offer entry form. I want the first box to contain the total offers entered today ( Number of orders) and total meters should be shown.

In the second box, yesterday's date and the total number of offers entered and their total meter should be shown. Similarly, in the next blue box, if no booking has been made the day before yesterday, then the previous date and their total number of offers and their total meter should be shown. Similarly, I want to show the date of today and last 6 days, number of offers of those days and their total meter in all the seven boxes.
Please suggest me suitable coding for this.
Thanks in advance.

[Welshgasman]

Look at DSum() and DCount()
Use required criteria as needed.

[deepaksharma]

Look at DSum() and DCount()
Use required criteria as needed.

Thanks for reply
Can you tell me proper coding according to attachment

[Welshgasman]

I do not see any attachment as you are on my IL
Just Google both those functions and get to understand them. They work very very similar, almost identical in fact.

You have to start doing some of the work yourself. That way you will learn. Having everything handed to you on a plate, means you will not learn.
I have seen that countless times.
You will just come back with something like ' that works, but now I need to add an extra criteria'.
If you learn to use them, then that or something like that, will not be an issue for you.

[Minty]

You could do a totals query restricted to the last seven days and simply display that in a small read only subform or even better a list box, rather than messing about with individual text boxes?
List boxes are great for displaying a small amount of read only data.

[deepaksharma]

You could do a totals query restricted to the last seven days and simply display that in a small read only subform or even better a list box, rather than messing about with individual text boxes?
List boxes are great for displaying a small amount of read only data.

you are right but i am not able to find suitable coding for that.

[Welshgasman]

you are right but i am not able to find suitable coding for that.

Very unlikely you will. The chances of someone wanting the same data as you is remote.
You find a totals query. Then you find a last number of days query, then combine them.

here is one I have jusy seen for Today -7 days.

select taskid, status, [updated] from
yourTableName
where
[updated] between Date()-7 And Date()
And
[status] = "updated";

[Minty]

To back up @Gasman's comments, what have you tried?
If you can't create a simple totals query after 300 posts you aren't learning much from your time and interactions here.

You can't break anything by dragging a table and some fields into the query designer, clicking the totals button and seeing what results you get by playing around.

[deepaksharma]

Today I tried a lot by creating a query with name 7box but did not get success
Please let me know if I am doing a foolish thing by making seven boxes on the offer_entry form ?

[Minty]

The short answer is yes.
The code need to make that work is quite awkward, as you would have to accommodate what happens at weekends, and holidays.
What if there no results for day 3, do you make it display day 4 instead?

Why make the data spread out over so much of the form, you can make a simple subform to display the data and make it look more attractive than a list box.
See the attached.
A16send_v1.zip

[deepaksharma]

The short answer is yes.

Thank you very much, I would prefer to proceed in the way you suggest. I am also facing some problems in this please help me to solve them.

1. I want to show the data from today's date to last 7 working days in 7 days sub form (in 7 rows). If there is a holiday on any day, then except that day, the data of the previous date should be taken. How will this happen? Please suggest correct criteria for date field in seven box query.

2. I am also not able to properly set the function of counting offers in the offer number field of 7 box query. Please check and suggest the correct method.

3. When a new offer is entered in the offer entry form or editing is done in any previous offer, its meter seven days is not getting updated in the sub form. For this, I have tried many ways by applying docmd.refreshrecord code. Still, Still the data of 7daysform is not getting auto updated.

4. Please tell me how you set the current date in red color in the seven days sub form. I couldn't find any coding for this anywhere.
Thanks again.

[mike60smart]

Hi
Can you upload what you have tried so far

[deepaksharma]

Hi
Can you upload what you have tried so far

Welcome mike please see attached file in post 10

[mike60smart]

OK

So the following http://www.databasedev.co.uk/calculate_work_days.html gives you the Code to use.

You will have to create a Table that contains a List of Holidays in your Country.

I added an After Update to the Meter Control and the attached now will update the Count Subform provided by Minty.

It also shows todays date in Red

[deepaksharma]

OK

So the following [URL]http://w

thanks mike
1. Actually, what I mean is from today to those last seven days in which orders have been noted, it is not necessary that the work of noting orders is closed only on holidays, it can also happen that the employee doing noting the orders is going on holiday or some day the orders may not have been received.
Therefore, I want to show the data of the last 7 days in which bookings have been registered.

2. You have set the 7 days sub form to auto update. Please tell how you did this.

3. Please also tell by which setting you have set the current date in red color in 7 days sub form.

4. Calculation of total offer in 7 days sub form is not correct please check.
Thank you.