expression in query

  • Thread starter Thread starter Todd
  • Start date Start date
T

Todd

I've got two tables (table1 and table2) I want to use in a query and their
relationship is linked by company number and it's a one to many relationship.
The linking fields are labeled the same (numCompanyNumber). The problem
I've got is that I have an expression I'd like to use in the query to show
project numbers in a certain format. The formatted expression in the query
field is as follows:

mgcid: [numCompanyNumber] & "-" & Format([submissiondate],"mm") & "-" &
Format([submissiondate],"yy") & "-" & Format([AEID],"0000")

Example of the format above: 4-07-08-0001 (the 0001 is an incrementing
number)

This expression works fine in the form and reports I use but in the query I
get an error because both tables have the same numCompanyNumber field and the
error says that it "refers to more than one table listed in the FROM clasue
of your SQL statement." I want to use table1 because it lists the company
name that I want to include in the query and not just the company number.

My questions finally is how can I specify in the query to direct the
expression to find only one of the two fields that are named the same?
 
Use the syntax


tableName.fieldName


to specify which of the two tables you want.


Vanderghast, Access MVP
 
Thanks! I tried that initially but had brackets around the table name. This
time I just had it all in the brackets [table1.fieldname] like you suggested.
--
Todd


Michel Walsh said:
Use the syntax


tableName.fieldName


to specify which of the two tables you want.


Vanderghast, Access MVP


Todd said:
I've got two tables (table1 and table2) I want to use in a query and their
relationship is linked by company number and it's a one to many
relationship.
The linking fields are labeled the same (numCompanyNumber). The problem
I've got is that I have an expression I'd like to use in the query to show
project numbers in a certain format. The formatted expression in the
query
field is as follows:

mgcid: [numCompanyNumber] & "-" & Format([submissiondate],"mm") & "-" &
Format([submissiondate],"yy") & "-" & Format([AEID],"0000")

Example of the format above: 4-07-08-0001 (the 0001 is an incrementing
number)

This expression works fine in the form and reports I use but in the query
I
get an error because both tables have the same numCompanyNumber field and
the
error says that it "refers to more than one table listed in the FROM
clasue
of your SQL statement." I want to use table1 because it lists the company
name that I want to include in the query and not just the company number.

My questions finally is how can I specify in the query to direct the
expression to find only one of the two fields that are named the same?
Click to expand...
Click to expand...
 
Back
Top