No Value Given for one or more required parameters

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

LLazuta@gmail.com - 31 Mar 2007 14:19 GMT

I am fairly new to Vba and have been trying to implement a Select
statement with 2 criterias but i keep getting the same error No value
given for one or more ewuired parameters. Any Help will be much
appreciated. This is my code as follows:

Dim myConnection As ADODB.Connection
Set myConnection = CurrentProject.Connection
Dim RS As New ADODB.Recordset
RS.ActiveConnection = myConnection
RS.Open "SELECT * FROM Attendance WHERE RS!ID = '" &
Form_Attendance.CmbID.Value & "' AND RS!DATE = '" &
Form_Attendance.TxtDate.Value & "'", , adOpenDynamic, adLockOptimistic

Reply to this Message

Ken Snell (MVP) - 31 Mar 2007 16:50 GMT

Don't use RS as a table name in the query's SQL statement:

RS.Open "SELECT * FROM Attendance WHERE ID = '" &
Form_Attendance.CmbID.Value & "' AND [DATE] = '" &
Form_Attendance.TxtDate.Value & "'", , adOpenDynamic, adLockOptimistic

Also, I note that you're using Date as the name of a field in a table. They
and many other words are reserved words in ACCESS and should not be used for
field names, etc. See these Knowledge Base articles for more information
about reserved words and characters that should not be used:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763

See this site for code that allows you to validate your names as not being
VBA keywords:

basIsValidIdent - Validate Names to Make Sure They Aren't VBA Keywords
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=18#18

Signature

Ken Snell
<MS ACCESS MVP>

>I am fairly new to Vba and have been trying to implement a Select
> statement with 2 criterias but i keep getting the same error No value
[quoted text clipped - 8 lines]
> Form_Attendance.CmbID.Value & "' AND RS!DATE = '" &
> Form_Attendance.TxtDate.Value & "'", , adOpenDynamic, adLockOptimistic

Reply to this Message

LLazuta@gmail.com - 31 Mar 2007 19:50 GMT

Thanks again for spending the time with this. Well I did all your
suggestions and now the error changed to "Data type mismatch in
criteria expression". If I take out the second criteria and execute it
only with ID it works perfectly. This is my revised code as follows:

RS.Open "SELECT * FROM Attendance WHERE ID = '" &
Form_Attendance.CmbID.Value & "' AND [DT] = '" &
Form_Attendance.TxtDate.Value & "'", , adOpenDynamic, adLockOptimistic

Reply to this Message

Ken Snell (MVP) - 31 Mar 2007 19:55 GMT

You'll need to use # delimiter for a date value, and explicitly format the
date to US format. Try this:

RS.Open "SELECT * FROM Attendance WHERE ID = '" &
Form_Attendance.CmbID.Value & "' AND [DT] = #" &
Format(Form_Attendance.TxtDate.Value, "\#mm\/dd\/yyyy\#"), , adOpenDynamic,
adLockOptimistic

Signature

Ken Snell
<MS ACCESS MVP>
https://mvp.support.microsoft.com/profile/Kenneth.Snell
www.cadellsoftware.org

> Thanks again for spending the time with this. Well I did all your
> suggestions and now the error changed to "Data type mismatch in
[quoted text clipped - 4 lines]
> Form_Attendance.CmbID.Value & "' AND [DT] = '" &
> Form_Attendance.TxtDate.Value & "'", , adOpenDynamic, adLockOptimistic

Reply to this Message

Ken Snell (MVP) - 31 Mar 2007 20:25 GMT

Sorry - left in an extra # character -- corrected code line:

RS.Open "SELECT * FROM Attendance WHERE ID = '" &
Form_Attendance.CmbID.Value & "' AND [DT] = " &
Format(Form_Attendance.TxtDate.Value, "\#mm\/dd\/yyyy\#"), , adOpenDynamic,
adLockOptimistic

Also, if ID field is numeric, remove the ' characters that you're using as
delimiters:

RS.Open "SELECT * FROM Attendance WHERE ID = " &
Form_Attendance.CmbID.Value & " AND [DT] = " &
Format(Form_Attendance.TxtDate.Value, "\#mm\/dd\/yyyy\#"), , adOpenDynamic,
adLockOptimistic

Signature

Ken Snell
<MS ACCESS MVP>
https://mvp.support.microsoft.com/profile/Kenneth.Snell
www.cadellsoftware.org

> You'll need to use # delimiter for a date value, and explicitly format the
> date to US format. Try this:
[quoted text clipped - 12 lines]
>> Form_Attendance.CmbID.Value & "' AND [DT] = '" &
>> Form_Attendance.TxtDate.Value & "'", , adOpenDynamic, adLockOptimistic

Reply to this Message

LLazuta@gmail.com - 31 Mar 2007 20:29 GMT

Well I tried that code and now a new error showed up: "Syntax error
(missing operator) in Query expression 'ID = 'A12345' and [DT] =
##01/03/2007#'.

Reply to this Message

LLazuta@gmail.com - 31 Mar 2007 20:36 GMT

On Apr 1, 2:29 am, LLaz...@gmail.com wrote:

> Well I tried that code and now a new error showed up: "Syntax error
> (missing operator) in Query expression 'ID = 'A12345' and [DT] =
> ##01/03/2007#'.

Ops i didn't see your post. That last part did the trick, thanks a
million. :)

Reply to this Message