The value '' cannot be converted to a number: Help anyone

[djkhalif]

Good morning all,

I have this query below, it works fine when I select a start date with a single digit day, ie. 9/7/2009. But any selected start date after the week containing 9/7/2009 I get this error:

The value '' cannot be converted to a number

SELECT     DATEADD(wk, DATEDIFF(wk, 0, DateProd), 0) AS Weekly_Production, sum(UnitsProd) AS Total_Units
FROM         tbl_Assembly_Production
WHERE dateProd Between '#FORM.dateFROM#' and '#FORM.dateTo#'
GROUP BY DATEADD(wk, DATEDIFF(wk, 0, DateProd), 0)
ORDER BY Weekly_Production

Has anyone seen this before? I've checked my table and all the dates seem good.

Thanks in advance,

djkhalif

[Dan_Bracuk]

Chances are your query is not returning any records and the error occurs when you try to process it.  Either cfdump it or look at your debugging info to verify this.  Other comments:

This:

DATEADD(wk, DATEDIFF(wk, 0, DateProd), 0) AS Weekly_Production

is very strange.  Datediff returns a number and DateAdd returns a date.  When you say that it works fine under certain circumstances, it might be running error free but could be giving you an incorrect answer.

This:

WHERE dateProd Between '#FORM.dateFROM#' and '#FORM.dateTo#'
has two or three problems.  The first is that you did not convert the strings to date objects.  The second is that you are not using cfqueryparam.  The third is that, if the datatype of dateProd has a date and time, you may be missing records.  Better logic would be

where proddate >= date1

and proddate < the day after date 2

[djkhalif]

Dan,

Thanks. I will try that this weekend.

djkhalif

[djkhalif]

Dan,

I uploaded my coded; would you mind looking at it?

Thanks,

djkhalif

[djkhalif]

Dan,

I posted my code; would you mind looking at it?

[Dan_Bracuk]

Where did you post it?

[djkhalif]

Dan,

I thought it had uploaded but it didn't happen.

[djkhalif]

Dan,

I found the problem. When I duped the pages all of the queries from the original were still on the duped pages. I removed the unnecessary <cfquery>'s from the pages and now they work fine.

Thanks,

djkhalif