DateAdd variable with Text Field

[Ricksimotosis]

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]

Understood. Consider employing a function if it turns out what you have now doesn't do all it should.
GL.

 

[Micron]

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.

DurationID	UrgencyID	UrgValue	UrgDuration
1	H3	3	w
2	M6	6	d
3	L12	9	m
4	etc	2	y