locked
Correct Expression? RRS feed

  • Question

  • I need the correct expression to return the correct tax to be deducted from an employee on the right date according to a table.                                                                                                                                                                                    I have a tax table that may keep 15000 records. 9 Years of deductions that should be deducted from employees salaries, depending on their salary. Query q02PAYE have the following 7 fields, ID, DateFrom, DateTo, StrtAmt, EndAmt, Younger65, 65andOlder.

     Another query, q03SalariesCurrent include fields, Salarydte, EmplAge, Salary. The expression should calculate the following. IF EmplAge <65, it should return what it finds in field "Younger65". If EmplAge >=65 then return whatever it finds in 65andOlder. Other conditions Salarydate should be between DateFrom and DateTo, and Salary should be between StrAmt and EndAmt.

    Saturday, May 14, 2016 9:29 PM

Answers

  • One problem with your expression is that when you build the string for the criteria argument the date values are not being concatenated into the string as date literals, which must be delimited by the # character.  They must also be either in US short date format, or in an otherwise international unambiguous format.  I favour the ISO standard format for date notation of YYYY-MM-DD.  So your expression would be written like this:

    DLookup("[Younger65]","q02PAYE","[StrtAmt]<=" & [Salary] & " And [EndAmt]>=" & [Salary] & " And [DateFrom]<=#" & Format([SalaryDte],"yyyy-mm-dd") & "# And [DateTo]>=#" & Format([SalaryDte],"yyyy-mm-dd") & "#")

    I think it could be simplified, however:

    DLookup("[Younger65]","q02PAYE", [Salary] & " Between [StrtAmt] And [EndAmt] And #" & Format([SalaryDte],"yyyy-mm-dd") & "# Between [DateFrom] And [DateTo]")

    Ken Sheridan, Stafford, England

    Sunday, May 15, 2016 3:34 PM

All replies

  • 
    
    
    
    I tried in a calculated field in q03SalariesCurrent.        DLookup("[Younger65]","q02PAYE","[StrtAmt]<=" & [Salary] and "[EndAmt]>=" & [Salary] and "[DateFrom]<=" & [SalaryDte] and "[DateTo]>=" & [SalaryDte]).
    
    Saturday, May 14, 2016 10:46 PM
  • With all respect but did you try this yourself first or are you just wanting a free solution without effort on your part?

    Best regards, George

    Sunday, May 15, 2016 9:28 AM
  • George, I dont have a clue where you are from or involvement with this forum. There is a hint of disrespect here. At 53 years old one never knows how many hours you gonna live. During the past 6 months I spent more than 50% of any hours of effort to get to build a database that should have been build by programmers that never got the job done. 30 months ago my wife died, she was a programmer who didn't finnish what she started. I paid a fortune to people that didn't get the job done. If you or this forum wants to earn deserved money from me, just be clear about it. I do not know where or how I can pay people for the help they do give me. This weekend my mind is a bit numb because of being overworked. Yesterday I spent 10 hours trying to get to correct expression. I am a businessman and accountant, not a programmer. At my age I started and I have more than 300 training videos on my hard drive, having watched most of them. So I do not know what I could have done more to proof my effort, and I dont need to. It does not seem that you saw in my previous last line that I did effort to find it myself, and if no one helps me I will continue until I have the result. So with the same respect you offer, I still do not have the right expression, and I am right now putting in more effort but will not stop whether I pay for it or not. I trief iif expr, Dsum and Dlookup so far. What are the "silent" expectations on this forum? How can I pay people for their effort?  Regards. Hans

    Sunday, May 15, 2016 11:31 AM
  • You cannot vote on your own postWith all respect but did you try? Can you post some code?

    Best regards, George

    Sunday, May 15, 2016 11:36 AM
  • PAYE2: =(DLookup("[Younger65]","q02PAYE","[StrtAmt]<=" & [Salary] & " and [EndAmt]>=" & [Salary] & " and [DateFrom]<=" & [SalaryDte] & " and [DateTo]>=" & [SalaryDte]))

     I am a very keen learner, and I taught myself in Access to use design, but I can do very little in Macro or VBA or SQL. I will post the expressions I tried with IIF and Dsum in a short while. This is the Dlookup I tried and I thought it should work, but it seems I may have a format issue with the tax tables I downloaded and converted to excel and then imported.
    Sunday, May 15, 2016 1:37 PM
  • Hint: You only need the square brackets ([]) if the expression contains spaces or reserved characters.

    Hint: Separate a long winding expression with
    a) newlines to enhance reading and understanding (use & _ )
    b) parenthesis to enhance troubleshooting

    Hint: Never use long winding expressions.


    Best regards, George



    Sunday, May 15, 2016 2:13 PM
  • George. However the expression should look. Help if you can. The Dsum expression look the same as Dlookup. Is there something about my notes that you dont understand. I am sure experienced programmers should get it much easier than me.
    Sunday, May 15, 2016 2:24 PM
  • One problem with your expression is that when you build the string for the criteria argument the date values are not being concatenated into the string as date literals, which must be delimited by the # character.  They must also be either in US short date format, or in an otherwise international unambiguous format.  I favour the ISO standard format for date notation of YYYY-MM-DD.  So your expression would be written like this:

    DLookup("[Younger65]","q02PAYE","[StrtAmt]<=" & [Salary] & " And [EndAmt]>=" & [Salary] & " And [DateFrom]<=#" & Format([SalaryDte],"yyyy-mm-dd") & "# And [DateTo]>=#" & Format([SalaryDte],"yyyy-mm-dd") & "#")

    I think it could be simplified, however:

    DLookup("[Younger65]","q02PAYE", [Salary] & " Between [StrtAmt] And [EndAmt] And #" & Format([SalaryDte],"yyyy-mm-dd") & "# Between [DateFrom] And [DateTo]")

    Ken Sheridan, Stafford, England

    Sunday, May 15, 2016 3:34 PM
  • Hi Hans van Niekerk,

    According to your description, I suggest that you could refer to below link about how to create and use expressions in Microsoft Access databases.

    Create an expression

    Examples of expressions

    Monday, May 16, 2016 3:07 AM
  • Thanks Ken. I keep record of all these expressions. Study them and try to remember and do it right in future.
    Monday, May 16, 2016 7:25 AM
  • Thank you sir.
    Monday, May 16, 2016 7:28 AM
  • Hans -

    Come here every day. Look at the first page of posts. If one interests you, read a few posts. You'll learn a lot, especially who to pay attention to (they usually have accrued many 'points'). If you have the time and the knowledge, try to answer someone else's question. It works. 


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Tuesday, May 17, 2016 2:34 AM
  • Thanks Peter. I will. As you know TIME is not much of a variable. I do neglect my business a little bit, because I have an obsession to create the perfect application or Dbase myself in Access. It is already in use, and I am proud of what there is so far, even though I still have a long list of nice to have's which I want to add. I probably going to ask quite a few more questions on here, and I will see if possible I can answer some questions. If I had more time I would have used it for my journey on Access.

     Regards.
    Tuesday, May 17, 2016 6:11 AM
  • Hi Ken. I guess you will find my challenge easy for you. I put in effort but no success. Query1 "q03SalaryIncrease" fields Emp_ID, DateTo, Salary. Query2 "q01Employees" fields EmpID, LtstIncrdate.

    In query q01Employees I need an expression to return the latest salary from q03SalaryIncrease.

    I tried. Dlookup("[Salary]","q03SalaryIncrease","[Emp_ID]=" & [EmpID] And #"[DateTo]=[LtstIncrDate]). I think my error comes after the "and".

    Regards.

    Hans

    Thursday, May 19, 2016 8:37 AM
  • Try this:

    SELECT q01Employees.*
    FROM q01Employees INNER JOIN q03SalaryIncrease
    ON q01Employees.EmpID = q03SalaryIncrease.Emp_ID
    AND q01Employees.DateTo = q03SalaryIncrease.LtstIncrDate;

    Ken Sheridan, Stafford, England

    Thursday, May 19, 2016 12:34 PM
  • I copied it. It may be that I had to swop around the fields in your last line. DateTo and LstIncrDate when I type it exactly as it is in my tables. It still returns "Type Mismatch in expression". My dbase is on Onedrive. Email address jdvn63@gmail.com Password Hans1963.
    Thursday, May 19, 2016 12:57 PM
  • I put in effort to find the correct criteria part when in an expression two dates are compared. Could you help me after the "and". If I want to say and DateTo = LstIncrDate.
    Thursday, May 19, 2016 1:16 PM
  • I tried. Dlookup("[Salary]","q03SalaryIncrease","[Emp_ID]=" & [EmpID] And #"[DateTo]=[LtstIncrDate]). I think my error comes after the "and".

    Hi Hans,

    I never use DLookup, but that is a different story.

    I would typically write the above as:

            Dlookup("[Salary]","q03SalaryIncrease","Emp_ID = " & EmpID & " And DateTo = " & As_date(LtstIncrDate))

    As_date is a small function that converts the date to ISO format, and surrounds the result with "#". That way you never have to bother agin about how to include dates in a SQL-string.

    An example of As_date is as follows, but you can extend it at will depending on your needs.

    Function As_date(cur_datum As Variant) As String
      ' ISO-format:  yyyy-mm-dd
      If (IsNull(cur_datum)) Then
        As_date = "Null"
      Else
        As_date = "#" & Format(cur_datum, "yyyy-mm-dd") & "#"
      End If
    End Function
    

    Imb.

    Thursday, May 19, 2016 2:29 PM
  • Hi, thank you. I copied your suggestion. It returned "Undefined function 'As_Date' in expression. My dbase is on Onedrive as described above.
    Thursday, May 19, 2016 2:42 PM
  • Hi, thank you. I copied your suggestion. It returned "Undefined function 'As_Date' in expression. My dbase is on Onedrive as described above.

    Hi Hans,

    You can place the As_date function in a general module.

    I fear I am not able to see your database, as I am still using the A2003 version.

    Imb.

    Friday, May 20, 2016 5:32 AM
  • >>> Query1 "q03SalaryIncrease" fields Emp_ID, DateTo, Salary. Query2 "q01Employees" fields EmpID, LtstIncrdate.

    In query q01Employees I need an expression to return the latest salary from q03SalaryIncrease.

    I tried. Dlookup("[Salary]","q03SalaryIncrease","[Emp_ID]=" & [EmpID] And #"[DateTo]=[LtstIncrDate]). I think my error comes after the "and".<<<

    According to your description, you can use the DMin and DMax functions to determine the minimum and maximum values in a specified set of records (a domain). Use the DMin and DMax functions in a Visual Basic for Applications (VBA) module, a macro, a query expression, or a calculated control. You could try like below:

    DLookup("[Salary]", "q03SalaryIncrease", "[Emp_ID]= #" & [EmpID] And "[DateTo]= " & DMax("[LtstIncrDate]", "q01Employees", "[Emp_ID]=" & [EmpID]) & "#")

    For more information, click here to refer about DMin, DMax Functions


    Friday, May 20, 2016 9:01 AM