A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Dlookup vs SQL......



 
 
Thread Tools Display Modes
  #1  
Old August 1st, 2004, 10:22 PM
external usenet poster
 
Posts: n/a
Default 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  
Old August 2nd, 2004, 06:13 AM
Graham R Seach
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

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


All times are GMT +1. The time now is 08:08 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.