Fetching items failed. Possible invalid string in filter query

[Smudgers9]

I have been using the below code for over 12 months in a PowerApp and now without any changes to the app or the datasource it has stopped working.  I can't work it out but the common suggestion is using an As statement.  can someone please advise how to correct this code?

If(And(datesSelected, varType <> ""), Filter( 'Loan Equipment', varType = HardwareType, IsEmpty( Filter( 'Loan Dates', Ref = AssetID, Or( BEndDatePicker.SelectedDate <= EndDate && BStartDatePicker.SelectedDate >= StartDate, BEndDatePicker.SelectedDate >= EndDate && BStartDatePicker.SelectedDate <= StartDate, BStartDatePicker.SelectedDate >= StartDate && BStartDatePicker.SelectedDate <= EndDate, BEndDatePicker.SelectedDate <= EndDate && BEndDatePicker.SelectedDate >= StartDate ) ) ) ) )

Really appreciate your help 

[RandyHayes]

@Smudgers9 

Your logic seems a little off on this formula, but a couple things...if this is an Items property, then you run the risk of leaving the Items empty - this is never a good thing and has bad results are run/play time.

Consider the following formula instead:

Filter( 'Loan Equipment', 
    HardwareType=varType &&
    
    datesSelected &&
    IsEmpty(        
       Filter('Loan Dates', 
           Ref = AssetID && 
           BEndDatePicker.SelectedDate > StartDate && 
           BStartDatePicker.SelectedDate < EndDate &&
           BEndDatePicker.SelectedDate <= EndDate &&
           BStartDatePicker.SelectedDate >= StartDate
       )
    )     
)

I couldn't really figure out your date logic because it seemed that you restricted to before and after in one logic statement and then shifted to in-between in another one.  So, I assumed you wanted the dates in between the selected start and end.

In the formula above, there is no need for the If statement you started out with.  Using that If would cause the Items to be blank if there was not a datesSelected (assuming variable) of true and varType to equal something.  The formula above incorporates that into the filter so the results are the same except that you are always returning a signature based table - even if with no rows.

I hope this is helpful for you. 

[Smudgers9 · Accepted solution]

Thanks for the response @RandyHayes but unfortunately I still get the same error when I run this formula.  Looking elsewhere online there is talk of an AS statement missing but I have no clue where this would go.

[RandyHayes]

@Smudgers9 

Can you provide more details on why you expect an AS statement would help?

Are you stating that either Ref or AssetID or both are used in both lists?

[Smudgers9]

I worked it out in the end.  It looks like some of the data sources naming conventions were a little strange from what I was provided so I have amended these along with changing how the filtering is run and this looks to have resolved the issue.  Thanks for the effort and advice though @RandyHayes I appreciate your time looking into this with me.