If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Dlookup vs SQL......
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 |
#2
|
|||
|
|||
Dlookup vs SQL......
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/WileyT...764559036.html Kenny wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
DLOOKUP in an external Database gives TYPE Mismatch-Error | Reiner Harmgardt | General Discussion | 1 | July 22nd, 2004 09:00 AM |
Function isn't available in expressions in query expression | Reiner Harmgardt | General Discussion | 4 | July 21st, 2004 09:30 AM |
Problem accessing SQL Server in upsizing wizard | Edward | General Discussion | 1 | July 5th, 2004 06:00 AM |
Oracle, SQL, WOrd and a batch file | rickson | General Discussion | 6 | June 22nd, 2004 05:09 PM |
multiple sql statements in OLE DB command | Jerry Nettleton | Running & Setting Up Queries | 2 | June 18th, 2004 04:58 AM |