How to update the static Age field based on how old an exhibitor is as of a given date

Summary: How to update the static Age field based on how old an exhibitor is as of a given date.

Detail:

When it comes to reporting an exhibitors “Age”, ShoWorks has three different fields to indicate how old an exhibitor is: “Exhibitor Date of Birth”, “Exhibitor Age Calculated” and simply “Age”. Knowing the difference between these can help you when it comes time to produce reports. Furthermore, we will provide a neat trick to manipulating these when you need to figure out how old an exhibitor is on a given date (as in the case of 4-H reporting).

Definitions:

Exhibitor Date of Birth

The birthdate of the exhibitor in #/#/## format.

Exhibitor Age Calculated

The age in years of the exhibitor as of “today” (based on your computer clock – which we assume is correct). This is a calculated field based on the field above and cannot be changed.

Age

A numeric field that you can type in any number. This value will never change unless you update it manually or use a simple trick below.

Solution:

So here’s the trick. Let’s say that you want to update the “Age” field (again, this is static meaning that it does not automatically change as the days go by). Perhaps you may even want it to display their age as of a given date (such as September 1st of 2019). You can use the following method:

Go to Reports>Report Manager>Queries>Run SQL Statement and copy/paste ONE the following (Ctrl+C to copy, Ctrl+V to paste) make sure that you are copying the entire text in bold:

To update the “Age” field to how old they are as of today (now):

UPDATE [Exhibitors and Sellers] SET [Exhibitors and Sellers].Age = IIf(Month(Now())<Month([Exhibitor Date of Birth]) Or (Month(Now())=Month([Exhibitor Date of Birth]) And Day(Now())<Day([Exhibitor Date of Birth])),Year(Now())-Year([Exhibitor Date of Birth])-1,Year(Now())-Year([Exhibitor Date of Birth]));

To update the “Age” field as of a certain date

In our example, we’ll say the date as of 9/1/19. Replace the FIVE instances of 9/1/19 with your own preferred date:

UPDATE [Exhibitors and Sellers] SET [Exhibitors and Sellers].Age = IIf(Month(#9/1/19#)<Month([Exhibitor Date of Birth]) Or (Month(#9/1/19#)=Month([Exhibitor Date of Birth]) And Day(#9/1/19#)<Day([Exhibitor Date of Birth])),Year(#9/1/19#)-Year([Exhibitor Date of Birth])-1,Year(#9/1/19#)-Year([Exhibitor Date of Birth]));

Of course, you would do this every year or whenever you wanted to update the records. Running it multiple times does no harm.