Help new User. Calculation: Field a + field b = field c (and express in all field c records in table)

[theDBguy]

This didn't work either:

SELECT

ID, ApplicationDate, Company, [Job Title], RejectionDate,
IIf(IsNull(RejectionDate), DateDiff("d",ApplicationDate,Date()),Null) AS TotalDays,
IIf(IsNull(RejectionDate), DateDiff("d",ApplicationDate,RejectionDate) AS TotalDaysComplete,
Date() AS EndDate

FROM Contacts;

Hi. See if this works.

SELECT ID, ApplicationDate, Company, [Job Title], RejectionDate,
    IIf(IsNull(RejectionDate), DateDiff("d", ApplicationDate, Date()), Null) AS TotalDays,
    IIf(IsNull(RejectionDate), Null, DateDiff("d", ApplicationDate, RejectionDate) AS TotalDaysComplete,
    Date() AS EndDate
FROM Contacts

Hope that helps...

 

[NewUser76]

Thanks for the help. No it doesn't work.
The syntax error error gives the same line:
"IIf(IsNull(RejectionDate), Null, DateDiff("d", ApplicationDate, RejectionDate) AS TotalDaysComplete,Date() AS EndDate"

 

[NewUser76]

Nice one. "This recordset is not updateable"........So Now i did the whole thing for nothing. The form can't be edited.....
It Seems my Contacts_query can't be modified. I don't understand, but i can't use the form to modify fields.
I didn't try to update the calculated columns. Just the other columns. Access manuals give the information
that a query can be modified via form. Than what is the problem I wonder.

Anyone knows that one?


Update:

I already solved this major goal blocker. By experimenting and a little luck I simply removed
the contacts_query that joined to the SollData_query that did the calculation.
Instead of the join construction, I copied the whole selection to the Calculated query.
So a join was not needed any longer., 2 became 1 Weird. I found that the problem of writing was in the contacts_query only.
In the calculated query I could modify by typing in the fields, so i knew only the 'contacts_query' had a updateable issue.
After coversion all to the 'SollData_query' query I could edit my form again.
Pfew....That's good. Now only that last 'EndDate' thing.

Thank you all so far.

 

[theDBguy]

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[NewUser76]

The question of a 2th action in the SQL boolean is still open. If someone knows how to use the argument so that it will set the enddate in another field it would be great.


ID, ApplicationDate, Company, [Job Title], RejectionDate,

IIf(IsNull(RejectionDate), DateDiff("d",ApplicationDate,Date()),Null) AS TotalDays,
DateDiff("d",ApplicationDate,RejectionDate) AS TotalDaysComplete ...AND SET ENDDATE IN ENDDATE FIELD as another action in the same 'else'..

FROM Contacts;

arnelgp, would you not know this?​

Must I create another post for this?

 

[NewUser76]

So, i found it is easier to test sql statements in separated queries to test if they work basically.
If anyone is interested, I found the way to calculate the end date in the same sql query as well.

select [all columns here that you need] ,

IIf(IsNull(RejectionDate),DateDiff("d",ApplicationDate,Date()),Null) AS TotalDays,
DateDiff("d",ApplicationDate,RejectionDate) AS TotalDaysComplete,
IIF(IsNull(RejectionDate), '', Date()) AS EndDate

FROM Contacts;

This did it for me. I am not sure yet if the EndDate will keep being stored
with the today's date. Let's see what happens.
Anyhow thanks for all your help so far. My original question was answered thanks guys,
it worked.

 

[theDBguy]

IIF(IsNull(RejectionDate), '', Date()) AS EndDate

Hi. Rather than that, would this work as well?

Nz([RejectionDate], Date()) AS EndDate

Just curious...

 

[NewUser76]

Hi theDBguy,

It gives the date in all rows, and it ignores the RejectionDate valued column.
In the RejectionDate column a date of 7-4-2021 is shown. 6 days ago.
Basically it is doing the opposite sort of.

Just a question left. How to store date() permanently in a column?
(So it will not change again tomorrow....)

 

[theDBguy]

Hi theDBguy,

It gives the date in all rows, and it ignores the RejectionDate valued column.
In the RejectionDate column a date of 7-4-2021 is shown. 6 days ago.
Basically it is doing the opposite sort of.

Just a question left. How to store date() permanently in a column?
(So it will not change again tomorrow....)

Hi. To permanently store today's date in a table, you can use an UPDATE query.

 

[NewUser76]

Thanks, how do you mean this?

UPDATE table SET newvalue WHERE criteria;

An update is to modify data in the column no?
What I want is to set a day with sql for a column today, and keep it unchanged tomorrow.
I think in my sql: "IIF(IsNull(RejectionDate), '', Date()) AS EndDate"
It will check if rejectiondate has a value, if yes it will go to 'else' and set the date with 'Date()' in EndDate.
The problem is, it will do the same and store tommorrows date a day later instead.
I want to store todays date and keep it permanent.