Another OpenRecordSet problem

[Confused Pete]

I'm trying to get a Gantt chart like report to generate from a query. So far I've used the code and help from these forums to show my report OK.

Now, I want to improve my query, so that it selects results. I can do this in the query with a parameter.

Unfortunatley, the gannt chart code uses an OpenRecordSet statement. Is it possible to modify the code to accept a query parameter? (I've tried to follow the suggestions given in these forums and it doesn't seem to work - for me!)

The code in question is:

Set rs = db.OpenRecordset("SELECT Min([Start Date]) AS MinOfStartDate " _
& " FROM qryPM1AssetPlannedLife", dbOpenSnapshot)

Has anyone got any suggestions as to how to try and resolve this issue?
Is it feasible?

Thanks!

 

[Confused Pete]

I've used a work around to resolve.

Made a MakeTable Query to pull the relevant records and then used this table as the basis of the Gantt chart.

It works, but is not as elegant as doing it all through the one Query / Code.

 

[jgc31]

Pete

It is standard practice to have a recordset based on a query.

Typically

strSQL= "SELECT * FROM some_table WHERE field=something"

set rst=db.openrecordset(strSQL)

suggest you search the forum where there will be lots of examples or check out any Access VBA textbook

 

[modest]

You dont need a query for min. Just use the DMin() function- DMin(expr, domain, [criteria])



Example:

Dim varMin As Variant

varMin = DMin("[Freight]", "Orders", "[ShipCountry] = 'UK'")

[Freight] ==> Field you want to find the minimum value of
Orders ==> Table or query the field is coming from
[ShipCountry] = 'UK' ==> Much like the where clause of a query w/o the WHERE, used to restrict the results