Expression with criteria comparing two numbers and a local date between two foreign dates.

[Perfac]

Someone put in effort to help me (thank you) and I used quality time but no success yet, but not ready to ever give up. I place a new thread for there are changes to my previous thread on this subject.

1. My system stores every months salaries successfully, every salary slip makes up one record. If a business has 50 employees, there will be 50 records stored after the first month, 100 after the second, 150 after the third of course.
2. For two years no issue to calculate the “Year to Date” numbers for salary slips.
3. I want to set up the system not to store records in a couple of sections, if I can learn how to set up salaries to calculate any historical month’s “Year to Date” numbers it will be great. It can be done.
4. I use the same expression a couple of places elsewhere and it works, unable this far to find, why not here.
5. Table t03SalNWgeReview as on the image stores 17 records, of 11 employees employed. If a salary never changes that record will be actual. When it changes the dates must cover all the time the employee is working. It is common that increases or changes of salary happens all the time.
6. In query “q04SalaryProcessing06” I achieved to filter the employees that worked for the months since the beginning of the financial year. If the financial year started 1 March and the salary date is now 31 October it is month 8 of 12. If an employee worked for three months, his name will be listed 3 times on this list.
7. My story here maybe long, but the short is I need an expression to return the salary from t03SalNWgeReview in q04SalaryProcessing06 for the month worked. The expression here should do it. I assumed it was the formatting of the dates, I changed the information to come from tables, where there are no formatting on the dates like the other places where this expression works, but something is still wrong.

Salary069e: DLookUp("SalOrWgeRate071","t03SalNWgeReview","Empl oy_ID071 =" & [Employ_ID069e] And " & Format([LstDayOfMnth069e],yyyy/mm/dd) & " Between [StartDate071] And [Enddate071a]). This expression should be placed in q04Salaryprocessing06, it is not there on the image.

[CJ_London]

something is still wrong.

how do you know it is wrong? wrong value? error? something else?

as mentioned on many occasions in the past, you need to use the # characters to tell sql that the text between them is to be treated as a date

And #" & Format([LstDayOfMnth069e],yyyy/mm/dd) & "# Between

[Perfac]

Thank you.

[CJ_London]

just realised you have not put the format into quotes


And #" & Format([LstDayOfMnth069e],"yyyy/mm/dd") & "# Between

also, the usual format is mm/dd/yyyy or yyyy-mm-dd if you find you are coming up with the wrong date

[Perfac]

Sorry for the hassles but I must keep on trying.

[CJ_London]

I was just focused on your date

you are also missing a & " here

"Empl oy_ID071 =" & [Employ_ID069e] & " And " &

[Perfac]

I have to add 10 characters in this thread.

[CJ_London]

by now you should be able to work out what the error message means - you need a " after [Enddate071a]

[Perfac]

Ajax. I thank you for your patience. I was 52, five years ago starting to learn Access, very limited real programming experience. Downloaded and watched more than 500 tutorial videos. Of course a lot of DSum and Dlookup expressions are needed in a system covering all aspects of a medium business, close to 2000 objects. My son Ruben has a 3 year college certificate in programming, but not fully around anymore. I listed most of these expressions, and I think it is common even for the clever ones to miss things sometimes, even the obvious. I will study more about DLookup and variations when and where the syntaxes should be, after all the work I am not well informed enough.

However. In post no1 here., You will see in the second image there are 4 records for employee number ENT0002. This is just test data. The start salary was 20 000 on 1 Jan 18. A review on 1Nov18 increased to 21000, and the fourth review brought it to 25000. I was excited after your last post to see a number in the field. But it returns the latest salary of 25000 in all the fields which is wrong. I have not had my best look at what the problem may be yet, but I will.

[CJ_London]

Only thing I can see is you have not included the correction identified in post #4

[Perfac]

My wife died a few years ago, sold my sizable business then. 3 of 4 children lives on the other side of the planet. Just have to deal with the changes. I enjoy spending most hours on Access. In 1980 I was national chess champion, maybe it means I can concentrate a bit, or is it could concentrate? The expression works well now, thank you very much. I will probably not battle with a same challenge again, and I will find more tutorials on the variations of criteria in Dlookup.

[CJ_London]

As I am sure you have been made aware using domain functions, particularly in queries, is not recommended because a) they are slow, b) they are high maintenance and c) they are impossible to upsize to sql server or equivalent. Use them occasionally in vba code or as an unbound controlsource on a form or report if required and once in a blue moon for queries. Instead you would use joins in a query

But sounds like you have gone too far down your chosen route to turn back and start again.

Good luck with your project

[Perfac]

If "Covid" does not intervene too much I may soon start a project importing second hand cars from Japan and the East. I have done that for 18 years, you may google www.Earnacar.co.za, Africa can mostly only afford those older cars. Maybe you have 20 000 pounds, I know how to make it a billion, there is 100% surety. Eventually 100 employees may work there. My son will then take over this development, he is responsible for more than 50% of it so far. From the beginning in 2017 I learnt from you and others what you say here. From the start I was paranoid not to name any two fields the same, or leave a space in the name. Wherever we could use joins we did. The domain functions calculating PAYE in my salary table, and wages table make my system slow, but we don't have an idea yet other than a function. But this thread is just another example where I do not see how otherwise than a domain function in a query. We use append queries expansively to create new tables for joining purposes. Wherever you see a table name in my communication starting with an X it is a table that was appended. Ruben already discussed changing stuff we haven't done best two years ago.

No we have not gone too far down our chosen route. Since last year January two smaller businesses are using our development, and we regularly upgrade and fix things, but those entrepreneurs are not too unhappy, there are many things our system do that I never found other systems doing. I would have expected some of that from Pastel, Quickbooks, Xero or others, but they do not have the intention to provide what I as a 30 years businessman wanted. There is obviously software around that I don't know about. I hired programmers that failed mainly because of time. They guessed they can do this in 3 months; NO.

You mention transferring to SQL server. Ruben and me started with that, since an important party was interested in buying in but they were reluctant for they want it to be fully Web based. I do not understand all, or have experience on complete "Web based". Local networking looks like no problem to Ruben and me, as well as VPN or for a business with branches to connect to head office through internet, to the main Back end of Access. But it bothers the back of my mind that I will want what Barry wants. All equipment Cell Phones, Tablets should from anywhere have access to our program. If never, it is OK, but it is on my mind. Maybe you should inform me if you understand me and that is what you may do. If we fall short we may want to find someone.

[CJ_London]

for web based solutions, but using access for the front end, you need to use a service like terminal server or citrix, but it is not free - probably around 600 rand/user/month.

Or for single user applications, consider something like team viewer or jump desktop. I use these myself so I can open access apps on my computer in the office from my phone or laptop.