[AccessD] Too few parameters expected 1

Three simple rules:

*Anything* that is defined locally such as a variable or function which uses a local variable 
needs to be embedded using:  " & myVar & " . 
(Think about it - only the current procedure  knows about AP14, the Jet engine doesn't know 
what it is.)  

If the item is a string, you also need a single quote before and after that: ' " & ... " '

If it is a string which can contain single quotes, you need to "escape" them by doubling 
them:   ' " &  Replace(strMyString,"  '  ","  '  '  ") & " '   

(extra spaces added above for clarity)

-- 
Stuart

On 26 May 2009 at 11:17, Kaup, Chester wrote:

> That got it. Thanks. Darn quotes get me a lot.
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters
> Sent: Tuesday, May 26, 2009 11:10 AM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Too few parameters expected 1
> 
> Hi Chester,
> 
> Perhaps this confirms the syntax issue.
> 
> '" & API14 & "' is the same as '42415017530000'
> 
> Dan
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester
> Sent: Tuesday, May 26, 2009 11:00 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Too few parameters expected 1
> 
> Forgot to add if I replace API12 with '42415017530000' the code works fine.
> API14 also shows to have a value in it.
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
> Sent: Tuesday, May 26, 2009 10:53 AM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Too few parameters expected 1
> 
> Chester:
> 
> When I get that error message, I copy the SQL into the SQL view of a query
> and then go to the design view.  Usually I can see the problem right away
> that way.
> 
> Rocky
>  
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester
> Sent: Tuesday, May 26, 2009 8:41 AM
> To: Access Developers discussion and problem solving
> Subject: [AccessD] Too few parameters expected 1
> 
> Here is the code the generates the error. The SQL statement does not seem to
> be reading the variable API14. It is dimmed as text.
> 
> Set RS1 = MyDb.OpenRecordset("dbo_DSS_StatusChanges")
> Set RS2 = MyDb.OpenRecordset("dbo_DSS_CompletionMaster")
> RS2.MoveFirst
> API14 = RS2.Fields(0)
> 
> strSql = "SELECT dbo_DSS_StatusChanges.PID,
> dbo_DSS_CompletionMaster.Well_Number,
> DateSerial(Year(Date()),Month(Date())-1,1) AS RecordDate,
> Max(dbo_DSS_StatusChanges.StatusDate) AS MaxOfStatusDate FROM
> dbo_DSS_StatusChanges INNER JOIN dbo_DSS_CompletionMaster ON
> dbo_DSS_StatusChanges.PID = dbo_DSS_CompletionMaster.PID WHERE
> (((dbo_DSS_StatusChanges.StatusDate) <= DateSerial(Year(Date()),
> Month(Date()) - 1, 1))and dbo_DSS_CompletionMaster.PID = API14) GROUP BY
> dbo_DSS_StatusChanges.PID, dbo_DSS_CompletionMaster.Well_Number
> ORDER BY dbo_DSS_CompletionMaster.Well_Number;"
> 
> 
> Set RS3 = MyDb.OpenRecordset(strSql)
> 
> 
> Chester Kaup
> Engineering Technician
> Kinder Morgan CO2 Company, LLP
> Office (432) 688-3797
> FAX (432) 688-3799
> 
>  
> No trees were killed in the sending of this message. However a large number
> of electrons were terribly inconvenienced.
> 
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>