Make a "simple" calculator work, please (1 Viewer)

Valery

Registered User.
Local time
Today, 01:14
Joined
Jun 22, 2013
Messages
363
Hi,

I have designed the form but cannot code it! Only my date picker works, LOL!

Anyone generous and talented enough to make this work? Essential for us to calculate dates between appointments... Calculation is limited to adding or subtracting years, months, weeks or days from a selected date.

The result of the calculation does not have to be saved anywhere. So I have no table.

THANK YOU!
 

Attachments

  • DateCalculator.mdb
    300 KB · Views: 86

Valery

Registered User.
Local time
Today, 01:14
Joined
Jun 22, 2013
Messages
363
Thank you Alex but I would have no idea where to include this coding - like how it can talk to one another. This calculator has to take into consideration if it is adding or subtracting - selected from a dropdown. It also has to add lets say 2 weeks + 4 days from this date... this is complex for a beginner like me. I do understand some of the coding in the query samples in your link but they are a real stretch to making this work.

This calculator would be used 10-12 times a day, on a average. Right now, all done manually or with the help of the Internet, from time to time, which is time consuming and not very professional when you have a client on the phone. It is the reason I am looking to have it directly in the database.

I also assumed the coding would be in the properties of the text boxes not in a query... but what do I know.

Perhaps this link can assist someone who wants to code the form.

Thanks again.
 
Last edited:

Valery

Registered User.
Local time
Today, 01:14
Joined
Jun 22, 2013
Messages
363
OK! Please help! I can't believe it but I got the Unbound combo box to work in selecting Adding or Subtracting.

Also got it to work in adding weeks to my date from the unbound field "WeeksCalc".

So here is what I have:
=IIf([CalcMode]=1,DateAdd("ww",[WeeksCalc],[Date]))

CalcMode = 1 means to add, = 2 means to subtract
WeeksCalc = the number of weeks to add (or subtract)

How do I include, in the above code, the adding of other values that may be in the following unbound fields:

YearsCalc = the number of years to add (or subtract)
MonthsCalc = the number of months to add (or subtract)
DaysCalc = the number of days to add (or subtract)

Please help! I got it this far! Not bad for a beginner, right!?
 

AlexHedley

Registered User.
Local time
Today, 08:14
Joined
Aug 28, 2012
Messages
171
Looks like you're doing a great job.

I'm sure you can pass in negative numbers into the Function.

You have the right concept so if you want it to add other parts just amend your code to look at other textboxes and there equivalents:

Code:
DateAdd("yyyy",[YearsCalc],[Date])
DateAdd("d",[DaysCalc],[Date])

If you are happy to use some VBA you could put this into some buttons and have one each for Days, Months, Years.
 

Valery

Registered User.
Local time
Today, 01:14
Joined
Jun 22, 2013
Messages
363
but how do they add this all together?

I tried that - probably don't know how to "put them together" after...

How will it add 1 year, 2 months, 5 days to this one date?

And thank you for the compliment - my thinking cap was really ON, LOL
 

AlexHedley

Registered User.
Local time
Today, 08:14
Joined
Aug 28, 2012
Messages
171
Say you have an unbound textbox on your form, let's call it "txtCalc".

You could then add a button to your Form.
Rename it "cmdAddRemoveYear".

Now in the events tab click the ... Button next to the Click event.
Open the Code Builder.

Code:
Sub cmdAddRemoveYear_Click()

End Sub

Now you can say
Code:
Sub cmdAddRemoveYear_Click()
    txtCalc = DateAdd("yyyy",[yearcalc],[txtCalc])
End Sub

You can then repeat the same with buttons for Days and Months changing the "yyyy" to "d" and "ww"

*Note I've not tested this.*
 

Valery

Registered User.
Local time
Today, 01:14
Joined
Jun 22, 2013
Messages
363
Ok - let me see if I understand this. For each value entered in my unbound text fields - Year, Month, Week, and Day - I would have a command button and would press on them, one by one - and see the result date change?

So Access would not - like I thought or was trying to do - Add the months, the days, the weeks I asked for together and then add them to the date. It would do it individually via command buttons?

So let's say my selected date is October 1st 2013 - and I want to add 2 months and 5 days to it.

1- I select: Add (vs Subtract)
2- I enter the number 2 in the Months field
3- I enter the number 5 in the Days field

4- I click the CmdButtonMonth - date changes to: Dec 1, 2013
5- I click the CmdButtonDay - date changes to: Dec 6, 2013

Is that what you mean?
 

Valery

Registered User.
Local time
Today, 01:14
Joined
Jun 22, 2013
Messages
363
WOW! I think this will work! Eliminated the combo box selection "Add" or "Subtract". Replace with Command buttons "+" and "-".

How do I code a Command button that clears all values and let's me start over an new calculation?

Still testing the rest... but thinking ahead, LOL !

Oh! And what is the reverse of the "function" DateAdd : DateSubstract ???
 

Mihail

Registered User.
Local time
Today, 10:14
Joined
Jan 22, 2011
Messages
2,373
Take a look
 

Attachments

  • Valery_DateCalculator.mdb
    204 KB · Views: 60

AlexHedley

Registered User.
Local time
Today, 08:14
Joined
Aug 28, 2012
Messages
171
Code:
DateAdd ( interval, number, date )

"number" can take positive and negative numbers so just type in "-1" to your textbox. (Or whichever number you require)

Add a button "cmdClear"

Depending on the names of your textboxes that you are using to input your Days, Weeks, Years to add/subtract.

Code:
Sub cmdClear_Click()
    txtDay = ""
    txtWeek = ""
    txtYear = ""
End Sub

This will remove any value(s) you currently have in.
 

Valery

Registered User.
Local time
Today, 01:14
Joined
Jun 22, 2013
Messages
363
Ok - all buttons are working! But they only add to the selected date - they do not "compound" the numbers. As per my example above - here are the results :

So let's say my selected date is October 1st 2013 - and I want to add 2 months and 5 days to it.

1- I select: Add (vs Subtract)
2- I enter the number 2 in the Months field
3- I enter the number 5 in the Days field

4- I click the CmdButtonMonth - date changes to: Dec 2, 2013
5- I click the CmdButtonDay - date changes to: Oct 7, 2013 - but should be: Dec 7, 2013

How do I change the coding to do that???

Is that what you mean?
 

Valery

Registered User.
Local time
Today, 01:14
Joined
Jun 22, 2013
Messages
363
CmdClear button is a SUCCESS! THANK YOU!
 

ypma

Registered User.
Local time
Today, 08:14
Joined
Apr 13, 2012
Messages
643
Valery , I have based my solution on your example and for what it worth here it is, remember leap year will be a day out .

Regards Ypma
 

Attachments

  • DateCalculator2003.mdb
    264 KB · Views: 81

Valery

Registered User.
Local time
Today, 01:14
Joined
Jun 22, 2013
Messages
363
Thank you!
 
Last edited:

ypma

Registered User.
Local time
Today, 08:14
Joined
Apr 13, 2012
Messages
643
Yes, but in my example, and as well as in the sample you sent me, it is not adding up all the components to the date - I want to be able to add 1 month and 3 days let's say, to a selected date.
As in my 2nd post: "It also has to add lets say 2 weeks + 4 days from this date... ".




I had not noticed you would require to add additional days , so i have now added extra fields to allow this , This example allows you to added to the new date but not subtract from it. Also note that Mihal's example will be more accurate as i have use 30 days per Month and not the function "m" . No attempt has been made to consider holidays or weekends . Hope this give you some idea as how you can achieve what you require.


Regards Ypma
 

Attachments

  • DateCalculator2003.mdb
    280 KB · Views: 76

Mihail

Registered User.
Local time
Today, 10:14
Joined
Jan 22, 2011
Messages
2,373
Hi Valery !
I have a "feeling" :) . I think that you are angry against me because, at a time, you ask me to become your friend and I refused you. That was because, by mistake, I pressed the wrong button. And I don't know how to undo this. At that moment I was not familiar with the forum interface. So... sorry for that.

On the other hand, I tried to help you any time when I saw a post from you. Of course in my knowledge limits.

For now, try my DB and press also the "+" buttons. You will see what happen.
I forgot to implement "weeks" in my example but should be an easy task for you while you have a pattern for this. If not, ask for a new DB from me.

I have learned a lot from others codes. In my opinion, to study others codes is the best way to learn.

ypma's "clear" button is a very good idea but, if you will use this idea with my DB you should use (= Null) instead (="").

Note, please, that we, all of us, including me, including ypma and including very skilled posters, are here in order to learn something.
And, to teach others (or only to try this), is the best way to learn yourself.

Good luck !
 

Valery

Registered User.
Local time
Today, 01:14
Joined
Jun 22, 2013
Messages
363
Angry???? NEVER - I appreciate ALL the help I receive. Before I came to this website, I was on my own and had so much difficulties that I almost gave up doing database work all together.

I totally agree with what you are saying about learning. You know, even when the solutions are given to me on a "silver platter" - I always make a point of studying every bit of code even though I understand nothing of it... This is how I have learned. I have never taken any courses in VBA or any other programming language. Everything I know, I thought myself from copying coding and reading and testing.

This forum is the greatest opportunity for me to learn not just to get a great database.

Thank you Mihail.

PS: I had not even remembered about the friend feature... I was just testing it out on this site...
 

Valery

Registered User.
Local time
Today, 01:14
Joined
Jun 22, 2013
Messages
363
YPMA: Thank you for all your help. Luckily, I don't need this calculator to be extremely precise. And it did not have to consider weekends or holidays. Just to calculate the date within a patient's existing or proposed appointment frequency.

Often, they will ask me on the phone: what if I want my appointment in 12 weeks instead? for example. And I look unprofessional and waste time as I can't calculate this very fast...

This tool is terrific! Thank you both!
 

Users who are viewing this thread

Top Bottom