Still Problem SQL in VBA

[Ant]

Thanks for the previous help

It is still not working. It is populating the string with
the actuall SQL statement and not the result of the SQL
statement. What do I have to do to capture the return
value of a SQL statement into a string, to be used further
in code.


Basically (simplified)
I have a table with 2 columns 'Name' and 'Path' I have a
combo box where
the user selects 'Name'. I want to use 'Path' in my code.

Private Sub cmdSelectDB_Click()
Dim strName As String
Dim strPath As String
Dim strFullPath As String


strName = me.lstName.Text
strPath = "SELECT MAx(Path) FROM TblCompNamePath Where
CompanyName = " & strName
strFullPath = strPath + "\" + strFileName + "_be"

End Sub

 

[Ron Weiner]

I think you want to do is use a Dlookup(). Try

strPath = nz(dlookup("Path","TblCompnamePath","CompanyName='" & strName &
"'"),"")

Look in Access Help for the d commands (Dlookup, Dmax, etc)

Ron W

 

[JohnFol]

strPath = DMax("[Path]", "TblCompNamePath", "[CompanyName] = '" & strName &
"'")