How to create calculated field in MS Access table (1 Viewer)

[SachAccess]

Hi,

I am trying to create a calculated field in MS Access Table
I have never done it before. Trying for the first time.

I have a date column in my table. I need to consider difference between Book Date and today and populate the value in the calculated field accordingly.
For example Book date is 30-Oct-22 and today is 6-Oct-2022 so the difference is less than 30 but more than 16 so the calculated field should have value as GREEN
If the difference is less than 16 and more than or equal to 5 then it should be AMBER
Anything less than 5 should be RED
I am not able to understand how to write this formula in Expression Builder. If possible can anyone please help me in this.

 

[arnelgp]

you need form for those Colors and use Conditional Formatting.

 

[plog]

You shouldn't use calculated fields in tables.

Normally if you need a value calculated you do that in a query and then reference the query instead of the table to get that value.

However, it seems this calculation is an intermediate step to making something conditionally formatted. This link will help you with that:

Highlight data with conditional formatting - Microsoft Support

You can set conditional formatting rules for controls on a form or report in Access desktop databases to highlight important data.

support.microsoft.com

Then since you want to calculate the difference between two dates you would use DateDiff to do the actual calculation:

MS Access: DateDiff Function

This MSAccess tutorial explains how to use the Access DateDiff function with syntax and examples. The Microsoft Access DateDiff function returns the difference between two date values, based on the interval specified.

www.techonthenet.com

 

[SachAccess]

you need form for those Colors and use Conditional Formatting.

My mistake, I need text as GREEN, AMBER and RED and not the color.
I am trying to populate text values in the existing table based on the date difference.

Thanks for the help. Have a nice day ahead.

 

[SachAccess]

You shouldn't use calculated fields in tables.

Normally if you need a value calculated you do that in a query and then reference the query instead of the table to get that value.

However, it seems this calculation is an intermediate step to making something conditionally formatted. This link will help you with that:

Highlight data with conditional formatting - Microsoft Support

You can set conditional formatting rules for controls on a form or report in Access desktop databases to highlight important data.

support.microsoft.com

Then since you want to calculate the difference between two dates you would use DateDiff to do the actual calculation:

MS Access: DateDiff Function

This MSAccess tutorial explains how to use the Access DateDiff function with syntax and examples. The Microsoft Access DateDiff function returns the difference between two date values, based on the interval specified.

www.techonthenet.com

Thanks a lot @plog , am checking this. Have a nice day ahead.

 

[arnelgp]

you can just have it in a Query:

select [Book Date], Switch(Date - [Book Date] > 16, "GREEN", Date - [Book Date] > 5, "AMBER", TRUE, "RED") As Status From yourTable;

 

[SachAccess]

you can just have it in a Query:

select [Book Date], Switch(Date - [Book Date] > 16, "GREEN", Date - [Book Date] > 5, "AMBER", TRUE, "RED") As Status From yourTable;

Thanks a lot @arnelgp for the help. Please give me some time to revert.

PS - Query is working perfectly. Now am editing the dates and re-checking for any mistakes from my side.
Thanks.

 

[SachAccess]

Hi,

I need one more help related to same query, hence adding to the original post only.

How do I combine below two queries.
First query is linked to form.
However, one of the column in the original table has in-correctly values.
Hence I took help from the forum to populate the column correctly but I do not know how to combine both the queries.
I tried making a UNION query simply copying from Google but it is not working.
I tried to merge the second query with first query but am getting one or the other error.
Can anyone please help me in this.

PS - I have edited original names while posting here.

SELECT tbl_First.*, [Vehicle] & " - " & [Title] AS VehicleNumberAndTitle,
tbl_Second.NumberPlate AS NP_Name,
tbl_First.RepairedBy AS ServicedBy,
[tbl_Third].[SpecialName] & ", " & [tbl_Third].[FirstName] AS Mechanic,
tbl_Second.PISTON, [tbl_Third_1].[SpecialName] & ", " & [tbl_Third_1].[FirstName] AS PISTON_Name,
tbl_Fourth.ClutchPlate, tbl_Fourth.Division, tbl_Fourth.VehicleLink,
tbl_Third.GEARBOX AS PLUMBER_GEARBOX, tbl_Third.FirstName AS PLUMBER_FirstName, tbl_Third.SpecialName AS PLUMBER_SpecialName, tbl_Third_1.FirstName AS PISTON_FirstName, tbl_Third_1.Comment, tbl_Fifth.RepairStatus
FROM tbl_Third LEFT JOIN (((((tbl_First LEFT JOIN tbl_Fourth ON tbl_First.Vehicle = tbl_Fourth.VehicleNumber) LEFT JOIN tbl_Second ON tbl_First.NumberPlate = tbl_Second.ID) LEFT JOIN tbl_Third AS tbl_Third_1 ON tbl_Second.PISTON = tbl_Third_1.GEARBOX) LEFT JOIN tbl_Sixth ON tbl_First.CarID = tbl_Sixth.CarID) LEFT JOIN tbl_Fifth ON tbl_Sixth.MaxOfID = tbl_Fifth.ID) ON tbl_Third.GEARBOX = tbl_First.PLUMBER
WHERE tbl_First.CarID <> NULL and tbl_First.Status = "Active" and tbl_First.OilDue <> NULL
ORDER BY tbl_First.OilDue;

SELECT tbl_First.OilDue, Switch(tbl_First.OilDue- Date()  > 16, "GREEN", tbl_First.OilDue -Date() > 5, "AMBER", TRUE, "RED") AS RAGStatus
FROM tbl_First;

 

[Pat Hartman]

All the underscores and square brackets make my teeth grind. You can minimize the clutter by using alias' for the tables.

Forms don't store data. Tables store data.

Hence I took help from the forum to populate the column correctly

Did you mean Form rather than forum?

Why don't you just fix the underlying bad data???????

 

[SachAccess]

All the underscores and square brackets make my teeth grind. You can minimize the clutter by using alias' for the tables.

Forms don't store data. Tables store data.



Did you mean Form rather than forum?

Why don't you just fix the underlying bad data???????

My apologies @Pat Hartman . I pasted the original query as it is, just changed the names.
I thought I should keep the original structure intact. Will take care going forward.

I meant from Forum only, I took help to calculate RAG status.
Will post with easier to understand names and structure.
Have a nice day ahead.

 

[Pat Hartman]

You addressed my aversion to underscores and square brackets which is neither here nor there but I appreciate it. If it doesn't bother you, then whatever. My aversion is personal. However, when you post sql and you have used long (especially offensive table names), just alias them. That makes the query easier to read and usually much shorter. You can easily do it using the QBE, then switch to SQL view and voila. Just remember to not save the query if you don't want to keep the alias'. I don't normally use alias' because unless you are working with the SQL directly, the QBE view doesn't benefit by them. When I have to work with SQL directly, I use them because they shorten the string, reduce typing and jus make the text easier to read.

Did you mean Form rather than forum?

My confusion is that how would anyone in the forum know where to get the data? It looked like you were asking us how to copy data from some open form?

As I look at the two queries again, why would you just not add the expression from the second query (Switch(tbl_First.OilDue- Date() > 16, "GREEN", tbl_First.OilDue -Date() > 5, "AMBER", TRUE, "RED") AS RAGStatus) into the first? You already have tbl_First in the query. And the second query is not using any other tables.

If you are asking how to actually color the query, that is a different problem. Use conditional formatting based on the value of OilDue. You don't need the color words.

 

[SachAccess]

You addressed my aversion to underscores and square brackets which is neither here nor there but I appreciate it. If it doesn't bother you, then whatever. My aversion is personal. However, when you post sql and you have used long (especially offensive table names), just alias them. That makes the query easier to read and usually much shorter. You can easily do it using the QBE, then switch to SQL view and voila. Just remember to not save the query if you don't want to keep the alias'. I don't normally use alias' because unless you are working with the SQL directly, the QBE view doesn't benefit by them. When I have to work with SQL directly, I use them because they shorten the string, reduce typing and jus make the text easier to read.

My confusion is that how would anyone in the forum know where to get the data? It looked like you were asking us how to copy data from some open form?

As I look at the two queries again, why would you just not add the expression from the second query (Switch(tbl_First.OilDue- Date() > 16, "GREEN", tbl_First.OilDue -Date() > 5, "AMBER", TRUE, "RED") AS RAGStatus) into the first? You already have tbl_First in the query. And the second query is not using any other tables.

If you are asking how to actually color the query, that is a different problem. Use conditional formatting based on the value of OilDue. You don't need the color words.

Thanks a lot.
Please give time till tomorrow morning to revert with details.

Have a nice day ahead.