Converting a Date field - Numeric Size error

[MoGrace]

I took an xls spreadsheet and formatted the dates to what I thought would be acceptable to AA. Originally the DOB field displayed as 22-Jan-1947 which I formatted to 01/22/1947. Then I saved the xls as a dbf file. In the table structure the date fields look correct as they show as D,8. However, I can't append or post the DOB to another Alpha table without this error. So I changed the field to be C,10 to see what it looked like, and I have 19470122 as the data which is the result you get from the CDATE() function. If I add another field to be the new DOB field, how do I convert the old field into a real date field?

?ctod("19470122")
={ / / }

Of course the answer is a function I never had reason to use

?stod("19470122")
= {01/22/1947}

Now to see if that works in an update for the new field...

Nope, I get an 'invalid date string' error

Edit: Even though it erred, it did do it, so I will try my post op again and see if it works now

Well the post op failed with the same numeric size error using my new date field. Any ideas on what else I might try?

[Stan Mathews]

Re: Converting a Date field - Numeric Size error

stod() will error if the value supplied is blank or not valid. Wrap it in *if_error().

dt = "19470122"
? stod(dt)
= {01/22/1947}
dt = ""
? stod(dt)
ERROR: Invalid date string
? *if_error(stod(dt),{})
= {  /  /    }

I think I put that in the code archive.

Is it possible your numeric size error is triggered by a calculated field rule triggered by the post op?

[MoGrace]

Re: Converting a Date field - Numeric Size error

Found a post that suggested another calc field rule might be tripping this error (which I did check first). And of course that was it though I don't know why. I have an age field of N,2 that calculates the age using age(dob) - and there are no ages over 2 digits. However setting this field (temporarily) to N,5 allowed the post to run. I suspect the Age() function computes a fractional amount that it then rounds off. I wonder why this only affects ops (append & post) and not data entry?

I do have some blank DOB values - thanks for that tip Stan.

[MoGrace]

Re: Converting a Date field - Numeric Size error

This is interesting - when I went back to my Age field to reset its size, I got an error message that explains my problem. Apparently record 959 has an age value of -2790.000000 which will not fit in a field with a numeric size of 3. Now to trace down that record...

Gotta love users - found the record; I guess you can't be born in 08/05/4807...lol

[MoGrace]

Re: Converting a Date field - Numeric Size error

This example presents a new challenge - how to validate a proper date range for a DOB field? I tried using the field rule CanWrite event but it is not giving me the right message that goes with the error I am trying to trap. Here's what I tried:

If f_member->dob > date() then
   msgbox("Date of Birth","DOB cannot be greater than Today, please correct")
   cancel()
else if year(f_member->dob) < (year(date()-100) then
   msgbox("Date of Birth","DOB is out of the valid range, please correct")
   cancel()
end if

On the first error, I got the 2nd message when I entered 01/01/2020!

[Ted Giles]

Re: Converting a Date field - Numeric Size error

I tried putting the date check in the Validation section of the Field Rule for that event

dob < date()-100
Message = "Out of date range, please check year and re-enter"

[MoGrace]

Re: Converting a Date field - Numeric Size error

I tried that place first but got nothing - will try it again...

[Ted Giles]

Re: Converting a Date field - Numeric Size error

This was in V11 Robin.
I have a very old V12 if you want me tobtry it?

[MoGrace]

Re: Converting a Date field - Numeric Size error

I'm still working on it - right now I get the 2nd error msg on every entry into the field. I would be glad to see whatever you come up with!

[Ted Giles]

Re: Converting a Date field - Numeric Size error

dates.JPG
Well this took some digging Robin. The pic above is from the Custom Validations.
I used the whole date and took approx. 100 years off as defining the past, and added one day to the future.
It was all I could think of - not elegant but it works.
I added the .T. as a test and I don't think it makes much difference but as it is working, I left it in.
You may have issues with V12 Error Messages, but this logic definitely works!
I can send you a zipped version if you wish, but I just added the test to me TestBed and you wouldn't want al of that!

[MoGrace]

Re: Converting a Date field - Numeric Size error

Hi Ted,
The logic seems wrong to me which is probably why I couldn't get it to work. In your example, you have the test for which dates are valid and so a F triggers the message - right? I think mine was doing it the other way around!

[Ted Giles]

Re: Converting a Date field - Numeric Size error

Like I said, it took some digging!
Are you good now?
If you edit the form and then edit the field rules from within the form, you get a better description of the features.