DateAdd variable with Text Field

Ricksimotosis

New Member
Joined
May 4, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Hi All,

New to the group so be gentle.

I've tinkered with Access for quite a while but I've come across something that I just can't get right.

I've got a database of assets that have been surveyed, the survey is imported into the database and the various assets need reassessing within a set period of time depending on the urgency. The codes returned in the survey are H3, M6, L12, where the numbers are amount of months from the survey date where action is required by.

I have a table that has the "survey date" and "urgency" so I need a query that says 'If Urgency is H3, add 3 months to Survey Date', if M6 - 6 months... and so on.

I've tried it a few ways and I either return every result possible (3,6 and 12 months added to one result) or I get a Duplicate Output Alias.

I'm sure it's very simple but the grey matter won't allow me to unlock this one and I can't find anything similar on other forums.

Any help would be greatly appreciated.

Cheers
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,180
Office Version
  1. 365
Platform
  1. Windows
Understood. Consider employing a function if it turns out what you have now doesn't do all it should.
GL.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,180
Office Version
  1. 365
Platform
  1. Windows
Update: what would be easier to maintain, since you can't control the values, would be to have a table in your existing query that has a values and duration for your Urgency. So instead of extracting a number from an Urgency value it would look like this (however, UrgencyID should be the record ID of a table of Urgency values, but something tells me you don't have that). Then the function I referred to before would get all these values and you wouldn't have to modify code, you'd only add records to this table.
DurationIDUrgencyIDUrgValueUrgDuration
1H33w
2M66d
3L129m
4etc2y
 

Forum statistics

Threads
1,136,727
Messages
5,677,400
Members
419,691
Latest member
jlk3208

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top