help with code - DAO Recordset

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
 
B

Bill Mosca, MS Access MVP

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)))
 
K

Karen

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...
 
B

Bill Mosca, MS Access MVP

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...
 
S

SIMON HOLLAND

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).
 
A

aaron.kempf

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...
 
K

Karen

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...
 
B

Bill Mosca, MS Access MVP

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
showing results of a SQL statement in code 2
help with code on event call 3
help with a loop 4
Need Help | Rolling out new App. | Trouble with some computers. 10
Further help with Email code please - Doug Steele, David Cox? 2
Problem with Select Case 2

Top