run-time variable to supply value to parameter query

[WalksFar]

OK... newbie here.

I've figured out how to create and use a parameter query. I've seen how the paramaters can be tied to the values on a form, to replace the individual pop-ups with the single form... I'm having a hard time figuring out how to use run-time variable values from a VBA sub instead. Is there a way of supressing the parameter pop-ups from displaying, and to provide the needed values at run-time via VBA?

 

[mdmilner]

Yes, you can embed function calls within SQL statements and have it evaluate to a text result which is then evaluated as a SQL statement.

I'm going to be very lazy and just dump a live example. In this example, DatePuller is a Public Function call that returns a String Value.

Incidentally, the below is a simple reminder query - I have a bunch of tasks (report generation) that are managed from an Access MDB - and the below shows me a list based on predefined schedules of what is left at any given point in time.

SELECT tblTask.task_name, tblTask.dteStamp, tblTask.ScheduleType, DatePuller([dteStamp],[ScheduleType]) AS Pending
FROM tblTask
WHERE (((DatePuller([dteStamp],[ScheduleType]))>=DateSerial(Year(DatePuller([dteStamp],[ScheduleType])),1,1) And (DatePuller([dteStamp],[ScheduleType]))<=Date()) AND ((IsNull([dteStamp]))=False) AND ((IsNull([ScheduleType]))=False))
GROUP BY tblTask.task_name, tblTask.dteStamp, tblTask.ScheduleType, tblTask.disable
HAVING (((tblTask.ScheduleType)<>'None') AND ((tblTask.disable)=False))
ORDER BY tblTask.ScheduleType, tblTask.task_name, tblTask.dteStamp;

 

[WalksFar]

Quite cool. Thank you very much.