Read into variable

[damarious]

Access 2000

If I have a table with 4 columns, all are text, how would I get the data from the 4th column into a variable using the 1st column as a reference.

For example:

cnum, cname, addy, IPs
-----------------------
1234, bob, 321 st, 1.1.1.1

I need to get a variable to contain 1.1.1.1 if cnum=1234

I don't think it's too tough, I'm just stuck.

[AKA]

Try this for example


Dim rst As Recordset, dbs As Database, strSQL As String
Set dbs = CurrentDb()
strSQL = "select IPs from T_Your_Table where cnum = '" & strCNum & "' ;"
Set rst = dbs.OpenRecordset(strSQL)
rst.MoveFirst
strIPs = rst![IPs]
rst.Close
Set dbs = Nothing

[damarious]

Thanks for the code chunk, it seems to be what I'm after, but it's throwing a "Type Mismatch" error where marked below.

Dim rst As Recordset, dbs As Database, strSQL As String
Dim strIPS As String

Set dbs = CurrentDb()
strSQL = "select IP from MainData where cnum = '" & cnumadd & "' ;"

Set rst = dbs.OpenRecordset(strSQL) <- THROWS ERROR!
rst.MoveFirst
strIPS = rst![IPs]
rst.Close
Set dbs = Nothing
MsgBox strIPS

[AKA]

It migth bee easier to understand why you get an error if you examine the strSQL variable. You can also post it here and I can check it right away at least for next 10 minutes.

[damarious]

strSQL = "select IP from MainData where cnum = '123456';"

That's it. The 123456 is a text string, not a number.

[AKA]

Is not the cnum column text ? You said that in your first post. If not remove the ' but I assume that you allready figured that out.

[damarious]

F.Y.I.

Dim rst As Recordset
Dim dbs As Database
Dim strSQL1 As String
Dim strIP As String
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("SELECT ips FROM MainData WHERE (MainData.cnum = '123456');") 'DIES HERE!

rst.MoveFirst
strIPS = rst![ips]
rst.Close
Set dbs = Nothing
MsgBox strIPS


DOES NOT WORK, But after I commented out

Dim rst As Recordset

I works fine.... go figure.