InStr Troubles

[sk84gtspd]

Hi People who make my life easier ,

I had trouble deciding where to post this thread so I apologize if it is misplaced.

I want to that the WHERE clause for a SQL statement that I am using options on a form to build. I intend to use the clause in opening a datasheet form.

This is the code I have for getting the substring

 Dim intPos As Integer
Dim tempString As String
Dim BaseQueryFormStr As String 
 'BaseQueryFormStr is used to reopen the BaseMasterQueryFrm with the specified parameters

 tempString = "WHERE"

 intPos = InStr(1, strSQL, tempString, vbTextCompare)
BaseQueryFormStr = Left(strSQL, intPos - 1)

 MsgBox (BaseQueryFormStr)

the value of intPos remains=0 and when the program hits the second to last line I get "run-time error 5"

Am I missing something really obvious here?

Thanks for the help.

 

[gemma-the-husky]

does vbtextcompare force a case-match? maybe that is the problem.


maybe something like this

 tempString = "WHERE"

 'test for greater than 1. if the sql starts "where" there is a different problem
if  InStr(1, strSQL, tempString)>1 then
     BaseQueryFormStr = Left(strSQL,  InStr(1, strSQL, tempString))
      MsgBox (BaseQueryFormStr)
 else
      msgbox tempstring & " was not found in query: " & strsql
 end if

 

[sk84gtspd]

:banghead: If I don't make any selections on the form where query is being built there is no where clause to add because I want all of the results.

Having the full string come out in the message box showed me that.

Maybe its time to take a short break.

 

[MSAccessRookie]

Obvious is a relative term, but one thing that I find missing from this code fragment is the definition (type and content) of the variable strSQL. The assumption that it will be a string and has an assigned value is not always going to be valid for every context. If the variable strSQL is undefined, or is Null then it could account for the run-time error 5. Set a breakpoint and evaluate the value of the variable strSQL.

-- Rookie

 

[sk84gtspd]

Yes I believe the runtime error was because I was trying to cut the string at index -1 i.e. intPos which was zero minus 1

strSQL is defined as a string data type BTW just neglected to put it in the code snippet.

Thanks for the kind words and advice MSAccessRookie

 

[gemma-the-husky]

well this then (I see I missed the -1 of the string slice in my first go)

 tempString = "WHERE"

 'test for greater than 1. if the sql starts "where" there is a different problem
if  InStr(1, strSQL, "where")>1 then
     BaseQueryFormStr = Left(strSQL,  InStr(1, strSQL, tempString)-1)
 else
     basequeryformstr = strsql
  end if
 msgbox basequeryformstr

 

[sk84gtspd]

I think I have another issue.

Example: Say

strSQL="SELECT last_name, first_name FROM people WHERE id=7"

Im trying to get

BaseQueryFormStr = "id=7"

I want everything from the right of "WHERE" The code, as it is, I don't believe will perform as I want it too.

I thought that I could just change from Left() to Right() but my msgbox doesn't contain what I would expect. It still contains large portions of the SELECT and FROM no WHERE.

My first suspicion is some sort of size constraint. intPos = 1444. I verified that this is correct by msgbox (len(strSQL)) before I start building the WHERE statement.

Im not sure how to get what I want now. Any suggestions?

 

[sk84gtspd]

This may seems to be what Im looking for but this is VB without the A haha

 BaseQueryFormStr = RSet(strSQL, len(strSQL)-intPos)

 

[sk84gtspd]

This ended up being what I needed I just apparently had some mental blocks.

This snippet returns the "WHERE" clause of a SQL string but does not contain the word where. This was done be able to open a datasheet form with a where statement.

 Dim intPos As Integer
Dim strSQL, tempString, BaseQueryFormStr As String
  
 tempString = "WHERE"

 intPos = InStr(1, strSQL, tempString, vbTextCompare)
       'intPos returns the position of the 'W' in "where"
       'below I add 5 to this position to account for the other
       'four letters and the space
  
 If intPos > 0 Then
     BaseQueryFormStr = Right(strSQL, Len(strSQL) - (intPos + 5))
     'MsgBox BaseQueryFormStr 'For testing
 Else
      MsgBox tempString & " was not found in query: " & strSQL 'For testing
 End If

Sometimes just feeling supported gives me the energy to keep trying

 

[MSAccessRookie]

I think I have another issue.

Example: Say

strSQL="SELECT last_name, first_name FROM people WHERE id=7"

Im trying to get

BaseQueryFormStr = "id=7"

I want everything from the right of "WHERE" The code, as it is, I don't believe will perform as I want it too.

I thought that I could just change from Left() to Right() but my msgbox doesn't contain what I would expect. It still contains large portions of the SELECT and FROM no WHERE.

My first suspicion is some sort of size constraint. intPos = 1444. I verified that this is correct by msgbox (len(strSQL)) before I start building the WHERE statement.

Im not sure how to get what I want now. Any suggestions?

Try using the Function InstrRev() where you currently are using the Function Instr() and see if that makes a difference.

-- Rookie

http://www.techonthenet.com/access/functions/string/instrrev.php

 

[vbaInet]

Try using the Function InstrRev() where you currently are using the Function Instr() and see if that makes a difference.

-- Rookie

http://www.techonthenet.com/access/functions/string/instrrev.php

This would have been a more sensible function to use because the WHERE part in your SQL string is towards the end.