Selecting values from a table and using them in vba code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I want to select a specific item from say columnY in a table(tblC) where a
value in columnZ = a variable. I then want this value to be assigned to Value
test.
So far i've tried:
DoCmd.RunSQL("SELECT FROM tblC VALUES ColumnY WHERE ColumnZ= variable) =
DateTakenTrueFalse

any help would be appreciated
 
Try using the DLookup function.

DateTakenTrueFalse = DLookup("ColumnY","TblC","ColumnZ=" & Variable)

Since you didn't specify variable type, columnType, etc. the above assumed that
ColumnZ was a number field and that ColumnY and DateTakenTrueFalse were of the
same data type. Also, if there is no matching record in TblC this will return a
NULL value which could cause an error if DateTakenTrueFalse is not a variant variable.
 
So far i've tried:
DoCmd.RunSQL( _
"SELECT FROM tblC VALUES ColumnY WHERE ColumnZ= variable) _
= DateTakenTrueFalse
Click to expand...

The easiest way to do this is to use DLookup, as John says. The general
solution is to get the SQL correct (you are not far wrong!):-

If ColumnZIsATextField Then
' text fields have to have quotes around them
strSomeValue = Chr(34) & "Eric" & Chr(34)

ElseIf ColumnZIsADate Then
' date fields have to be formatted carefully
strSomeValue = Format(Date(), "\#yyyy\-mm\-dd\#")

Else
' number fields are easier
strSomeValue = CStr(39374)

End If

' now get the main SQL syntax straight -- check the
' select command in help files, note the order of the
' clauses
strSQL = "SELECT ColumnY " & _
"FROM tblC " & _
"WHERE ColumnZ = " & strSomeValue


' open the recordset
' this is the only way to get back >1 field or >1 record
' db should be set to an open database, usually CurrentDB()
'
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbForwardOnly)

' chicken out if no records returned
if rs.BOF then Exit Something

' or else get the value(s)
ReturnedColumnValue = rs!ColumnY

' all done
rs.Close

It is a long way round for one value (that's why they invented DLookup),
but it's the "normal" way to get a set of values.

Hope it helps


Tim F
 
Back
Top