Multiple Tables in Reports

[Guest]

I built a report, and in a text box at the top I want to reference the
department that the report applies to. The title changes each time I run the
report based on the department that I am querying. So I added a "Department"
field to the title line.

However, I'd like to type in an abbreviation for the Department, i.e., FIN
for Finance, and I'd like the title to read the full name "Finance". I built
a table listing all abbreviations and all full names, but I don't know how to
link the two together so that when I type in the Abbreviation, the full names
comes up.

Thanks for any suggestions.

KK

 

[Guest]

Set the Department field control source property to

=DLookup("[FullName]","YourTable","[Abbreviation] = '" & Me.AbbreviationBox
& "'")

Substitute your field and table names above.

 

[Guest]

I was with you up until the second equal sign. Here's what I have so far:

=DLookUp("[CommunityName])","TBL-Communities","[Abbreviation] = '" &
Me.AbbreviationBox & "'")

I have used DLookup before, so the first part was familiar, but I don't know
what is after the second equal sign.

Can you clarify the second part of that expression? Thanks!

KK

Set the Department field control source property to

=DLookup("[FullName]","YourTable","[Abbreviation] = '" & Me.AbbreviationBox
& "'")

Substitute your field and table names above.

I built a report, and in a text box at the top I want to reference the
department that the report applies to. The title changes each time I run the
report based on the department that I am querying. So I added a "Department"
field to the title line.

However, I'd like to type in an abbreviation for the Department, i.e., FIN
for Finance, and I'd like the title to read the full name "Finance". I built
a table listing all abbreviations and all full names, but I don't know how to
link the two together so that when I type in the Abbreviation, the full names
comes up.

Thanks for any suggestions.

KK

 

[Duane Hookom]

I would add the department table to the report's record source. If you can't
join the fields from this table to any other table then add a criteria under
the abbreviation field:
[Enter Dept]
Include the full name field in the query to use in the report.

I do recommend using forms for all criteria rather than parameter prompts.

 

[Guest]

First of all take the bracket ) off behind [CommunityName].
In English the statement is saying Get the community name from the
TBL-Communities table where the abbreviation matches the one you have typed
into the AbbreviationBox on your form.
e.g. if you typed FIN into the abbreviationbox, the DLookUp is

=DLookUp("[CommunityName]","TBL-Communities","[Abbreviation] = 'FIN'")

which should bring back 'Finance'

So basically the 3rd part of the DLookUp is like a SQL where clause without
the word where.

Hope this explains.

I was with you up until the second equal sign. Here's what I have so far:

=DLookUp("[CommunityName])","TBL-Communities","[Abbreviation] = '" &
Me.AbbreviationBox & "'")

I have used DLookup before, so the first part was familiar, but I don't know
what is after the second equal sign.

Can you clarify the second part of that expression? Thanks!

KK

Set the Department field control source property to

=DLookup("[FullName]","YourTable","[Abbreviation] = '" & Me.AbbreviationBox
& "'")

Substitute your field and table names above.

I built a report, and in a text box at the top I want to reference the
department that the report applies to. The title changes each time I run the
report based on the department that I am querying. So I added a "Department"
field to the title line.

However, I'd like to type in an abbreviation for the Department, i.e., FIN
for Finance, and I'd like the title to read the full name "Finance". I built
a table listing all abbreviations and all full names, but I don't know how to
link the two together so that when I type in the Abbreviation, the full names
comes up.

Thanks for any suggestions.

KK

 

[Guest]

I apologize, but I'm not getting this...

Here's the corrected expression which is in the control source field of the
text box within the report header of my report.

=DLookUp("[CommunityName]","TBL-Commmunities","[Abbreviation]='" &
Me.AbbreviationBox & "'")

I understand the concept of where the info is coming from, but I still don't
understand the part behind the second equals sign, or this part:

...... '" & Me.AbbreviationBox & "'")

What's Me.AbbreviationBox mean and where does that come from (syntax-wise?)
"Abbreviation" is a field in the TBL-Communities table. When I used this in
the control source and run the report, I get a "enter parameter value" box
that asks me for "Me".

Thanks for trying to explain this to me!

KK

First of all take the bracket ) off behind [CommunityName].
In English the statement is saying Get the community name from the
TBL-Communities table where the abbreviation matches the one you have typed
into the AbbreviationBox on your form.
e.g. if you typed FIN into the abbreviationbox, the DLookUp is

=DLookUp("[CommunityName]","TBL-Communities","[Abbreviation] = 'FIN'")

which should bring back 'Finance'

So basically the 3rd part of the DLookUp is like a SQL where clause without
the word where.

Hope this explains.

I was with you up until the second equal sign. Here's what I have so far:

=DLookUp("[CommunityName])","TBL-Communities","[Abbreviation] = '" &
Me.AbbreviationBox & "'")

I have used DLookup before, so the first part was familiar, but I don't know
what is after the second equal sign.

Can you clarify the second part of that expression? Thanks!

KK

Set the Department field control source property to

=DLookup("[FullName]","YourTable","[Abbreviation] = '" & Me.AbbreviationBox
& "'")

Substitute your field and table names above.

:

I built a report, and in a text box at the top I want to reference the
department that the report applies to. The title changes each time I run the
report based on the department that I am querying. So I added a "Department"
field to the title line.

However, I'd like to type in an abbreviation for the Department, i.e., FIN
for Finance, and I'd like the title to read the full name "Finance". I built
a table listing all abbreviations and all full names, but I don't know how to
link the two together so that when I type in the Abbreviation, the full names
comes up.

Thanks for any suggestions.

KK

 

[Guest]

In your intitial question, you said you wanted a box where you could type in
FIN.
It is this box's name that you put in place of AbbreviationBox in my example.
I am assuming this box is on a form ?
I am also assuming you launch the report from a button on this form ?

I apologize, but I'm not getting this...

Here's the corrected expression which is in the control source field of the
text box within the report header of my report.

=DLookUp("[CommunityName]","TBL-Commmunities","[Abbreviation]='" &
Me.AbbreviationBox & "'")

I understand the concept of where the info is coming from, but I still don't
understand the part behind the second equals sign, or this part:

..... '" & Me.AbbreviationBox & "'")

What's Me.AbbreviationBox mean and where does that come from (syntax-wise?)
"Abbreviation" is a field in the TBL-Communities table. When I used this in
the control source and run the report, I get a "enter parameter value" box
that asks me for "Me".

Thanks for trying to explain this to me!

KK

First of all take the bracket ) off behind [CommunityName].
In English the statement is saying Get the community name from the
TBL-Communities table where the abbreviation matches the one you have typed
into the AbbreviationBox on your form.
e.g. if you typed FIN into the abbreviationbox, the DLookUp is

=DLookUp("[CommunityName]","TBL-Communities","[Abbreviation] = 'FIN'")

which should bring back 'Finance'

So basically the 3rd part of the DLookUp is like a SQL where clause without
the word where.

Hope this explains.

I was with you up until the second equal sign. Here's what I have so far:

=DLookUp("[CommunityName])","TBL-Communities","[Abbreviation] = '" &
Me.AbbreviationBox & "'")

I have used DLookup before, so the first part was familiar, but I don't know
what is after the second equal sign.

Can you clarify the second part of that expression? Thanks!

KK

:

Set the Department field control source property to

=DLookup("[FullName]","YourTable","[Abbreviation] = '" & Me.AbbreviationBox
& "'")

Substitute your field and table names above.

:

I built a report, and in a text box at the top I want to reference the
department that the report applies to. The title changes each time I run the
report based on the department that I am querying. So I added a "Department"
field to the title line.

However, I'd like to type in an abbreviation for the Department, i.e., FIN
for Finance, and I'd like the title to read the full name "Finance". I built
a table listing all abbreviations and all full names, but I don't know how to
link the two together so that when I type in the Abbreviation, the full names
comes up.

Thanks for any suggestions.

KK

 

[Guest]

I'm getting there. Thanks for your patience.

Now I understand AbbreviationBox, but I guess I thought that I could build
this into a report, not a form. I apologize for not being clearer. I put a
text box into the header of a report where I thought the title "Finance"
would go. The report is based on a query that asks what community I want to
Query. I type in FIN, and it brings me only those records. I think I see
the disconnect, but I'm not sure. I need to do a little more research before
I can even ask a meaningful question at this point.

Can this work in a report, or do I need to go back and figure out how to do
this in a form? I've done forms, but not to generate a report, so I need to
go back and figure out how to do that. Maybe I set it up incorrectly by
using a report.

Lots to learn...

KK

In your intitial question, you said you wanted a box where you could type in
FIN.
It is this box's name that you put in place of AbbreviationBox in my example.
I am assuming this box is on a form ?
I am also assuming you launch the report from a button on this form ?

I apologize, but I'm not getting this...

Here's the corrected expression which is in the control source field of the
text box within the report header of my report.

=DLookUp("[CommunityName]","TBL-Commmunities","[Abbreviation]='" &
Me.AbbreviationBox & "'")

I understand the concept of where the info is coming from, but I still don't
understand the part behind the second equals sign, or this part:

..... '" & Me.AbbreviationBox & "'")

What's Me.AbbreviationBox mean and where does that come from (syntax-wise?)
"Abbreviation" is a field in the TBL-Communities table. When I used this in
the control source and run the report, I get a "enter parameter value" box
that asks me for "Me".

Thanks for trying to explain this to me!

KK

First of all take the bracket ) off behind [CommunityName].
In English the statement is saying Get the community name from the
TBL-Communities table where the abbreviation matches the one you have typed
into the AbbreviationBox on your form.
e.g. if you typed FIN into the abbreviationbox, the DLookUp is

=DLookUp("[CommunityName]","TBL-Communities","[Abbreviation] = 'FIN'")

which should bring back 'Finance'

So basically the 3rd part of the DLookUp is like a SQL where clause without
the word where.

Hope this explains.

:

I was with you up until the second equal sign. Here's what I have so far:

=DLookUp("[CommunityName])","TBL-Communities","[Abbreviation] = '" &
Me.AbbreviationBox & "'")

I have used DLookup before, so the first part was familiar, but I don't know
what is after the second equal sign.

Can you clarify the second part of that expression? Thanks!

KK

:

Set the Department field control source property to

=DLookup("[FullName]","YourTable","[Abbreviation] = '" & Me.AbbreviationBox
& "'")

Substitute your field and table names above.

:

I built a report, and in a text box at the top I want to reference the
department that the report applies to. The title changes each time I run the
report based on the department that I am querying. So I added a "Department"
field to the title line.

However, I'd like to type in an abbreviation for the Department, i.e., FIN
for Finance, and I'd like the title to read the full name "Finance". I built
a table listing all abbreviations and all full names, but I don't know how to
link the two together so that when I type in the Abbreviation, the full names
comes up.

Thanks for any suggestions.

KK

 

[Guest]

Change the column in the query that is providing the prompt to

FullName: DLookUp"([CommunityName]","TBL-Commmunities","[Abbreviation]='" &
[Enter Department Abbreviation] & "'")

The text box on your report should then be called FullName so that it will
use what is in the query column from above.

I'm getting there. Thanks for your patience.

Now I understand AbbreviationBox, but I guess I thought that I could build
this into a report, not a form. I apologize for not being clearer. I put a
text box into the header of a report where I thought the title "Finance"
would go. The report is based on a query that asks what community I want to
Query. I type in FIN, and it brings me only those records. I think I see
the disconnect, but I'm not sure. I need to do a little more research before
I can even ask a meaningful question at this point.

Can this work in a report, or do I need to go back and figure out how to do
this in a form? I've done forms, but not to generate a report, so I need to
go back and figure out how to do that. Maybe I set it up incorrectly by
using a report.

Lots to learn...

KK

In your intitial question, you said you wanted a box where you could type in
FIN.
It is this box's name that you put in place of AbbreviationBox in my example.
I am assuming this box is on a form ?
I am also assuming you launch the report from a button on this form ?

I apologize, but I'm not getting this...

Here's the corrected expression which is in the control source field of the
text box within the report header of my report.

=DLookUp("[CommunityName]","TBL-Commmunities","[Abbreviation]='" &
Me.AbbreviationBox & "'")

I understand the concept of where the info is coming from, but I still don't
understand the part behind the second equals sign, or this part:

..... '" & Me.AbbreviationBox & "'")

What's Me.AbbreviationBox mean and where does that come from (syntax-wise?)
"Abbreviation" is a field in the TBL-Communities table. When I used this in
the control source and run the report, I get a "enter parameter value" box
that asks me for "Me".

Thanks for trying to explain this to me!

KK

:

First of all take the bracket ) off behind [CommunityName].
In English the statement is saying Get the community name from the
TBL-Communities table where the abbreviation matches the one you have typed
into the AbbreviationBox on your form.
e.g. if you typed FIN into the abbreviationbox, the DLookUp is

=DLookUp("[CommunityName]","TBL-Communities","[Abbreviation] = 'FIN'")

which should bring back 'Finance'

So basically the 3rd part of the DLookUp is like a SQL where clause without
the word where.

Hope this explains.

:

I was with you up until the second equal sign. Here's what I have so far:

=DLookUp("[CommunityName])","TBL-Communities","[Abbreviation] = '" &
Me.AbbreviationBox & "'")

I have used DLookup before, so the first part was familiar, but I don't know
what is after the second equal sign.

Can you clarify the second part of that expression? Thanks!

KK

:

Set the Department field control source property to

=DLookup("[FullName]","YourTable","[Abbreviation] = '" & Me.AbbreviationBox
& "'")

Substitute your field and table names above.

:

I built a report, and in a text box at the top I want to reference the
department that the report applies to. The title changes each time I run the
report based on the department that I am querying. So I added a "Department"
field to the title line.

However, I'd like to type in an abbreviation for the Department, i.e., FIN
for Finance, and I'd like the title to read the full name "Finance". I built
a table listing all abbreviations and all full names, but I don't know how to
link the two together so that when I type in the Abbreviation, the full names
comes up.

Thanks for any suggestions.

KK

 

[Guest]

Dennis, I'm making progress. This helped a lot. Here's what I did...I'm
almost there.

I added the "Community" field (from my table) to the header in my report,
then used this expression in the control properties:

=DLookUp("[CommunityName]","TBL-Communities","[Abbreviation]='" & [What
community] & "'")

I left the Query as is, using the Community column to ask the question "What
community?"

Bingo, it worked! But I think I've asked twice for community (once in the
query and once in the report), so I get two prompts, both of which I have to
complete, that ask me "What community?" But if I fill in both prompts with
the abbreviation (FIN), I get "Finance" in my report header.

That will work for me for now, but if there's a way of eliminating the
double entry that would be great.

Thanks very much for your help this morning. I appreciate your time!

KK

Change the column in the query that is providing the prompt to

FullName: DLookUp"([CommunityName]","TBL-Commmunities","[Abbreviation]='" &
[Enter Department Abbreviation] & "'")

The text box on your report should then be called FullName so that it will
use what is in the query column from above.

I'm getting there. Thanks for your patience.

Now I understand AbbreviationBox, but I guess I thought that I could build
this into a report, not a form. I apologize for not being clearer. I put a
text box into the header of a report where I thought the title "Finance"
would go. The report is based on a query that asks what community I want to
Query. I type in FIN, and it brings me only those records. I think I see
the disconnect, but I'm not sure. I need to do a little more research before
I can even ask a meaningful question at this point.

Can this work in a report, or do I need to go back and figure out how to do
this in a form? I've done forms, but not to generate a report, so I need to
go back and figure out how to do that. Maybe I set it up incorrectly by
using a report.

Lots to learn...

KK

In your intitial question, you said you wanted a box where you could type in
FIN.
It is this box's name that you put in place of AbbreviationBox in my example.
I am assuming this box is on a form ?
I am also assuming you launch the report from a button on this form ?

:

I apologize, but I'm not getting this...

Here's the corrected expression which is in the control source field of the
text box within the report header of my report.

=DLookUp("[CommunityName]","TBL-Commmunities","[Abbreviation]='" &
Me.AbbreviationBox & "'")

I understand the concept of where the info is coming from, but I still don't
understand the part behind the second equals sign, or this part:

..... '" & Me.AbbreviationBox & "'")

What's Me.AbbreviationBox mean and where does that come from (syntax-wise?)
"Abbreviation" is a field in the TBL-Communities table. When I used this in
the control source and run the report, I get a "enter parameter value" box
that asks me for "Me".

Thanks for trying to explain this to me!

KK

:

First of all take the bracket ) off behind [CommunityName].
In English the statement is saying Get the community name from the
TBL-Communities table where the abbreviation matches the one you have typed
into the AbbreviationBox on your form.
e.g. if you typed FIN into the abbreviationbox, the DLookUp is

=DLookUp("[CommunityName]","TBL-Communities","[Abbreviation] = 'FIN'")

which should bring back 'Finance'

So basically the 3rd part of the DLookUp is like a SQL where clause without
the word where.

Hope this explains.

:

I was with you up until the second equal sign. Here's what I have so far:

=DLookUp("[CommunityName])","TBL-Communities","[Abbreviation] = '" &
Me.AbbreviationBox & "'")

I have used DLookup before, so the first part was familiar, but I don't know
what is after the second equal sign.

Can you clarify the second part of that expression? Thanks!

KK

:

Set the Department field control source property to

=DLookup("[FullName]","YourTable","[Abbreviation] = '" & Me.AbbreviationBox
& "'")

Substitute your field and table names above.

:

I built a report, and in a text box at the top I want to reference the
department that the report applies to. The title changes each time I run the
report based on the department that I am querying. So I added a "Department"
field to the title line.

However, I'd like to type in an abbreviation for the Department, i.e., FIN
for Finance, and I'd like the title to read the full name "Finance". I built
a table listing all abbreviations and all full names, but I don't know how to
link the two together so that when I type in the Abbreviation, the full names
comes up.

Thanks for any suggestions.

KK