Get last field value /skip null fields

[black123]

Hi


I'm new to the forum.
first, thanks for having this forum to help who's in need.

Please take it easy on me, I'm not professional coder and my Access skills are rusty

I have a forum where user input date range and the forum auto input/generate dates in fields.
There are 45 fields.

User will input vacations took in those year, the program will auto calculate the result.

I want the program to automatically copy the last generated result in another field.

How can I achieve that?


I'd try something like "if field is null ... else ... "but I don't think that is practical.
Thanks in advance.
Picture for refence :


Sorry if there is similar question posted before, I tried searching for something that might help

[ranman256]

vYR=dmax(“[year]”,”tableqry”,whereclause)
Txtbox= dlookup(“[id]”,”tableqry”,”[year]=‘“ & vYR & “‘“ & whereClause)

[ssanfu]

@black123,
Welcome to the forum....

Be aware that "Left" and "Year" are reserved words in Access and shouldn't be used for object names.

I'd try something like "if field is null ... else ... "but I don't think that is practical.

That is SQL syntax. In VBA, you would have to use the function "IsNull()" .



Not sure what the image is .... it looks like an Excel spreadsheet.

If it is an Access form, is the form record source a table or query? It makes a difference - a table has no order, so saying "last field value", the "Last" value can change without warning.

I ALWAYS use queries for a form record source because I can limit/choose the number of fields included (smaller result set) and I can sort on the field(s) I want.

There are several different ways to get the "last" value and put it into a text box control on a form. But right now there is not enough info to suggest specific code.

[black123]

@ranman256

Thank you.
"Year" is just a label not a table's field, I've each year stored individually, as each year contain it's own data of vacation, balance and left that need to be used when generating report.


@ssanfu

Thank you, I'm aware of that, they are just label names

I tried working with query, but it a hit or miss with me, can't get it to work right at all.

The page is Form, my manager suggested we make it as simple as possible to avoid user's errors,
not many smart PC users where I work.
We're just trying to make a simple easy program to help us getting work done faster and avoid user's manual calculations.

I've uploaded a copy of what I made so far, still wip.
Some pages were removed to compress size, please ignore missing links and errors.
Edit: Nvm, still too big to upload, I uploaded it on another site https://easyupload.io/rsv7h2

And please don't laugh at me, last time I used Access was 6 years ago in collage and forgot almost everything .


Thank you so much everyone!

[black123]

For anyone who may be having similar issue, this is what I did.
Starting from the first field to last.

If not isnull([Field1]) Then [Total] = [field1]
If not isnull([Field2]) Then [Total] = [field2]

and so on...

It do the trick for me
I know it's not professional but it may help someone out figuring things on their own or something.
Thanks again!