By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,638 Members | 2,249 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,638 IT Pros & Developers. It's quick & easy.

Left and SQL

P: 76
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!
Jan 13 '10 #1
Share this Question
Share on Google+
10 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
try ...
Expand|Select|Wrap|Line Numbers
  1. left(len([fieldname])-2)
  2.  
Jan 13 '10 #2

P: 76
Thanks!
I tried this and received an #Error?
Jan 13 '10 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
OK change the query view to SQL and post the full query here.
Jan 13 '10 #4

nico5038
Expert 2.5K+
P: 3,072
Guess you worded your question a bit vague.
I think you want to get the ## or ### and ignore the /y
Then use:
Expand|Select|Wrap|Line Numbers
  1. left([fieldname],instr([fieldname],"/")-1)
  2.  
When the "/y" is a literal you could also use:
Expand|Select|Wrap|Line Numbers
  1. Replace([fieldname],"/y","")
  2.  
Nic;o)
Jan 13 '10 #5

P: 76
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.....
Jan 13 '10 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
There are a couple of ways you could do this but try ...
Expand|Select|Wrap|Line Numbers
  1. IIf(NZ([fieldname],"")<>"",left([fieldname],instr([fieldname],"/")-1),"")
  2.  
Jan 13 '10 #7

nico5038
Expert 2.5K+
P: 3,072
Good sample Mary, but it can be done a bit shorter:
Expand|Select|Wrap|Line Numbers
  1. IIf(IsNull([fieldname],"",left([fieldname],instr([fieldname],"/")-1))
  2. ' or better when also empty fields or fields without a "/" exist:
  3. IIF(Instr(NZ([fieldname),"/")>0,left([fieldname],instr([fieldname],"/")-1),"")
  4.  
I would use the second proposal as that's really 100% "safe".

Nic;o)
Jan 13 '10 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
I like the second one too :D

It allows for all eventualities.
Jan 13 '10 #9

P: 76
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?
Jan 14 '10 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
Just change the last set of empty double quotes in the IIf statement to 0.
Jan 14 '10 #11

Post your reply

Sign in to post your reply or Sign up for a free account.