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

Instr only for non-missing?

P: n/a
I have a date stored like this '2004 - 2006' and I use this code to
get startyear and stopyear

StartYear: Trim(Left([Project Dates],InStr([Project Dates],"-")-1))
StopYear: Trim(Right([Project Dates],InStr([Project Dates],"-")-1))

Is there a way to get this to only run on NON MISSING Project Dates?

Thanks!
Alex
Aug 7 '08 #1
Share this Question
Share on Google+
3 Replies


P: n/a
JvC
You want to make sure it is not null, and that is has the correct
length:

If not isnull([Project Dates] and Len(Trim([Project Dates])) = 11 then
StartYear: Trim(Left([Project Dates],InStr([Project Dates],"-")-1))
StopYear: Trim(Right([Project Dates],InStr([Project Dates],"-")-1))
endif

John

Alex Pavluck laid this down on his screen :
I have a date stored like this '2004 - 2006' and I use this code to
get startyear and stopyear

StartYear: Trim(Left([Project Dates],InStr([Project Dates],"-")-1))
StopYear: Trim(Right([Project Dates],InStr([Project Dates],"-")-1))

Is there a way to get this to only run on NON MISSING Project Dates?

Thanks!
Alex

Aug 7 '08 #2

P: n/a
Thanks, John!
One last question. Where would I put this code? In the SQL editor
directly? I thought that SQL used case logic rather than if logic?

Thanks again,
Alex
On Aug 7, 2:57*pm, JvC <johnv...@earthlink.netwrote:
You want to make sure it is not null, and that is has the correct
length:

If not isnull([Project Dates] and Len(Trim([Project Dates])) = 11 then
* * StartYear: Trim(Left([Project Dates],InStr([Project Dates],"-")-1))
* * StopYear: Trim(Right([Project Dates],InStr([Project Dates],"-")-1))
endif

John

Alex Pavluck laid this down on his screen :
I have a date stored like this '2004 - 2006' and I use this code to
get startyear and stopyear
StartYear: Trim(Left([Project Dates],InStr([Project Dates],"-")-1))
StopYear: Trim(Right([Project Dates],InStr([Project Dates],"-")-1))
Is there a way to get this to only run on NON MISSING Project Dates?
Thanks!
Alex
Aug 7 '08 #3

P: n/a
JvC
Alex,

Ahhh! It's from a query. My bad!

StartYear: Trim(iif(Isnull([Project Dates],"", iif(len(trim([Project
Dates 0, Left([Project Dates],InStr([Project Dates],"-")-1),''))
StopYear: Trim(iif(Isnull([Project Dates],"", iif(len(trim([Project
Dates 0, Right([Project Dates],InStr([Project Dates],"-")-1),''))

That should do it!

John

Alex Pavluck brought next idea :
Thanks, John!
One last question. Where would I put this code? In the SQL editor
directly? I thought that SQL used case logic rather than if logic?

Thanks again,
Alex
On Aug 7, 2:57*pm, JvC <johnv...@earthlink.netwrote:
>You want to make sure it is not null, and that is has the correct
length:

If not isnull([Project Dates] and Len(Trim([Project Dates])) = 11 then
* * StartYear: Trim(Left([Project Dates],InStr([Project Dates],"-")-1))
* * StopYear: Trim(Right([Project Dates],InStr([Project Dates],"-")-1))
endif

John

Alex Pavluck laid this down on his screen :
>>I have a date stored like this '2004 - 2006' and I use this code to
get startyear and stopyear
>>StartYear: Trim(Left([Project Dates],InStr([Project Dates],"-")-1))
StopYear: Trim(Right([Project Dates],InStr([Project Dates],"-")-1))
Is there a way to get this to only run on NON MISSING Project Dates?
Thanks!
Alex

Aug 7 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.