Having problem with SQL statement - error no value given for one or more required parameters

[JIm]

The debug print show a value of 1/5/2004.
get an error message "no value given for one or more required
parameters"
Thanks in advance

Sub FindRecord()
Dim rst As ADODB.Recordset
Dim startdate As Date

Set rst = New ADODB.Recordset

startdate = #1/5/2004#

rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic

Debug.Print "value of startdate"; startdate
rst.Open "SELECT STATS.Date, STATS.MDPIX from stats WHERE
STATS.Date = startdate ;"

rst.Close
Set rst = Nothing
End Sub

 

[Peter van der Goes]

The debug print show a value of 1/5/2004.
get an error message "no value given for one or more required
parameters"
Thanks in advance

Sub FindRecord()
Dim rst As ADODB.Recordset
Dim startdate As Date

Set rst = New ADODB.Recordset

startdate = #1/5/2004#

rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic

Debug.Print "value of startdate"; startdate
rst.Open "SELECT STATS.Date, STATS.MDPIX from stats WHERE
STATS.Date = startdate ;"

rst.Close
Set rst = Nothing
End Sub

I'll try this from memory, so some tweaking may be needed.
'startdate = #1/5/2004#
'won't work, IIRC
startdate = 1/5/2004
'then try
rst.Open "SELECT Stats.Date, Stats.Mdpix FROM Stats WHERE Stats.Date = " & _
"#startdate#"

Hope this helps some.

 

[Gerald Stanley]

2 problem areas are immediately obvious

1. I have noticed that you appear to have named on the
columns 'Date'. This is a reserved word and must be
surrounded by []. It is bad practice to use any reserved
word as a column name and if you have done this I would
advise changing it.

2. The use of the variable startdate should not be included
within the string. Also it should be enclosed in ## as it
is a date. So the statement should look something like

rst.Open "SELECT STATS.Date, STATS.MDPIX from stats WHERE
STATS.Date = #" & startdate & "#;"

Hope That Helps
Gerald Stanley MCSD

 

[Douglas J. Steele]

Just a slight refinement on Gerald's solution.

On the off chance that your application may be used on machines where the
short date format has been set to dd/mm/yyyy, it's important to realize that
dates in SQL MUST be in mm/dd/yyyy format, so use:

rst.Open "SELECT STATS.[Date], STATS.MDPIX from stats WHERE
STATS.[Date] = #" & Format(startdate, "mm/dd/yyyy") & "#"

or, my preference,

rst.Open "SELECT STATS.[Date], STATS.MDPIX from stats WHERE
STATS.[Date] = " & Format(startdate, "\#mm\/dd\/yyyy\#")

(Okay, I'm lying when I say they must be in mm/dd/yyyy. They can actually be
in any unambiguous format. The point is that Access doesn't respect the
regional settings, and dates in dd/mm/yyyy format will ALWAYS be
interpretted as mm/dd/yyyy for days 1-12 of each month.)

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

2 problem areas are immediately obvious

1. I have noticed that you appear to have named on the
columns 'Date'. This is a reserved word and must be
surrounded by []. It is bad practice to use any reserved
word as a column name and if you have done this I would
advise changing it.

2. The use of the variable startdate should not be included
within the string. Also it should be enclosed in ## as it
is a date. So the statement should look something like

rst.Open "SELECT STATS.Date, STATS.MDPIX from stats WHERE
STATS.Date = #" & startdate & "#;"

Hope That Helps
Gerald Stanley MCSD

-----Original Message-----
The debug print show a value of 1/5/2004.
get an error message "no value given for one or more required
parameters"
Thanks in advance

Sub FindRecord()
Dim rst As ADODB.Recordset
Dim startdate As Date

Set rst = New ADODB.Recordset

startdate = #1/5/2004#

rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic

Debug.Print "value of startdate"; startdate
rst.Open "SELECT STATS.Date, STATS.MDPIX from stats WHERE
STATS.Date = startdate ;"

rst.Close
Set rst = Nothing
End Sub
.

 

[Jeff Williams]

I noticed that the OP is using ADO. I am not sure that ADO recognizes the #
delimiter for dates. I believe you need to use the SQL standard single
quote for the date delimeter. I may be wrong...

Just a slight refinement on Gerald's solution.

On the off chance that your application may be used on machines where the
short date format has been set to dd/mm/yyyy, it's important to realize that
dates in SQL MUST be in mm/dd/yyyy format, so use:

rst.Open "SELECT STATS.[Date], STATS.MDPIX from stats WHERE
STATS.[Date] = #" & Format(startdate, "mm/dd/yyyy") & "#"

or, my preference,

rst.Open "SELECT STATS.[Date], STATS.MDPIX from stats WHERE
STATS.[Date] = " & Format(startdate, "\#mm\/dd\/yyyy\#")

(Okay, I'm lying when I say they must be in mm/dd/yyyy. They can actually be
in any unambiguous format. The point is that Access doesn't respect the
regional settings, and dates in dd/mm/yyyy format will ALWAYS be
interpretted as mm/dd/yyyy for days 1-12 of each month.)

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

2 problem areas are immediately obvious

1. I have noticed that you appear to have named on the
columns 'Date'. This is a reserved word and must be
surrounded by []. It is bad practice to use any reserved
word as a column name and if you have done this I would
advise changing it.

2. The use of the variable startdate should not be included
within the string. Also it should be enclosed in ## as it
is a date. So the statement should look something like

rst.Open "SELECT STATS.Date, STATS.MDPIX from stats WHERE
STATS.Date = #" & startdate & "#;"

Hope That Helps
Gerald Stanley MCSD

-----Original Message-----
The debug print show a value of 1/5/2004.
get an error message "no value given for one or more required
parameters"
Thanks in advance

Sub FindRecord()
Dim rst As ADODB.Recordset
Dim startdate As Date

Set rst = New ADODB.Recordset

startdate = #1/5/2004#

rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic

Debug.Print "value of startdate"; startdate
rst.Open "SELECT STATS.Date, STATS.MDPIX from stats WHERE
STATS.Date = startdate ;"

rst.Close
Set rst = Nothing
End Sub
.

 

[Douglas J. Steele]

# is fine in ADO, as long as you're going against a Jet database.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

I noticed that the OP is using ADO. I am not sure that ADO recognizes the #
delimiter for dates. I believe you need to use the SQL standard single
quote for the date delimeter. I may be wrong...

Just a slight refinement on Gerald's solution.

On the off chance that your application may be used on machines where the
short date format has been set to dd/mm/yyyy, it's important to realize that
dates in SQL MUST be in mm/dd/yyyy format, so use:

rst.Open "SELECT STATS.[Date], STATS.MDPIX from stats WHERE
STATS.[Date] = #" & Format(startdate, "mm/dd/yyyy") & "#"

or, my preference,

rst.Open "SELECT STATS.[Date], STATS.MDPIX from stats WHERE
STATS.[Date] = " & Format(startdate, "\#mm\/dd\/yyyy\#")

(Okay, I'm lying when I say they must be in mm/dd/yyyy. They can

actually

be
in any unambiguous format. The point is that Access doesn't respect the
regional settings, and dates in dd/mm/yyyy format will ALWAYS be
interpretted as mm/dd/yyyy for days 1-12 of each month.)

 

[Peter van der Goes]

2 problem areas are immediately obvious

1. I have noticed that you appear to have named on the
columns 'Date'. This is a reserved word and must be
surrounded by []. It is bad practice to use any reserved
word as a column name and if you have done this I would
advise changing it.

2. The use of the variable startdate should not be included
within the string. Also it should be enclosed in ## as it
is a date. So the statement should look something like

rst.Open "SELECT STATS.Date, STATS.MDPIX from stats WHERE
STATS.Date = #" & startdate & "#;"

Hope That Helps
Gerald Stanley MCSD

Now I remember ;-)
Gerald's syntax is correct. Mine was incorrect.

 

[Gerald Stanley]

As this thread used ADO, I thought that I would take the
opportunity to promote a way of using it to overcome 2
traps that newcomers usually fall into when composing SQL
strings; firstly the unambiguous entry of dates and
secondly the entry of text strings containing quotes

Using the original example, declare a ADO command object,
instantiate it and set its ActiveConnection property e.g.

Dim cm As ADODB.Command

Set cm = New ADODB.Command
Set cm.ActiveConnection = CurrentProject.AccessConnection or
Set cm.ActiveConnection = CurrentProject.Connection

Then use the PARAMETERS syntax of SQL in the definition of
our CommandText
cm.CommandText = "PARAMETERS pDate Date; SELECT [Date],
MDPIX from Stats WHERE [Date] = pDate;"

Now set the recordset to the output from the command's
Execute method, the second parameter of which takes the
declared parameter(s)

Set rst = cm.Execute (,startDate)

This approach overcomes the use of quotation marks in
strings as the entire CommandText string is defined within
one set of double quotes to start with.

Where multiple parameters need to be entered, you have to
declare a variant array which will go in place of the
startDate in the Execute statement. The subType of each
element in the array must correspond to the Type
declaration in the PARAMETERS statement.

Hope This Helps
Gerald Stanley MCSD

-----Original Message-----
Just a slight refinement on Gerald's solution.

On the off chance that your application may be used on machines where the
short date format has been set to dd/mm/yyyy, it's important to realize that
dates in SQL MUST be in mm/dd/yyyy format, so use:

rst.Open "SELECT STATS.[Date], STATS.MDPIX from stats WHERE
STATS.[Date] = #" & Format(startdate, "mm/dd/yyyy") & "#"

or, my preference,

rst.Open "SELECT STATS.[Date], STATS.MDPIX from stats WHERE
STATS.[Date] = " & Format(startdate, "\#mm\/dd\/yyyy\#")

(Okay, I'm lying when I say they must be in mm/dd/yyyy. They can actually be
in any unambiguous format. The point is that Access doesn't respect the
regional settings, and dates in dd/mm/yyyy format will ALWAYS be
interpretted as mm/dd/yyyy for days 1-12 of each month.)

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

2 problem areas are immediately obvious

1. I have noticed that you appear to have named on the
columns 'Date'. This is a reserved word and must be
surrounded by []. It is bad practice to use any reserved
word as a column name and if you have done this I would
advise changing it.

2. The use of the variable startdate should not be included
within the string. Also it should be enclosed in ## as it
is a date. So the statement should look something like

rst.Open "SELECT STATS.Date, STATS.MDPIX from stats WHERE
STATS.Date = #" & startdate & "#;"

Hope That Helps
Gerald Stanley MCSD

-----Original Message-----
The debug print show a value of 1/5/2004.
get an error message "no value given for one or more required
parameters"
Thanks in advance

Sub FindRecord()
Dim rst As ADODB.Recordset
Dim startdate As Date

Set rst = New ADODB.Recordset

startdate = #1/5/2004#

rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic

Debug.Print "value of startdate"; startdate
rst.Open "SELECT STATS.Date, STATS.MDPIX from stats WHERE
STATS.Date = startdate ;"

rst.Close
Set rst = Nothing
End Sub
.

.