Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-10-2019, 12:12 AM   #31
irsmalik
Newly Registered User
 
Join Date: Jan 2015
Location: Multan - Pakistan
Posts: 43
Thanks: 0
Thanked 0 Times in 0 Posts
irsmalik is on a distinguished road
Send a message via Skype™ to irsmalik
Re: Update query - calcualte a percentage input by user?

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 is offline   Reply With Quote
Old 08-10-2019, 02:12 AM   #32
irsmalik
Newly Registered User
 
Join Date: Jan 2015
Location: Multan - Pakistan
Posts: 43
Thanks: 0
Thanked 0 Times in 0 Posts
irsmalik is on a distinguished road
Send a message via Skype™ to irsmalik
Re: Update query - calcualte a percentage input by user?

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
irsmalik is offline   Reply With Quote
Old 08-10-2019, 03:44 PM   #33
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,037
Thanks: 20
Thanked 381 Times in 374 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Update query - calcualte a percentage input by user?

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.

Mark_ is offline   Reply With Quote
Old 08-16-2019, 11:46 PM   #34
irsmalik
Newly Registered User
 
Join Date: Jan 2015
Location: Multan - Pakistan
Posts: 43
Thanks: 0
Thanked 0 Times in 0 Posts
irsmalik is on a distinguished road
Send a message via Skype™ to irsmalik
Re: Update query - calcualte a percentage input by user?

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

irsmalik is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Accept User Input for a Query praveen_khm Queries 5 07-10-2008 02:50 AM
Wildcard query with User Input Parameter Not Working shewolf Queries 1 07-12-2007 01:58 PM
Query : Group by & Sum Total with user input? Demoniak Queries 5 03-18-2007 04:58 PM
Passing parameter to a query from within a report (not user input) nelly Reports 2 10-19-2004 07:59 AM
[SOLVED] Storing the user input of a parameter query. skhan20 General 2 08-08-2002 04:11 PM




All times are GMT -8. The time now is 09:32 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World