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

NewUser76

New member
Local time
Today, 17:14
Joined
Apr 9, 2021
Messages
14
Hi Guys,

Thank for allowing me on the forum.
I am just a new user in access. It is silly, but I never used it really much.
I was hoping to find some experienced forum members here that could help me further.
What I try to do is very simple. Just i don't know the best road to get there.

situation:
I have created a table and a form. Mainly it is a database
to organise my job applications a bit. Mainly I just took the template for contacts that access 2016 offers
and I modified it to my needs. Also I was forced to start understanding the basic idea of how access works and this is only good.

what I did: I have a table and a form with fields. (Note here: I use the 'DATE' option in all field properties in the table where a Date is stored)
I have a 'StartDate' field that has the date on which I've sent the job application to a certain contact person.
Further, I have a 'TotalDays' field that is meant to express the total of (days/weeks/months) an application
is running. The idea was to be able to filter quickly in the table to collect the applications that need attention.

ok. that's the basic idea.


My question:

i need to do a very simple calculation. And I already found some possibilities on the net.
But none of them gives all steps to take for the beginning access user.
I tried by experimenting, but I am not getting there.

I want to calculate the running time by a formula like: (Current timestamp) - (StartDate) = (TotalDays)
where (and this is the part where I keep in the orbit of a satellite around the core of Access)
the TotalDays result of the formula is stored in the TotalDays field in the table for all records.
So, to be clear, I am not looking for a SQL formula where the result is only displayed in the form field only.

I looked trough forum idea's in visual basic, I looked in people writing about macro's, I looked into SQL queries,
all pointing is a certain syntax and a direction, but I can't find an answer for my needs.
All of those solutions assume knowledge about the access application in general.
That's where i don't see the whole.

Is a visual basic approach, a macro, or a query needed here to get this very simple job done?

If I would find a solution, there is 1 more thing I like to program as an addon:
2 extra fields would be required: 'CloseDate'. and 'EndDays'

Than a boolean would be required ,with something like this:

If (CloseDate <> empty or null)

{ 1.copy value Totaldays to the EndDays field in the table for all records.
2. reset the TotalDays field for all records with a CloseDate to null }



In fact this last part is not essential needed, but it is elegant.
These are the very humble demands I need. And I feel silly not to be able to sort it out.
Can Anyone help this poor Access starter?

Thanks for any help in advantage.
 
Last edited:

Jon

Access World Site Owner
Staff member
Local time
Today, 16:14
Joined
Sep 28, 1999
Messages
7,304
Welcome to the forums! We are the most active Microsoft Access community on the internet by far, with posts going back over 20 years!

To get started, I highly recommend you read the post below. It contains important information for all new users to this forum.

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We look forward to having you around here, learning stuff and having fun!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:14
Joined
May 7, 2009
Messages
19,169
firstly you do not need TotalDays and EndDate in your Table.
they can be a Calculated Column on a Query:

Select StartDate, JobApplicationTitle,
CloseDate,
IIf(IsNull(CloseDate), DateDiff("d", StartDate, Date()), Null) As TotalDays,
StartDate + DateDiff("d", StartDate, CloseDate) As EndDate
From yourTable;
 
Last edited:

NewUser76

New member
Local time
Today, 17:14
Joined
Apr 9, 2021
Messages
14
Sorry Jon, for posting my question here. Could you help me to copy the post to the required section then?

Arnelgp, thanks very much for your quick sql-boolean. I don't understand it quite, but that's me.
DateDiff("d", StartDate, Date()), Null) => will this not give a negative value since you do: startdate - current date?
same for the other DateDiff.

What about the reset for TotalDays to 0?

Most important question;I don't know the steps to use this in my access table or query and activate the results of it in my form field.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:14
Joined
May 7, 2009
Messages
19,169
Select StartDate, JobApplicationTitle,
CloseDate,
IIf(IsNull(CloseDate), DateDiff("d", StartDate, Date()), 0) As TotalDays,
StartDate + DateDiff("d", StartDate, CloseDate) As EndDate
From yourTable;
 

cheekybuddha

AWF VIP
Local time
Today, 16:14
Joined
Jul 21, 2014
Messages
2,237
Hi,

Try it like:
SQL:
SELECT
  JobApplicationTitle,
  StartDate,
  CloseDate,
  DateDiff("d", StartDate, IIf(IsNull(CloseDate), Now(), CloseDate)) As TotalDays
FROM yourTable;
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:14
Joined
May 7, 2009
Messages
19,169
to copy the post to the required section then
i am no, moderator.

so it is EndDays


Select StartDate, JobApplicationTitle,
CloseDate,
IIf(IsNull(CloseDate), DateDiff("d", StartDate, Date()), 0) As TotalDays,
DateDiff("d", StartDate, CloseDate) As EndDate
From yourTable;
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:14
Joined
May 7, 2009
Messages
19,169
demo. see Query1
 

Attachments

  • myApplications.accdb
    436 KB · Views: 462

NewUser76

New member
Local time
Today, 17:14
Joined
Apr 9, 2021
Messages
14

thanks guys for all answers.​

Arnelgp, I was wrong. I tried it out by opening a query and simple use your sql query and let it run the selection under "query"
it worked perfect. You were right. Just virtualise 'TotalDays' and 'EndDate' in a 'calculated column' in a query, gives all i need.
It makes the 'reset to 0' idea i had total unnecessary.
It is my lack of knowledge. Why do you need to filter the table for 'TotalDays' or 'EndDate' if you can filter on the query rows?
I will now go trough the answers to understand how to get the results in my form. I might have another ignorent questions. Thanks for your patience.
 

NewUser76

New member
Local time
Today, 17:14
Joined
Apr 9, 2021
Messages
14
So, i still have one problem in my access database. I have one form I meant to use as a sort of "application gui"
This form has all fields based on 1 control source which is the table where I started.
I want to change one of the fields called 'EndDate' to take data from the new query instead of the table.
So, I changed the 'control source' of that field to =[SollData_query]![TotalDays] to have it referring to the query instead of the table.
I thought this was a logical next step to get the calculated column data field in the form.
Apparently, access doesn't work like that, cause the result in the new configured 'EndDate' field is "#Name?" now for every record.

How to manipulate the form to achieve my goal? It seems so logical to assume you can simply add a link to the query as the fields 'control source'
to get the results in the existing form.

There is much to learn. It costs me hours.....
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:14
Joined
May 21, 2018
Messages
8,463
If you have a form that is based on a recordsource and you need to pull a value that is not contained in the recordsource you can use a domain aggregate function. So you can have a calculated control to display that value
control source: =dlookup("totalDays","Sol_Data_query")

You do not say if there is a criteria, because that will just return the first record in Sol_data_query.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:14
Joined
Oct 29, 2018
Messages
21,358
Hi @NewUser76. Welcome to AWF!

I moved your thread out of the Introduction forum. Please let us know if you want it somewhere else other than the Queries forum.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:14
Joined
Feb 19, 2002
Messages
42,970
Use the query as the RecordSource for the form. That way you will be able to see the calculated fields on the form.

@cheekybuddha,
Do NOT use Now() when you really mean Date(). Introducing time into a field where only date is required just leads to confusion.
 

cheekybuddha

AWF VIP
Local time
Today, 16:14
Joined
Jul 21, 2014
Messages
2,237
@Pat Hartman
From the OP
I want to calculate the running time by a formula like: (Current timestamp) - (StartDate) = (TotalDays)
I chose Now() specifically.

But no need to get your knickers in a twist, Date() contains a time element anyway even if it has a value of .0.

But you're right, it's all moot anyway since using DateDiff("d") will return the same in either instance.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:14
Joined
Feb 19, 2002
Messages
42,970
My knickers aren't in a twist. I commented because newbe's don't always understand the difference and when they end up with "time" in a date field, they don't understand why their queries don't work. So the point is - if you don't need time of day, use Date(). If you do need the time, use Now(). If the two functions produced the same result, there would be no need for two of them. Don't use a screwdriver when you need a hammer.
 

NewUser76

New member
Local time
Today, 17:14
Joined
Apr 9, 2021
Messages
14
sorry Guys, I was still refreshing the old location of this post yesterday, so I didn't see the help posts coming in the other night.

Thanks for all your time.

changing control source to =DLookup(TotalDays, SollData_query)
gives an 'invalid syntax' error. I double checked the names of the query and column. They are correct.

Looking at this source:
support.microsoft.com/en-us/office/dlookup-function-8896cb03-e31f-45d1-86db-bed10dca5937

I can see what DLookup does in access. Still I do not exactly get the idea in my example.
Found in another source:

"....The DLookup function returns a single field value based on the information specified in criteria. Although criteria is an optional argument, if you don't supply a value for criteria, the DLookup function returns a random value in the domain."

So, if i understand right it makes it unusable for my purpose. I don't want to lookup a single hard individual field (criteria)
The fields in the column TotalDays should be matched exact for every single record in the form. (With the connected table and corresponding rows)
Like a SQL join works you could say.
I don't understand why it is so hard to use another 'domain' in the same form as being just another field.
Why can't I just open control source in properties and simply browse to the query and field to get it running in the form?

It must be my poor understanding.


update:

The right context for dlookup in control source was:
=DLookUp("TotalDays";"SollData_query")

At least it stopped the annoying error. But it doesn't work.
I think is logical. DLookUp only works when a search value is given in the end, like ;"Criteria= 'string'"
 
Last edited:

mike60smart

Registered User.
Local time
Today, 16:14
Joined
Aug 6, 2017
Messages
1,899
Hi
The DLookup does not work because it should be as shown in Post #11 and below:-

=dlookup("totalDays","Sol_Data_query")
 

NewUser76

New member
Local time
Today, 17:14
Joined
Apr 9, 2021
Messages
14
no really not. that's what I tried first. It gives a "The expression you entered contains invalid syntax" error instandly.
That's why I start searching the error on the net and i found =DLookUp("TotalDays";"SollData_query")
If you mean the domain and field name I used earlier, this was my mistake. In the example I created earlier in this post,
I used wrong names. TotalDays and SollData_query are the correct ones.
 
Last edited:

mike60smart

Registered User.
Local time
Today, 16:14
Joined
Aug 6, 2017
Messages
1,899
DLookup normally has a criteria section as shown below:-

=DLookUp("PreStartTaskID","tblCandidateAdminPreStartTasks","CandidateID = " & [CandidateID])
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:14
Joined
May 7, 2009
Messages
19,169
NewUser76, do you have Autonumber field in your table?
add one if you don't.
also on the query you made, add also the Autonumber field.

create a new Query from your Table Joining it to your Query (join by the Autonumber field).
select all the field from your table to be displayed on the query.
select the totalDays and EndDays to be displayed in the new query.

use this new query as recordsource of your form.
you do not need to use Dlookup() just bind the TotalsDays and EndDays to a textbox on your form.

EDIT:
forget about creating new query, just add the autonumber to the first query.
just tested it and the query is updateable.
 
Last edited:

Users who are viewing this thread

Top Bottom