I am creating an Inventory Usage BAQ and would like the user to be able to enter the Through Date much like they do in the printed report. To accomplish this I though it would be very simple to use a parameter where needed.
For some reason, I keep getting the error “Invalid parameter 1 specified for dateadd.” I believe this is correct SQL syntax so I really do not understand why this would not work. Does anyone know why this is occurring? Can I not use it in the expression editor like this?
I was originally using the “Current date -12 months” value which shows as ‘dateadd(month, -12, Constants:Today)’ or something similar to that in the query. I would like it to be the same thing but instead of using the today constant, I would like to have the user enter in the date they would like.
Very important!! - At least on my version of E10 - changing the Operation on a table or sub query criteria row, can sometimes change the Table field back to Company
select
[PartTran].[TranNum] as [PartTran_TranNum],
[PartTran].[TranDate] as [PartTran_TranDate],
(dateadd(month, -1, @EndDate)) as [Calculated_StartDate],
(@EndDate) as [Calculated_LastDate]
from Erp.PartTran as PartTran
where (dateadd(month, -1, @EndDate)) <= PartTran.TranDate
Are you entering a value for the parameter?
edit
Won’t matter if no parameter value is entered.
Check the formula for the Month calc field. And “Month” may be a reserved word. Try changing it to Mon (you’ll have to delete it and create a new one)
I know. I was just stating my personal preference.
There are some I know that still insist on using Month, Year, Name for column names … I tend to shy away from all of that on the SQL side just to prevent any possibility of mixups.
EDIT: Doesn’t Epicor add Calculated_ in front of it anyway?
It seems I had to use mm to fix it here. I thought I could have used month as I believe it is still correct syntax but I guess it just didn’t like that argument in this situation. Thanks for your help.
It also may have just been redoing the BAQ several times to clear out whatever syntax error was occurring.
Note: Your right. I probably shouldnt use “Month” as the calc field name but like Doug said I think Epicor changes it automatically and it wasnt causing any issues prior to this.
Thanks again.
Yes changing mm to month in the dateadd function causes the invalid parameter error. Seems very odd that its set like that it is only accepting ‘mm’ and ‘m’.
