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
3  2224 
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
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
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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: J. Muenchbourg |
last post by:
I have a field name "tracks" of string data type that has multiple names
in it (for example: calder delaware$ aqueduct ), and I want to check to
see if this certain field has "delaware$" in it....
|
by: DTB |
last post by:
I am trying to convert a complex function from Oracle to SQL Server
and have come across Oracle's Instr() function. I see SQL Server has
CHARINDEX() which is similar, however it does not provide...
|
by: Chris Calzaretta |
last post by:
Hello Everybody,
Question
instr function will give you the first instance of the finding
so EX:
so your string looks like
string1 = "testing>This is > just a test > testtesttest"...
|
by: Gordon |
last post by:
Hi;
I am trying to extract a substring using a combination of the mid() and
Instr() i.e.
aString = "Jones, Thomas R, Dr."
hold = InStr(1, aString, " ," , 1)
newString = Mid(aString, 1,...
|
by: Mary |
last post by:
MemberID VID
1002 1001
1003 1002
1004 1003
1005 1003
1007 1001...
|
by: fischerspooner |
last post by:
Hi,
I'm banging my head against the desk because I can't find a solution for the following simple problem.
Case: There is a column in a table that has FamilyName and FirstName(s) in one field....
|
by: rodchar |
last post by:
hey all,
i'm getting a result that i don't understand
i have a string "test1, test2"
If InStr("test1") and InStr("test2") Then
'Inside
EndIf
The inside is not running for some reason. Any...
|
by: Puneet Bhatnagar |
last post by:
A textual comparison starting at position 4. Returns 6.
MyPos = Instr(4, SearchString, SearchChar, 1)
' A binary comparison starting at position 1. Returns 9.
MyPos = Instr(1, SearchString,...
|
by: lstrudeman |
last post by:
Hello;
A friend gave me this syntax and they are unavailable at the moment and I need this asap. I am trying to figure out how SQL figures this out. Below the syntax takes a field in a file and...
|
by: Brian |
last post by:
what is the equivlant of the vb 6 instr
I have a string there has "*D*N"
I want to find the position that the D is in with a dotnet (VB) function
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |
