UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Mid, Access 2016    
 
   
mike60smart
post Jun 30 2020, 06:04 AM
Post#1


UtterAccess VIP
Posts: 14,072
Joined: 6-June 05
From: Dunbar,Scotland


Hi Everyone

I have the following field in a query:- QN: [QuoteNo] & "-" & [Revision]

This produces the following string:- 1-1A

How would I use Trim or MID to Not display the First 2 Characters "1-" and only display "1A" ?

Any help appreciated.

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
isladogs
post Jun 30 2020, 07:29 AM
Post#2


UtterAccess VIP
Posts: 2,460
Joined: 4-June 18
From: Somerset, UK


You would use Mid(FieldName, InStr(FieldName,"-")+1) BUT why bother? Just use the Revision field.

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
mike60smart
post Jun 30 2020, 09:01 AM
Post#3


UtterAccess VIP
Posts: 14,072
Joined: 6-June 05
From: Dunbar,Scotland


Hi Colin

My apologies.

I should have said also that I can't just use the Revision as there is a Module which adds a Character depending on the actual Revision Number.

I meant to say it currently displays "1-1A"

How can I remove the Hyphen and the Numeric After the First Character?


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
isladogs
post Jun 30 2020, 10:33 AM
Post#4


UtterAccess VIP
Posts: 2,460
Joined: 4-June 18
From: Somerset, UK


Sorry but I don't understand that response.
I already gave you the expression using the Mid function that you asked for in post #1

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
mike60smart
post Jun 30 2020, 10:58 AM
Post#5


UtterAccess VIP
Posts: 14,072
Joined: 6-June 05
From: Dunbar,Scotland


Hi Colin

Currently the Quote No is displayed as follows:-

Attached File  Current.JPG ( 13.77K )Number of downloads: 0


Initial Quote No 1 displays as 1-
1st Revision displays as 1-1A
2nd Revision displays as 1-1B

When I apply your recommended MID function then it displays as follows:-

Initial Quote displays as Blank
1st Revision displays as 1A
2nd Revision displays as 1B

However when it comes to Quote No 2 it displays as follows:-

Initial Quote No 2 displays as 2-
1st Revision displays as 2-1A

I need to display as follows:-

Attached File  Need.jpg ( 50.05K )Number of downloads: 8


So the Mid Function needs to remove the -1 from the Revision Field if possible?




--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
Larry Larsen
post Jun 30 2020, 01:40 PM
Post#6


UA Editor + Utterly Certified
Posts: 24,593
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi Mike

Just having a look and could suggest a small idea, to extract first & last char...

SQL:
CODE
SELECT Table1.Data, IIf(Len([data])>3,Left([Data],1) & Right([Data],1),"") AS X
FROM Table1;


Result:
Data X
11-
11-1B 1B
11-1A 1A
22-1A 2A
22-
33-1A 3A
33-1B 3B
44-

Table Data:
ID Data
1 11-
2 11-1B
3 11-1A
4 22-1A
5 22-
6 33-1A
7 33-1B
8 44-

On the assumption [Data] is greater than (3)..

HTH's
thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
isladogs
post Jun 30 2020, 01:49 PM
Post#7


UtterAccess VIP
Posts: 2,460
Joined: 4-June 18
From: Somerset, UK


The Mid expression I suggested was in response to your original request to return the value after the hyphen.
If you want the First and last characters, that obviously won't work, but Larry's reply should do the job.
.
If that's still not what you mean, then you will need to explain it again more clearly.

--------------------
Colin (Mendip Data Systems)
Website, email
Go to the top of the page
 
mike60smart
post Jun 30 2020, 02:23 PM
Post#8


UtterAccess VIP
Posts: 14,072
Joined: 6-June 05
From: Dunbar,Scotland


Hi Larry & Colin

You solution works a treat Larry.

I had gone down the route of using Left & Right as 2 separate fields but yours is far simpler.

Many thanks Both
cheers.gif

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    3rd August 2020 - 04:52 AM