I'm trying to display the latest signed months of data, I've selected the MAX months and then I need to display all the months that are
<= the selected month in the prompt.
When running the report I am getting a Conversion failed as shown in the subject, the prompt loads fine but when actually getting to the data this happens.
I've tried a couple of converts and casts but to no joy. Also this is in a nested query, I don't know if I'm just trying to do something that isn't possible or if I'm just lacking knowledge or both ???
'AND tblsomething.Month <= (#prompt('DatePicked')#)' was the initial entry
This is one I tried but no joy 'AND CAST (tblsomething.Month AS DATETIME) <= (#prompt('DatePicked')#)'
Any help, tips or advice would be appreciated.
What is the contents of your tblsomething.Month column? Is it a string containing the month name or an integer containing the month number? If it contains something like "January" or "01" then there is no way to convert that to a date.
The Month is a DATETIME column.
Then you don't need to cast it.
Yeah that's what I thought, but with or without the cast I'm running into the conversion failed which I can't understand why.
I've now put in a Date Prompt object which is doing what I want it to do but it'd be nice to know why my initial method of doing it wouldn't work.
Apologies if it's too vague.
My guess would be that the problem originated with the prompt side of things, so using the right prompt control is a good way to go.
Will have to play around with it, recreate the problem and see if I can make it work another way :)
Thanks for your help and advice Lynn
What type of prompt control were you using originally? If it was a text box prompt then I would hazard a guess that the way the date was entered might affect the success of the cast.
A couple things to consider:
- You can specify the datatype as part of the prompt macro
- Understanding your database's default date format might be relevant. For example, the flavor of DB2 I am working with wants a string formatted as YYYY-MM-DD to cast to date. In previous years working with clients using Oracle the default string format for date conversion was YYYY-MON-DD (or something vaguely like if memory serves)
- Using the date control is a good way to make it foolproof for your users so they can specify the desired date without running into conversion issues
Good luck!