Query Data (1 Viewer)

[kitty77]

I have a table that has data that looks like this... I need to create a query that gives me everything before the first space

ADVA80403 Advanced
AIRC23072 Air
BETV16523 Tom

Need it to look like this.
ADVA80403
AIRC23072
BETV16523

 

[plog]

You would use an InStr() inside a Mid():

MS Access: InStr Function

This MSAccess tutorial explains how to use the Access Instr function with syntax and examples. The Microsoft Access Instr function returns the position of the first occurrence of a string in another string.

www.techonthenet.com

MS Access: Mid Function

This MSAccess tutorial explains how to use the Access Mid function with syntax and examples. The Microsoft Access Mid function extracts a substring from a string (starting at any position).

www.techonthenet.com

Mid extracts a substring from a string and InStr Identifies the spot in a string where a specific substring (or character/space) occurs.

 

[CJ_London]

Don’t you mean inside a left function?

 

[kitty77]

Don’t you mean inside a left function?

Can you provide me an example? The field is [cust]

Thanks.

 

[CJ_London]

Can you provide me an example?

after over 700 posts you still don't know the basics?

left([cust], instr([cust]," ")-1)

 

[plog]

Nope. Inside a Mid()

Left() will work in this case. But Left() really isn't needed because Mid() does everything it does and more.

 

[kitty77]

after over 700 posts you still don't know the basics?

left([cust], instr([cust]," ")-1)

Thanks for the example but keep you 2 cents to yourself next time. Sorry I'm not an expert like you!

 

[CJ_London]

no problems, I'll not assist you in the future in case it offends