Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Akchayan is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Aug 2022
    Posts
    102

    applyFilter Function with two conditions

    I have this code:
    Code:
    DoCmd.ApplyFilter , "[Rechnungsdatum] between " & Format(FilterDatum1, "\#yyyy\-mm\-dd\#") & _            " AND " & Format(FilterDatum2, "\#yyyy\-mm\-dd\#") & "*'" & _
       " And [IDKunde] Like '*" & [Forms]![foUmsatz]![cmbKunde] & "*'"
    but this Error occures:
    Syntax error in string in query expression [InvoiceDate] between " & Format(FilterDate1, "\#yyyy-mm\-dd\#") & _ " & Format(FilterDate2, "\#yyyy-mm\-dd\#") & "*'".


    " AND " & Format(FilterDate2, "\#yyyy-mm\-dd\#") & "*'" & _
    " And [IDCustomer] Like '*"

    Does anybody know where the Error in my Code is?

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,860
    I tend to put the criteria into a string variable for domain functions if not a single simple criteria.
    Then I can debug.print them to see if I have the syntax correct.
    Then when correct, I can use that in the function.
    Plus, if you cannot see the error, you can copy and paste here or eslewhere, so someone can point out the error.?

    What is this on the first line?

    & "*'".
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Akchayan is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Aug 2022
    Posts
    102
    Or maybe if i put it like this:
    Code:
    DoCmd.ApplyFilter , "[IDKunde] = [Forms]![foUmsatz]![cmbKunde]"
    
    
    DoCmd.ApplyFilter , "[Rechnungsdatum] between " & Format(FilterDatum1, "\#yyyy\-mm\-dd\#") & _
                " AND " & Format(FilterDatum2, "\#yyyy\-mm\-dd\#")
    Do you know how too combine both of them too one?

  4. #4
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    As suggested always stick things like this in to a string , then examine them

    Code:
    Dim StrCriteria as string
    
    StrCriteria = "[IDKunde] = " &  [Forms]![foUmsatz]![cmbKunde] &  " AND   [Rechnungsdatum] between " & Format(FilterDatum1, "\#yyyy\-mm\-dd\#") & _            " AND " & Format(FilterDatum2, "\#yyyy\-mm\-dd\#")
    Debug.Print strCriteria
    
    Docmd.ApplyFilter  strCriteria
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  5. #5
    xps35's Avatar
    xps35 is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    229
    Use Debug.Print to see how the string you build actually looks like.
    Groeten,

    Peter

  6. #6
    Akchayan is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Aug 2022
    Posts
    102

    When i use the Code of Minty this is the String.
    Click image for larger version. 

Name:	Screenshot 2023-03-16 173321.png 
Views:	19 
Size:	2.2 KB 
ID:	49895
    And the filter only filters between the 2 Dates

  7. #7
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    If you paste that into the filter on the form does it work?
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  8. #8
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,860
    Quote Originally Posted by Akchayan View Post

    When i use the Code of Minty this is the String.
    Click image for larger version. 

Name:	Screenshot 2023-03-16 173321.png 
Views:	19 
Size:	2.2 KB 
ID:	49895
    And the filter only filters between the 2 Dates
    I cannot see why that would be the case , you could even try it as a WHERE clause in a test query?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  9. #9
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    What is the data type of IDKunde? If it's text that should be "1" and not 1.
    And the filter only filters between the 2 Dates
    That means what? You get records where IDKunde is not equal to 1 but in those records the dates are as expected?
    IDKunde value comes from a combo on a form. Does the combo bound column have numeric values or are you looking at the wrong column? Is there always a value in that combo?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    Akchayan is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Aug 2022
    Posts
    102
    You get records where IDKunde is not equal to 1 but in those records the dates are as expected? --> Yes
    Is there always a value in that combo? --> Yes
    Does the combo bound column have numeric values or are you looking at the wrong column? Numeric Values

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Forgot one: what is the data type of the field IDKunde? Numeric simply means the characters are what we recognize as numbers. That does not mean the table field is a data type of number. In table design view you should see "number" as the type in the Data Type column.
    You get records where IDKunde is not equal to 1 but in those records the dates are as expected? --> Yes
    Then this is a stumper for me, even if the data type really is a number. In that case I can only imagine that the issue is the bound combo column is feeding something into the filter that you're not seeing, or if one looks at the data, there is more than one record with a field value of 1.

    If that doesn't help I'd prefer to see a compacted and zipped copy of the db with instructions for which form(s) to play with.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,860
    As a last resort try
    Code:
    StrCriteria = "([IDKunde] = " &  [Forms]![foUmsatz]![cmbKunde] &  ") AND  ( [Rechnungsdatum] between " & Format(FilterDatum1, "\#yyyy\-mm\-dd\#") & _
         " AND " & Format(FilterDatum2, "\#yyyy\-mm\-dd\#)& ")")
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  13. #13
    Akchayan is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Aug 2022
    Posts
    102
    Quote Originally Posted by Welshgasman View Post
    As a last resort try
    Code:
    StrCriteria = "([IDKunde] = " &  [Forms]![foUmsatz]![cmbKunde] &  ") AND  ( [Rechnungsdatum] between " & Format(FilterDatum1, "\#yyyy\-mm\-dd\#") & _
         " AND " & Format(FilterDatum2, "\#yyyy\-mm\-dd\#)& ")")
    I dont know what exatcly is wrong but i copied ur code and the code is red.
    Here is the code:
    Code:
    Dim StrCriteria As String
    
    StrCriteria = "([IDKunde] = " &  [Forms]![foUmsatz]![cmbKunde] &  ") AND  ( [Rechnungsdatum] between " & Format(FilterDatum1, "\#yyyy\-mm\-dd\#") & _
         " AND " & Format(FilterDatum2, "\#yyyy\-mm\-dd\#)& ")")
    Debug.Print StrCriteria
    
    
    DoCmd.ApplyFilter StrCriteria

  14. #14
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,860
    Well all I was trying to do, was to put each logic element between brackets?

    You try it and use the Debug. Print to get it correct

    (Logic1) AND (logic2)
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  15. #15
    Akchayan is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Aug 2022
    Posts
    102
    Quote Originally Posted by Welshgasman View Post
    Well all I was trying to do, was to put each logic element between brackets?

    You try it and use the Debug. Print to get it correct

    (Logic1) AND (logic2)
    It worked like this:
    Code:
    If IsNull(Me!cmbKunde) Then            MsgBox "Füllen Sie die Felder"
            Else
                DoCmd.ApplyFilter , "[IDKunde] = [Forms]![foUmsatz]![cmbKunde] And [Rechnungsdatum] between " & Format(FilterDatum1, "\#yyyy\-mm\-dd\#") & _
                " AND " & Format(FilterDatum2, "\#yyyy\-mm\-dd\#")
        End If
    so the problem was 2 of these signs --> ", were to much. so i just had to delete them
    Thank you guys for the help tho.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 03-01-2023, 10:46 AM
  2. ApplyFilter in ACCDR
    By Cedarguy in forum Access
    Replies: 5
    Last Post: 05-11-2015, 05:49 AM
  3. Replies: 14
    Last Post: 05-22-2013, 03:33 PM
  4. Help with ApplyFilter
    By kabichan in forum Programming
    Replies: 1
    Last Post: 01-15-2010, 09:21 PM
  5. Using ApplyFilter in Form...help please
    By playfuljade in forum Forms
    Replies: 8
    Last Post: 12-19-2005, 09:35 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