Right(trim etc not returning expected result (1 Viewer)

dynamictiger

Registered User.
Local time
Today, 08:27
Joined
Feb 3, 2002
Messages
261
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?
 

theDBguy

I’m here to help
Local time
Today, 00:27
Joined
Oct 29, 2018
Messages
10,090
Hi. Sorry but it's hard to tell what you're seeing from your post. Can you post a screenshot instead? Thanks.
 

dynamictiger

Registered User.
Local time
Today, 08:27
Joined
Feb 3, 2002
Messages
261



Hope this helps
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 03:27
Joined
May 21, 2018
Messages
3,136
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.
 

Micron

AWF VIP
Local time
Today, 03:27
Joined
Oct 20, 2018
Messages
2,711
Maybe it's an rtf field and there are trailing non-printable characters. Trim would ignore them but Right would probably count them.
 

Micron

AWF VIP
Local time
Today, 03:27
Joined
Oct 20, 2018
Messages
2,711
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
 

dynamictiger

Registered User.
Local time
Today, 08:27
Joined
Feb 3, 2002
Messages
261
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.
 

Micron

AWF VIP
Local time
Today, 03:27
Joined
Oct 20, 2018
Messages
2,711
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:

dynamictiger

Registered User.
Local time
Today, 08:27
Joined
Feb 3, 2002
Messages
261
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
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 03:27
Joined
May 21, 2018
Messages
3,136
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)
 

Micron

AWF VIP
Local time
Today, 03:27
Joined
Oct 20, 2018
Messages
2,711
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?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:27
Joined
Feb 19, 2013
Messages
12,092
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?
 

Micron

AWF VIP
Local time
Today, 03:27
Joined
Oct 20, 2018
Messages
2,711
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:

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 08:27
Joined
Sep 12, 2006
Messages
13,892
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," ")))
 

dynamictiger

Registered User.
Local time
Today, 08:27
Joined
Feb 3, 2002
Messages
261
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.
 

dynamictiger

Registered User.
Local time
Today, 08:27
Joined
Feb 3, 2002
Messages
261
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
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom