Report Header

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report that references a parameter query as it's record source.
When you click on the report, the parameter query dialog box opens and asks
the user to enter the dept. number. The query works great, it populates the
report accordingly. What info do I set as the header so the department name
poplulates the report accordingly?

Thank you, Karen
 
I guess the DeptNo uniquely determines the DeptName?

If it does, you can create a calculated TextBox in the Report Header using
the DLookUp() function in the ControlSource to get the DeptName, e.g. set
the ControlSource to something like:

= DLookUp("DeptName", "tblDept", "[DeptNo] = " & [DeptNoParam])

including the equal sign where [DeptNoParam] is the name of the Parameter in
the Query.
 
Thank you for your help - I adjusted the titles accordingly and this is what
I have.

=DLookUp("DepartmentName","tblDepartment","[DeptID] = " & [DeptNoParam])

Since this report references a parameter query as it's record source, the
department that I enter in the intial parameter query dialog box (the query
the report is based on) is the one the report recognizes. What would be the
exact syntax for the header to elimiante the second parameter dialog box?
I thought it would be:
=DLookUp("DepartmentName","tblDepartment","[DeptID])
but this is not working.

Thank you, Karen




Van T. Dinh said:
I guess the DeptNo uniquely determines the DeptName?

If it does, you can create a calculated TextBox in the Report Header using
the DLookUp() function in the ControlSource to get the DeptName, e.g. set
the ControlSource to something like:

= DLookUp("DeptName", "tblDept", "[DeptNo] = " & [DeptNoParam])

including the equal sign where [DeptNoParam] is the name of the Parameter in
the Query.

--
HTH
Van T. Dinh
MVP (Access)



Karen said:
I have a report that references a parameter query as it's record source.
When you click on the report, the parameter query dialog box opens and
asks
the user to enter the dept. number. The query works great, it populates
the
report accordingly. What info do I set as the header so the department
name
poplulates the report accordingly?

Thank you, Karen
Click to expand...
Click to expand...
 
In the 3rd argument (criteria) of the DLookUp I posted:

"[DeptNo] = " & [DeptNoParam]

[DeptNo] is the Field name in the Table.

[DeptNoParam] is the name of the Parameter in the Query.

If both names are "DeptID", then change the criteria argument to:

"[DeptID] = " & [DeptID]

Access will recognise the first [DeptID] is supposed to be from the Table,
i.e. Field name and the second [DeptID] is a value external from the Table /
Domain "tblDepartment".

--
HTH
Van T. Dinh
MVP (Access)



Karen said:
Thank you for your help - I adjusted the titles accordingly and this is
what
I have.

=DLookUp("DepartmentName","tblDepartment","[DeptID] = " & [DeptNoParam])

Since this report references a parameter query as it's record source, the
department that I enter in the intial parameter query dialog box (the
query
the report is based on) is the one the report recognizes. What would be
the
exact syntax for the header to elimiante the second parameter dialog box?
I thought it would be:
=DLookUp("DepartmentName","tblDepartment","[DeptID])
but this is not working.

Thank you, Karen




Van T. Dinh said:
I guess the DeptNo uniquely determines the DeptName?

If it does, you can create a calculated TextBox in the Report Header
using
the DLookUp() function in the ControlSource to get the DeptName, e.g. set
the ControlSource to something like:

= DLookUp("DeptName", "tblDept", "[DeptNo] = " & [DeptNoParam])

including the equal sign where [DeptNoParam] is the name of the Parameter
in
the Query.

--
HTH
Van T. Dinh
MVP (Access)



Karen said:
I have a report that references a parameter query as it's record source.
When you click on the report, the parameter query dialog box opens and
asks
the user to enter the dept. number. The query works great, it
populates
the
report accordingly. What info do I set as the header so the department
name
poplulates the report accordingly?

Thank you, Karen
Click to expand...
Click to expand...
Click to expand...
 
I changed the criteria argument to:
=DLookUp("DepartmentName","tblDepartment","[DeptID] = " & [DeptID])

Now it asks me for the department no. twice.
What am I doing wrong?

Thank you, Karen




Van T. Dinh said:
In the 3rd argument (criteria) of the DLookUp I posted:

"[DeptNo] = " & [DeptNoParam]

[DeptNo] is the Field name in the Table.

[DeptNoParam] is the name of the Parameter in the Query.

If both names are "DeptID", then change the criteria argument to:

"[DeptID] = " & [DeptID]

Access will recognise the first [DeptID] is supposed to be from the Table,
i.e. Field name and the second [DeptID] is a value external from the Table /
Domain "tblDepartment".

--
HTH
Van T. Dinh
MVP (Access)



Karen said:
Thank you for your help - I adjusted the titles accordingly and this is
what
I have.

=DLookUp("DepartmentName","tblDepartment","[DeptID] = " & [DeptNoParam])

Since this report references a parameter query as it's record source, the
department that I enter in the intial parameter query dialog box (the
query
the report is based on) is the one the report recognizes. What would be
the
exact syntax for the header to elimiante the second parameter dialog box?
I thought it would be:
=DLookUp("DepartmentName","tblDepartment","[DeptID])
but this is not working.

Thank you, Karen




Van T. Dinh said:
I guess the DeptNo uniquely determines the DeptName?

If it does, you can create a calculated TextBox in the Report Header
using
the DLookUp() function in the ControlSource to get the DeptName, e.g. set
the ControlSource to something like:

= DLookUp("DeptName", "tblDept", "[DeptNo] = " & [DeptNoParam])

including the equal sign where [DeptNoParam] is the name of the Parameter
in
the Query.

--
HTH
Van T. Dinh
MVP (Access)



I have a report that references a parameter query as it's record source.
When you click on the report, the parameter query dialog box opens and
asks
the user to enter the dept. number. The query works great, it
populates
the
report accordingly. What info do I set as the header so the department
name
poplulates the report accordingly?

Thank you, Karen
Click to expand...
Click to expand...
Click to expand...
 
* Just confirming: The name of the Parameter in the RecordSource Query /
SQL String is [DeptID]?

* Try it this way: pick a [DeptID] value you want to test, says, 1. Change
the DLookUp use this explicit value, i.e.:

=DLookUp("DepartmentName","tblDepartment","[DeptID] = 1")

and try tp open the Report in the Preview mode. Does Access ask you doe the
[DeptID] once or twice? Please note the actual Parameter name(s) as appear
on the Parameter dialog. Please post the result back here.


--
HTH
Van T. Dinh
MVP (Access)



Karen said:
I changed the criteria argument to:
=DLookUp("DepartmentName","tblDepartment","[DeptID] = " & [DeptID])

Now it asks me for the department no. twice.
What am I doing wrong?

Thank you, Karen




Van T. Dinh said:
In the 3rd argument (criteria) of the DLookUp I posted:

"[DeptNo] = " & [DeptNoParam]

[DeptNo] is the Field name in the Table.

[DeptNoParam] is the name of the Parameter in the Query.

If both names are "DeptID", then change the criteria argument to:

"[DeptID] = " & [DeptID]

Access will recognise the first [DeptID] is supposed to be from the
Table,
i.e. Field name and the second [DeptID] is a value external from the
Table /
Domain "tblDepartment".

--
HTH
Van T. Dinh
MVP (Access)



Karen said:
Thank you for your help - I adjusted the titles accordingly and this is
what
I have.

=DLookUp("DepartmentName","tblDepartment","[DeptID] = " &
[DeptNoParam])

Since this report references a parameter query as it's record source,
the
department that I enter in the intial parameter query dialog box (the
query
the report is based on) is the one the report recognizes. What would
be
the
exact syntax for the header to elimiante the second parameter dialog
box?
I thought it would be:
=DLookUp("DepartmentName","tblDepartment","[DeptID])
but this is not working.

Thank you, Karen




:

I guess the DeptNo uniquely determines the DeptName?

If it does, you can create a calculated TextBox in the Report Header
using
the DLookUp() function in the ControlSource to get the DeptName, e.g.
set
the ControlSource to something like:

= DLookUp("DeptName", "tblDept", "[DeptNo] = " & [DeptNoParam])

including the equal sign where [DeptNoParam] is the name of the
Parameter
in
the Query.

--
HTH
Van T. Dinh
MVP (Access)



I have a report that references a parameter query as it's record
source.
When you click on the report, the parameter query dialog box opens
and
asks
the user to enter the dept. number. The query works great, it
populates
the
report accordingly. What info do I set as the header so the
department
name
poplulates the report accordingly?

Thank you, Karen
Click to expand...
Click to expand...
Click to expand...
 
Thank you for your help - The dialog box appears once now - Although when I
type the Department ID into the dialog box, the department name doesn't
appear. When you say, "Please note the actual Parameter name(s) as appear on
the Parameter dialog." What do you mean by that? Do you mean - what
criteria was entered into the query criteria field? If it's that, I entered
[Please Enter Department Number]
Thank you

Van T. Dinh said:
* Just confirming: The name of the Parameter in the RecordSource Query /
SQL String is [DeptID]?

* Try it this way: pick a [DeptID] value you want to test, says, 1. Change
the DLookUp use this explicit value, i.e.:

=DLookUp("DepartmentName","tblDepartment","[DeptID] = 1")

and try tp open the Report in the Preview mode. Does Access ask you doe the
[DeptID] once or twice? Please note the actual Parameter name(s) as appear
on the Parameter dialog. Please post the result back here.


--
HTH
Van T. Dinh
MVP (Access)



Karen said:
I changed the criteria argument to:
=DLookUp("DepartmentName","tblDepartment","[DeptID] = " & [DeptID])

Now it asks me for the department no. twice.
What am I doing wrong?

Thank you, Karen




Van T. Dinh said:
In the 3rd argument (criteria) of the DLookUp I posted:

"[DeptNo] = " & [DeptNoParam]

[DeptNo] is the Field name in the Table.

[DeptNoParam] is the name of the Parameter in the Query.

If both names are "DeptID", then change the criteria argument to:

"[DeptID] = " & [DeptID]

Access will recognise the first [DeptID] is supposed to be from the
Table,
i.e. Field name and the second [DeptID] is a value external from the
Table /
Domain "tblDepartment".

--
HTH
Van T. Dinh
MVP (Access)



Thank you for your help - I adjusted the titles accordingly and this is
what
I have.

=DLookUp("DepartmentName","tblDepartment","[DeptID] = " &
[DeptNoParam])

Since this report references a parameter query as it's record source,
the
department that I enter in the intial parameter query dialog box (the
query
the report is based on) is the one the report recognizes. What would
be
the
exact syntax for the header to elimiante the second parameter dialog
box?
I thought it would be:
=DLookUp("DepartmentName","tblDepartment","[DeptID])
but this is not working.

Thank you, Karen




:

I guess the DeptNo uniquely determines the DeptName?

If it does, you can create a calculated TextBox in the Report Header
using
the DLookUp() function in the ControlSource to get the DeptName, e.g.
set
the ControlSource to something like:

= DLookUp("DeptName", "tblDept", "[DeptNo] = " & [DeptNoParam])

including the equal sign where [DeptNoParam] is the name of the
Parameter
in
the Query.

--
HTH
Van T. Dinh
MVP (Access)



I have a report that references a parameter query as it's record
source.
When you click on the report, the parameter query dialog box opens
and
asks
the user to enter the dept. number. The query works great, it
populates
the
report accordingly. What info do I set as the header so the
department
name
poplulates the report accordingly?

Thank you, Karen
Click to expand...
Click to expand...
Click to expand...
 
OK. That's what I need. You need to use *exactly* the same Parameter name
in the DLookUp, i.e.:

=DLookUp("DepartmentName","tblDepartment","[DeptID] = " &
[Please Enter Department Number])

(type as 1 line in the ControlSource Property)
 
Thank you - It works great!

Van T. Dinh said:
OK. That's what I need. You need to use *exactly* the same Parameter name
in the DLookUp, i.e.:

=DLookUp("DepartmentName","tblDepartment","[DeptID] = " &
[Please Enter Department Number])

(type as 1 line in the ControlSource Property)


--
HTH
Van T. Dinh
MVP (Access)



Karen said:
Thank you for your help - The dialog box appears once now - Although when
I
type the Department ID into the dialog box, the department name doesn't
appear. When you say, "Please note the actual Parameter name(s) as appear
on
the Parameter dialog." What do you mean by that? Do you mean - what
criteria was entered into the query criteria field? If it's that, I
entered
[Please Enter Department Number]
Thank you
Click to expand...
Click to expand...
 
Back
Top