Dlookup vs SQL......

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

Guest

Hi Guys,

I am now battling to pass the result of a SQL string to a variable.

I can access the result that I need through the Dlookup function, but I
would like to get this right in SQL. Is this possible?

I have pasted the SQL string generated
by the QBE grid, but I keep on getting an error that that says "a RunSQL
action requires a SQL string as an argument"

personalcert = DoCmd.RunSQL "SELECT Count(certnumgen.Pilotcode) AS
CountOfPilotcode FROM certnumgen WHERE
(((certnumgen.Pilotcode)=[Forms]![Pilotdata]![Pilotcode]));"

personalcert is a string variable.

Ok guys I am sure I am doing something stupid here. Thanks for the help.

Kenny
 
Kenny,

You can't do it that way; you can use DLookup or a recordset (which is shown
below).

Dim rs As DAO.Recordset
Dim sSQL As String

sSQL = "SELECT Count(Pilotcode) AS CountOfPilotcode " & _
"FROM certnumgen " & _
"WHERE Pilotcode = " & Forms!Pilotdata!Pilotcode
'If PilotCode is text, then use this instead:
' "WHERE Pilotcode = """ & Forms!Pilotdata!Pilotcode & """"

Set rs = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)
If rs.AbsolutePosition > -1 Then
personalcert = rs!CountOfPilotCode
Else
personalcert = ""
End If

rs.Close
Set rs = Nothing

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
Back
Top