Left and SQL

[artemetis]

Stupid question I'm sure.....

Field entryType has values of ##/y and ###/y.

In a simple Access SQL I'd like to trim, from the right up to the "#".
I don't need/want the "/y"

Thanks!

[MMcCarthy]

try ...

left(len([fieldname])-2)
 

[artemetis]

Thanks!
I tried this and received an #Error?

[MMcCarthy]

OK change the query view to SQL and post the full query here.

[nico5038]

Guess you worded your question a bit vague.
I think you want to get the ## or ### and ignore the /y
Then use:

left([fieldname],instr([fieldname],"/")-1)
 

When the "/y" is a literal you could also use:

Replace([fieldname],"/y","")
 

Nic;o)

[artemetis]

Thanks Guys!

Nico', that worked perfectly!!! Sorry my initial question wasn't quite clear.

To take it one step further....there are several records that are missing data in the field. For these records, the query returns the #Error code. Is there a way (IIF?) I could display a 0 rather than the err?

I'm trying to get this on my own using IIF, not sure if this is the right route.....

[MMcCarthy]

There are a couple of ways you could do this but try ...

IIf(NZ([fieldname],"")<>"",left([fieldname],instr([fieldname],"/")-1),"")
 

[nico5038]

Good sample Mary, but it can be done a bit shorter:

IIf(IsNull([fieldname],"",left([fieldname],instr([fieldname],"/")-1))
' or better when also empty fields or fields without a "/" exist:
IIF(Instr(NZ([fieldname),"/")>0,left([fieldname],instr([fieldname],"/")-1),"")
 

I would use the second proposal as that's really 100% "safe".

Nic;o)

[MMcCarthy]

I like the second one too :D

It allows for all eventualities.

[artemetis]

I used Msquared's code - it worked fine!

I'm using this IIF in an Append Query.
Is it possible, to insert/display a 0 when the field is blank?

[MMcCarthy]

Just change the last set of empty double quotes in the IIf statement to 0.