Quick Query on Queries!

[NigelShaw]

Hi,

im struggling on something i thought would be straight forward.

i have a query that needs to be filtered on a date. the open form has a date field that displays the date in the format dd/mm/yyyy

the form is open when the query runs and i set the criteria to

Forms!MainForm!TxtDateBox

when the query runs i get an error saying "Too few parameters, Expected 1"
when i add a parameter, i get a promt box to enter the date that is in the forms box.

The query is run by a recordset which is set up like

dim db As DAO.Database
dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = Db.OpenRecordset("MyQuery", dbOpenDynaset)

i get my error message then the code breaks and highlights the "set rs" line of the code.

why is it doing this? all i want is a simple filtered query......



hope someone can shed some light with a somewhat powerful torch



Nigel

 

[gemma-the-husky]

1. does "myquery" open normally, or does it need some field value setting

2. try it without the opendynaset - some of these things arent always compatible

 

[NigelShaw]

Hi G-T-H

The text value is to filter the record. If I remove the criteria, the query pulls all records but I need them filtered

I also removed the opendynaset but nothing changed.

I had a thought though. As this is a date filter, would a normal textbox suffice?do I have to construct the criteria with #'s?


Nigel

 

[ByteMyzer]

You can not use the OpenRecordset method for a query object in access with a Parameter Reference to a Form Control, even if the Form is loaded.


This scenario will not work:
MyQuery
-------
SELECT * FROM MyTable WHERE MyDate = Forms!MyForm!txtMyDate

Set rs = db.OpenRecordset("MyQuery")

I recommend using the SQL statement in your OpenRecordset clause:

Set rs = db.OpenRecordset("SELECT *" _
    & " FROM MyTable" _
    & " WHERE MyDate = #" & Forms("MyForm").txtMyDate & "#;")

 

[NigelShaw]

Hi

That's the annoying thing. I had this first but the recordset always come back empty!

I'll post the db when I get chance


Nigel

 

[NigelShaw]

Hi,

i have changed it to SQL and again i get the problem.

ive tried

Dim rs 'This gives me the error "Object Required"
Dim rs As Recordset "This gives me no Error"
Dim rs As DAO.Recordset "This gives me no Error"

when i use the SQL, my recordset remains empty. i know this as i have a msgbox to show the recordset count and i get nothing.

i placed a break on the msgbox line and the "Set rs As" line contains nothing.

VV frustrating.

can i get a query to have a criteria set on a function? if so, i could set the date in the function and let the query filter that way. would that work?


Nigel

 

[ByteMyzer]

NigelShaw,

What happened to "I'll post the db when I get chance"?

Until you do, any answers we could offer would be, at best, guesswork. Once we see the actual database, we have a better chance of providing an answer that fits your situation.

 

[NigelShaw]

hI,

Yeah sorry. posted on my iphone which unfortunately doesnt handle database..

Here it is. it is a calendar form downloaded from this site. it would be great if it works. i was intending to re-post it as a working sample but it is the SQL section with th recordset that is troublesome.

SQL isnt my thing at the minute. i have noted the issue routine with '## and it is on the frmCalendar_Daily

many thanks,


Nigel

 

[NigelShaw]

ooops!

obviously the esscence of posting the db is to actually attach it!


NS

 

[ByteMyzer]

I hate to break this to you, NigelShaw, but not everyone here has Access 2007, and not all of us can load ACCDB files. Would it be possible for you to convert it to an MDB file and attach it here?

 

[NigelShaw]

Sorry mate,

here it is 2003 version.

i found a snippet on the internet
http://msdn.microsoft.com/en-us/library/dd671279.aspx

this explains how to have a query read a vba module & form object by adding parameters. im going to look at this in the meantime as the only thing the example on the site doesnt do is have the vba setting up a recordset...


Nigel

 

[NigelShaw]

Well,

im a little further forward but much. i currently have it set up with a recordset again and a query that filters against a vba function.

i can get appointments to show on the calendar daily form but the same on every form except days ahead of today. im guessing its a filter issue


Nigel

 

[ByteMyzer]

In your form frmCalendar_Daily, in Sub DisplayDailyMeetings, change:

[COLOR="Navy"]Dim[/COLOR] rs [COLOR="navy"]As[/COLOR] Recordset

...to:

[COLOR="navy"]Dim[/COLOR] rs [COLOR="navy"]As[/COLOR] ADODB.Recordset

...and change:

[COLOR="navy"]Set[/COLOR] rs = CurrentDb.OpenRecordset(strSQL)

...to:

[COLOR="navy"]Set[/COLOR] rs = [COLOR="navy"]New[/COLOR] ADODB.Recordset
rs.Open strSQL, CurrentProject.Connection, adOpenKeyset

 

[WIS]

Hi,

im struggling on something i thought would be straight forward.

i have a query that needs to be filtered on a date. the open form has a date field that displays the date in the format dd/mm/yyyy

the form is open when the query runs and i set the criteria to

Forms!MainForm!TxtDateBox

when the query runs i get an error saying "Too few parameters, Expected 1"
when i add a parameter, i get a promt box to enter the date that is in the forms box.

The query is run by a recordset which is set up like

dim db As DAO.Database
dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = Db.OpenRecordset("MyQuery", dbOpenDynaset)

i get my error message then the code breaks and highlights the "set rs" line of the code.

why is it doing this? all i want is a simple filtered query......



hope someone can shed some light with a somewhat powerful torch



Nigel

Here's the fix - I put it on this site about 3-4 weeks ago for someone else:
Courtesy of Litwin, Getz et al

Dim db As Database, rst As Recordset, qdf As QueryDef, prm As Parameter
Set db = CurrentDb
Set qdf = db.QueryDefs("qrySomethingWithDateFilter")

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rst = qdf.OpenRecordset(dbOpenDynaset)

 

[NigelShaw]

Hi,

i managed to fix it before any of the above answers but if the above are more specific, then i'll try them. here was my fix-

Set recordset

    Set db = CurrentDb
    Set rst = db.OpenRecordset("qryDailyApps")

Query SQL

SELECT tblAppointments.*, tblHour.HourID
FROM tblAppointments INNER JOIN tblHour ON tblAppointments.ApptStartTime = tblHour.Hours
WHERE (((tblAppointments.ApptDate)=AddDate()));

Function to get date

Public Function AddDate()
AddDate = dtePubMyDate 'This is set when either of the forms open using openargs
End Function

for anyone else who is looking at this calendar, there is a type on the large form "Set Details" routine where the variable dtePubMyDate is set but called as dtePubDate

i'll post the calendar when ive done it as it works great for me except the opening of existing records. just figuring that one out now



Nigel