Deleting text after a specific character (1 Viewer)

D

Dudebrains

New member

Local time

Today, 16:25

Joined

Nov 9, 2019

Messages

1

• Nov 9, 2019

• #1

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

 

pbaldy

Wino Moderator

Staff member

Local time

Today, 13:25

Joined

Aug 30, 2003

Messages

36,124

• Nov 9, 2019

• #2

Use the Left function with the InStr function to find the underscore.

 

M

MrHans

Registered User

Local time

Today, 22:25

Joined

Jul 27, 2015

Messages

147

• Nov 9, 2019

• #3

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

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

 

theDBguy

I’m here to help

Staff member

Local time

Today, 13:25

Joined

Oct 29, 2018

Messages

21,453

• Nov 9, 2019

• #4

MrHans said:

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

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

Click to expand...

Hi Hans. That should work but maybe not in an update query, which is what I think the OP wanted. Cheers!

 

M

MrHans

Registered User

Local time

Today, 22:25

Joined

Jul 27, 2015

Messages

147

• Nov 9, 2019

• #5

Ahh yes, you're right about that.

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

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