Page 2 of 4 FirstFirst 1234 LastLast
Results 16 to 30 of 53
  1. #16
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Although you say the expression for PayeBelow65 works, it contains more than one syntax error as pointed out by others previously.
    Your handling of date ranges is wrong.
    This should work correctly:

    Code:
    PayeBelow65: Nz(DLookUp("Younger6","t03PAYE","Salary02 Between [StartAmount01] And [EndAmount01] And DocumentDate10 Between #" & Format([StartDate05],"yyyy-mm-dd") & "# And #" & Format([EndDate02],"yyyy-mm-dd") & "#"),0)
    Now for the VatRate99.
    Date handling also wrong
    You have spaces around [VatCode01] - remove these
    Its not clear to me whether VatCode01 is a text or number field



    If its a number field:
    Code:
    VatRate99: Dlookup("VatRate01","q02VatRate","VatCode01=" & VatCodePurchases01 & " AND DocumentDate01 Between #" & Format([StartDate11],"yyyy-mm-dd") & "# And #" & Format([EndDate13],"yyyy-mm-dd") & "#")
    If its text:
    Code:
    VatRate99: Dlookup("VatRate01","q02VatRate","VatCode01='" & VatCodePurchases01 & "' AND DocumentDate01 Between #" & Format([StartDate11],"yyyy-mm-dd") & "# And #" & Format([EndDate13],"yyyy-mm-dd") & "#")
    I should stress that although I've done this many times before I've not tested the above.
    Hopefully no errors

    It would be worth spending time how the various types of delimiters work together as you will use the ame ideas repeatedly
    I recommend debugging by printing formulas to the immediate window so you can see how they work in practice.

    I'd also recommend using more meaningful names than StartDate05 and StartDate11 etc ....
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  2. #17
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    I copied your suggestion, on the image you can see it returns "null". I tried to find why, no luck yet. VatCode01 is TextClick image for larger version. 

Name:	t02VatRateDatasheet.png 
Views:	18 
Size:	126.0 KB 
ID:	33575Click image for larger version. 

Name:	t02VatRateDesgn.png 
Views:	18 
Size:	77.6 KB 
ID:	33576Click image for larger version. 

Name:	t02VatRateDesign3.png 
Views:	17 
Size:	112.5 KB 
ID:	33577

  3. #18
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    EndDate13 is a calculated field so you need to substitute with the code used to calculate that value
    Its likely that will make your code unmanageably long.

    Suggest you replace with a function - something like this (adapt as necessary)
    Code:
    Function GetVATRate01()
    
    Dim EndDate13 As Date
    
    EndDate13 = ..... 'enter your formula here
    
    GetVatRate01=Dlookup("VatRate01","q02VatRate","VatCode01='" & Forms!YourFormName.VatCodePurchases01 & "' AND DocumentDate01 Between #" & Format(Forms!YourFormName.[StartDate11],"yyyy-mm-dd") & "# And #" & Format([EndDate13],"yyyy-mm-dd") & "#")
    
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #19
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    https://drive.google.com/open?id=1SI...3kPj0VJK1w21Fa

    Above is a link to Google Drive. Just a 5meg table with the involved objects. The last field in q02InvPurchase stores the expression. I changed Enddate13 to EndDate12. EndDate12 is not calculated.

  5. #20
    Join Date
    Apr 2017
    Posts
    1,673
    Code:
    =Nz(DLookup("Younger65","t03PAYE","Salary02  Between " & StartAmount & " And " & EndAmount & " And DocumentDate10 Between #" & Format([StartDate05],"yyyy-mm-dd") & "# And #" Format([EndDate02],"yyyy-mm-dd") & "#"),0)
    i.e. the formula string for DLookup() must evaluate like
    Code:
    "DLookup("Younger65", "t03PAYE", "Salary02 Between 1 a And 2 And DocumentDate10 Between #2018-01-20# And #2018-01-31#")
    Here I assumed, that DocumentDate10 is a date field. So long at it is, the format you use to display it in your app, is moot. You have to include between "#" only dates you read from your form (And you need to format these dates only then, when your system date format (regional Settings in Window) is not "mm/dd/yyyy" or "yyyy-mm-dd").

    When DocumentDate10 is e.g. text field (contains datestrings, not dates) you have to use some formula which returns DocumentDate10 values as dates in format "mm/dd/yyyy" or "yyyy-mm-dd" instead. Or you have to use formulas to calculate Between() parameters as texts and replace all "#" with "'" (This is applicable only in case, when you can order datestrings correctly. E.g. you can order correctly datestrings in format "yyyymmdd", but you cant order correctly datestrings in format "ddmmyyyy")


    Btw. Maybe I'm wrong, but field names like Salary02, DocumentDate10, StartDate05 indicate, that probably you have to return often into forum with various problems, you need to find solution for!

  6. #21
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Surely you guys’s are experienced and have the answer.
    we are, but personally I struggle to understand what you don't understand. Your current structure is not normalised (it is Excel style) and therefore difficult to get the results you want

    1. You should have two tables

    tblVateCodes
    VatCodePK autonumber primary key)
    VATCode text (e.g. T1, T2, etc)
    VATDesc text
    EffectiveTo Date (only populated when the VAT code ceases and contains no time element, otherwise Null)

    tblVATRates

    VatRatePK autonumber
    VATCodeFK long (links to VATCodePK in tblVATCodes)
    VATRate number (percentage)
    EffectiveFrom Date (always populated and contains no time element)

    However VAT codes are a) short, b) unique and c) consistent, so these tables can be simplified

    tblVateCodes
    VATCodePK text (e.g. T1, T2, etc) (primarykey)
    VATDesc text
    EffectiveTo Date (only populated when the VAT code ceases, otherwise Null)

    tblVATRates

    VatRatePK autonumber
    VATCodeFK text (links to VATCodePK in tblVATCodes)
    VATRate number (percentage)
    EffectiveFrom Date (always populated and contains no time element)



    2. to find the rate effective on a particular day, based on the simplified tables your dlookup becomes

    Dlookup("VATRate","tblVATRates","EffectiveFrom BETWEEN #" & Format([StartDate05],"mm/dd/yyyy") & "# And #" Format([EndDate02],"mm/dd/yyyy") & "#")

    I don't know where your startdate05 and enddate02 come from, so adjust as required. I've also changed the format to what sql expects as an unambiguous date, your way may work, but I've never tried it.

  7. #22
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Thank you. I am always learning. I have now more than 800 fields in my database, and not one with the same name than another. I believe that is good. Some fields have the same name but I use the suffix to not have the exact name. FieldName most used already at no 38. I also give a new suffix when creating and renaming calculated fields in queries. I listen with passion to new advice but I assume that my naming convention gets more than 8/10. No information in any field ever duplicated.

    If you read this thread you will see that I wrote the first expression(the one you "corrected") is working perfectly on 4 places in my system. It is the second expression that I desperately need to make work. If you look at post 19 here you will find a link with a very small dbase where I copied only the needed objects. I will jump 2cm high if I get help.

  8. #23
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Hi Ajax. Thank you. There is a very small dbase link in my post 19 here that have the query and tables. In q02InvPurchaseSub the last field is where the expression is. In South Africa as you can see in the table t02VatRate there were a few changes in Rates. Against the Vatcode which consists of 2 letters one VatCode may appear a few times. In the invoice query the Date and Vatcode of the invoice is needed return the correct VatRate. I will test your suggestion but please look at the link.

  9. #24
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    =Nz(DLookup("VatRate01","t02VatRate","VatCode01=" & VatcodePurchases02 And DocumentDate10 Between #" & Format([StartDate05],"yyyy-mm-dd") & "# And #" Format([EndDate02],"yyyy-mm-dd") & "#"),0)

    Error message "The expression you entered has an invalid date value.

  10. #25
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    please look at the link.
    Sorry, for safety reasons, I don't use those links, please upload a copy to the forum instead - make sure you compact and zip it before uploading.

  11. #26
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Iv tried a couple of times, but fail to upload.
    Click image for larger version. 

Name:	Ajax1.png 
Views:	18 
Size:	243.1 KB 
ID:	33578Click image for larger version. 

Name:	Ajax2.png 
Views:	18 
Size:	216.6 KB 
ID:	33579

  12. #27
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Some fields have the same name but I use the suffix to not have the exact name
    your naming convention is up to you, my own philosophy is that a field with the same data has the same name. If it is the same data, but for a different purpose, then use a suffix or prefix to indicate that purpose. Aside from the fact they are reserved words, Date and Description are good examples - so rather than using (I presume from your convention) Date01, Date02, I would use OrdDate, InvDate, or ProdDescription, CustTypeDescription - typically based in some way on the table name/purpose. There are some exceptions where the same data is stored twice, e.g. an invoice line should store the product price (even tho' it is in the product price table) - in which case the product price table will have a field ProdPrice and the invoice line table InvProdPrice - If you don't store the price you would need to use a lookup along the lines of the dlookups of this thread to find the price correct on the date of the invoice.

    In a more relevant example, rather than using EndDate12, I would use VATRateEndDate or VREndDate - and I would modify my example to VREffectiveFrom (and VATEffectiveTo for the other table)

  13. #28
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by Perfac View Post
    Thank you. I am always learning. I have now more than 800 fields in my database, and not one with the same name than another. I believe that is good. Some fields have the same name but I use the suffix to not have the exact name. FieldName most used already at no 38. I also give a new suffix when creating and renaming calculated fields in queries. I listen with passion to new advice but I assume that my naming convention gets more than 8/10. No information in any field ever duplicated.

    If you read this thread you will see that I wrote the first expression(the one you "corrected") is working perfectly on 4 places in my system. It is the second expression that I desperately need to make work. If you look at post 19 here you will find a link with a very small dbase where I copied only the needed objects. I will jump 2cm high if I get help.
    Disagree with your naming system. The whole point of a name (whether it is a field name or a control name) is that it is clear what it does.
    So LastName and FirstName are much better than Name01 and Name02.
    By contrast, EndDate11 or EndDate13 are essentially meaningless.

    It doesn't really matter if you have the same name in more than one table as any query or form/control record source will explicitly reference each table used

    In general, I also disagree with using calculated fields as these will go out of date. Calculate items using queries as needed
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  14. #29
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you'll get that error if you try to insert inline a file which is not an image file.

  15. #30
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    =Nz(DLookup("VatRate01","t02VatRate","VatCode01=" & VatcodePurchases02 And DocumentDate10 Between #" & Format([StartDate05],"yyyy-mm-dd") & "# And #" Format([EndDate02],"yyyy-mm-dd") & "#"),0)

    Error message "The expression you entered has an invalid date value.
    what is your question? how is sql to know that yyyy-mm-dd is valid? if the value is 2018-01-12, should it treat is as 1st Dec or 12th Jan? As previously advised if the date can be ambiguous, you need to use the specific format of mm/dd/yyyy. Or you can make it unambiguous by formatting as yyyy-mmm-dd which will return say 2018-Dec-01. However I don't know if even that represents a valid date from sql's perspective

Page 2 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