Record Count - What am I doing Wrong?

[Keith]

I am not trying to do anything specific but trying to learn more about Vb.
I have learned much from this Forum but I am now stuck.
I have a select query that gets all the customers from the customers table with the first letter begining with the letter "B" this is the SQL view:-

SELECT tblCustomer.LastName
FROM tblCustomer
WHERE (((Left([LastName],1))="B"));

My problem comes when I try to do this in code. My code is:-

Dim strSql As String
Dim rst As Recordset
Dim ing As Integer

strSql = " SELECT tblCustomer.LastName FROM tblCustomer WHERE ((Left([LastName],1)="B"))"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSql)
rst.MoveFirst
rst.MoveLast
ing = rst.RecordCount

Me.txtResult = ing

End Sub

The code works without the WHERE clause but with it I get the following error,

"Compile Error Expected: end of statement" and the letter "B" is highlighted in the strSql.
I have searched this forum untill my eyes ache without any clues as to what I am doing wrong, admittedly I might be getting a bit senile. I would apreciate your help.
Keith.

 

[pbaldy]

Change the double quotes around B to single quotes. You've confused it as to where the SQL string starts and ends.

 

[KernelK]

You need to use single quotes within your double quotes, ie :
strSql = "SELECT tblCustomer.LastName FROM tblCustomer WHERE ((Left([LastName],1)='B'))"

What's happening is that as the compiler goes through that string it is hitting the double quote before the letter B and interpreting it as the end of the string, then it keeps going and finds characters with no meaning to it.

 

[Keith]

Many thanks for your prompt reply, my headache has gone.

 

[Keith]

You need to use single quotes within your double quotes, ie :
strSql = "SELECT tblCustomer.LastName FROM tblCustomer WHERE ((Left([LastName],1)='B'))"

How do I reference a text box in the sql statement? i.e. Enter a letter in a text box and the sql statement select a record set of all last names beginning with that letter. I have tried Dim letter as String and inserting letter in place of the 'B', I have put it in single quotes and also between && with and without single quotes. I have also tried substituting the letter "B" with the reference 'Forms!frmTest.txtLetter' but I cannot make it work. Help please
Keith

 

[KernelK]

Using a string varible strLetter would be:
strSql = "SELECT tblCustomer.LastName FROM tblCustomer WHERE ((Left([LastName],1)='" & strLetter & "'))"

Direct field reference would be:
strSql = "SELECT tblCustomer.LastName FROM tblCustomer WHERE ((Left([LastName],1)='" & me.txtLetter & "'))"

 

[Keith]

Using a string varible strLetter would be:
strSql = "SELECT tblCustomer.LastName FROM tblCustomer WHERE ((Left([LastName],1)='" & strLetter & "'))"

Direct field reference would be:
strSql = "SELECT tblCustomer.LastName FROM tblCustomer WHERE ((Left([LastName],1)='" & me.txtLetter & "'))"

Many thanks KernelK, I'll get the hang of using quotes hopefully
Keith

 

[KernelK]

You'll get it. Just remember double quotes are strictly for putting around your string statements in code, and single quotes are required for string values within your string statements. There are other conventions, but I find this is by far the easiest to remember/use.

 

[Keith]

I have tried both methods suggested but putting ' " & strLetter & " ' or ' " & me,txtLetter & " ' I get a compile error "Expected End of Statement". Without the double quotes I get a message box Runtime Error '3021' No Current Record. In the debug window both strLetter & me.txtLetter contain "B". any suggestions please

 

[KernelK]

Where is the debugger stopping when it says Expected End of Statement?
Because if you were to create a new procedure:

Public Sub TestStr()
    Dim strLetter As String
    Dim strSql As String
    
    strLetter = "B"
    
    strSql = "SELECT tblCustomer.LastName FROM tblCustomer WHERE ((Left([LastName],1)='" & strLetter & "'))"
    Debug.Print strSql
    
End Sub

and in the immiediate window type: call teststr
it should return: SELECT tblCustomer.LastName FROM tblCustomer WHERE ((Left([LastName],1)='B'))
without any errors. Post the code for your modified procedure and where the debugger is throwing the error from.

 

[Keith]

I typed call teststr in the immediate window and get Compile error: Sub or Function not defined

 

[KernelK]

My fault. Don't put the code into your form or whatnot. Create a new module and put it in there. Should test fine that way.

 

[Keith]

Sorry put the code in the wrong place. I put it in a module and call teststr in immediate window gives no errors

 

[KernelK]

Use that code in the procedure where you really need it at, only set your strLetter variable from your text box, instead of explicity setting it to "B":

strLetter = me.txtLetter
instead of
strLetter = "B"

 

[Keith]

silly me, missed space between & and " '. Many many thanks for your help

 

[gemma-the-husky]

Have you got this yet. I always use chr(34) - the double quote to avoid errors. The sql strg needs to be formatted as a string. Try msgbox ing it first to see if it looks right

try this

strSql = "SELECT tblCustomer.LastName FROM tblCustomer WHERE Left(tblcustomer.LastName,1) = " & chr(34) & textfld & chr(34)

msgbox(strsql)

 

[Keith]

Thanks Gemma I got it to work I missed the spaces between the & and the quotes which caused the problem.
Code now
strSql = "SELECT tblCustomer.LastName FROM tblCustomer WHERE ((Left([LastName],1)='" & strLetter & "'))"

At least I've found out how to use the immediate window and debug print which would have saved me many hours had i realised how useful it is.

 

[gemma-the-husky]

the other thing thats really useful is break points. (not so much for unravelling sql statements though). You click in the code left margin, and Access puts a brown blob (breakpoint) there. When the code its the breakpoint it stops, and puts you in the code window - you can then advance through the code a line at a time, and you can inspect any variable by hovering the mouse over it.