I've done a bit of searching on the site to make sure I'm not repeating a problem that's already been solved but I couldn't find a solution that worked for me.
I've got a column, in Text Format, titled "Date_Logged". I also have two DatePicker controls "contStartDate" and "contEndDate". I am trying to Filter() a table based on something being between the two dates picked in the controls. Currently I have:
Filter( Table1, contStartDate.SelectedDate <= DateValue("Date_Logged") And contEndDate.SelectedDate >= DateValue("Date_Logged") )
I am using the DateValue function for the first time in my experience here, so it's possible that the error lies there. I am getting a Boolean value error for the Filter() function.
You have a bit of a syntax error in your filter. The And is in all caps. The actual And function is "And".
You can replace the AND with And and try that. Also, you can use the && notation to provide the same And capablity...such as:
Filter( Table1, contStartDate.SelectedDate <= DateValue("Date_Logged") && contEndDate.SelectedDate >= DateValue("Date_Logged") )
I hope this is helpful for you.
Can you provide a screenshot of the error and the formula showing the red underlines and any relevant error message?
Oh, and I just noticed you have quotes around your "Date_Logged". Have you tried removing them? On second glance, I believe that is possibly your issue. You are trying to get the date value of "Date_Logged" (which would be nothing relevant). But DateValue(Date_Logged) would be relevant.
Attached is a screenshot. I've tried removing the quotations but nothing changed. I'm referencing a column in the Excel sheet which I believe requires the quotations for.
As an additional piece of information, this is in the Items property of the table.
Ah, now we're getting somewhere...that formula will not work that way.
You have a few conditions there based on the radio buttons and there are a few things you need to take into account. First, your date range filter should go against the inner filter of the table you are searching. Since that is a pain to put in over and over again, you can construct your formula like this:
Search( Filter(DiveLogs, contStartDate.SelectedDate <= DateValue(Date_Logged) && contStartDate.SelectedDate >= DateValue(Date_Logged) ), Switch(Radio1.Selected.Value, "PERNER", TextInput3.Text, "Last Name", TextInput3.Text, "First Name", TextInput3.Text, "Hub ID", TextInput3.Text, "Team", contTeam.SelectedText.Value, "Location", contLocation.SelectedText.Value), Switch(Radio1.Selected.Value, "PERNER", "PERNER", "Last Name", "Name", "First Name", "Name", "Hub ID", "Hub_x0020_ID, "Team", "Team", "Location", "Location") )
In this case, we're filtering the DiveLogs table to start with and then placing that as the data source for the Search. The second parameter is the text we want to search. Since that varies based on your radio, the switch function is there to choose the right one.
The third parameter is the field we want to search. Again, since that varies based on the radio, the switch function is there to choose the right one.
This could be crafted several different ways, but this should be pretty much what you're looking for.
Oh, and no...the fields should not have quotes around them when you are referencing them in this way. Quotes around fields is only for certain parameters of some functions (like search).
Give the above a try and see if that gets you where you need to be...or at least closer.
I hope this is clear and helpful.
Alright, I've replaced the code with the one you've provided. It returned an error saying that the Search function is expecting Text in the second paramter. I pruned through your code to make sure the assets matched what I am using in the application and it all checks out. I read more on how to use a Switch function and I see how that replaces the If syntax I had earlier. I'm unsure of why the Switch function isn't returning text values considering if a match is made, we've got it programmed to return a Text value from whichever radio button is selected.
At a glance, without knowing your data specifically, I am suspecting "Hub_x0020_ID". What kind of column is that.
You can ONLY use search on text columns. You cannot use search to search a numeric column.
See if that is what you're up against.
There is one column whose format is a custom number format and this preferrably stays this way. Am I able to use a Text() function or something similar?
Edit: I've removed both items that are not a generic Text format in Excel and the error remains.
Continue your learning in our online communities.
Next Wednesday, August 21st at 8am PDT
Let's thank our top community contributors
Features releasing from October 2019 through March 2020
Coming to a city near you
Fill out a quick form to claim your user group badge now!
Connect, share, and learn with your peers year-round
Features releasing from April 2019 through September 2019!
August 20-23rd 2019