concatenating fields for links in queries

[Ted]

hey all,

i would like to link a PolicyNumber field concatenated w a field called
INVSUB to a PolicyNumber field in another table. can i do that or do i have
to create another field and update it to [PolicyNumber] & [INVSUB]?

here is the FROM section of SQL code. i would like to concatenate
ALLCOMPANIESDATA.[POL#] w ALLCOMPANIESDATA.[INVSUB]

"FROM ALLCOMPANIESDATA INNER JOIN qryCPRackleyRecords ON
ALLCOMPANIESDATA.[POL#] = qryCPRackleyRecords.[Policy Number]"

TIA
Ted

 

[Guest]

To concatenate fields in a query, you use a calculated field

SELECT [PolicyNumber] & [INVSUB] As WholeNumber

Or if you are using the query designer:
WholeNumber: [PolicyNumber] & [INVSUB]?

 

[John Spencer]

You can join on a calculated value, however you cannot build this type
of query using the Design view, but must set the join up in SQL view.

FROM ALLCOMPANIESDATA INNER JOIN qryCPRackleyRecords
ON (ALLCOMPANIESDATA.[PolicyNumber] & ALLCompaniesDate.[InvSub]) =
qryCPRackleyRecords.[Policy Number]

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================