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]

maybe
DateAdd("m",Mid([Urgency],2),[Survey Date])

No one can write sql for a query for you without knowing the table and field names. However, if that expression works you could incorporate it. To test it, you could use the immediate window of the vb editor, or just plunk it in to your query. To generate a field name for the resulting calculation, add AS Expr1 to the end. Your results should show up in a field with that name. I'll test in the meantime. Post back if it's not right.

EDIT - I had a function parameter in the wrong place. Corrected now.

 

[Ricksimotosis]

Thanks Micron,

I'll plug it in shortly and report back to you.

 

[Ricksimotosis]

Apologies, have only managed just to jump on the computer and try again.

Your solution wasn't what I was looking for but that's because of my poor description I expect.

I've attached the table I'm using. The Urgency Field is a text string that returns either:
U2- 2 Weeks
H3 - 3 Months
M6 - 6 Months
L12 - 12 Months
NA - Not required.

These need to be added to the survey date to either populate 'Action Work By' on the table, or just display in a query box that I can attach to the form. Doesn't really matter which.

I'm kind of after in mixed up SQL/Expression jargon (apologies to any one who's offended by the below)

[Tree Report]![Action Work By]
If [Tree Report]![Urgency] = U2, then DateAdd ('ww',2,[Survey Date]), else if [Tree Report]![Urgency] =M3, then DateAdd('m',3, etc etc etc.

Thanks again for your help, hopefully the table and the additional explanation will help.

 

[Micron]

What I gave you is an expression that does the calculation you asked for. In testing, if Urgency was L12 it added 12 months to the input date. If M6 it added 6. I expected that you'd incorporate that into the query you mentioned, but if you wanted someone to write the entire sql I missed that. If you run the following, do so on a copy of your table (change Tree Reports to the name of this table of course) and test it first. In the beginning you neglected to mention that you had values with no numbers (NA) so that needs to be dealt with. The IIF function should be ok for that. If your dates are really date data and not just text it should work but I'm not 100% sure because your date format isn't US format. You should closely examine the inserted dates to be sure none are wonky as a result.

UPDATE [Tree Reports] SET [Action Work By] = IIF([Urgency] = "NA",Null,DateAdd("m",Mid([Urgency],2),[Survey Date]))

Do yourself a favour and ditch the spaces in your object names. See

Naming conventions

General: Commonly used naming conventions

MS Access Naming Conventions

_________________________________________________________ Naming Conventions. This document is aimed at the user who is unfamiliar with any naming convention. It is based upon personal experience and the interaction I have had with others. It is not a hard and fast rule. Why use a...

www.access-programmers.co.uk

What not to use in names

Microsoft Access tips: Problem names and reserved words in Access

List of problem field names and reserved words in Microsoft Access and JET

allenbrowne.com

 

[Ricksimotosis]

Hi Micron,

That's awesome. Thank you. I'm getting more into access and I understand SQL in it's basic form but some of the terms you used I would have had scratched my head for hours about. Time to get my head into an SQL book/course.

Yeah, naming conventions.... I got half way through doing this and realised my error on that one. Really should have started it with no spaces.

Thanks again!

 

[Micron]

I take it that it worked as desired and you can set this one as solved?
Does something (e.g. the expression that does the calculation) need explaining? NP if so.

 

[Ricksimotosis]

Cheers Micron,

It works perfectly. There's a couple of issues surrounding it but that's just due to the text string and it being an open ended response, for instance I have a couple of 'ASAP's but i'll handle those differently in a report with a filter. The only thing your code doesn't do is allow for 'U2' which is two weeks, it takes it as 2 months. But again, with 5 years of reports I haven't seen one that's returned a U2 yet, it's always 'ASAP' so I'm not actually that fussed by it.

Your work above has let me dig into how to use those SQL terms, so something to add to my toolbox.

Thanks again.

 

[Micron]

If you review your first post you didn't say anything about weeks, only months. To fix "ASAP" (if that's another [Urgency] field value you have 2 options I think.
1) Run an update query & change all "ASAP" to a standard value and going forward, don't allow values that are not in some kind of control list.
2) Use a function called by the query. That function can be modified as required if new values arise in the future and can choose to add any date duration you need.

If you need help with either, post back.
Good luck!

 

[Ricksimotosis]

Thanks Micron,

Yeah, I updated with much more info. Completely my bad. Unfortunately the reports are from an external contractor so I don't have much say on his inputs, it's also why some of the field names have spaces because otherwise it won't allow me to do a simple import of data.

Once again, thanks for all your help. Very much appreciated.