Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-09-2019, 08:55 AM   #1
Dudebrains
Newly Registered User
 
Join Date: Nov 2019
Posts: 1
Thanks: 1
Thanked 0 Times in 0 Posts
Dudebrains is on a distinguished road
Arrow Deleting text after a specific character

Hey folks!
I have a database with a field called block_id in a table called tbl_data_Trips and the data is in the following format:
2345_67
I want to remove everything after the underscore AND remove the underscore to get something like this: 2345 using an update query.
How should I proceed?
Please keep in mind that the string isn't always 4 digits then 2 digits!

Thanks ahead of time for the help!

-Dudebrains

Dudebrains is offline   Reply With Quote
Old 11-09-2019, 08:59 AM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,263
Thanks: 13
Thanked 4,110 Times in 4,042 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Deleting text after a specific character

Use the Left function with the InStr function to find the underscore.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
Dudebrains (11-09-2019)
Old 11-09-2019, 10:08 AM   #3
MrHans
Newly Registered User
 
Join Date: Jul 2015
Location: NL - Amsterdam
Posts: 139
Thanks: 58
Thanked 19 Times in 17 Posts
MrHans is on a distinguished road
Re: Deleting text after a specific character

Or use the Split function to return the first part of the result.

StrFirstPart = Split("Block_id", "_")(0)

MrHans is offline   Reply With Quote
Old 11-09-2019, 11:15 AM   #4
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,721
Thanks: 57
Thanked 1,260 Times in 1,241 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Deleting text after a specific character

Quote:
Originally Posted by MrHans View Post
Or use the Split function to return the first part of the result.

StrFirstPart = Split("Block_id", "_")(0)
Hi Hans. That should work but maybe not in an update query, which is what I think the OP wanted. Cheers!
__________________
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 online now   Reply With Quote
Old 11-09-2019, 12:44 PM   #5
MrHans
Newly Registered User
 
Join Date: Jul 2015
Location: NL - Amsterdam
Posts: 139
Thanks: 58
Thanked 19 Times in 17 Posts
MrHans is on a distinguished road
Re: Deleting text after a specific character

Ahh yes, you're right about that.

Daniel Pineault provided a nice and simple workaround here:
https://www.devhut.net/2018/04/22/ac...it-in-a-query/

But I guess the left and instr functions mentioned earlier would be more efficient in this case.

MrHans is offline   Reply With Quote
Reply

Tags
update query

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to Delete the First Character if it Equals a Specific Character mdurschlag Reports 7 03-14-2012 02:08 PM
Help with deleting specific characters within text fields in a table. Neal_F Queries 3 08-12-2011 09:06 AM
Removing a specific character deejay_totoro Modules & VBA 1 09-04-2007 01:09 AM
Deleting the first character of a string when importing troncarver Modules & VBA 13 02-03-2006 08:05 PM
Function for deleting first character of a string L'apprentis Modules & VBA 1 11-29-2005 03:53 AM




All times are GMT -8. The time now is 08:04 AM.


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