Update query - calcualte a percentage input by user?

[hernandez0206]

I need help with my project I need help with my queries update the MonthlyFee field by increasing it by 10% thank you.

 

[irsmalik]

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

 

[Minty]

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]

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]

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]

if you can share your db with us, we will see what we can do.
just mention which table to work.

 

[irsmalik]

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]

select your query from the navigation panel.
on the ribbon menu, Create->Report.
you may need to edit the report.

 

[irsmalik]

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_]

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

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]

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]

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_]

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

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

Me.StartDate = Me.EndDate -30

but I know most businesses don't always want 30 days.

 

[irsmalik]

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