Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-14-2019, 03:31 PM   #1
dynamictiger
Newly Registered User
 
Join Date: Feb 2002
Location: Perth
Posts: 257
Thanks: 11
Thanked 0 Times in 0 Posts
dynamictiger
Right(trim etc not returning expected result

I have a fairly complex situation where I need to get monthly historic data for comparison in a report. The complexity is the month is not fixed. Our month rolls from Tuesday closest to the 17th of the month to the following months Tuesday closest to the 17th except where there is 5 or more working days after the following Tuesday to end of the month. It works practically allowing time for paperwork processing however it is presenting an interesting challenge in historical data compilation.

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


As I have the data collected into jobs by month names I thought the simplest way to sort this would be to get the job name remove the 2019 for example and replace with 2018 and its there the issue shows up.

I am using the following for the test of this concept:
Code:
SELECT qryCrownJobDescriptionSearch.MJDescrip, Right(Trim([MjDescrip]),4) AS Expr1
FROM qryCrownJobDescriptionSearch
GROUP BY qryCrownJobDescriptionSearch.MJDescrip, Right(Trim([MjDescrip]),4);
I am assuming this will return the 2019 portion of MjDescrip however the outcome is:
Code:
MJDescrip	Expr1
"CT Male Vitality Chemical Only May 2019"	"9"
"CT Plunge Pool Chemical Only May 2019"	"19"
"CT Port Cochere Wf Chemical Only May 2019"	""

Not expected output. I don't do this sort of work often enough to recall what the issue is?

__________________
Perth, the most isolated capital in the world
dynamictiger is offline   Reply With Quote
Old 06-14-2019, 03:51 PM   #2
theDBguy
Im here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,035
Thanks: 36
Thanked 721 Times in 704 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Right(trim etc not returning expected result

Hi. Sorry but it's hard to tell what you're seeing from your post. Can you post a screenshot instead? Thanks.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 06-14-2019, 03:59 PM   #3
dynamictiger
Newly Registered User
 
Join Date: Feb 2002
Location: Perth
Posts: 257
Thanks: 11
Thanked 0 Times in 0 Posts
dynamictiger
Re: Right(trim etc not returning expected result




Hope this helps

__________________
Perth, the most isolated capital in the world
dynamictiger is offline   Reply With Quote
Old 06-14-2019, 04:24 PM   #4
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,077
Thanks: 476
Thanked 893 Times in 848 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Right(trim etc not returning expected result

There is Nothing?
__________________
Code:
                 |||||
               @(~^~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 06-14-2019, 05:40 PM   #5
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,597
Thanks: 26
Thanked 478 Times in 454 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Right(trim etc not returning expected result

I got no clue, and never seen anything like that. The only way I could think you could get those results if you did not have "trim" in the calculation and you had values like

Code:
"CT Male Vitality Chemical Only May 2019   "
"CT Plunge Pool Chemical Only May 2019  "	
"CT Port Cochere Wf Chemical Only May 2019    "
Would have to see your db to see what is going on.
MajP is offline   Reply With Quote
Old 06-14-2019, 06:03 PM   #6
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 717
Thanks: 3
Thanked 147 Times in 141 Posts
Micron will become famous soon enough Micron will become famous soon enough
Re: Right(trim etc not returning expected result

Maybe it's an rtf field and there are trailing non-printable characters. Trim would ignore them but Right would probably count them.
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
The Following User Says Thank You to Micron For This Useful Post:
dynamictiger (06-15-2019)
Old 06-14-2019, 06:20 PM   #7
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 717
Thanks: 3
Thanked 147 Times in 141 Posts
Micron will become famous soon enough Micron will become famous soon enough
Re: Right(trim etc not returning expected result

just tested a short text field (not rtf format) with

dog
cat

Mid 3,4 returns g
Mid 4, 4 returns what looks like an empty string
Asc(Mid 4,4) returns 13
Obviously I left out the field & table names from the Mid function.

So the reason is likely unprintable characters like cr/lf's

Micron is offline   Reply With Quote
The Following User Says Thank You to Micron For This Useful Post:
dynamictiger (06-15-2019)
Old 06-14-2019, 08:27 PM   #8
dynamictiger
Newly Registered User
 
Join Date: Feb 2002
Location: Perth
Posts: 257
Thanks: 11
Thanked 0 Times in 0 Posts
dynamictiger
Re: Right(trim etc not returning expected result

The data has been harvested using an online tool, transferred to excel, uploaded to SQL and now I am using Access to link to the tables. So how would anyone suggest I deal with it. The trailing characters seem to vary with the 'phrase' of the job.
__________________
Perth, the most isolated capital in the world
dynamictiger is offline   Reply With Quote
Old 06-14-2019, 08:47 PM   #9
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 717
Thanks: 3
Thanked 147 Times in 141 Posts
Micron will become famous soon enough Micron will become famous soon enough
Re: Right(trim etc not returning expected result

For me, depends on whether or not your query data should ever have carriage return or line feed characters. If yes, then I have no idea. If not, then I would probably examine the records for any non-printable characters at the end of your string. Something like Asc(Right("your string here",2)) should spot them. If any are found I'd check out what they are then decide. Perhaps Replace function to remove them. Maybe:

Replace("your string here", Asc(Right("string here",2)),"")

Hopefully I'm close with any of that; it's off the top of my head and it's getting late...

No comment on whether or not this involves rtf field? Based on my test and what your results seemed to be, I think not, but it matters.

EDIT - that Replace might just be

Replace("your string here", Right("string here",2),"")

Last edited by Micron; 06-14-2019 at 08:54 PM.
Micron is offline   Reply With Quote
The Following User Says Thank You to Micron For This Useful Post:
dynamictiger (06-15-2019)
Old 06-15-2019, 12:21 AM   #10
dynamictiger
Newly Registered User
 
Join Date: Feb 2002
Location: Perth
Posts: 257
Thanks: 11
Thanked 0 Times in 0 Posts
dynamictiger
Re: Right(trim etc not returning expected result

Thanks, I ended up having to do multiple Ascii searches looking for 49. Once I found 49 I then trimmed to length 4,5 or 6 characters this returned the correct phrase.

Hope this is consistent I will check using a few sample months and see.

Nonetheless this is the correct direction at least. Thanks very much
__________________
Perth, the most isolated capital in the world
dynamictiger is offline   Reply With Quote
Old 06-15-2019, 04:55 AM   #11
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,597
Thanks: 26
Thanked 478 Times in 454 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Right(trim etc not returning expected result

Code:
Public Function RightNoSpecial(TheString As String, Length As Long)
  Dim chr As String
  Dim i As Integer
  '32 to 127
  'Debug.Print TheString
  For i = Len(TheString) To 1 Step -1
    chr = Mid(TheString, i, 1)
    'Debug.Print chr & " Chr"
    If Asc(chr) > 32 And Asc(chr) < 127 Then
      RightNoSpecial = chr & RightNoSpecial
      'Debug.Print rtn & " rtn"
      If Len(RightNoSpecial) = Length Then Exit Function
    End If
  Next i
End Function

Public Sub test()
  Dim x As String
  x = "adassdf may 2019"
  x = x & vbCrLf & chr(10) & chr(13)
  Debug.Print RightNoSpecial(x, 4)
End Sub
You could replace
Right(Trim([MjDescrip]),4)
with
RightNoSpecial([MjDescrip],4)
MajP is offline   Reply With Quote
Old 06-15-2019, 08:54 AM   #12
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 717
Thanks: 3
Thanked 147 Times in 141 Posts
Micron will become famous soon enough Micron will become famous soon enough
Re: Right(trim etc not returning expected result

Quote:
Originally Posted by dynamictiger View Post
Thanks, I ended up having to do multiple Ascii searches looking for 49. Once I found 49 I then trimmed to length 4,5 or 6 characters this returned the correct phrase.
Why? That isn't a hidden character, right? It's the number 1?
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
Old 06-15-2019, 09:12 AM   #13
NauticalGent
Pristine Curmudgeon
Gold Supporter
 
NauticalGent's Avatar
 
Join Date: Apr 2015
Location: Naples, Italy (EXPAT from Virginia)
Posts: 1,778
Thanks: 386
Thanked 240 Times in 210 Posts
NauticalGent has a spectacular aura about NauticalGent has a spectacular aura about
Re: Right(trim etc not returning expected result

Hi there. I had a similar problem with some text strings from Outlook. I found this thread and it effectively strips all unwanted characters. Give it a try...

https://access-programmers.co.uk/for...d.php?t=144810
__________________
I had the RIGHT to remain silent...but I didnt have the ABILITY. - Ron White
NauticalGent is offline   Reply With Quote
Old 06-15-2019, 09:38 AM   #14
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,903
Thanks: 40
Thanked 3,538 Times in 3,419 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: Right(trim etc not returning expected result

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?
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 06-15-2019, 09:39 AM   #15
Micron
Newly Registered User
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 717
Thanks: 3
Thanked 147 Times in 141 Posts
Micron will become famous soon enough Micron will become famous soon enough
Re: Right(trim etc not returning expected result

As long as anyone who uses those methods realizes that it will strip out anything that doesn't belong in what you might consider the standard English set then OK. Anyone with keyboards that use other language characters or strings with extended characters like sigma (math stuff) or special characters for fractional values like 1/4 should be wary of those procedures. Validation before hand might be the way for them to go so that they don't remove characters they need to keep.


BTW - won't > 32 (post 11) remove spaces?


Last edited by Micron; 06-15-2019 at 09:42 AM. Reason: added question
Micron 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:38 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