runtime error '3061'

[Never Hide]

Hi guys,

I'm trying to open a recordset in vba and I'm getting the 3061 runtime error,Expected 1.
After some searching I found out that the problem is that I'm trying to open a recordset with a query that uses a referance to a form control.

Could someone please explain to me how I should go about it to correct that problem?

 searchtable1 = "qInVisio_RSV"
Set rs = db.OpenRecordset(searchtable1, dbOpenDynaset, dbSeeChanges)

This is the sql of the query:
The highlighted parted is the form referance ( I know it's obvious, just for easier spotting )

SELECT dbo_FOLIO.FOLIOID, dbo_FOLIO.KIND, dbo_FOLIO.RSVID, dbo_FOLIO.CHKIDATE, dbo_RLIST.ROOMID, dbo_ROOM.ROOMNO, dbo_AGN.LINAPRG
FROM (dbo_ROOM INNER JOIN (dbo_FOLIO INNER JOIN dbo_RLIST ON dbo_FOLIO.RSVID = dbo_RLIST.RSVID) ON dbo_ROOM.ROOMID = dbo_RLIST.ROOMID) LEFT JOIN dbo_AGN ON dbo_FOLIO.AGNID = dbo_AGN.AGNID
WHERE (((dbo_FOLIO.KIND)=101) AND ((dbo_FOLIO.CHKIDATE)>=[COLOR="Red"][Forms]![frmCleaningPlan]![DTPicker][/COLOR]));

also as you can see it's a datepicker control, so the value is a date

 

[pr2-eugin]

If you are trying to reference Form contol in your query, you have to enclose the Form values inside " so.. Try the following..

SELECT dbo_FOLIO.FOLIOID, dbo_FOLIO.KIND, dbo_FOLIO.RSVID, dbo_FOLIO.CHKIDATE, dbo_RLIST.ROOMID, dbo_ROOM.ROOMNO, dbo_AGN.LINAPRG
FROM (dbo_ROOM INNER JOIN (dbo_FOLIO INNER JOIN dbo_RLIST ON dbo_FOLIO.RSVID = dbo_RLIST.RSVID) ON dbo_ROOM.ROOMID = dbo_RLIST.ROOMID) LEFT JOIN dbo_AGN ON dbo_FOLIO.AGNID = dbo_AGN.AGNID
WHERE (((dbo_FOLIO.KIND)=101) AND ((dbo_FOLIO.CHKIDATE)>=[COLOR=Red][B]#" &[/B] [COLOR=black][Forms]![frmCleaningPlan]![DTPicker][/COLOR] [B]& "#[/B][/COLOR]));

I believe the DTPicker is a Date field so I have enclosed inside ## if they are strings replace the # with ' (single quotes), If it is just a number then do not have any special characters.

 

[Never Hide]

Hello pr2, thank you very mych for your reply,

I used what you suggested in the sql source of the query, but I'm a new error now, runtime error 3075
saying

Syntax error at date in query expretion 
(((dbo_FOLIO.KIND)=101) AND ((dbo_FOLIO.CHKIDATE)>=#" & [Forms]![frmCleaningPlan]![DTPicker] & "#));

this is the WHERE clause

P.S. The description of the error might not be the exact words cause I had to translate them cause in the company I work we unfortunatelly have license the Greek version of Office