Data Input to Queries

[Luvflt]

I have a data base file (.adp) that has a report which is printed from a query. It is basically an attendance sheet which gets the names of the students from a student table. I need to input certain information into the report that does not come from any table or query. Example: Each attendance sheet is for a specific week so I would like to enter the week at the time I go to print these sheets so the week number (i.e. 4) shows up on the report next to the label "WEEK". The existing query already uses as a criteria = @class_no and = @class_year so the user can be asked to input the class number and class year and the query finds this information in the student table then prints it out in the report. I just want to add something so the user can input the WEEK in the same fashion. I also need to add the date for each day of that specific week. (i.e. if week 4 is 8/3/15 to 8/7/15 I need the report to show Mon (8/3/15), Tue (8/4/15), Wed (8/5/15) etc.)



Playing around with this I was able to get the dates where I wanted them by using the date function and the dateadd function. The only problem is it used the date function uses todays date and I need to make sure the first date is the date of that specific week. I was looking at some way to again have the user enter the week start date as well then use the dateadd for the rest of the week.

I have played around with this for some time and it seems I can't enter this info without it being in a table. Is there a way just to queue the user to enter the week and start date and treat them as variables printing out the variables later in the report?

As always any help or idea is appreciated. I am a novice so I would hope this is a simple matter.

[pbaldy]

If you have them enter the value on a form, you can refer to that form from the report, like:

="Week " & Forms!FormName.TextboxName

[Luvflt]

I am not sure I understand. The info comes from the query and goes into the report. The user does not enter the value on a form. The input request comes from the query that is why I was hoping there was some way to get the info into the query without it being in a table. Are you telling me the only way to do that is to have them input it from a form? Thank you but I am still trying to understand.

[pbaldy]

I don't use adp's (though I use SQL Server extensively). How do they enter the current parameters? If they are just prompts, you can probably add another and just return it in the SQL.

SELECT OtherFields, @NewPrompt
FROM...

[June7]

Users should not work directly with tables and queries.

I NEVER use dynamic parameterized queries. I prefer to use VBA to build filter criteria. Users enter/select criteria in UNBOUND controls. Review: http://www.allenbrowne.com/ser-62.html

Then as Paul shows, textbox on report can reference form control(s) or maybe just put textbox(es) in report header bound to field(s) used for filtering.