Results 1 to 9 of 9

Expression

  1. #1
    Perfac is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Jan 2018
    Location
    Gauteng Johannesburg
    Posts
    238

    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 online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    18,948
    You need delimiters:

    DLookup Usage Samples
    Paul (wino moderator)
    MS Access MVP
    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 is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Jan 2018
    Location
    Gauteng Johannesburg
    Posts
    238
    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 is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Jan 2018
    Location
    Gauteng Johannesburg
    Posts
    238
    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:	12 
Size:	93.5 KB 
ID:	36161Click image for larger version. 

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

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

  6. #6
    Perfac is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Jan 2018
    Location
    Gauteng Johannesburg
    Posts
    238
    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 online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    18,948
    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
    www.BaldyWeb.com

  8. #8
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    18,948
    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
    www.BaldyWeb.com

  9. #9
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    18,948
    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
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

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

Posting Permissions

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