recordset help? 2

[jamesmills]

can anyone tell me why this is not working?
i am new to this recordset business!

i am sure i have this correct?
it says there is a synax error in the query expression

Dim currentServerType As String
Dim currentServerNumber As String
Dim serverIP As String

currentServerType = controlPanelType.Value
currentServerNumber = serverNumber.Value

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT serverIP FROM tblServerList WHERE serverType = " & currentServerType & " AND serverName = " & currentServerNumber & ";&quot
serverIP = rs("serverIP&quot
rs.Close

 

[dhaveedh]

I would suggest the following:

dim sSQL AS String to capture your SQL for clarity

sSQL = "SELECT serverIP FROM tblServerList WHERE serverType = " & currentServerType & " AND serverName = " & currentServerNumber & ";&quot

set rs = CurrentDb.OpenRecordset(sSQL)

serverIP = rs.fields(0)

rs.fields(0) holds the record returned from the recordset which is only one field the serverIP

Hope this helps.

Shazam

 

[jamesmills]

ok so i changed my code to what you said!

I am still getting a run-time error 3075

It says synta error (missing operator) in query expression 'serverType = Plesk AND serverName = Server 3'.

Whats going on?
There is a tbl called tblServerList and there are heading called serverType and serverNumber

Thanks

 

[TonyJollans]

Hi jamesmills,

You need some quotes to tell it that Plesk is a literal.

Set rs = CurrentDb.OpenRecordset("SELECT serverIP FROM tblServerList WHERE serverType =

'

" & currentServerType & "

'

 AND serverName = " & currentServerNumber & ";")

Enjoy,
Tony

 

[jamesmills]

amazing!

it did not work at first, but after adding the '' around both of them and not just the first one it works!

Could you explain this, thanks

 

[TonyJollans]

Hi jamesmills,

My mistake. You need quotes around all your non-numeric literals.

I didn't read your post closely enough and saw the CurrentServerNumber as 3 rather than as "Server 3". Assuming it to be numeric, I didn't put it in quotes. You did and you were right.

Enjoy,
Tony