Results 1 to 9 of 9
  1. #1
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618

    Expression


    Me!VatRate002 = Dlookup("VatRate","q04VatRate","VatCode03=" & Me!VatCodeSales002)

    I am missing something. VatCode03 is a field in foreign query q04VatRate, it is always a string value i.e. SS.
    VatCodeSales002 is a field in local query, also always a string.
    I assume because of strings, there must be a colon or two??

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You need delimiters:

    DLookup Usage Samples
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    lccrews is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Aug 2017
    Location
    Los Angeles
    Posts
    194
    Try: Me!VatRate002 = Dlookup("VatRate","q04VatRate", "VatCode03='" & Me!VatCodeSales002 & "'")

    Just going on a blind hunch. If that doesn't work, tell me more about what you are doing.

  4. #4
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Thank you. It works. In South Africa Vat Rates changed in 1992 and also on 1 Apr 2018 from 14% to 15%. There are 4 different categories, probably similar to USA, UK, Canada etc. We have a VAT Table where one record will be added when there is a change in one category. Then follows a Total query which return the latest rate on every single category(The latest rate against every category) Not too much use for this though. We want our system to do historical processing accurate. I.e. even though it is November. If we now process an Invoice with document date 10 March, the Vat Rate was 14% not 15% like current. We never want to use expression in the queries, we choose to use VBA because it prevents lagging so far for us. The same expression that works for us in the query, we fail to make work in VBA. I will post that in a few minutes.

  5. #5
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Please ignore my previous expression posted. The one directly below does not work in VBA, we would like to make it work.

    VatRate001: DLookUp("[VatRate063]","t02VatRate","VatCode063='" & Me!VatCodePurchases001 & "' And #" & Format(Me!DocumentDate001b,"yyyy-mm-dd") & "# Between [StartDate063] And [EndDate063b]")

    This one works correct in the query, but we would like to make it work in VBA.
    VatRate08: DLookUp("[VatRate063]","t02VatRate","VatCode063='" & [VatCodePurchases001] & "' And #" & Format([DocumentDate001b],"yyyy-mm-dd") & "# Between [StartDate063] And [EndDate063b]")

    The bottom image is the local query where we need to return the answer.

    Click image for larger version. 

Name:	VatRate.png 
Views:	14 
Size:	93.5 KB 
ID:	36161Click image for larger version. 

Name:	VatRate2.png 
Views:	14 
Size:	100.5 KB 
ID:	36162Click image for larger version. 

Name:	PurchaseInvSub.png 
Views:	14 
Size:	70.2 KB 
ID:	36163

  6. #6
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    If you go to form f02PurchaseInvoiceSub. Look at the VBA, the line in red is not working. Ignore the stuff that is commented out. In the query q02PurchaseInvoiceSub you will find the last field that is working correct.
    Attached Files Attached Files

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You could have just posted the offending line of code. The : should be =

    VatRate001: DLookUp(...

    should be

    VatRate001 = DLookUp(...
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    By the way, I would disambiguate with

    Me.VatRate001

    Otherwise, what is VatRate001? It could be a variable, field, control, whatever. Access will usually figure it out, but you or a later developer may wonder later when it isn't fresh in your mind.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Oh, and a final "by the way". I'd create a public function that accepts the code and date as inputs and returns the rate. Then you can call it from anywhere without having to recreate the DLookup() every time.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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