Recent content by RonPaii

Put a label behind each sub-form with the message you want when the sub-form is empty. If the sub-form is empty, set it's Visible property to False.

I think you are looking for some hardware magic bullet that will run software fast no matter how it's written. There is no such thing! For example, an earlier post with sample code to calculate perfect numbers for 2 to 10000 ran in a 5 seconds on a 4 year old i7 with 32 GB memory and P2 drive...

The second opening of query did not wipe out the parameters, it is completely independent of the first. The only way to set parameters of a query opened with docmd is form variables or temp variables.

Your function could be as simple as this. Dim strQueryName As String TempVars![parExamFreq] = "Weekly" TempVars![parExamPeriod] = Date strQueryName = "qry_Par_Start_Date_In_Range" e with CurrentDb with .QueryDefs(strQueryName) with .OpenRecordset(dbOpenDynaset)...

The easiest is with temp vars per my example in post #15. You will always need to pre-fill the temp vars before opening a query referencing a temp var.

This should work using temp vars. Dim db As DAO.Database Dim qdf As DAO.QueryDef Dim strQueryName As String Dim rs As DAO.RecordSet Dim prm As DAO.Parameter Dim sFreq As String Dim dDate As Date sFreq = "Weekly" dDate = Date TempVars![parExamFreq] = sFreq...

Those are 2 independent instances of the same query; setting parameters in 1 instance has no affect on another.

A 2nd look at your function, you opened the query 2 times, the 2nd time without providing values for the parameters. 1st time as a record set with the parameters pre-filled. Set db = CurrentDb Set qdf = db.QueryDefs(strQueryName) qdf.Parameters(sPar1).Value = sFreq...

Ken said "Another problem is that the IN operator does not accept a parameter as its argument." Instead of a function, wouldn't it be great if Microsoft allow us to pass IN as a parameter.

So did adding the defined parameters help? Adding line breaks makes it much easer to see what you are doing. PARAMETERS [parExamFreq] Text ( 255 ), [parExamPeriod] Text ( 255 ); SELECT tblEquipment.EquipmentID FROM tblEquipment WHERE ((([parExamFreq])="Weekly") AND...

x64 will give you a large memory space. P2, one of the single board style drive that connects directly to the motherboard as opposed to an SSD that plugs in like a HHD. Core speed would be more important than count with Access.

so you are saying ([parExamFreq]) = "Weekly") will not exist in your final query? I would think something like ([tblEquipment].[ExamFreq] =[parExamFreq]) would be more likely.

Try defining the parameters at the top of your query. PARAMETERS [parExamFreq] Text ( 255 ), [parExamPeriod] Text ( 255 ) SELECT tblEquipment.EquipmentID, ( DatePart("yyyy", [tblEquipment].[StartOfServiceDate]) * 1000 ) + DatePart("ww", [tblEquipment].[StartOfServiceDate])...

Leave out the brackets in the parameter name string. sPar1 = "parExamFreq" sPar2 = "parExamPeriod"

You can use a sub-select to git rid of the district SELECT tblCompanies.CompanyName FROM tblCompanies WHERE tblCompanies.CompanyID In (SELECT JobTypeID.CompanyID FROM JobTypeID WHERE JobTypeID.JobTypeName IN ("Plumbing","HVAC"));