Deleting text after a specific character

[Dudebrains]

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]

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

 

[MrHans]

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

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

 

[theDBguy]

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!

 

[MrHans]

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.