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....ther e 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.