Page 1 of 4 1234 LastLast
Results 1 to 15 of 53
  1. #1
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618

    Expression


    VatRate01: Dlookup(" [VatRate01] ","q02VatRate","VatCode01=" & VatCodePurchases01 AND #" & Format([DocumentDate01]), "yyyy-mm-dd") & "# Between [StartDate11] And [EndDate13]")

    I use a very similar expression elsewhere in my system and it works perfect. You may see the error it gives me here on the image. “The expression you entered has an ivalid date value”. The format I mainly use for dates is 14-Apr-2018.
    You may see in this lower line I typed the “Double Qoutes” are tilted when I type in MS Word. But the top line was not typed in MS Word. Why does Word type it tilted? When I copy this tilted quotes to Access, it errors.
    Click image for larger version. 

Name:	Error.png 
Views:	26 
Size:	107.5 KB 
ID:	33563Click image for larger version. 

Name:	VatrateQuery.png 
Views:	26 
Size:	120.3 KB 
ID:	33564

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Yes, Access does not like the 'tilted quotes'. Will have to type over to replace.

    Need to place the AND operator within quote marks and concatenate.

    & " AND #" &
    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. #3
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Thanks. Sorry. "And" appears twice. Will you please just change and return. I didnt use tilted quotes, just learning.

    VatRate01: Dlookup(" [VatRate01] ","q02VatRate","VatCode01=" & VatCodePurchases01 AND #" & Format([DocumentDate01]), "yyyy-mm-dd") & "# Between [StartDate11] And [EndDate13]")

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    VatRate01: Dlookup(" [VatRate01] ","q02VatRate","VatCode01=" & VatCodePurchases01 & " AND #" & Format([DocumentDate01]), "yyyy-mm-dd") & "# Between [StartDate11] And [EndDate13]")
    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.

  5. #5
    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:	Error2.png 
Views:	26 
Size:	147.6 KB 
ID:	33571
    It highlights the empersand before the "And"

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    because it is the wrong double quote

    and just out of curiosity, if your dates are displayed dd-mmm-yyyy, why do you have in your dlookup criteria yyyy-mm-dd?

  7. #7
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Maybe 20 months ago someone helped me with that expression and it works at at least 4 places in my system. I will retype the expression character by character only in Access. Thank you. I will change the date format.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    you can always copy to then from Notepad. It will override most special characters and use those defined in the system character set. It's a good way to strip out any formatting stuff that you'll pick up from Word.

  9. #9
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    "The expression you entered has an invalid date value".
    I retyped it and it seemed I didn't make a typing error, then tried changing the date format to dd-mmm-yyyy. Is it compulsory to format the date since both objects have the same format of date?

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Depends. Access default is MM/DD/YYYY structure. Review http://allenbrowne.com/ser-36.html
    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. #11
    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]")

    I am taking a little bit of strain here. No progress in 2 days. 00H50 where I am, monday morning a serious meeting. I am still trying to work out why the above mentioned expression works and why I can not get it right now. As you can see the date format doesnt seem right but it works?

  12. #12
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I am still trying to work out why the above mentioned expression works and why I can not get it right now.
    I find these two parts contradictory and confusing.
    It's been my experience that domain lookup functions require each part to be within double quotes. In post 5 you have
    "VatCode01=" & rather than
    "VatCode" = &
    As for your latest post, I would think [Salary04] & " Between
    needs to be
    "[Salary04]" & " Between...
    You are even missing the beginning quote before [Salary].
    I might not say this if you were building the query in vba, but you're not.
    Did you try opening this query from sql view? Usually, that causes the offending part to be highlighted.

  13. #13
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    The expression in post 11 works exactly like that. I copied it and it is used in three more places and they all work correct.

    It is used a little different now. The difference in the "where condition" is not with the date part, it confuses me that I replace the part between two amounts with comparing VatCode01=" & VatCodePurchases01, but it errors the date part which I haven't changed.
    The latest I tried is here below, but it returns in the query field #Error. At least it shows a different error than my last try.

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Syntax in posts 4 and 11 look correct. I don't think [Salary] should be within quotes as this value is intended to come from the current record.
    However, I am not sure about the use of Format() function for the date. I am in the U.S. so not concerned about international date.

    Why do you have a * in the expression shown in post 5? It is not in my example.
    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.

  15. #15
    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)

    I say again, the above expression works correct in 4 different places in my system, but it is for a different purpose. If I can find a different solution it will be fine. I am trying to replace the part in red with "Vatcode01=" & [VatCodePurchases02]
    I assume that we are just missing something small here

    VatRate99: Dlookup(" [VatRate01] ","q02VatRate","VatCode01=" & VatCodePurchases01 & " AND #" & Format([DocumentDate01]), "yyyy-mm-dd") & "# Between [StartDate11] And [EndDate13]")

    Surely you guys’s are experienced and have the answer. My issue is to return in q02InvPurchase a VatRate against a VatCode at a certain date. What is the simplest expression you would have used to obtain that.

    In query q02InvPurchase I have fields DocumentDate01, VatCodePurchases01.
    In table t02VatRate fields VatRate01, StartDate11, EndDate13, VatCode01

Page 1 of 4 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