Adding a Name and ID Together

[JK]

On The report you need just the text box with the last code I sent you (the
one that uses the Dlookup function)

JK, I have both the text box and the Combo Box working on my Client Form
which one should I use!
Which one is easiest to show on my Client statement Report?......Thanks
Bob

Based on a Table, Might have something to do with Last Name, First name,
Title ..thanks bob

Yes JK this is in the header and Owner and Client mean the same thing,
this is the Clients name for his statement, there is an address box
below that..Thanks Bob
Bob,

This is incomplete code but I get the idea.

What is your report based on (query/table)?
Is this code in a page header, body or what? (looks like header to me)

From the code it appears that the report is not based on tblOwnerInfo
but only looks at it. This being the case the source code of the code
is slightly different:

=Left(Dlookup("[OwnerLastName]","tblOwnerInfo","[OwnerID]=" &
[OwnerID]),3) & [OwnerID]

Regards


On my Report this is what the Name code looks like
=IIf(DLookUp("OwnerTitle","tblOwnerInfo","OwnerID=" & [tbOwnerID])=""
Or IsNull(DLookUp("OwnerTitle","tblOwnerInfo","OwnerID=" &
[tbOwnerID])),"",DLookUp("OwnerTitle","tblOwnerInfo","OwnerID=" &
[tbOwnerID]) & " ") &

Calculated Field How do I go about that . I want to be able to Print
it on the Report....Thanks Bob

Bob,

Why link?, just add a calculated field to the report. Will that do
the trick?
(make it invisible if you don't want it printed)

Regards

JK now that is is on my form how do I link that with my Report
(Statement) I have called the text box on my form
ClientRef.....Thanks Bob

BRILLIANT JK just BRILLIANT I added the text box with the source
control
=Left([OwnerLastName],3) & [OwnerID]
Just Brillant thanks for your help....Bob


Bob,

You can do it in one of the following:

1. Design (or modify the) form to be based on a query that
includes the calculated field
Or
2. In the existing form add a text box and in the Control Source
(in the Properties) to:
=Left([OwnerLastName],3) & [OwnerID]
(The equal sign is mandatory, without it you will get an
error)

Regards/JK




Brilliant JK worked, so now how do I get each form to show there
individual ref so then I can assign it to there statement
..thanks Bob
I Query design view, in an *empty* column at the top row enter:

refCode: Left([OwnerLastName],3) & [OwnerID]

and run the query

You may rename the column with any other name by replacing
refCode: with any other expression but always with colon ) at
the end

Regards/JK


JK Ive created a New Query which has the 2 fields what do I do
from there..Thanks Bob
Hi Bob,

Create a calculated field in a query based on your table.

refCode: Left([OwnerLastName],3) & [OwnerID]

Regards/JK

 

[JK]

Bob,

I thought so (that you are using the one table only) for the report

You can dramatically simplify building the report by basing it on a query
that includes both the tblOwnerInfo and the table that includes the body of
the report (transactions, I imagine).

1. Make a new query and bring both tables into it (tbleOnwnerInfo and the
other one)
2. Link the tables On OwnerID (Access may have already done that for you)
3. Drag down *ALL* the fields that you need for the report, whether in the
header, body or footer
4. Add a calculated field:

ClientRef: Left([OwnerLastName],3) & [tblOwnerInfo].[OwnerID]

(the reason for including the table name is that because you have OwnerID in
both tables you will get an error message unless you specify which one to
use)

5. Specify the field and sort type (ascending/descending ) you want in the
report AND a criteria for printing, transaction date (I imagine) and/or any
other criteria

You will see the Header data in every line - that is ok

Create a new report based on this query and simply drag all the fields to
where they belong in the report, header, body or footer (if any) - you will
need no additional code, other than presumably totals in the footer that
will be calculated field(s).

A bit of more preparation work but a lot easier to maintain.

Regards

Based on a Table, Might have something to do with Last Name, First name,
Title ..thanks bob

Yes JK this is in the header and Owner and Client mean the same thing,
this is the Clients name for his statement, there is an address box below
that..Thanks Bob

Bob,

This is incomplete code but I get the idea.

What is your report based on (query/table)?
Is this code in a page header, body or what? (looks like header to me)

From the code it appears that the report is not based on tblOwnerInfo
but only looks at it. This being the case the source code of the code is
slightly different:

=Left(Dlookup("[OwnerLastName]","tblOwnerInfo","[OwnerID]=" &
[OwnerID]),3) & [OwnerID]

Regards


On my Report this is what the Name code looks like
=IIf(DLookUp("OwnerTitle","tblOwnerInfo","OwnerID=" & [tbOwnerID])=""
Or IsNull(DLookUp("OwnerTitle","tblOwnerInfo","OwnerID=" &
[tbOwnerID])),"",DLookUp("OwnerTitle","tblOwnerInfo","OwnerID=" &
[tbOwnerID]) & " ") &

Calculated Field How do I go about that . I want to be able to Print
it on the Report....Thanks Bob

Bob,

Why link?, just add a calculated field to the report. Will that do
the trick?
(make it invisible if you don't want it printed)

Regards

JK now that is is on my form how do I link that with my Report
(Statement) I have called the text box on my form
ClientRef.....Thanks Bob

BRILLIANT JK just BRILLIANT I added the text box with the source
control
=Left([OwnerLastName],3) & [OwnerID]
Just Brillant thanks for your help....Bob


Bob,

You can do it in one of the following:

1. Design (or modify the) form to be based on a query that
includes the calculated field
Or
2. In the existing form add a text box and in the Control Source
(in the Properties) to:
=Left([OwnerLastName],3) & [OwnerID]
(The equal sign is mandatory, without it you will get an error)

Regards/JK




Brilliant JK worked, so now how do I get each form to show there
individual ref so then I can assign it to there statement
..thanks Bob
I Query design view, in an *empty* column at the top row enter:

refCode: Left([OwnerLastName],3) & [OwnerID]

and run the query

You may rename the column with any other name by replacing
refCode: with any other expression but always with colon ) at
the end

Regards/JK


JK Ive created a New Query which has the 2 fields what do I do
from there..Thanks Bob
Hi Bob,

Create a calculated field in a query based on your table.

refCode: Left([OwnerLastName],3) & [OwnerID]

Regards/JK

 

[Bob]

JK I don't understand the two fields that I want to join together are in the
same table, The report is so complicated I could not reproduce it, If I cant
get the owner last name and id together I will try and make another query
first 3 letters of My Company Name and there ID because they are on
different tables, wont be as good but at least I will be able to complete it
....Thanks Bob

Bob,

I thought so (that you are using the one table only) for the report

You can dramatically simplify building the report by basing it on a query
that includes both the tblOwnerInfo and the table that includes the body
of the report (transactions, I imagine).

1. Make a new query and bring both tables into it (tbleOnwnerInfo and the
other one)
2. Link the tables On OwnerID (Access may have already done that for you)
3. Drag down *ALL* the fields that you need for the report, whether in the
header, body or footer
4. Add a calculated field:

ClientRef: Left([OwnerLastName],3) & [tblOwnerInfo].[OwnerID]

(the reason for including the table name is that because you have OwnerID
in both tables you will get an error message unless you specify which one
to use)

5. Specify the field and sort type (ascending/descending ) you want in the
report AND a criteria for printing, transaction date (I imagine) and/or
any other criteria

You will see the Header data in every line - that is ok

Create a new report based on this query and simply drag all the fields to
where they belong in the report, header, body or footer (if any) - you
will need no additional code, other than presumably totals in the footer
that will be calculated field(s).

A bit of more preparation work but a lot easier to maintain.

Regards

Based on a Table, Might have something to do with Last Name, First name,
Title ..thanks bob

Yes JK this is in the header and Owner and Client mean the same thing,
this is the Clients name for his statement, there is an address box
below that..Thanks Bob
Bob,

This is incomplete code but I get the idea.

What is your report based on (query/table)?
Is this code in a page header, body or what? (looks like header to me)

From the code it appears that the report is not based on tblOwnerInfo
but only looks at it. This being the case the source code of the code
is slightly different:

=Left(Dlookup("[OwnerLastName]","tblOwnerInfo","[OwnerID]=" &
[OwnerID]),3) & [OwnerID]

Regards


On my Report this is what the Name code looks like
=IIf(DLookUp("OwnerTitle","tblOwnerInfo","OwnerID=" & [tbOwnerID])=""
Or IsNull(DLookUp("OwnerTitle","tblOwnerInfo","OwnerID=" &
[tbOwnerID])),"",DLookUp("OwnerTitle","tblOwnerInfo","OwnerID=" &
[tbOwnerID]) & " ") &

Calculated Field How do I go about that . I want to be able to Print
it on the Report....Thanks Bob

Bob,

Why link?, just add a calculated field to the report. Will that do
the trick?
(make it invisible if you don't want it printed)

Regards

JK now that is is on my form how do I link that with my Report
(Statement) I have called the text box on my form
ClientRef.....Thanks Bob

BRILLIANT JK just BRILLIANT I added the text box with the source
control
=Left([OwnerLastName],3) & [OwnerID]
Just Brillant thanks for your help....Bob


Bob,

You can do it in one of the following:

1. Design (or modify the) form to be based on a query that
includes the calculated field
Or
2. In the existing form add a text box and in the Control Source
(in the Properties) to:
=Left([OwnerLastName],3) & [OwnerID]
(The equal sign is mandatory, without it you will get an
error)

Regards/JK




Brilliant JK worked, so now how do I get each form to show there
individual ref so then I can assign it to there statement
..thanks Bob
I Query design view, in an *empty* column at the top row enter:

refCode: Left([OwnerLastName],3) & [OwnerID]

and run the query

You may rename the column with any other name by replacing
refCode: with any other expression but always with colon ) at
the end

Regards/JK


JK Ive created a New Query which has the 2 fields what do I do
from there..Thanks Bob
Hi Bob,

Create a calculated field in a query based on your table.

refCode: Left([OwnerLastName],3) & [OwnerID]

Regards/JK