Update query - calcualte a percentage input by user? (1 Viewer)

hernandez0206

New member
Local time
Today, 10:39
Joined
Mar 14, 2013
Messages
1
I need help with my project I need help with my queries update the MonthlyFee field by increasing it by 10% thank you.
 

irsmalik

Registered User.
Local time
Today, 21:39
Joined
Jan 28, 2015
Messages
88
How to enter dates from vba / query

Hello friends

I want to make a report which display data as below
Column 1 Jan to till today.
Column 2 Last 30 days like 16 Jul to 15 Aug.

This report is completed and all data is being displayed... see attachment

But I have to mention 16 Jul to 15 Aug manually.
How can computer write itself this sentence automatically.

Kindly guide me ...

Thanks
irsmalik
 

Attachments

  • REPORT.jpg
    REPORT.jpg
    62.9 KB · Views: 69

Minty

AWF VIP
Local time
Today, 17:39
Joined
Jul 26, 2013
Messages
10,355
This is a 6 year old unrelated thread!

I would suggest starting a new one or get a mod to move it for you.
 

irsmalik

Registered User.
Local time
Today, 21:39
Joined
Jan 28, 2015
Messages
88
Dear Minty

6 years ago I was member of this forum... so how could I know that this topic has discussed. I will as the question that I need.
If this is a knowledge base forum... .then you must help me by solving my problem...
your remarks are not appreciable.

irsmalik
 

Minty

AWF VIP
Local time
Today, 17:39
Joined
Jul 26, 2013
Messages
10,355
If you look carefully the last posting date is at the top of every post.

Your report would need to be based on two queries one to produce the current data, and one to produce the total to date. These could then be joined onto the reports data source as a third query.

Start by making two queries that do the first parts then join them to produce the third part. If you get stuck postup some sample data and I'm sure we can assist further.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:39
Joined
May 7, 2009
Messages
19,169
if you can share your db with us, we will see what we can do.
just mention which table to work.
 

irsmalik

Registered User.
Local time
Today, 21:39
Joined
Jan 28, 2015
Messages
88
Here is my query: which run behind my report.

SELECT DM.Class, DM.ClassName, DM.Size, DM.SizeName, DM.TDate, Sum(DM.BG) AS SumOfBG, Sum(DM.Demo) AS SumOfDemo, Sum(DM.DTP) AS SumOfDTP, Sum(DM.FAS) AS SumOfFAS, Sum(DM.FM) AS SumOfFM, Sum(DM.HPM) AS SumOfHPM, DM.Division
FROM DM
GROUP BY DM.Class, DM.ClassName, DM.Size, DM.SizeName, DM.TDate, DM.Division
HAVING (((DM.TDate) Between Date() And Date()-30))
ORDER BY DM.ClassName, DM.TDate;

When I run my query, my required data display.... it is OK... Now I want to print this part of my query on my report.
HAVING (((DM.TDate) Between Date() And Date()-30)).

How to do it.... plz guide.
thanks
irsmalik
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:39
Joined
May 7, 2009
Messages
19,169
select your query from the navigation panel.
on the ribbon menu, Create->Report.
you may need to edit the report.
 

irsmalik

Registered User.
Local time
Today, 21:39
Joined
Jan 28, 2015
Messages
88
Yes.. Mr arnelgp.... Query Data is not problem. It is fine.

Basically I want to print in Report Title / Description
"16 July to 15 Aug" and it will go on for further period.

At present, I do it manual... but when the user will run this report, he will not be allowed to go into Design Mod.

If any one could help me.... thanks
irsmalik
 

Mark_

Longboard on the internet
Local time
Today, 10:39
Joined
Sep 12, 2017
Messages
2,111
I would change "Having" to be a WHERE clause.

You can then add a from, "FormForDates", with two unbound text fields, "DateStart" and "DateThru" to enter your starting date and ending date. You would then reference these two fields in your where to give a start and end date.

Your query would then look like
Code:
SELECT DM.Class, DM.ClassName, DM.Size, DM.SizeName, DM.TDate, Sum(DM.BG) AS SumOfBG, Sum(DM.Demo) AS SumOfDemo, Sum(DM.DTP) AS SumOfDTP, Sum(DM.FAS) AS SumOfFAS, Sum(DM.FM) AS SumOfFM, Sum(DM.HPM) AS SumOfHPM, DM.Division
FROM DM
WHERE (((DM.TDate) Between [Forms]![FormForDates]![DateStart] And [Forms]![FormForDates]![DateEnd]))
GROUP BY DM.Class, DM.ClassName, DM.Size, DM.SizeName, DM.TDate, DM.Division
ORDER BY DM.ClassName, DM.TDate;

Your user would then type in "16 July" and "15 Aug" to get the dates you are currently looking for. They can also type in "16 Aug" and "15 Sep" for the next month.

If you want them to ONLY enter one date and calculate a one month range OR select only a month that always starts on the 16th, please let us know.
 

irsmalik

Registered User.
Local time
Today, 21:39
Joined
Jan 28, 2015
Messages
88
Dear Mr Mark

Thanks for your reply... yes this is a good idea and a better solution of my problem. I will appreciate if you also explain your second option of One Date.... can you plz explain it. thanks
irsmalik
 

irsmalik

Registered User.
Local time
Today, 21:39
Joined
Jan 28, 2015
Messages
88
Dear Mr Mark...

I have just tried your idea... its working fine as per my requirement.

thanks for your help.... but I will request again regarding your second option of Single date...
Thanks
irsmalik
 

Mark_

Longboard on the internet
Local time
Today, 10:39
Joined
Sep 12, 2017
Messages
2,111
If I understand what you are looking for (your sample data was "16 July to 15 Aug") you will want to do the following.
1) On form "FormForDates" set the default value for EndDate to be Date() to replicate what you are currently doing.
2) Set the tab order to have EndDate be the first field the user enters.
3) Add the following code to the BeforeUpdate event for EndDate

Code:
Dim DaysInMont As Integer
Dim CurYear As Integer
Dim CurMonth As Integer
Dim CurDay As Integer
    'Start by taking the date apart.
    CurYear = DatePart("yyyy", Me.EndDate)
    'Use the previous month,
    CurMonth = DatePart("m", Me.EndDate) - 1
    'and set the Start day as the day AFTER the current one
    CurDay = DatePart("d", Me.EndDate) + 1
    'Make sure we know the maximum number of days in the previous month.
    DaysInMonth = DatePart("d", DateSerial(DatePart("yyyy", Me.EndDate), DatePart("m", Me.EndDate), 0))
    'If we would be using a date that doesn't exist, use the last day of the month.
    If DaysInMonth < CurDay Then CurDay = DaysInMonth
    
    'Once we have the pieces, set the start date.
    Me.StartDate = DateSerial(CurYear, CurMonth, CurDay)

This SHOULD give you what you want and allow an end user to change either the start or end date as needed. If you want them to keep a previous StartDate that they've type in you will need to check if there is a value in Me.StartDate before running this code.

If you always need ONLY 30 days, you can replace this with a simple
Code:
Me.StartDate = Me.EndDate -30
but I know most businesses don't always want 30 days.
 

irsmalik

Registered User.
Local time
Today, 21:39
Joined
Jan 28, 2015
Messages
88
Dear Mr Mark

Thanks for the code... but is this complete code.... ? I think last part of the code is missing.... can you plz write the complete code...

thanks
irsmalik
 

Users who are viewing this thread

Top Bottom