Ryan Steiger Ryan Steiger -4 years ago 139
SQL Question

Need Date Calculation MS Access

I need a flexible table that displays dates. By flexible, I mean, if I put in June 15, 1976, it displays as such. But if I put 20, Access calculates that 20 in the same field as today's date - 20 years.

I set the formatting of the date/time field in table: tbl_ageLimit to #, so it displays a serial date rather than a user readable date. This table has one field: ageLimit.

I am trying to develop a query to recognize if the date is not relevant in its current state and then convert it to something relevant and put it in another table that will constantly update.

Right now, I'm just trying to get formula to work on recognizing and converting the date. This is the formula that works splendidly in excel, but doesn't seem to be working in MS Access:


It recognizes if the field has a number in it that is less than 100. But it's not doing the math and displaying a new record in the new table. Below is the sql:

SELECT tbl_ageLimit.ageLimit INTO tbl_allAges
FROM tbl_ageLimit
WHERE (((tbl_ageLimit.ageLimit)=IIf([ageLimit]<=100,Date()-365*[ageLimit],[ageLimit])));

Can someone kindly point out to me what I'm doing wrong? Thank you.

As requested, here are snapshots of the problem:

tbl_ageLimit Time/Date Field formatted as serial datetbl_allAges showing four records instead of five. Where's the fifth record?

The table that is created is not showing the fifth record. Stumped.

Answer Source

Consider removing the WHERE clause as you do not need to filter records. Since you intend to evaluate the IIF() expression, place in the SELECT clause:

SELECT IIf([ageLimit]<=100, Date()-365*[ageLimit], [ageLimit]) As [age_Limit]
INTO tbl_allAges
FROM tbl_ageLimit
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download