Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-15-2019, 01:25 PM   #16
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,769
Thanks: 55
Thanked 1,022 Times in 988 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Right(trim etc not returning expected result

expn = "CT Male Vitality Chemical Only May 2019"

given the above, if you want to retrieve the last two words irrespecitive of word length (May 2019) then this will do it in a single operation. So it works for June 2019, August 2019 etc.

Basically split the string using a space separator, then re-join the penultimate and last words.
Probably more efficient as a function, as this way splits the string 4 times, but it should still be pretty quick.

split(expn," ")(ubound(split(expn," "))-1) & " " & split(expn," ")(ubound(split(expn," ")))

__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
The Following User Says Thank You to gemma-the-husky For This Useful Post:
dynamictiger (06-16-2019)
Old 06-16-2019, 01:54 PM   #17
dynamictiger
Newly Registered User
 
Join Date: Feb 2002
Location: Perth
Posts: 261
Thanks: 11
Thanked 1 Time in 1 Post
dynamictiger
Re: Right(trim etc not returning expected result

Quote:
Originally Posted by CJ_London View Post
not sure if it is related but the OP says

'We use month names so for clarity June 2019 started on Tuesday 21st of May and will end on Tuesday the 18th of June.'

So when does May end and July start? If May ends 21st May then aren't you double counting?

Not quite. When I talk of closing the month we close the Monday night, harvest the data by Excel and upload to SQL on the Tuesday. Then on the Tuesday the field staff start the new job number and we remove the old job numbers online. Just used shortened description for what we are doing as simpler to explain.


Been working like this since 2001.
__________________
Perth, the most isolated capital in the world
dynamictiger is offline   Reply With Quote
Old 06-16-2019, 01:58 PM   #18
dynamictiger
Newly Registered User
 
Join Date: Feb 2002
Location: Perth
Posts: 261
Thanks: 11
Thanked 1 Time in 1 Post
dynamictiger
Re: Right(trim etc not returning expected result

Quote:
Originally Posted by gemma-the-husky View Post
expn = "CT Male Vitality Chemical Only May 2019"

given the above, if you want to retrieve the last two words irrespecitive of word length (May 2019) then this will do it in a single operation. So it works for June 2019, August 2019 etc.

Basically split the string using a space separator, then re-join the penultimate and last words.
Probably more efficient as a function, as this way splits the string 4 times, but it should still be pretty quick.

split(expn," ")(ubound(split(expn," "))-1) & " " & split(expn," ")(ubound(split(expn," ")))
Looks more efficient than my current approach. Will see what the client says this morning before I move ahead. I have proof of concept for the report, know what the report looks like, but am uncertain it adds to the clients efforts. The answer will dictate how much further I go. What I have is useful for my purposes. However if Client wants the envisaged report monthly then I will need to revisit this.

Thanks all


__________________
Perth, the most isolated capital in the world
dynamictiger is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Reports not returning expected averages Nymandus Reports 1 05-09-2013 10:49 PM
Comparing actual result vs expected result sarcasym Reports 3 04-21-2010 07:35 AM
Query not returning all expected data slovell Queries 9 09-07-2007 05:54 PM
Query returning MORE results than Expected halem2 Queries 3 03-02-2007 06:39 AM
Nz result is not as expected srburk General 2 01-05-2005 06:25 AM




All times are GMT -8. The time now is 07:00 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World