Solved

MSAccess2013, Multi-Tabbed Form, Current_DATE. Can someone help with this problem?

Posted on 2014-10-28
10
85 Views
Last Modified: 2014-12-01
I have a Tabbed Form with 8 Tabs I added a text field to the base Form, then added the first name + " " + the last name from the  first Tab. The name is shown no matter which Tab that your on and I buttons if you use the block of navigation buttons at the bottom of each tab and move up or down the records the name follows in sync with the records. Now, the problem is I carry dates in the database, (i.e. Birthdays, Wedding dates, or Children's Birthdays). My attempt is to hold the current date on the form base to use  in date math so that I can calculate the age of the members.  Since these things change as the days pass, I can make it let me know when and who to direct my attention to.

I can do it in MS-EXCEL, but MS-ACCESS is giving me fits.
0
Comment
Question by:Frank Vertin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 40410380
Hi,

you described that you have problems with this, but you didn't show your tries and what's the error in Access. Maybe you should start to show your solution in Excel and then what you've tried in Access so it's easier to follow you.

If you want to calculate with dates there are VBA functions you can use like DateAdd or Year.
You should also keep in mind that Access can use a "not defined" value called NULL (not the number 0) especially mostly used in case of dates when the user leaves the field empty. As NULL means "not defined" any calculation with NULL results again in NULL so if you would try to calculate "1 + NULL" the result is again NULL. If you add an expression in a field on an Access form this would result in an empty field.
You can use a replacement value using the function "Nz" (Not Zero, better would be it would be named as NN for Not NULL). Nz allows you to use a replacement value if a field is NULL. But in case of dates it mostly makes no sense - what date should you use as replacement if the user didn't enter a birth date? So here it's better to use an expression which also leaves the calculated field empty, like "=Year(Now())-Year([BirthdayDateField])".

If you try to calculate that in VBA it may result in an error (depending on your method), so here you may need to check first if the date field you want to use is NULL. (There is a "IsNull" function to do that.)

But to be more detailed you must describe in detail what you tried and what was the result (show the code or expression and the exact error messages).

Cheers,

Christian
0
 
LVL 19

Expert Comment

by:Richard Daneke
ID: 40410382
I see two issues.

1. Display the current date.
I assume you have this handled with an unbound control, the value set to =Date().  
The function is not Today()

2. Calculate from the current date. This can be confusing in Access since you need to refer to the form data control. Don't get distracted. The value in the form control will be date(), so use that value in your calculations.

I hope this helps you over your issue.
0
 
LVL 85
ID: 40410456
if you use the block of navigation buttons at the bottom of each tab
Tab pages don't have distinct Navigation Buttons. You must be referring to a Form's Navigation Buttons.

Are you using Subforms? If so, then each of the Subforms could have a set of Navigation buttons as well, and referring to data/values between a Mainform and a Subform (or between different Subforms) can be troublesome as well.

As the others have said, you need to provide more detail as to exactly what sort of issue you're having. If possible, show screenshots and indicate where the trouble is, or upload a copy of the database here (be sure to remove or obfuscate any sensitive information, of course). If you do provide a copy of the db, be sure to let us know exactly how to recreate your issue.
0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 19

Expert Comment

by:Richard Daneke
ID: 40410545
When you calculate ages
To make my suggestion clearer, I am recommending to use the Date() function when calculating the ages and to not try to use the value on the form..
0
 

Author Comment

by:Frank Vertin
ID: 40414030
I need some clarification  on this, I appreciate your effort. Can you help me understand?
0
 
LVL 19

Accepted Solution

by:
Richard Daneke earned 500 total points
ID: 40418150
When you calculate someone's age in Visual Basic, the only variable you need is the person's date.  Today's date is available in code.
See the code segment below.  I use this to calculate age in an Access database when using a birthdate field from the current form or table.  The result is put into an Age field stored for the person and displayed on the form.  
Private Sub Birthdate_AfterUpdate()
    Dim varAge As Variant, varBirthdate As Variant
    varBirthdate = Me.Birthdate
    If IsNull(varBirthdate) Then Age = 0: Exit Sub
      varAge = DateDiff("yyyy", varBirthdate, Now)
      If Date < DateSerial(Year(Now), Month(varBirthdate), Day(varBirthdate)) Then
         varAge = varAge - 1
      End If
      Age = CInt(varAge)
      'varAge = Nothing

End Sub

Open in new window


The test in the code (If Date < DateSerial...) adjusts the result when the birthday is earlier in the current year.
0
 

Author Comment

by:Frank Vertin
ID: 40420281
This looks like the best idea I've seen, yet.  Everybody came up with different versions of sticking =Date() in a text field or property field of that text field just gave me a compiler error. I will try yours and let you know.
0
 

Author Comment

by:Frank Vertin
ID: 40420582
Hi Richard,

Just a little clarification, where do you suggest I put this code? I've done code that affects Excel, but  Access is foreign  to me as far as doing something with record macro, then looking at the code and working it out. I don't get that comfort level with Access.

Thanks, Frank
0
 
LVL 19

Expert Comment

by:Richard Daneke
ID: 40429275
Frank,
You can place the code above in the AfterUpdate of your age field in a form where Birthdate is the name of the field on which you want to calculate the age in years.
0
 

Author Closing Comment

by:Frank Vertin
ID: 40474474
You did it in a smart way, Richard.
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Companies keep a much closer eye on costs today, so changing to new Technology – Microsoft Office 365 is the smartest move to take.
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question