Search results

You can nest the Replace functions: Expr1: Replace(Trim(Replace(Replace(Replace(Replace([MyFieldName],"~~","~"),"~~","~"),"~~","~"),"~"," "))," ","~") While that works for the string example given, it messes up any other records that contain " " (spaces). I'd call a custom function: Function...

InStr finds the specified character in a string by searching the string from left to right. InStrRev searches from right to left, so: MyNewName: Mid([YourFieldNameHere],InStrRev([YourFieldNameHere],"(")+1,InStrRev([YourFieldNameHere],")")-(InStrRev([YourFieldNameHere],"(")+1))

Oops! Knew I shoulda tested that! Sorry... MyNewName: Mid([YourFieldNameHere],InStr(1,[YourFieldNameHere],"(")+1,InStrRev([YourFieldNameHere],")")-(InStr(1,[YourFieldNameHere],"(")+1)) also works (watch them darn parentheses!)

Try: MyNewName:Mid([YourFieldNameHere], InStr(1, [YourFieldNameHere], "(") + 1, InStrRev([YourFieldNameHere], ")") - InStr(1, [YourFieldNameHere], "("))

Found it! Debug.Print ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(1).Formula Returns (in my case): =SERIES(,'AHU #5 (TC-B) Supply Air Temp'!$U$4:$U$24,'AHU #5 (TC-B) Supply Air Temp'!$V$4:$V$24,1) It would be nice to get the individual range formulas for the chart values and...

I know how to set a charts source data using VBA as in: With ActiveSheet.ChartObjects("Chart 1").Chart .SetSourceData Source:=ActiveSheet.Range(Cells(4, 22), Cells(I - 1, 22)), _ PlotBy:=xlColumns End With But how do I return with VBA the range used on the sheet for the source...

Expr1: IIf(Left([Field1],1)="H" And IsNumeric(Mid([Field1],2)),"",[Field1]) Assigns Expr1 as zero length string if first character of Field1 is "H" and Field1 from the 2nd character to the end of Field1 can be evaluated as a number, otherwise Expr1 = Field1.

Function fncSortStr(strPassed As String) As String Dim Temp As String Dim iTemp As Integer Dim I As Integer While Len(strPassed) iTemp = 1 Temp = Left(strPassed, 1) For I = 2 To Len(strPassed) If StrComp(Mid(strPassed, I, 1), Temp, vbTextCompare) = 0 Then...

Try this: Function fncSortStr(strPassed As String) As String Dim Temp As String Dim I As Integer While Len(strPassed) Temp = Left(strPassed, 1) For I = 2 To Len(strPassed) If Mid(strPassed, I, 1) < Temp Then Temp = Mid(strPassed, I, 1)...

?IsNumeric(Right("abc1234567",7)) In the immediate window returns "True".

You can nest Instr functions to determine if there are 2 spaces in the string like so: ?instr(instr(1, "test test test", " ")+1,"test test test"," ") returns 10, but ?instr(instr(1, "test testtest", " ")+1,"test testtest"," ") returns 0 as does ?instr(instr(1, "testtesttest", "...

InStr([TryRt2]," ") should also return 0. After all, there are no spaces in the string starting from either the left or right of the string. Try nesting your Left or Right functions in an Iif: Iif(Instr(1,[MyString]," "), Left([MyString],SomeNumber), [MyString])

#Error in a query result in this case more than likely means the value resolved for the length argument is negative. Any expression that resolves to a positive number - even one with decimal places - will usually work for length. Example: ?Left("testing", Iif(Instr(1,"testing","k") = 0...

Because the Instr function is returning the character position of the first space in FULLNAME from the left, and the Right function is taking that many characters from the right of FULLNAME. Let FULLNAME = "Mrs. Cindy Anderson". Then the first " " from the left is at character postion 5, and...

Very Good! Good luck with your project!

Did you paste the SQL into the SQL window or the query design grid? It should go in the SQL window. Create a new query in design view. Close the Show Tables window. Under the View Menu, select SQL. Delete any text in the window. Paste the first SQL into that window. Under the View Menu...

Fix those that didn't go, then run the query again (still using the COPY of your original data) but with one small alteration: UPDATE MyTable SET MyTable.FirstName = Mid([MyTable].[Fullname],InStrRev([MyTable].[FullName]," ")+1), MyTable.FullName =...

Try Running this on a COPY of your table: UPDATE MyTable SET MyTable.LastName = Mid([MyTable].[Fullname],InStrRev([MyTable].[FullName]," ")+1), MyTable.FullName = Mid([MyTable].[FullName],1,InStrRev([MyTable].[FullName]," ")-1); It updates the LastName field to the characters after the first...

I don't know know if this will help or not, but if I remove the [lostpersetup] reference from the false part of the Iif construction, the BAD query in the latest zip file works fine. Change to Its kinna weird tho - I get errors in the BAD query result running the ZIP file as is, but removing...

Expr1: IIf(InStr(1,[ttext],"("),Mid([ttext],InStr(InStr(1,[ttext],"("),[ttext]," ")+1,InStr(1,[ttext],")")-InStr(InStr(1,[ttext],"("),[ttext]," ")-1),"") Grabs everything from the first space after the first left parentheses(?) to the first right parentheses. You'll get errors if there's a...