I am trying to make a detail report pulling information from our data warehouse. I want to pull all active sales prices. To do this, my filter needs to look at our field "Ending Date" for a value that is in the future or not populated. I discovered, in our data warehouse if the Ending Date is not populated in NAV, Jet returns 01/01/1753. Therefore, I want my filter to be 01/01/1753 or greater than today. I created an NP(DateFilter) function for both individually that work successfully. I attempted a NP(Union) and receive #VALUE.
My Check Error results are:
The data provider returned the following error:
Conversion failed when converting date and/or time from character string.
I have attempted to nest the NP function in my NL function and tried to create my NP functions outside of my NL function. Both return the same error.
=@NL("Rows","Sales Price",,"DataSource=","JetDWProd","Sales Code",$K$2,"Ending Date",NP("Union",$K$3,$K$4))
Cell K3: =@NP("DateFilter",,"01/01/1753")
Cell K4: =@NP("DateFilter",TODAY())
I am new to Jet so I may be using the wrong functions. Any shared information or recommendations will be welcome.