help with code - DAO Recordset

  • Thread starter Thread starter Karen
  • Start date Start date
K

Karen

I'm trying to create a record set and am running into errors...

I want this...

Dim rst As DAO.Recordset
Dim MyWksp As Workspace
Dim MyDB As DAO.Database
Dim MySQL As String, MyCriteria As String

Set MyWksp = DBEngine.Workspaces(0)
Set MyDB = MyWksp.Databases(0)


MySQL = "SELECT DISTINCT tblfillrecord.fldproduct,
tblfillrecord.fldlot, tblfillrecord.fldcode,
tblQualityRecord.fldexpiresdate, tblQualityRecord.fldmadedate FROM
tblfillrecord INNER JOIN tblQualityRecord ON (tblfillrecord.fldcode =
tblQualityRecord.fldcode) AND (tblfillrecord.fldlot =
tblQualityRecord.fldlotno) WHERE (((tblfillrecord.fldproduct) Like
[forms]![frm cert dialog box]![text0]) AND (tblfillrecord.fldlot)=
IIf(Len([forms]![frm cert dialog box]![text2])=Val(8)Or
(Len([forms]![frm cert dialog box]![text2])=Val(7) And Mid([forms]![frm
cert dialog box]![text2],4,1)<>'1'),Left([forms]![frm cert dialog
box]![text2],4), Left([forms]![frm cert dialog box]![text2],3)))"


Set rst = MyDB.OpenRecordset(MySQL, dbOpenForwardOnly)

but i get an error about a missing ), ], or ITEM in the expression (and
then the error shows the Where section of the above)

If I try to change this statement so that i'm using where
tblfillrecord.fldproduct like [forms]![frm cert dialog box]![text0])
AND (tblfillrecord.fldlot)= '425' "
I get an error about Too Few Parameters. Expecting 1.

Any ideas where I've gone wrong?

Thanks,
Karen
 
I cleaned up your WHERE statement as best I could without testing. You do
not need Val() for numerics. and you do no need single quotes around the 1
in <>'1'.

Access tends to over-punctuate WHERE clauses which makes them a bear to
debug. I removed all the ones that appear extraneous.

WHERE(tblfillrecord.fldproduct Like [forms]![frm cert dialog box]![text0])
AND(tblfillrecord.fldlot = IIf(Len([Forms]![frm cert dialog box]![text2]) =
8 Or (Len([Forms]![frm cert dialog box]![text2]) = 7 And Mid([Forms]![frm
cert dialog box]![text2], 4, 1) <> 1), Left([Forms]![frm cert dialog
box]![text2], 4), Left([Forms]![frm cert dialog box]![text2], 3)))
 
I've cleaned up my Where statement as recomended.. I get an error
reading Too Few Parameters. Expected 2. I believe this is triggered by
the
Set rst = MyDB.OpenRecordSet(MySQL, db OpenForwardOnly) statement.

What would cause that?


I cleaned up your WHERE statement as best I could without testing. You do
not need Val() for numerics. and you do no need single quotes around the 1
in <>'1'.

Access tends to over-punctuate WHERE clauses which makes them a bear to
debug. I removed all the ones that appear extraneous.

WHERE(tblfillrecord.fldproduct Like [forms]![frm cert dialog box]![text0])
AND(tblfillrecord.fldlot = IIf(Len([Forms]![frm cert dialog box]![text2]) =
8 Or (Len([Forms]![frm cert dialog box]![text2]) = 7 And Mid([Forms]![frm
cert dialog box]![text2], 4, 1) <> 1), Left([Forms]![frm cert dialog
box]![text2], 4), Left([Forms]![frm cert dialog box]![text2], 3)))

--
Bill Mosca, MS Access MVP


Karen said:
I'm trying to create a record set and am running into errors...

I want this...

Dim rst As DAO.Recordset
Dim MyWksp As Workspace
Dim MyDB As DAO.Database
Dim MySQL As String, MyCriteria As String

Set MyWksp = DBEngine.Workspaces(0)
Set MyDB = MyWksp.Databases(0)


MySQL = "SELECT DISTINCT tblfillrecord.fldproduct,
tblfillrecord.fldlot, tblfillrecord.fldcode,
tblQualityRecord.fldexpiresdate, tblQualityRecord.fldmadedate FROM
tblfillrecord INNER JOIN tblQualityRecord ON (tblfillrecord.fldcode =
tblQualityRecord.fldcode) AND (tblfillrecord.fldlot =
tblQualityRecord.fldlotno) WHERE (((tblfillrecord.fldproduct) Like
[forms]![frm cert dialog box]![text0]) AND (tblfillrecord.fldlot)=
IIf(Len([forms]![frm cert dialog box]![text2])=Val(8)Or
(Len([forms]![frm cert dialog box]![text2])=Val(7) And Mid([forms]![frm
cert dialog box]![text2],4,1)<>'1'),Left([forms]![frm cert dialog
box]![text2],4), Left([forms]![frm cert dialog box]![text2],3)))"


Set rst = MyDB.OpenRecordset(MySQL, dbOpenForwardOnly)

but i get an error about a missing ), ], or ITEM in the expression (and
then the error shows the Where section of the above)

If I try to change this statement so that i'm using where
tblfillrecord.fldproduct like [forms]![frm cert dialog box]![text0])
AND (tblfillrecord.fldlot)= '425' "
I get an error about Too Few Parameters. Expecting 1.

Any ideas where I've gone wrong?

Thanks,
Karen
Click to expand...
Click to expand...
 
Karen

"Too few parameters" deals with the query syntax. I should have caught that
in the first place. Take the textbox variables out of the SQL and use them
as variables.

WHERE(tblfillrecord.fldproduct Like '" & [forms]![frm cert dialog
box]![text0] & "')
AND(tblfillrecord.fldlot = IIf(Len('" & [Forms]![frm cert dialog
box]![text2] & "') =
8 Or (Len('" & [Forms]![frm cert dialog box]![text2] & "') = 7 And Mid('"
[Forms]![frm
cert dialog box]![text2] & "', 4, 1) <> 1), Left('" & [Forms]![frm cert
dialog
box]![text2] & "', 4), Left('" & [Forms]![frm cert dialog box]![text2] & "',
3)))

The whole idea is to use the control as a variable instead of putting it
directly into the query. You have to do it that way when using VBA. If the
control is a string it must be surrounded by single quotes.

--
Bill Mosca, MS Access MVP


Karen said:
I've cleaned up my Where statement as recomended.. I get an error
reading Too Few Parameters. Expected 2. I believe this is triggered by
the
Set rst = MyDB.OpenRecordSet(MySQL, db OpenForwardOnly) statement.

What would cause that?


I cleaned up your WHERE statement as best I could without testing. You do
not need Val() for numerics. and you do no need single quotes around the
1
in <>'1'.

Access tends to over-punctuate WHERE clauses which makes them a bear to
debug. I removed all the ones that appear extraneous.

WHERE(tblfillrecord.fldproduct Like [forms]![frm cert dialog
box]![text0])
AND(tblfillrecord.fldlot = IIf(Len([Forms]![frm cert dialog
box]![text2]) =
8 Or (Len([Forms]![frm cert dialog box]![text2]) = 7 And Mid([Forms]![frm
cert dialog box]![text2], 4, 1) <> 1), Left([Forms]![frm cert dialog
box]![text2], 4), Left([Forms]![frm cert dialog box]![text2], 3)))

--
Bill Mosca, MS Access MVP


Karen said:
I'm trying to create a record set and am running into errors...

I want this...

Dim rst As DAO.Recordset
Dim MyWksp As Workspace
Dim MyDB As DAO.Database
Dim MySQL As String, MyCriteria As String

Set MyWksp = DBEngine.Workspaces(0)
Set MyDB = MyWksp.Databases(0)


MySQL = "SELECT DISTINCT tblfillrecord.fldproduct,
tblfillrecord.fldlot, tblfillrecord.fldcode,
tblQualityRecord.fldexpiresdate, tblQualityRecord.fldmadedate FROM
tblfillrecord INNER JOIN tblQualityRecord ON (tblfillrecord.fldcode =
tblQualityRecord.fldcode) AND (tblfillrecord.fldlot =
tblQualityRecord.fldlotno) WHERE (((tblfillrecord.fldproduct) Like
[forms]![frm cert dialog box]![text0]) AND (tblfillrecord.fldlot)=
IIf(Len([forms]![frm cert dialog box]![text2])=Val(8)Or
(Len([forms]![frm cert dialog box]![text2])=Val(7) And Mid([forms]![frm
cert dialog box]![text2],4,1)<>'1'),Left([forms]![frm cert dialog
box]![text2],4), Left([forms]![frm cert dialog box]![text2],3)))"


Set rst = MyDB.OpenRecordset(MySQL, dbOpenForwardOnly)

but i get an error about a missing ), ], or ITEM in the expression (and
then the error shows the Where section of the above)

If I try to change this statement so that i'm using where
tblfillrecord.fldproduct like [forms]![frm cert dialog box]![text0])
AND (tblfillrecord.fldlot)= '425' "
I get an error about Too Few Parameters. Expecting 1.

Any ideas where I've gone wrong?

Thanks,
Karen
Click to expand...
Click to expand...
Click to expand...
 
hello karen try and delet a few thing or see if you have got a virus just by
going into your bug buster or control panel.from simon
(e-mail address removed).
 
yeah; because MDB is a piece of crap ass language.

that is why it trys to over-puncuate.

under NO circumstance should ANYONE use MDB or DAO for ANYTHING

-Aaron

I cleaned up your WHERE statement as best I could without testing. You do
not need Val() for numerics. and you do no need single quotes around the 1
in <>'1'.

Access tends to over-punctuate WHERE clauses which makes them a bear to
debug. I removed all the ones that appear extraneous.

WHERE(tblfillrecord.fldproduct Like [forms]![frm cert dialog box]![text0])
AND(tblfillrecord.fldlot = IIf(Len([Forms]![frm cert dialog box]![text2]) =
8 Or (Len([Forms]![frm cert dialog box]![text2]) = 7 And Mid([Forms]![frm
cert dialog box]![text2], 4, 1) <> 1), Left([Forms]![frm cert dialog
box]![text2], 4), Left([Forms]![frm cert dialog box]![text2], 3)))

--
Bill Mosca, MS Access MVP


Karen said:
I'm trying to create a record set and am running into errors...

I want this...

Dim rst As DAO.Recordset
Dim MyWksp As Workspace
Dim MyDB As DAO.Database
Dim MySQL As String, MyCriteria As String

Set MyWksp = DBEngine.Workspaces(0)
Set MyDB = MyWksp.Databases(0)


MySQL = "SELECT DISTINCT tblfillrecord.fldproduct,
tblfillrecord.fldlot, tblfillrecord.fldcode,
tblQualityRecord.fldexpiresdate, tblQualityRecord.fldmadedate FROM
tblfillrecord INNER JOIN tblQualityRecord ON (tblfillrecord.fldcode =
tblQualityRecord.fldcode) AND (tblfillrecord.fldlot =
tblQualityRecord.fldlotno) WHERE (((tblfillrecord.fldproduct) Like
[forms]![frm cert dialog box]![text0]) AND (tblfillrecord.fldlot)=
IIf(Len([forms]![frm cert dialog box]![text2])=Val(8)Or
(Len([forms]![frm cert dialog box]![text2])=Val(7) And Mid([forms]![frm
cert dialog box]![text2],4,1)<>'1'),Left([forms]![frm cert dialog
box]![text2],4), Left([forms]![frm cert dialog box]![text2],3)))"


Set rst = MyDB.OpenRecordset(MySQL, dbOpenForwardOnly)

but i get an error about a missing ), ], or ITEM in the expression (and
then the error shows the Where section of the above)

If I try to change this statement so that i'm using where
tblfillrecord.fldproduct like [forms]![frm cert dialog box]![text0])
AND (tblfillrecord.fldlot)= '425' "
I get an error about Too Few Parameters. Expecting 1.

Any ideas where I've gone wrong?

Thanks,
Karen
Click to expand...
Click to expand...
 
It took some work but this is what the fix was. Thanks Bill.
Karen
Karen

"Too few parameters" deals with the query syntax. I should have caught that
in the first place. Take the textbox variables out of the SQL and use them
as variables.

WHERE(tblfillrecord.fldproduct Like '" & [forms]![frm cert dialog
box]![text0] & "')
AND(tblfillrecord.fldlot = IIf(Len('" & [Forms]![frm cert dialog
box]![text2] & "') =
8 Or (Len('" & [Forms]![frm cert dialog box]![text2] & "') = 7 And Mid('"
[Forms]![frm
cert dialog box]![text2] & "', 4, 1) <> 1), Left('" & [Forms]![frm cert
dialog
box]![text2] & "', 4), Left('" & [Forms]![frm cert dialog box]![text2] & "',
3)))

The whole idea is to use the control as a variable instead of putting it
directly into the query. You have to do it that way when using VBA. If the
control is a string it must be surrounded by single quotes.

--
Bill Mosca, MS Access MVP


Karen said:
I've cleaned up my Where statement as recomended.. I get an error
reading Too Few Parameters. Expected 2. I believe this is triggered by
the
Set rst = MyDB.OpenRecordSet(MySQL, db OpenForwardOnly) statement.

What would cause that?


I cleaned up your WHERE statement as best I could without testing. You do
not need Val() for numerics. and you do no need single quotes around the
1
in <>'1'.

Access tends to over-punctuate WHERE clauses which makes them a bear to
debug. I removed all the ones that appear extraneous.

WHERE(tblfillrecord.fldproduct Like [forms]![frm cert dialog
box]![text0])
AND(tblfillrecord.fldlot = IIf(Len([Forms]![frm cert dialog
box]![text2]) =
8 Or (Len([Forms]![frm cert dialog box]![text2]) = 7 And Mid([Forms]![frm
cert dialog box]![text2], 4, 1) <> 1), Left([Forms]![frm cert dialog
box]![text2], 4), Left([Forms]![frm cert dialog box]![text2], 3)))

--
Bill Mosca, MS Access MVP


I'm trying to create a record set and am running into errors...

I want this...

Dim rst As DAO.Recordset
Dim MyWksp As Workspace
Dim MyDB As DAO.Database
Dim MySQL As String, MyCriteria As String

Set MyWksp = DBEngine.Workspaces(0)
Set MyDB = MyWksp.Databases(0)


MySQL = "SELECT DISTINCT tblfillrecord.fldproduct,
tblfillrecord.fldlot, tblfillrecord.fldcode,
tblQualityRecord.fldexpiresdate, tblQualityRecord.fldmadedate FROM
tblfillrecord INNER JOIN tblQualityRecord ON (tblfillrecord.fldcode =
tblQualityRecord.fldcode) AND (tblfillrecord.fldlot =
tblQualityRecord.fldlotno) WHERE (((tblfillrecord.fldproduct) Like
[forms]![frm cert dialog box]![text0]) AND (tblfillrecord.fldlot)=
IIf(Len([forms]![frm cert dialog box]![text2])=Val(8)Or
(Len([forms]![frm cert dialog box]![text2])=Val(7) And Mid([forms]![frm
cert dialog box]![text2],4,1)<>'1'),Left([forms]![frm cert dialog
box]![text2],4), Left([forms]![frm cert dialog box]![text2],3)))"


Set rst = MyDB.OpenRecordset(MySQL, dbOpenForwardOnly)

but i get an error about a missing ), ], or ITEM in the expression (and
then the error shows the Where section of the above)

If I try to change this statement so that i'm using where
tblfillrecord.fldproduct like [forms]![frm cert dialog box]![text0])
AND (tblfillrecord.fldlot)= '425' "
I get an error about Too Few Parameters. Expecting 1.

Any ideas where I've gone wrong?

Thanks,
Karen
Click to expand...
Click to expand...
Click to expand...
 
Glad I could help.

--
Bill Mosca, MS Access MVP


Karen said:
It took some work but this is what the fix was. Thanks Bill.
Karen
Karen

"Too few parameters" deals with the query syntax. I should have caught
that
in the first place. Take the textbox variables out of the SQL and use
them
as variables.

WHERE(tblfillrecord.fldproduct Like '" & [forms]![frm cert dialog
box]![text0] & "')
AND(tblfillrecord.fldlot = IIf(Len('" & [Forms]![frm cert dialog
box]![text2] & "') =
8 Or (Len('" & [Forms]![frm cert dialog box]![text2] & "') = 7 And Mid('"
[Forms]![frm
cert dialog box]![text2] & "', 4, 1) <> 1), Left('" & [Forms]![frm cert
dialog
box]![text2] & "', 4), Left('" & [Forms]![frm cert dialog box]![text2] &
"',
3)))

The whole idea is to use the control as a variable instead of putting it
directly into the query. You have to do it that way when using VBA. If
the
control is a string it must be surrounded by single quotes.

--
Bill Mosca, MS Access MVP


Karen said:
I've cleaned up my Where statement as recomended.. I get an error
reading Too Few Parameters. Expected 2. I believe this is triggered by
the
Set rst = MyDB.OpenRecordSet(MySQL, db OpenForwardOnly) statement.

What would cause that?



Bill Mosca, MS Access MVP wrote:
I cleaned up your WHERE statement as best I could without testing. You
do
not need Val() for numerics. and you do no need single quotes around
the
1
in <>'1'.

Access tends to over-punctuate WHERE clauses which makes them a bear
to
debug. I removed all the ones that appear extraneous.

WHERE(tblfillrecord.fldproduct Like [forms]![frm cert dialog
box]![text0])
AND(tblfillrecord.fldlot = IIf(Len([Forms]![frm cert dialog
box]![text2]) =
8 Or (Len([Forms]![frm cert dialog box]![text2]) = 7 And
Mid([Forms]![frm
cert dialog box]![text2], 4, 1) <> 1), Left([Forms]![frm cert dialog
box]![text2], 4), Left([Forms]![frm cert dialog box]![text2], 3)))

--
Bill Mosca, MS Access MVP


I'm trying to create a record set and am running into errors...

I want this...

Dim rst As DAO.Recordset
Dim MyWksp As Workspace
Dim MyDB As DAO.Database
Dim MySQL As String, MyCriteria As String

Set MyWksp = DBEngine.Workspaces(0)
Set MyDB = MyWksp.Databases(0)


MySQL = "SELECT DISTINCT tblfillrecord.fldproduct,
tblfillrecord.fldlot, tblfillrecord.fldcode,
tblQualityRecord.fldexpiresdate, tblQualityRecord.fldmadedate FROM
tblfillrecord INNER JOIN tblQualityRecord ON (tblfillrecord.fldcode
=
tblQualityRecord.fldcode) AND (tblfillrecord.fldlot =
tblQualityRecord.fldlotno) WHERE (((tblfillrecord.fldproduct) Like
[forms]![frm cert dialog box]![text0]) AND (tblfillrecord.fldlot)=
IIf(Len([forms]![frm cert dialog box]![text2])=Val(8)Or
(Len([forms]![frm cert dialog box]![text2])=Val(7) And
Mid([forms]![frm
cert dialog box]![text2],4,1)<>'1'),Left([forms]![frm cert dialog
box]![text2],4), Left([forms]![frm cert dialog box]![text2],3)))"


Set rst = MyDB.OpenRecordset(MySQL, dbOpenForwardOnly)

but i get an error about a missing ), ], or ITEM in the expression
(and
then the error shows the Where section of the above)

If I try to change this statement so that i'm using where
tblfillrecord.fldproduct like [forms]![frm cert dialog
box]![text0])
AND (tblfillrecord.fldlot)= '425' "
I get an error about Too Few Parameters. Expecting 1.

Any ideas where I've gone wrong?

Thanks,
Karen
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

One-to-Many Query 3
Invalid use of property -- with vba openrecordset Please help 2
too few parameters 11
Reusable Form & Code 2
too few parameters error 4
showing results of a SQL statement in code 2
help with a loop 4
help with code on event call 3

Back
Top