LEFT() Function

[TEE45SEE]

the syntax for the above is LEFT(string, number_of_chars) which is quite straightforward. However I have a case where the (number of chars) will vary from case to case. I have tried using a lngValue rather than a hard number 4 for example but without success. Is there something I am missing or is there a workaround for this?

Thanks

[Micron]

Usually it involves other functions nested somehow. Impossible to be even remotely specific about that without some kind of sample data and a good idea of how representative the sample is compared to the full data set. In other words, what's also important is if there is some kind of key that can be used, such as 'always return/extract all/n of the characters after the first 0 on the left/right'. Seldom are these issues fully explained at the outset - it takes a few tries to nail down a pattern and thus a solution.

EDIT - afterthought: if you're saying that you tried to use a variable and a function would not recognize a value for that variable, then that is another issue. Variables will work in functions such as Left as long as they contain a value that the function will accept.

[isladogs]

Try something like Left ("YourString", InStr(YourString", "SearchCharacter"))

For example
?Left("asxzde",instr("asxzde","x"))
asx

[orange]

Perhaps you could describe what is causing the problem/confusion/unexpected action with an example or 2 to provide more context.

[TEE45SEE]

the syntax for the above is LEFT(string, number_of_chars) which is quite straightforward. However I have a case where the (number of chars) will vary from case to case. I have tried using a lngValue rather than a hard number 4 for example but without success. Is there something I am missing or is there a workaround for this?

Thanks

Here is the code that I am using. If I just insert either 2 or 3 or or100 ..... in pace of lngConLen it works like a charm. Which points me back to my original question. Many thanks
Private Sub cboConversation_AfterUpdate()

Dim strSearchItem As String
Dim strSQL As String
Dim lngConLen As Long 'ConversationLength

strSearchItem = Me.cboConversation.Column(0) & ":" ' Colon is added here in order to be used by InStr
Debug.Print strSearchItem

strSearchItem = Left(strSearchItem, InStr(strSearchItem, ":") - 1)
Debug.Print strSearchItem

lngConLen = Len(strSearchItem)
Debug.Print lngConLen

strSQL = "SELECT * FROM qryComments" _
& " WHERE Left ([memComment],lngConLen) Like " & """" & strSearchItem & """"
Debug.Print strSQL
Me.RecordSource = strSQL
End Sub

[Micron]

Beyond saying you've not had success I've yet to read what the problem is. I surmise that everything looks ok in the variable debugs so perhaps your issue is in the sql debug or the sql returns no records.

First, there's no point in using Like without wildcards. You might as well just use equals ( = ).
Second, the first column (0) of a combo usually holds the long integer (e.g. autonumber) pk value of a record. If you have designed that way, you're saying "LIKE 5" or some other number.

You need to be clear on what the issue is beyond saying you've not had success with something.

[isladogs]

I'm also unclear what the issue is.
However, in your code you place a colon at the end of a search string then search for everything to the left of the colon...which means it returns the original search string.
Somewhat pointless, methinks.

Agree with comments re use of Like

[CJ_London]

I suspect the issue is with the construction of the sql

strSQL = "SELECT * FROM qryComments" _
& " WHERE Left ([memComment],lngConLen) Like " & """" & strSearchItem & """"

think it should be
strSQL = "SELECT * FROM qryComments" _
& " WHERE Left ([memComment],” & lngConLen & “) Like " & """" & strSearchItem & """"

[TEE45SEE]

Thanks again for the responses.
Simply put the question is "why will my code within the LEFT() work correctly with a digit like 4 but will not work with the variable (lngConLen) which has the value of 4"

Something I have just noticed. Running through my code the DebugPrint lngConLen has a value of 4 when I hover over it but it shows no value when I hover over it within the SQL. It is also asking me to "Enter Parameter Value" which, if I enter 4 it works as it should.

[Gicu]

You need to isolate the variable inside the strSQL string as shown by CJ_London in post # 8.
https://learn.microsoft.com/en-us/of...s-and-controls
Cheers,

[TEE45SEE]

Thanks again for ALL responses. Vlad was correct that CJ_London had it right. The only issue with that was the different double quotes CJ used. ” & lngConLen & “ When I changed it to " & lngConLen & " it worked worked perfectly.

I have also removed the addition of the colon (pointless indeed) as well as changing the "LIKE" to =
The code now stands as follows
Private Sub cboConversation_AfterUpdate()

Dim strSearchItem As String
Dim strSQL As String
Dim lngConLen As Long 'ConversationLength

strSearchItem = Me.cboConversation.Column(0)
lngConLen = Len(strSearchItem)
strSQL = "SELECT * FROM qryComments" _
& " WHERE Left ([memComment]," & lngConLen & ") = " & """" & strSearchItem & """"
Me.RecordSource = strSQL
End Sub

[Gicu]

Much easier on the eyes !
Cheers,

[CJ_London]

[QUOTE]The only issue with that was the different double quotes CJ used[/QUOTE]

it’s because I was on my phone- different character set