Multiple DateFilter Using NP


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.

Thank you,

Kristina Frank 

1 comment

Please sign in to leave a comment.