Put Parameter Results in Query Heading

[cindyredo]

Hi.

I have a query that pulls multiple items, of course.

Something like this:

Name, IDNumber, Date, HoursWorked

I have a parameter set on the date field so user can enter the date they want to run hours for. However, rather than have a heading of HoursWorked in the query results, I want the date entered by the user as its heading.

Is this possible.

Thanks much!!

 

[WayneRyan]

Cindy,

If you want a form/report to display its selection criteria, then
you will have to store them (it?) on something like a dialog
box or other calling form. Just have the form open, launch
a report that uses the query and you'll be OK.

hth,
Wayne

 

[cindyredo]

Thank you for the quick response.

I am not putting this data into a form or report -- it is in a query that I am exporting to Excel.

Think it can be done within a query???

 

[WayneRyan]

Cindy,

What your SQL does is select some fields from a
table. The fields can have "other" names like
the "Field1 As Total". From now on that field
will be total.

When it comes to the [Enter Some Date] it knows
that it isn't a field so it prompts and whatever
the user types in is substituted. This is just
a text substitution. What they type in doesn't
make a new field, and can't have a name. It's
just part of the criteria.

Select Field1 As Total,
       SomeOtherField,
       LastField
From   SomeTable
Where  DateField > [Enter Some Date];

It's very bad practice, but the only way that
this could work is to have them enter it twice,
first for field contents and then for the criteria.
I WOULD NOT suggest doing it this way.

Select Field1 As Total,
       SomeOtherField,
       LastField,
       [Hey this is the criteria!] <-- I've never tried this
From   SomeTable
Where  DateField > [Enter Some Date];

You'd be better off just running the whole event
from a nice little dialog box.

Wayne

 

[dcx693]

If you want a form/report to display its selection criteria, then
you will have to store them (it?) on something like a dialog
box or other calling form.

Actually, if you're using a parameter query, which I think the user is, it's far easier than you'd think. The technique is described here: Display Criteria from Query.

Problem is, the user wants to export directly from the query, not from a report. There is no easy way to put the heading at the top of a query, as you've already pointed out. My suggestion would be to export from the report. Don't include any grouping or summarizing in the report, and use the technique I mention in the link to place the query parameter(s) in the report header.

 

[R6Flyer]

Something like the SQL below will give you and additional field in your query containing the parameter.

SELECT tblABSENCES.AbsenceID, tblABSENCES.AbsenceStart,
tblABSENCES.AbsenceEnd, tblABSENCES.AbsentDays,
tblABSENCES.AbsenceReason,
[Enter Start date] AS StartDate
^^^^^^^^^^^^^^^^^^^^
FROM tblABSENCES
WHERE (((tblABSENCES.AbsenceStart) Like [Enter Start date]));

When you export the query to Excel you will have an additional column containing the user entered parameter.

Is this the sort of thing that you are looking for?

Cheers

Flyer