CODEWITHSUNDEEP
ms-accessms-access-2007ms-access-2003
roncansan
roncansan

Reputation: 2380

how to delete everything after a blank space in access

I have a table->column first-name with values:

Juan
Manuel l.
Richard Wit

I'm trying to do a query that return

Juan
Manul
Richard

I want to eliminate after a space.

Upvotes: 1

Views: 3786

Answers (2)

BlueRaja - Danny Pflughoeft
BlueRaja - Danny Pflughoeft

Reputation: 86006

Assuming you want SQL, try

SELECT MID(name  + " a", 1, INSTR(name + " a", " ")-1) AS FirstName FROM myTable

In VBA it would be similar, since Mid() and InStr() work exactly the same as in Access-SQL

Really, though, it would be better to just store first and last name in separate fields so you don't have to do this sort of finagling in the SQL.

Upvotes: 3

mwolfe02
mwolfe02

Reputation: 24227

SELECT Left([First-Name], InStr([First-Name] & ' ', ' ') - 1) As CleanFirstName
FROM table

Keep in mind that running this will be inefficient since you will be executing VBA functions against every record in your query. Depending on how you are using this, you may want to have the query return the full field and do the processing after the fact.

Upvotes: 1

Related Questions