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

[Pat Hartman]

Did you substitute the query that included the calculation for the table name in the form's RecordSource? If you do, that will allow you to bind controls to the calculated fields. A dLookup() would not be necessary.

 

[NewUser76]

arnelgp,​

Thanks a lot! Your method works.
i was struggeling with access SQl to get it working. It is a bit different compared to MySQL
After a while I gave up and tried the Access gui method. (Create relation)
In the end it worked and when I looked for the SQL code it was like this:


SELECT Contacts.ID, Contacts.Company, Contacts.[Last Name], Contacts.[First Name], Contacts.[E-mail Address], Contacts.AppointmentDate, Contacts.ApplicationDate, Contacts.TotaalTijd, Contacts.[Job Title], Contacts.[Business Phone], Contacts.Address, Contacts.City, Contacts.[Country/Region], Contacts.[Web Page], Contacts.Notes, Contacts.Attachments, Contacts.Motivatie,

SollData_query.TotalDays, SollData_query.EndDate

FROM Contacts

INNER JOIN SollData_query ON Contacts.ID = SollData_query.ID;


After that it was just a matter of adapting the record source of the form to the new created 'Contacts_query'
And voila, I could simply choose TotalDate and EndDate in the record sources in the fields of my form.
Thanks for leading me to the answer

There is one more question I like to ask.

I like to add one more field in the form to give the date when an application is closed.
Now I have the total days an application is running in the field TotalDays.
I also like to have a full date of which the application is closed.
To have this I tried to alter the calculation query to something like this:

SELECT

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

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

IIf(IsNotNull(RejectionDate), Date() AS EndDate

FROM Contacts;

But i am not handy with IsNull or and IsNotNull.
The idea was to have another boolean to write the system date in EndDate only when
the RejectionDate has a Date. But it doesn't work. Give a syntax error for the 'IIf(IsNotNull(RejectionDate), Date() AS EndDate' part.

You are handy with access SQL.
How can I do a second boolean with the IIf.....structure in the same SQL line?

 

[NewUser76]

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;

 

[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.