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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,284
Office Version
  1. 365
Platform
  1. Windows
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

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

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

Ricksimotosis

New Member
Joined
May 4, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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.
 

Attachments

  • 2021-05-05.png
    2021-05-05.png
    23.4 KB · Views: 3

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,284
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
SQL:
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

What not to use in names
 
Solution

Ricksimotosis

New Member
Joined
May 4, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jun 3, 2015
Messages
2,284
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
May 4, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jun 3, 2015
Messages
2,284
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 4, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,148,140
Messages
5,745,028
Members
423,916
Latest member
Bhavik123

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