Page 4 of 4 FirstFirst 1234
Results 46 to 53 of 53
  1. #46
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    C. the vat rate should be stored along with the other pertinent details of the record (transaction?). Having to find it via a complicated expression is not only not necessary, it smacks of less than optimal design. The vat rate you save with each record should probably come from an unbound combo on a form that allows users to look up and apply the rate.

    Generally speaking
    - any amounts that are calculated now, or again in the future when we're talking about historical records, should be calculated in a query or form.
    - values such as discounts, tax rates, that are part of the calculation, should be saved as part of the record
    - values such as prices, which are subject to change and would cause historical records to be incorrect (e.g. an old invoice) need special handling.
    OK, I am not an expert on financials and most of my db experience has been in maintenance, reliability and quality/environmental systems, so talk is cheap - I'm short on such experience.

    I think way back someone had a suggestion as to how your tables should look. I know it would be a pain after all you've just gone through, but if you don't rebuild your db and create the necessary tables, you will be back here with more issues. One of them I believe you're overlooking (I commented on this) is the use of mv (multi value) fields. Unless you're dealing with Sharepoint, it is best to avoid them. If you want to pull a value from one of these fields, it requires special handling. Think of it this way: Access creates and stores a hidden table and the value you see in one of these fields is not really there. So if you want to query it, you can't in the normal fashion because it doesn't really exist where you're seeing it.

    D. I know. However, it is not part of the domain upon which you are doing the DLookup, thus it will never work IMHO.


    G. How the form looks is irrelevant. What's behind the form that governs the way it works is just as important. You don't seem to have that right.
    H. Your cat i doomed if you don't understand how a function such as DLookup works. Did anyone refer you to here?
    Note the part that says

    Criteria
    Optional. It is the WHERE clause to apply to the TableName.
    You can't include other tables when trying to build the criteria part of the expression.

  2. #47
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Paye03: DLookUp("[Younger65]","q02PAYE",[Salary04] & " Between [StartAmount] And [EndAmount] And #" & Format([DocumentDate07],"yyyy-mm-dd") & "# Between [StartDate05] And [EndDate02]")

    In post 35 of this thread I placed images of this expression that somebody helped me with on the forum almost two years ago and it works right now. If I understand you correct and the link you gave, the two fields in red should be in the foreign domain. They are not, they are in the local query where I put the expression. That is why I say this expression does what we need and kitty is safe. I am not sure how long we will live, but I am sure this programming takes extreme amount of time. We will study with great focus this thread and try to finalize our skill on doing this expression. Me and my 20 y.o. son who finished college last year are partners in this and proud what we have achieved so far. We have a policy to be open for change to the better all the time. The best decision should be made at all time and if reprogramming is needed so be it. First make the correct decision make sure not re re reprogram the same thing.

    Thank you for advice. My accounting and business experience is great, not my programming. My deceased wife was a programmer. Our system works in a way that currently combo boxes are used in invoices to select the VatCode and therefore historical invoices each line will be connected to a record in the VatRate table. Which is not wrong.

    We are paranoid about user focus. When people use our system, they should not have to click the mouse once about VAT. Things should be done automatically. You tell me how we do that without expressions, for when there are combo boxes there are human error. My son Ruben studied design and some programming and is not doing bad to build our ULTIMATE PROCESS APPARATUS. But we expect to learn a lot in coming months. The subject Access Database is much bigger than we thought previously.

    You mentioned that discounts, VAT and prices should be stored with the invoice. Our insight fall short of that now. My insight is that expressions must recalculate every time a historical document is called up. It is and was part of our thoughts but I don't know you store Tax, prices and discount on each invoice without using expressions. Is it a situation of being between a rock and a hard place? We want to reduce combo boxes and user errors, at the same time expressions may make certain objects too "heavy". It is now 00H27 where I am, my son will be here in a few hours and we will discuss this issues, but I am very happy if you inform us.

    There is no doubt that Access has many wonderful abilities that we still have to learn.

  3. #48
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If I understand you correct and the link you gave, the two fields in red should be in the foreign domain. They are not, they are in the local query where I put the expression
    Yes, pretty sure the fields have to be in the domain upon which you wish to do the DLookup. Maybe someone will disagree here and prove me wrong.

    You can tell Access to "bring me data from Field1 in tableA where Field3 in tableA contains this and Field4 in tableA contains this and and Field5 in tableA contains this ..."

    You cannot say "bring me data from Field1 in tableA where Field3 in tableA contains this and Field4 in tableA contains this and and Field1 in tableB contains this ..." by using DLookup.

    As for "My insight is that expressions must recalculate every time a historical document is called up."we can agree to disagree that this is always the case, because I say it's not.
    Here's why:
    Expression: Quantity x Price (10 x 1.00) = 10.00 (sale amount)
    Price increases to 2.00, look at historical data again in query or form: Quantity x Price (10 x 2.00) = 20.00
    Expression returns calculation that old sale was for 20.00 because the price is a variable. I see no difference in the result if the VAT changes and your expression acts upon old data.
    If you don't agree, that's fine. You are free to do as you wish. I'd say the main thing now is that the reason for the expression not working here has been discovered. If it worked elsewhere in the past, I do not know why. Something had to be different such that you weren't trying to DLookup with criteria across different domains. That is why I suggested you probably have to ensure all fields used in the criteria are part of that domain, even if you have to base the lookup on a query that assembles the fields into a common domain rather try to go across more than one table.

  4. #49
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    When you say CDate function I would be elated if you guide me by giving it exactly and I can copy it.
    google' vba cdate'

    plenty of returns - https://nortonsafe.search.ask.com/we...ctype=&tpr=121

    Something Micron said

    After more thought I cannot see how this would ever work. ThisCode:
    DLookUp("VatRate01","q02VatRate","VatCode01='" & [VatCodePurchases02] & "' AND DocumentDate01 Between #" & [StartDate11] & "# And #" & [EndDate12] & "#")
    Is DocumentDate01 in the query using the Dlookup?
    Are StartDate11 and EndDate12 in the query q02VatRate?

    if the answer is yes to both, then the implication is that DocumentDate01 is a date type field from some table in the calling query and StartDate11 and EndDate12 are date type fields from whatever table is used in q02VatRate, in which case this should work

    DLookUp("VatRate01","q02VatRate","VatCode01='" & [VatCodePurchases02] & "' AND #" & format([DocumentDate01],"mm/dd/yyyy") & "# Between [StartDate11] And [EndDate12]")

    This may have been suggested before, and if the yyyy-mm-dd is still being used, the only thing I can think is the sql interpreter is interpreting with the month and day swapped round, perhaps before the data used was unambiguous in that the day was greater that 12.

    Whilst writing, just seen your latest post. Following my suggested table layout many posts ago,

    . Our system works in a way that currently combo
    boxes are used in invoices to select the VatCode and therefore historical
    invoices each line will be connected to a record in the VatRate table.

    ...
    ...
    We are paranoid about user focus. When people
    use our system, they should not have to click the mouse once about VAT. Things
    should be done automatically.
    the vat code applicable to a product or service should be stored with that product or service record - no subsequent user intervention required unless you want to give them the ability to override a business rule. In the UK, there is a further override in that certain customers for certain purchases have a different VAT rate applied. Applies particularly to construction industry customers which complicates matters further because you also need to reference the project and/or customer record to determine the correct VAT code to use. I would imagine in SA you also have the situation that if the customer is abroad, the VAT is not applied - or to be more precise a different VAT code is used (and stored in the customer record) which applies a zero rate. The invoice line record needs to store the VAT Code as a minimum otherwise how are you going to look up the rate applicable for the date of the invoice? but common (sense) practice is to store the rate as well - which also makes for a faster system, particularly if you are using dlookups. Also means if for some reason, you make a correction to the rate table (rate or effective date), it will not result in amending the historical record. For the same reason, you would also store other data like product price, discounts and possibly description so you can provide an exact duplicate in the future if required. But you wouldn't store value (price*quantity), VAT amount (value*VAT Rate) etc because that can be calculated as and when required.

    You tell me how we do that without expressions,
    you use queries linking the relevant tables together, no calculation required, certainly you don't need dlookups but you may need a sub query or two in the criteria. You might use dlookups in vba code behind your entry form to populate a field but, not to confuse the issue, there are more sophisticated ways using things like arrays, collections and dictionaries.

  5. #50
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    @Ajax
    Is DocumentDate01 in the query using the Dlookup?
    per posts 41 and 43 (I think) I've said it/they are not if you mean is it in the domain being looked up. It is in the query as a calculated field, but the expression is trying to use criteria that's not in that query. It's not even in the domain that DLookup is looking at.

  6. #51
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    It is in the query as a calculated field, but the expression is trying to use criteria that's not in that query. It's not even in the domain that DLookup is looking at.
    oh well, I give up then 50 posts and no further forward

  7. #52
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    My 20 y.o. found the following and it works. Thank you very much for everybody!

    VatRate100: DLookUp("[VatRate01]","t02VatRate","VatCode01='" & [VatCodeSales01] & "' And #" & Format([DocumentDate02],"yyyy-mm-dd") & "# Between [StartDate11] And [EndDate13]")

  8. #53
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Good luck with your project.
    Until next time...

Page 4 of 4 FirstFirst 1234
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