Help to extract data from the table

[Starmo]

Hello peoples!
I have a table as in the picture. How to get out of the first column in 2013, or any other year so I could know the sum of the third column.
Could I extract data for example from 02.01.2013 to 08.05.2014.
How could I do it over the form?

Thank you in advance!

[mks123]

Hello peoples!
I have a table as in the picture. How to get out of the first column in 2013, or any other year so I could know the sum of the third column.
Could I extract data for example from 02.01.2013 to 08.05.2014.
How could I do it over the form?

Thank you in advance!

perhaps Aggregate Function could solve your problem.

follow link https://www.teachucomp.com/aggregate...cess-tutorial/


create query and save it down, after that goto form design and select "Subquery control" and change source object to the query you create.

[Starmo]

Hello mks123!

You did not help me much, or I do not know how to follow your instructions.
Anyway, thanks for the try!

[Minty]

Replace the column names with your field names, then paste the following into the SQL view of the query designer.

SELECT Sum(Your3rdColumn) 
FROM YourTable
WHERE [Column1Field] >= #02.01.2013# And [Column1Field]  <=#08.05.2014# 
Group By Datepart("yyyy",[Column1Field])

[Starmo]

Replace the column names with your field names, then paste the following into the SQL view of the query designer.

SELECT Sum(Your3rdColumn) 
FROM YourTable
WHERE [Column1Field] >= #02.01.2013# And [Column1Field]  <=#08.05.2014# 
Group By Datepart("yyyy",[Column1Field])

I did exactly what you told me to do, but this is out

[Minty]

Are those dates actually stored as dates or text ?

Assuming they are actually stored as dates , try formatting them slightly differently?

WHERE [Column1Field] >= #02/01/2013# And [Column1Field]  <=#08/05/2014#

[Starmo]

Are those dates actually stored as dates or text ?

Assuming they are actually stored as dates , try formatting them slightly differently?

WHERE [Column1Field] >= #02/01/2013# And [Column1Field]  <=#08/05/2014#

Yes, that's right. Thank you very much.
Is it possible to change this in form, so that I pull this information through the form?
It would be much simpler.

[Minty]

Yes assuming your form is call frmReports , and your two date controls were called txtDateFrom and txtDateTo something like

WHERE YourDateField Between Format([Forms]![frmReports]![txtDateFrom],"#yyyy/mm/dd#") AND  Format([Forms]![frmReports]![txtDateTo ],"#yyyy/mm/dd#")

The use of the format should ensure compatibility anywhere in the world, no matter what the windows locale settings are.

[Starmo]

Yes assuming your form is call frmReports , and your two date controls were called txtDateFrom and txtDateTo something like

WHERE YourDateField Between Format([Forms]![frmReports]![txtDateFrom],"yyyy/mm/dd") AND  Format([Forms]![frmReports]![txtDateTo ],"yyyy/mm/dd")

The use of the format should ensure compatibility anywhere in the world, no matter what the windows locale settings are.

Thank you for your selfless help in building this project. I did not think it would be so complicated, but now I will not give up, when at least I think I got away quite far. For me the total beginner has done so far a big step forward.
I'm not sure I can implement this your last post.
I opened a FORM DESIGN new form. Do I need to take two TEX BOXs and work over them?
Where do I put that code you sent me?
If you can, please explain me a bit more closely.


Thank you very much!

[Minty]

Yes add two text boxes call them something sensible as per my earlier post.
Format them both to short date, this will then allow the datepicker to be used, which is better for users.

Add a command button to open the query we had earlier with the changed where clause in post#8 ,
OR open the query designer and in the criteria for the date click on the expression builder and you can navigate to your new form and pick the controls to put into your criteria.

if you get stuck post up a stripped down version of what you have, and we'll take a look for you.

[Starmo]

Yes add two text boxes call them something sensible as per my earlier post.
Format them both to short date, this will then allow the datepicker to be used, which is better for users.

Add a command button to open the query we had earlier with the changed where clause in post#8 ,
OR open the query designer and in the criteria for the date click on the expression builder and you can navigate to your new form and pick the controls to put into your criteria.

if you get stuck post up a stripped down version of what you have, and we'll take a look for you.

Hello Minty!

I have not managed to solve the "homework", but it is not so urgent.
I have other things to ask. In my program, there are plenty of tables, reports, queries.
I made a FORM that opens certain tables, forms or queries.
This program should be shared with two other colleagues.
How can I restrict or "hide" tables and other things and allow them to enter and view data.
I would also like to have the program open at FORM, which I described above (FORM, which opens certain forms, forms or queries).