Expression Builder to Display Specify Column Field

[raychow22]

Hi Guys,



Is it possible to use expression builder to display a specify column field.

Right now I want a field to display the QEIinitials of the [Inspector Names] rather than it's [ID] which is displayed.




Thanks!

[raychow22]

The expression builder is the "File Name" field column

[JoeM]

Join the table holding those values to the Query (joining then), and return the QEInitials field.

[raychow22]

Join the table holding those values to the Query (joining then), and return the QEInitials field.

Sorry! I don't exactly know how to set that up.. can you explain it more?

[JoeM]

Where are you entering this expression?
Is it in a query?
What is the name of your table with the QE details?

[raychow22]

tblQEINames and tblInspectionReports are the two tables from the picture. I want the File Name to display 201767-RC instead of 201767-1. "1" comes from the name ID frm tblQEInames.

[JoeM]

But are you doing this in a query?

If so, add both of those tables to the query and join them.
Then, for the last piece of your expression, select the QEInitials field from the tblQEINames table.

[raychow22]

I believe I am limited on fields in my tblInspectionReports. Is there a way to add queries into the expression builder?

[JoeM]

I am still trying to find out where exactly you are trying to do this.
In Access, there are various objects - Tables, Queries, Forms, Reports, Macros, and Modules.
Which one of these are you trying to build this expression in?

[raychow22]

JoeM,

I found the solution. I was trying to build the expression in Tables. Solution was to get rid of the "ID name" in the query row source in both my "Forms" and "Table". It worked out perfectly.

[JoeM]

I highly recommend NOT doing that. Even though the new version of Access allow you to do expressions (calculated fields) right in the table level, it is generally recommended that you don't do that.
No other database programs allow for that. So if you ever needed to convert to SQL or something like that down the road, it wouldn't work.
Usually, the best place for calculations are queries. Then you can use queries as the Control Source of your Forms, Reports, Exports, etc.

[raychow22]

I highly recommend NOT doing that. Even though the new version of Access allow you to do expressions (calculated fields) right in the table level, it is generally recommended that you don't do that.
No other database programs allow for that. So if you ever needed to convert to SQL or something like that down the road, it wouldn't work.
Usually, the best place for calculations are queries. Then you can use queries as the Control Source of your Forms, Reports, Exports, etc.

Thanks JoeM for the insight.

I wouldn't really know how to do that... so what I'm basically trying to accomplish here is to make a Unique ID for each record (From Form to Table) that my daily inspector's report. So it would be the Date() & the Initials() of the inspector. Do you think writing a code in my "Form" would be better vs. having a calculated field?

[JoeM]

what I'm basically trying to accomplish here is to make a Unique ID for each record

Your Unique ID should not be an Expression, as if the fields used in the Expression change, so will this field, and that could cause real problems. It should be a hard-coded value. It can be calculated (usually via VBA, but it should not be formula (Expression).

I wouldn't really know how to do that...

Are you saying that you don't know how to create a query in Access? I am not trying to be mean or anything, but that is one of the most basic things you can do in Access, and one of the first things to learn (it is one of the main reasons for using Access). You definitely will want to how to join Tables in a Query, and the different types of Joins and what they do. See here for some reading:
https://support.office.com/en-us/art...3-b39572400c19
You may also want to pick up a good introductory book on Access, as so much builds on the basic stuff, and without a good foundation, you are going to be in for a lot of frustration.