Change Query Parameters On The Fly

[Earl C Phillips]

I am a volunteer programmer for the number one food bank in the USA and we want to track of the number of people and the hours of work contributed by volunteers toward feeding the hungry.

I have some a query that accepts a beginning and ending date for the reservation records requested. It is an update query, so the dates are crucial. I want to be able to check that the ending date is no greater than today. (The field being updated is the number of people who actually show up and the total hours they contributed. We cannot know how many will show up until the actual date is done). There are edits in the reservations code prohibiting entry of the actual number of volunteers before today's date.

The actual VBA code is:
DoCmd.OpenQuery "qryDBCleanup", acViewNormal, acReadOnly
stDocName = "rptHoursMonthly"
DoCmd.OpenReport stDocName, acViewPreview, , , acWindowNormal

The SQL code for the query is:
PARAMETERS [Enter Start Date:] DateTime, [Enter Todays Date:] DateTime;
UPDATE tblMain INNER JOIN tblActivity ON tblMain.ID = tblActivity.ID SET tblActivity.FinalNumber = IIf([FinalNumber]=0,[NumberScheduled],[FinalNumber]), tblActivity.Adults = [NumberScheduled]-[Children], tblActivity.FinalHours = (DateDiff("s",[ArrivalTime],[DepartureTime])*[FinalNumber]/(60*60)), tblActivity.Activity = IIf([Activity] Like "*-H*","Cancelled-H",IIf([Activity] Like "Canc*" Or [Activity] Like "Sick","Cancelled",IIf([Activity] Like "No *" Or [Activity] Like "NC*","NCNS",[Activity])))
WHERE (((tblActivity.ScheduleDate) Between [Enter Start Date:] And [Enter Todays Date:]));

How can I intercept the parameter for the ending date and check that it is today or less?

Earl C Phillips
ex mainframer

 

[Kiwiman]

Howzit

Try making the user select the dates from the form (have a coupe of text boxes on your form to hold the dates) you are running the query from, and then link query parameters to the form.

You can then check to see if the dates fit within your requirements

Your code will look something like

if me.yourtodate <= Date then
    DoCmd.OpenQuery "qryDBCleanup", acViewNormal, acReadOnly
    stDocName = "rptHoursMonthly"
    DoCmd.OpenReport stDocName, acViewPreview, , , acWindowNormal 
else 
    msgbox "To Date must be before today", vbcritical, "Wrong date"
end if

and your sql to for your query will look something like

UPDATE tblMain INNER JOIN tblActivity ON tblMain.ID = tblActivity.ID SET tblActivity.FinalNumber = IIf([FinalNumber]=0,[NumberScheduled],[FinalNumber]), tblActivity.Adults = [NumberScheduled]-[Children], tblActivity.FinalHours = (DateDiff("s",[ArrivalTime],[DepartureTime])*[FinalNumber]/(60*60)), tblActivity.Activity = IIf([Activity] Like "*-H*","Cancelled-H",IIf([Activity] Like "Canc*" Or [Activity] Like "Sick","Cancelled",IIf([Activity] Like "No *" Or [Activity] Like "NC*","NCNS",[Activity])))
WHERE (((tblActivity.ScheduleDate) Between [Forms]![yourform]![yourfromdate] And [Forms]![yourform]![yourtodate]));

 

[Earl C Phillips]

I use the suggested method in another application and it works correctly. I was hoping to learn another method that might apply to other applications.

Thanks for the suggestion.