problem with query in vba

  • Thread starter Thread starter tw
  • Start date Start date
T

tw

Access 2002

I have this saved query as qryWeeklyVisits1, and it works fine it takes the
date for the where clause between statement from the form and runs code to
determine Sunday and Saturday dates around the date in that form.

SELECT DISTINCT [LCM Acutal Visits].[LCMV-FK Cert Period], [LCM Acutal
Visits].[LCMV-FK Type of Visit], [LCM Acutal Visits].[LCMV DOS]
FROM [LCM Acutal Visits]
WHERE ((([LCM Acutal Visits].[LCMV DOS]) Between
beginningofweek([forms]![frmqryonedateparameter].[txtdate].[value]) And
endofweek([forms]![frmqryonedateparameter].[txtdate].[value])));


and this saved query as qryWeeklyVisits2, and it works fine

SELECT DISTINCT WorkSpaceDoctorsOrders.[DO-FK Cert Period id],
WorkSpaceDoctorsOrders.[DO-FK Type of Visit], 0 AS Expr1
FROM WorkSpaceDoctorsOrders;


and this saved query as qryWeeklyVisitsUnion, and it works fine

Select *
From [qryWeeklyVisits1]
UNION Select *
From [qryWeeklyVisits2];


all three of these queries work as I want them to work when I run them from
the query window

here is my problem...
I have this code in vba on the sub txtDate_AfterUpdate event


<snip>
strSqlP = "select * from qryWeeklyVisitsUnion Order by [lcmv-fk cert period]
& [LCMV-FK type of visit]"
Set rsp = CurrentDb.OpenRecordset(strSqlP)


I'm getting an error message "Too few parameters: Expected 1"
I'm not sure if the parameter it is expecting is
form!frmqryOneDateParameter.txtDate, but the form is still there with a
date. It's from that field's AfterUpdate event that this code is running.
Can anyone see what may be wrong?
 
I changed all the field names in qryWeekly1 and qryWeekly two to cert, tov,
dos, and added ini because it was needed. q1 and q2 still work fine my
saved query union still works fine but I'm still having problems with the
query in code. I changed the orinal select statement referencing my saved
union query to just create the union query in code and now I'm getting a
different error.

I changed the strsqlp to this

strsqlp = "select [cert], [tov], [dos], [ini] from [qryWeeklyVisits1] "
strsqlp = strsqlp & "UNION Select [cert], [tov], [dos], [ini] from
[qryWeeklyVisits2] "
strsqlp = strsqlp & "Order by [cert] & [tov]"

now I'm getting order by expression (cert & tov) includes fields that are
not selected by the query

Please help me solve this problem
Thanks for all your help
 
I fixed the order by clause below to [cert], [tov] and now I'm back to the
original error which is too few parameters: expected parameters

tw said:
I changed all the field names in qryWeekly1 and qryWeekly two to cert, tov,
dos, and added ini because it was needed. q1 and q2 still work fine my
saved query union still works fine but I'm still having problems with the
query in code. I changed the orinal select statement referencing my saved
union query to just create the union query in code and now I'm getting a
different error.

I changed the strsqlp to this

strsqlp = "select [cert], [tov], [dos], [ini] from [qryWeeklyVisits1] "
strsqlp = strsqlp & "UNION Select [cert], [tov], [dos], [ini] from
[qryWeeklyVisits2] "
strsqlp = strsqlp & "Order by [cert] & [tov]"

now I'm getting order by expression (cert & tov) includes fields that are
not selected by the query

Please help me solve this problem
Thanks for all your help

tw said:
Access 2002

I have this saved query as qryWeeklyVisits1, and it works fine it takes
the date for the where clause between statement from the form and runs
code to determine Sunday and Saturday dates around the date in that form.

SELECT DISTINCT [LCM Acutal Visits].[LCMV-FK Cert Period], [LCM Acutal
Visits].[LCMV-FK Type of Visit], [LCM Acutal Visits].[LCMV DOS]
FROM [LCM Acutal Visits]
WHERE ((([LCM Acutal Visits].[LCMV DOS]) Between
beginningofweek([forms]![frmqryonedateparameter].[txtdate].[value]) And
endofweek([forms]![frmqryonedateparameter].[txtdate].[value])));


and this saved query as qryWeeklyVisits2, and it works fine

SELECT DISTINCT WorkSpaceDoctorsOrders.[DO-FK Cert Period id],
WorkSpaceDoctorsOrders.[DO-FK Type of Visit], 0 AS Expr1
FROM WorkSpaceDoctorsOrders;


and this saved query as qryWeeklyVisitsUnion, and it works fine

Select *
From [qryWeeklyVisits1]
UNION Select *
From [qryWeeklyVisits2];


all three of these queries work as I want them to work when I run them
from the query window

here is my problem...
I have this code in vba on the sub txtDate_AfterUpdate event


<snip>
strSqlP = "select * from qryWeeklyVisitsUnion Order by [lcmv-fk cert
period] & [LCMV-FK type of visit]"
Set rsp = CurrentDb.OpenRecordset(strSqlP)


I'm getting an error message "Too few parameters: Expected 1"
I'm not sure if the parameter it is expecting is
form!frmqryOneDateParameter.txtDate, but the form is still there with a
date. It's from that field's AfterUpdate event that this code is
running. Can anyone see what may be wrong?
Click to expand...
Click to expand...
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Not sure. But the reference to the form control doesn't require the
..Value property (that's the default). And, MS recommends using
exclamation points instead of periods between form name and control
name.

[forms]![frmqryonedateparameter]![txtdate]

You could also put a PARAMETERS clause on the query to truly define the
data type of the referenced control's value.

PARAMETERS [forms]![frmqryonedateparameter]![txtdate] Date;
SELECT .... etc.

You might also want to debug the functions while the query is running,
use a breakpoint in each of the functions, to see if the error is in the
functions.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQk2mLIechKqOuFEgEQKK3gCfW+kQ+zJwuZQaoOxAfF2slOV3BjkAoPyW
kdfoIMLKfQIHMQ1cd+MJQxks
=GZzD
-----END PGP SIGNATURE-----
 
I changed [forms]![frmqryonedateparameter].[txtdate].[value] to this
[forms]![frmqryonedateparameter]![txtdate]

I added the parameter statement to the query string in code to get this

strSQLp = "PARAMETERS [forms]![frmqryonedateparameter]![txtDate] Date; "
strSQLp = strSQLp & "Select * From [qryWeeklyVisits1] UNION Select * From
[qryWeeklyVisits2] "
strSQLp = strSQLp & "Order by cert, tov"

I put a break point in both functions. The functions ran without error when
I run the queries from the query window. When I run from code the error
occurrs before the functions are called. I still get the error too few
parameters: expected 1


MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Not sure. But the reference to the form control doesn't require the
.Value property (that's the default). And, MS recommends using
exclamation points instead of periods between form name and control
name.

[forms]![frmqryonedateparameter]![txtdate]

You could also put a PARAMETERS clause on the query to truly define the
data type of the referenced control's value.

PARAMETERS [forms]![frmqryonedateparameter]![txtdate] Date;
SELECT .... etc.

You might also want to debug the functions while the query is running,
use a breakpoint in each of the functions, to see if the error is in the
functions.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQk2mLIechKqOuFEgEQKK3gCfW+kQ+zJwuZQaoOxAfF2slOV3BjkAoPyW
kdfoIMLKfQIHMQ1cd+MJQxks
=GZzD
-----END PGP SIGNATURE-----

Access 2002

I have this saved query as qryWeeklyVisits1, and it works fine it takes
the date for the where clause between statement from the form and runs
code to determine Sunday and Saturday dates around the date in that form.

SELECT DISTINCT [LCM Acutal Visits].[LCMV-FK Cert Period], [LCM Acutal
Visits].[LCMV-FK Type of Visit], [LCM Acutal Visits].[LCMV DOS]
FROM [LCM Acutal Visits]
WHERE ((([LCM Acutal Visits].[LCMV DOS]) Between
beginningofweek([forms]![frmqryonedateparameter].[txtdate].[value]) And
endofweek([forms]![frmqryonedateparameter].[txtdate].[value])));


and this saved query as qryWeeklyVisits2, and it works fine

SELECT DISTINCT WorkSpaceDoctorsOrders.[DO-FK Cert Period id],
WorkSpaceDoctorsOrders.[DO-FK Type of Visit], 0 AS Expr1
FROM WorkSpaceDoctorsOrders;


and this saved query as qryWeeklyVisitsUnion, and it works fine

Select *
From [qryWeeklyVisits1]
UNION Select *
From [qryWeeklyVisits2];


all three of these queries work as I want them to work when I run them
from the query window

here is my problem...
I have this code in vba on the sub txtDate_AfterUpdate event


<snip>
strSqlP = "select * from qryWeeklyVisitsUnion Order by [lcmv-fk cert
period] & [LCMV-FK type of visit]"
Set rsp = CurrentDb.OpenRecordset(strSqlP)


I'm getting an error message "Too few parameters: Expected 1"
I'm not sure if the parameter it is expecting is
form!frmqryOneDateParameter.txtDate, but the form is still there with a
date. It's from that field's AfterUpdate event that this code is
running. Can anyone see what may be wrong?
Click to expand...
Click to expand...
 
Back
Top