In the report, define the field as character, and then in the table, define a calculated field "A" using the ctod() function. You should then see which ones are not valid dates as Monarch will recognise them as null.
Define another calculated field "B" which adds one to the month and sets the day as "01", and ctod that. Then the final calculated field "C" as if(isnull(A)=1;B;A). Hide A & B, export your data into Access, and column C should give you good data.
Hope this helps,
Hello p12 and welcome to the forum.
Dates are often 'interesting' because different formats are used in different parts of the world.
Unless you have something very peculiar going on Monarch should allow you to specify the default date format suppied by a report that might have a different default to your PC's regional date setting. There are a couple of ways to make adjustment and revisions to thing to suit inputs with unfriendly date formats. Olly has indicated one approach above.
Have you already checked for consistency of format between your PC and the report you are working with?
I am importing a report into monarch 11.3 which includes a column "Date Due". The report has a number of dates which are outside of the regular calendar. For example, 04/31/2012 and 02/30/2012. When I import the data from Monarch into Access, the dates are not importing into Access and I am getting error messages. If I manually adjust the dates to the first day of the next month, it will import ok. I am trying to find a solution which will not require manual corrections. Any ideas?[/QUOTE]
Quick question. Is there a specific reason your report has dates that don't exist?
The report is from an outside source, external to my organization. I assume it may be how the date due (the date that the next loan payment is due) is being calculated and is not taking into account the actual number of days in the month or if it is a leap year.[/QUOTE]
Is the report setup old? Is it prone to errors? I only ask as adding values to dates is fairly common and simple in almost all programming languages and software products. I haven't seen bad date values like that on a report since college.