Expression to link two tables together not working

[Sam]

Hello,

I have the following expression in query "Deposits3" under field [Office
Name], table [Deposits] for the report "rptDeposits3":

Trim(Str([Offices]![OffName]))


What I'm trying to do is link the shown table [Offices] field [OffName] to
the field [Office Name] within [Deposits] to show, when running
"rptDeposits3", the actual name of the office instead of the OfficeID that it
is showing now (stored in Offices).

Currently when I do the above, an error comes out saying it's incorrect or
too complex.

If I do a blank expression with a relationship between the two instead,
there is just a type mismatch error.

So nothing seems to be working (again, trying to make my OfficeID show as
OffName in my report).

Please help. Thank you!

 

[tina]

please post the entire SQL statement of the query.

hth

 

[Sam]

SELECT Deposits.[Office Name], Deposits.[Date Received], Deposits.[Packet #],
Deposits.[Deposit Date], Deposits.ReceivedBy, Deposits.GivenTo,
Deposits.GivenDate, Deposits.EnteredBy, Deposits.EnteredDate
FROM Offices, Deposits
WHERE (((Deposits.[Office Name])=Trim(Str([Offices]![OffName]))) AND
((Deposits.EnteredDate) Is Null) AND ((DateDiff("d",[Current
Date],Date()))<3));

please post the entire SQL statement of the query.

hth

Hello,

I have the following expression in query "Deposits3" under field [Office
Name], table [Deposits] for the report "rptDeposits3":

Trim(Str([Offices]![OffName]))


What I'm trying to do is link the shown table [Offices] field [OffName] to
the field [Office Name] within [Deposits] to show, when running
"rptDeposits3", the actual name of the office instead of the OfficeID that it
is showing now (stored in Offices).

Currently when I do the above, an error comes out saying it's incorrect or
too complex.

If I do a blank expression with a relationship between the two instead,
there is just a type mismatch error.

So nothing seems to be working (again, trying to make my OfficeID show as
OffName in my report).

Please help. Thank you!

 

[tina]

you don't have any links in the query; you have set a criteria on
Deposits.OfficeName.

you need to link the two tables, Deposits and Offices. since apparently
OffName must be trimmed in order to match OfficeName, suggest you create a
query from Offices, i'll call it qryOffices. create a calculated field for
OffName, as

SELECT Trim([OffName]) As TrimmedName FROM Offices;

and include whatever other fields you need from table Offices.

use qryOffices in your posted query, rather than table Offices. INNER JOIN
Deposits and qryOffices on fields [Office Name] and [TrimmedName].

hth

SELECT Deposits.[Office Name], Deposits.[Date Received], Deposits.[Packet #],
Deposits.[Deposit Date], Deposits.ReceivedBy, Deposits.GivenTo,
Deposits.GivenDate, Deposits.EnteredBy, Deposits.EnteredDate
FROM Offices, Deposits
WHERE (((Deposits.[Office Name])=Trim(Str([Offices]![OffName]))) AND
((Deposits.EnteredDate) Is Null) AND ((DateDiff("d",[Current
Date],Date()))<3));

please post the entire SQL statement of the query.

hth

Hello,

I have the following expression in query "Deposits3" under field [Office
Name], table [Deposits] for the report "rptDeposits3":

Trim(Str([Offices]![OffName]))


What I'm trying to do is link the shown table [Offices] field [OffName] to
the field [Office Name] within [Deposits] to show, when running
"rptDeposits3", the actual name of the office instead of the OfficeID

that
it

is showing now (stored in Offices).

Currently when I do the above, an error comes out saying it's incorrect or
too complex.

If I do a blank expression with a relationship between the two instead,
there is just a type mismatch error.

So nothing seems to be working (again, trying to make my OfficeID show as
OffName in my report).

Please help. Thank you!

 

[Dale Fye]

Actually, Sam, I'm not exactly sure why you want to use your Offices table
in this query at all. All the fields in the Select clause are from the
Deposits table, so the only reason I can think of for using the Offices
table in this query is to limit the results, which implies that the Offices
table does not have the same set of Office_Names as does the Deposit table.
Can you expain why you are including the Offices table?

Personally, I would have an Office_ID (autonumber) field in my Offices
table, and use that same field (as a long integer) in the Deposits table.
This way, you don't have to worry about joining on text fields when they
might not be identical (spaces added).

Dale

SELECT Deposits.[Office Name], Deposits.[Date Received], Deposits.[Packet
#],
Deposits.[Deposit Date], Deposits.ReceivedBy, Deposits.GivenTo,
Deposits.GivenDate, Deposits.EnteredBy, Deposits.EnteredDate
FROM Offices, Deposits
WHERE (((Deposits.[Office Name])=Trim(Str([Offices]![OffName]))) AND
((Deposits.EnteredDate) Is Null) AND ((DateDiff("d",[Current
Date],Date()))<3));

please post the entire SQL statement of the query.

hth

Hello,

I have the following expression in query "Deposits3" under field
[Office
Name], table [Deposits] for the report "rptDeposits3":

Trim(Str([Offices]![OffName]))


What I'm trying to do is link the shown table [Offices] field [OffName]
to
the field [Office Name] within [Deposits] to show, when running
"rptDeposits3", the actual name of the office instead of the OfficeID
that it
is showing now (stored in Offices).

Currently when I do the above, an error comes out saying it's incorrect
or
too complex.

If I do a blank expression with a relationship between the two instead,
there is just a type mismatch error.

So nothing seems to be working (again, trying to make my OfficeID show
as
OffName in my report).

Please help. Thank you!

 

[Sam]

Well I needed to somehow link the offices and deposits together because what
I'm trying to accomplish is showing the Office Name in the report instead of
the OfficeID (which is now shown). The only instance of the name is in
offices, the deposits only has the ID.

Actually, Sam, I'm not exactly sure why you want to use your Offices table
in this query at all. All the fields in the Select clause are from the
Deposits table, so the only reason I can think of for using the Offices
table in this query is to limit the results, which implies that the Offices
table does not have the same set of Office_Names as does the Deposit table.
Can you expain why you are including the Offices table?

Personally, I would have an Office_ID (autonumber) field in my Offices
table, and use that same field (as a long integer) in the Deposits table.
This way, you don't have to worry about joining on text fields when they
might not be identical (spaces added).

Dale

SELECT Deposits.[Office Name], Deposits.[Date Received], Deposits.[Packet
#],
Deposits.[Deposit Date], Deposits.ReceivedBy, Deposits.GivenTo,
Deposits.GivenDate, Deposits.EnteredBy, Deposits.EnteredDate
FROM Offices, Deposits
WHERE (((Deposits.[Office Name])=Trim(Str([Offices]![OffName]))) AND
((Deposits.EnteredDate) Is Null) AND ((DateDiff("d",[Current
Date],Date()))<3));

please post the entire SQL statement of the query.

hth


Hello,

I have the following expression in query "Deposits3" under field
[Office
Name], table [Deposits] for the report "rptDeposits3":

Trim(Str([Offices]![OffName]))


What I'm trying to do is link the shown table [Offices] field [OffName]
to
the field [Office Name] within [Deposits] to show, when running
"rptDeposits3", the actual name of the office instead of the OfficeID
that
it
is showing now (stored in Offices).

Currently when I do the above, an error comes out saying it's incorrect
or
too complex.

If I do a blank expression with a relationship between the two instead,
there is just a type mismatch error.

So nothing seems to be working (again, trying to make my OfficeID show
as
OffName in my report).

Please help. Thank you!