Page 3 of 4 FirstFirst 1234 LastLast
Results 31 to 45 of 53
  1. #31
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    I am in South Africa and owned three sizable businesses over 30 years. My preferred format of date is dd-MMM-yyyy. Someone on a forum suggested that expression to me some months ago and it still works, I do not fix what is not broken. I will be happy if we do not have to format the date. Do we have to? The aim of this expression is to return the VatRate percentage, the date will not be seen. I watched 500 videos and spent 11 months hour by hour, I am a qualified accountant but not well enough trained to understand this expression. I didn't make a choice on the format, I used help, and I will not give up I have to get this right. It can not be this difficult, can it?

  2. #32
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Corrected expression from post 24:

    =Nz(DLookup("VatRate01", "t02VatRate", "VatCode01=" & VatcodePurchases02 & " And " & Format(DocumentDate10, "yyyy-mm-dd") & " Between Format([StartDate05], 'yyyy-mm-dd') And Format([EndDate02], 'yyyy-mm-dd')"),0)

    Tested and works for me. But again, my dates are saved in Access default structure and my system is U.S. date.

    Did you read Allen Browne article referenced earlier?

    Did a little test in VBA Immediate Window:

    ?IsDate("2017-Dec-01")
    True

    That expression in query also returns True.

    If your saved dates represent structure of DD-MM-YYYY, then it serves no purpose for me to download you db as my system will read those date values as MM-DD-YYYY.

    If you have a query that does work correctly with your date fields, please post it.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #33
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    My Windows7 date setup for shortdate dd-MMM-yyyy. Whatever the format need to be as long as this expression return the VatRate. I copied your latest suggestion. See image for error. I will post to you the one that works.

  4. #34
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Click image for larger version. 

Name:	June07.png 
Views:	14 
Size:	179.5 KB 
ID:	33583Click image for larger version. 

Name:	t02VatRateDatasheet.png 
Views:	16 
Size:	126.0 KB 
ID:	33584

  5. #35
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    PayeBelow65: Nz(DLookUp("[Younger65]","t03PAYE",[Salary02] & " Between [StartAmount01] And [EndAmount01] And #" & Format([DocumentDate10],"yyyy-mm-dd") & "# Between [StartDate05] And [EndDate02]"),0)

    This expression work in 4 different places.
    Click image for larger version. 

Name:	June07 01.png 
Views:	15 
Size:	71.1 KB 
ID:	33585Click image for larger version. 

Name:	June07 02.png 
Views:	14 
Size:	67.3 KB 
ID:	33586

  6. #36
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Don't see how that error has anything to do with the DLookup() expression. You should post query SQL statement.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #37
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    The query above
    SELECT t01Employee.EmployID, t03MonthlySalarySummary.DocumentDate10 AS SalaryDate01, t01CombinedEntity.EntityName01 AS EmployeeName04, t01Employee.SalaryType, q03SalaryReview.Salary02, Format([paye04],"Fixed") AS Paye03, IIf([iAge]<65,[PayeBelow65],IIf([iAge]>=65 And [iAge]<75,[PayeBetween65and75],[PayeAbove74])) AS Paye04, IIf(([Salary02]*[UifRate01])>[UifMaxRate01],[UifMaxRate01],[Salary02]*[UifRate01]) AS Uif, [Salary02]*[PensFndRate01] AS PensFund, [Salary02]*[MediAidRate01] AS MedAid, q03SalaryReview.OtherDeductions03, [Salary02]-[Paye04]-[PensFund]-[MedAid]-[Uif]-[OtherDeductions03] AS NetSalary, ([DocumentDate10]-[DateOfBirth01])/365.25 AS iAge, Nz(DLookUp("[Younger65]","t03PAYE",[Salary02] & " Between [StartAmount01] And [EndAmount01] And #" & Format([DocumentDate10],"yyyy/mm/dd") & "# Between [StartDate05] And [EndDate02]"),0) AS PayeBelow65, Nz(DLookUp("[65to74]","t03PAYE",[Salary02] & " Between [StartAmount01] And [EndAmount01] And #" & Format([DocumentDate10],"dd/mm/yyyy") & "# Between [StartDate05] And [EndDate02]"),0) AS PayeBetween65and75, Nz(DLookUp("[75andOver]","t03PAYE",[Salary02] & " Between [StartAmount01] And [EndAmount01] And #" & Format([DocumentDate10],"yyyy/mm/dd") & "# Between [StartDate05] And [EndDate02]"),0) AS PayeAbove74
    FROM q04SalaryReview01 INNER JOIN (q03SalaryReview INNER JOIN ((t01Employee INNER JOIN t01CombinedEntity ON t01Employee.EmployID = t01CombinedEntity.CmbEntID) LEFT JOIN t03MonthlySalarySummary ON t01Employee.SlSmMn_ID01 = t03MonthlySalarySummary.SlSmMnID) ON q03SalaryReview.Employ_ID06 = t01Employee.EmployID) ON (q04SalaryReview01.StartDate_Employ_ID06 = q03SalaryReview.Employ_ID06) AND (q04SalaryReview01.StartDate07 = q03SalaryReview.StartDate07)
    WHERE (((t01Employee.SalaryType)="Monthly"));

    Click image for larger version. 

Name:	June07 03.png 
Views:	14 
Size:	77.1 KB 
ID:	33587

  8. #38
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    SELECT t02InvPurchaseSub.InPuSuID, "INP" & " " & [InvPch_ID01] AS DocNum04, t02InvPurchaseSub.InvPch_ID01, t02InvPurchaseSub.LdgAcc_ID03, 11 AS VatLdgAccNo03, 63 AS VatSubLdgAccNo02, t02InvPurchaseSub.CmbEnt_ID04, q04CombinedEntity05.EntityName01 AS Description05, t02InvPurchaseSub.VatRte_ID01, t02LedgerAccount.LedgerAccNum01, t01CombinedEntity.Check011, q04CombinedEntity05.VatClassification01, IIf([t01combinedentity.Check011]=-1,1,0) AS Vat01, IIf([q04CombinedEntity05].[VatClassification01]=1 And [Vat01]=1,1,0) AS Vat05, [VatRate01]*[Vat05] AS VatRate03, t02InvPurchaseSub.Quantity02, t02InvPurchaseSub.UnitPrice05, [Debit02]-[Vat06] AS ExclVat01, [Debit02]/(1+[VatRate03])*[Vatrate03] AS Vat06, t02InvPurchaseSub.Debit02, t02InvPurchaseSub.Credit01, t02LedgerAccount.LedgerAccDescription01, t02InvPurchase.DocumentDate01, t01Units.Unit01, q04CombinedEntity05.LtstPchPrc01, t01CombinedEntity.EntityName01 AS SupplierName04, q04CombinedEntity05.VatCodePurchases02, Nz(DLookUp("VatRate01","t02VatRate","VatCode01=" & [VatcodePurchases02] & " And " & Format([DocumentDate10],"yyyy/mm/dd") & " Between Format([StartDate05], 'yyyy-mm-dd') And Format([EndDate02], 'yyyy-mm-dd')"),0) AS Vat99
    FROM (((((t02InvPurchaseSub INNER JOIN t02LedgerAccount ON t02InvPurchaseSub.LdgAcc_ID03 = t02LedgerAccount.LdgAccID) LEFT JOIN t02InvPurchase ON t02InvPurchaseSub.InvPch_ID01 = t02InvPurchase.InvPchID) LEFT JOIN t01CombinedEntity ON t02InvPurchase.CmbEnt_ID15 = t01CombinedEntity.CmbEntID) INNER JOIN q02Vatrate ON t02InvPurchaseSub.VatRte_ID01 = q02Vatrate.VatRteID) INNER JOIN q04CombinedEntity05 ON t02InvPurchaseSub.CmbEnt_ID04 = q04CombinedEntity05.CmbEntID) INNER JOIN t01Units ON q04CombinedEntity05.Unt_ID02 = t01Units.UntID;

  9. #39
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I am in South Africa and owned three sizable businesses over 30 years. My preferred format of date is dd-MMM-yyyy. Someone on a forum suggested that expression to me some months ago and it still works, I do not fix what is not broken. I will be happy if we do not have to format the date. Do we have to?
    I have also owned businesses and am also an accountant neither of which is relevant to the problem, I also have over 20 years experience of Access and over 30 on databases.

    I will be happy if we do not have to format the date. Do we have to?
    Fact: dates are stored as numbers, not the format you see
    Fact: format as a function returns a string
    Fact: the format property displays as requested by the format property, the underlying value remains a number (note if the date value is taken from a form textbox, it will be treated as text)
    Fact: the # chars tells SQL that the string between the two # chars is to be interpreted as a date
    Fact: unless a date is presented unambiguously SQL will expect a date string in the format mm/dd/yyyy

    You say 'My preferred format of date is dd-MMM-yyyy' but your code is using 'yyyy-mm-dd' and as explained in post #21, this is ambiguous.

    if you wish, you can use the CDate function instead of the # symbols and format function - this converts a date string (if it can) into a date value - but if there is any ambiguity, it will assume a US structure - and as I said I don't know if it willl recognise yyyy-mm-dd as year/month/day or year/day/month, or not at all as a recognised date format.

  10. #40
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Don't see any reference to an object named 'PS' in that query.

    I did download your db. Ran all three queries. No error messages. None of them have the DLookup() expressions. Tried to copy/paste the posted query but it is dependent on another query not in the db. Also no forms. Not going to try to reconstruct db in order to test. You must provide what is relevant to the issue.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #41
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Usually most of the time I refrain from downloading from drop boxes if the OP won't at least try to post db here. I did, and no big deal for me to change my regional settings for testing. It's a bit difficult to recall every nuance of each post that we've seen so far, so what follows may not be a valid point.

    I noticed something being done that I would have thought not possible and couldn't see until looking at the db. The DLookup is trying to utilize fields from different tables (tables includes queries) in the criteria portion. It's a domain function, so can we reference fields from other domains in the criteria expression? I would think not but I've never tried, believing all fields in the criteria must belong to the domain being searched. Mainly I'm asking my more esteemed peers, because if I take out all the variables from this expression
    Code:
    DLookUp("VatRate01","q02VatRate","VatCode01='" & [VatCodePurchases02] & "' AND DocumentDate01 
    Between #" & Format([StartDate11],"yyyy-mm-dd") & "# And #" & Format([EndDate12],"yyyy-mm-dd") & "#")
    so that I have
    Code:
    VatRate99: DLookUp("VatRate01","q02VatRate","VatCode01='NV' AND [DocumentDate01] 
    Between #2017-01-01# And #2018-12-31#")
    Access tells me that it "can't find DocumentDate01. It doesn't matter if it's DocumentDate01 or [DocumentDate01].

    The use of multi-value fields isn't a great idea, sorry to say.

  12. #42
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    I assume you can see post 35 and 37 above, and you read that I placed that expression after someone suggested it because it worked and still works. I am still too inexperienced to make a decision how the expression should look. When you say CDate function I would be elated if you guide me by giving it exactly and I can copy it. I do understand that every date has an underlying value. I have a few calculations with an expression that is in the same object and calculating dates with no extras as if deducting two fixed amounts from one another. I accept Dlookup works different.

  13. #43
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Quote Originally Posted by June7 View Post
    Don't see any reference to an object named 'PS' in that query.

    I did download your db. Ran all three queries. No error messages. None of them have the DLookup() expressions. Tried to copy/paste the posted query but it is dependent on another query not in the db. Also no forms. Not going to try to reconstruct db in order to test. You must provide what is relevant to the issue.
    Just checked, the download you should have I found. the query is q02InvPurchaseSub and the last field does have the DLookup expression. I feel that what is relevant is there. No form needed. The expression must return the VatRate. Thank you.

  14. #44
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    After more thought I cannot see how this would ever work. This
    Code:
    DLookUp("VatRate01","q02VatRate","VatCode01='" & [VatCodePurchases02] & "' AND DocumentDate01 Between #" & [StartDate11] & "# And #" & [EndDate12] & "#")
    is basically saying (fields bold black, domain bold red),
    "Look up VatRate01 in domain q02VatRate WHERE VatCode01 in that domain = some value AND DocumentDate01 in that domain is BETWEEN date1 AND date2"
    There is no DocumentDate01 field in q02VatRate.

    You might get somewhere if you can add the field to q02VatRate somehow. I tried by adding Invpurchasesub and Invpurchase to q02vatrate (so I could pull in DocumentDate01), then added the field to q02vatrate and got
    VatRate99 q02Vatrate.DocumentDate01
    0.1 2017-03-01
    0.1 2017-03-01
    0.1 2017-03-01
    0.1 2017-03-01

    The data may be meaningless seeing as I have no idea what fields to join (as mentioned before, appending numbers to these fields is not a common practice as it makes things harder to trouble shoot) but it does seem to prove that the original expression can work. So I stand by my assertion as to what the cause is when it doesn't work. If I missed something from the many prior posts, don't hesitate to correct me.
    Last edited by Micron; 04-15-2018 at 02:59 PM. Reason: added info

  15. #45
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Micron; Thank you very much. When I discover this expression I will never forget what you guys taught me. The need here includes;
    a. Sales Invoices or Purchase Invoices happens every day.
    b. Vat Rates change sometimes.
    c. Even when an invoice of the past is opened, the VatRate of that period should be returned by the expression. Of Course historical Invoices may not change when VatRates change. New invoices should use new VatRates if there was a change.
    d. DocumentDate01 is the invoice date in q02InvPurchaseSub.
    e. t02VatRate and its "mirror image" query q02VatRate stores VatRates as they change periodically. Therefore there is StartDate and EndDate. DocumentDate01 is not in t02VatRate and neither should it be. The same VatCodes that change wil appear multiple times in t02VatRate. Therefore in this expression there are two conditions.
    f. I have two tables t01InventoryItem and t01GoodAndServices. Products like milk, bread, eggs and fuel raise no Vat, there are five VAT catagories therefore you find a common field that are joined from those two table from the main table t01CombinedEntity. The field's name is VatCodePurchases02. When an INVENTORYITEM or any OTHER GOODS Goods are registered the Vatcode for the item is now there
    G. South African Revenue Services expect a VAT Return every second month on a certain format. We have designed that form exactly the way they want it. Maybe the USA, Canada or UK VAT works the same I don,t know. Items that are classified as Zero Rated, or Exempted or No Vat all get calculated at zero VAT but the amount should still display on the VAT form, so each line of an invoice may differ.
    H. I don't know how yet, but I will bet my cat's life that there is an expression, since I already have such expressions which I posted here that works "Lucky Cat". Those expressions work almost exactly as the need is here.

Page 3 of 4 FirstFirst 1234 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 11-16-2017, 03:33 AM
  2. Replies: 4
    Last Post: 05-03-2016, 01:51 PM
  3. Replies: 5
    Last Post: 09-25-2013, 09:35 AM
  4. Replies: 2
    Last Post: 11-20-2012, 03:21 AM
  5. Replies: 4
    Last Post: 10-26-2012, 12:49 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums