Thanks Brian, I inserted what I did and my progress in your reply below. I probably did have some '08 dates also. ColinL
-----Original Message----- From: Brian Hancock <[email protected]> To: [email protected] Sent: Wed, 29 Apr 2009 8:28 pm Subject: Re: [Dataperf] Report selecting two conditions in one field Hi Colin I can smell a Y2K issue with that two digit year. Although DP can use the two digit dates in field and has a field indicator to overcome y2K issues, there is no such protection with the CONVERT function which will use the 20th century so instead of 2009 it will be seeing it as 1909, and since there is no other function that will take your text field with two digit year and convert then you have to do some massaging. However since you have doing some work exporting and reimporting data into a test database here are some suggestions. For a start stop using a date format DMMDDYYYY and instead use the DMDY99-99-9999. as the field format. A few months ago someone pointed a serious bug when using the DMM-DD-YYYY format so the older method is safest… The formula you used is correct is P1F3 is a date field, but it you have used the bare format DMMDDYYYY without an delimiters then I suspect that importing text data which is formatted with an embedded “-“ and perhaps snidle d igits in the month an day parts, may be causing problems. The safest way to perform the conversion of your existing data, from a text to a date field is to add a new date field to your panel and then use a report to populate that field I presume you know how to add and remove fields in DP, but just a quick recap. Export all the Panel data with Shift-F7, Built in Short report, to a disk file in WP format. Delete all the Panel Data, Alt-F5, make sure only to delete the panel data and do not empty the database Add the new date field,. Put it adjacent to the current text field holding the data. And use the format DMDY99-99-9999 Import all the data from the file you created in step 1. CTRL-F5 DONE This should bring all the data back into the correct fields, and the new date field should be empty YES Create a report on the panel, to massage the field with the text date in it, and then save it back to the field. ???? I DONT GET WHAT SAVING IT BACK TO THE FIELD MEANS For example create the formula in say RV1.If all the data is in the 21st century it is easy, but if some is from the last millennium you will have a few extra problems. I will give you the formula for handling 21st century dates only, but if you need the more complex=2 0formula let me know and I will give you that ome /* Store value in Report Variable 1*/ CONVERT[“DMDY99-99-9999”; CAT.C[ SUBFIELD[P1F3;”-“;1] ; “-“ ; SUBFIELD[P1F3;”-“;2] ; “-“ ; “20” ; /* this bit will need modifying if you have a mix of 1900 and 2000 dates */ SUBFIELD[P1F3;”-“;3] ] ] This will store a number which will be 0 if it is a blank field or an invalid date, or else it will be a number representing the numbers of days since the 1st March 1900, the date the universe begins for DataPerfect. You can verify the data by printing RV1 using the field format DMDY99-99-9999 If you also output the P1F3 field and (put the scroll lock on while you run the report to mae it display only a page at a time) you will quickly see any errors that are going to appear. The most likely thing you will see will be blank data in RV1, meaning that the value is a 0, which either means you had no data in P1F3, in which both output fields will be blank, or an invalid date in P1F3 in which case you will see the entered data for P1F3 but blank for RV1 ALL THE P1F3 FIELDS CAME OUT BLANK I PUT THE ABOVE FORMULA IN THE REPORT BODY SECTION When you are content that you can save the data, save the value of RV1 into your panel’s new Date field If this all looks ok then the next thing you need to do is to remove the old P1F3 Text field, and to recap how to do that. Change the edit order for the panel so that the Text Field P1F3 is the last field in the edit list. Export all the Panel data with Shift-F7, Built in Short report, to a disk file in WP format. Delete all the Panel Data, Alt-F5, make sure only to delete the panel data and do not empty the database Delete Field P1F3 Import all the data from the file you created in step 2. CTRL-F5 This should get everything tidied up, and the report formula you want to use, but of course based on the new Date field P1Fx <=today OR P1Fx = 0 s hould work. I know this is a lot of work, but you did commit a couple of cardinal sins of using 2 digits years in your date and storing a date in a text field… tsk tsk Alternatively to converting your data, you could use the formula above to convert the text field into a date and just use it in the formula I sent before Regards Brian From: [email protected] [mailto:[email protected]] On Behalf Of [email protected] Sent: Wednesday, 29 April 2009 12:52 PM To: [email protected] Subject: Re: [Dataperf] Report selecting two conditions in one field Brian, I tried both ways. 1. Current situation with A8 text field entering dates as 02-13-09 in the body of the report I entered Ctrl F7, 1, 4 then the convert below for rv1 with no hard return after the formula. It had no effect on the report, everything was collected. 2. I started a test database to a new directory and deleted all the data and changed the date fields to date fields, DMMDDYYYY, I'm in LA. In the report body I put P1F3<=TODAY OR P1F3=0 plus the fields I wanted in the report. The report when run pulled out the first correct data, hung the computer requiring a reboot and in the field put Ec6g or something similar. No epiphany yet, what next, consider me a newbie^2 Thanks Colin -----Original Message----- From: Brian Hancock <[email protected]> To: [email protected] Sent: Tue, 28 Apr 2009 5:14 pm Subject: Re: [Dataperf] Report selecting two conditions in one field Hi Colin, If Field is text field you will need to convert it to a date (which is actually a number), assuming the down under format of dd/mm/yyyy you would use /* Stored in RVx */ convert[“DDMY99/99/9999”;Field ] <= today OR Field=”” which will set RVx to true (NOT 0) for Field less than or equal to today’s date if Field is blank, it will set RVx to false (ie 0) for everything else including invalid dates (eg 32/4/2009), which convert[] to 0 Its generally not a good idea to use a text field to store a date as it will allow invalid dates to be entered If Field is already a date field then Field <= today OR Field = 0 Regards Brian From: dataperf-bounce [email protected] [mailto:[email protected]] On Behalf Of [email protected] Sent: Wednesday, 29 April 2009 3:57 AM To: [email protected] Subject: [Dataperf] Report selecting two conditions in one field G'day all, I need a report which collects records based on a field (text field, ? should be date field). Records need to be20included which; 1 Have a date equal to or before 'today's date' in the field and 2 If the same field is blank. Thanks Colin Lambert Can't afford a new spring wardrobe? Go shopping in your closet instead! _______________________________________________ Dataperf mailing list [email protected] http://lists.dataperfect.nl/mailman/listinfo/dataperf Can't afford a new spring wardrobe? Go shopping in your closet instead! _______________________________________________ Dataperf mailing list [email protected] http://lists.dataperfect.nl/mailman/listinfo/dataperf
_______________________________________________ Dataperf mailing list [email protected] http://lists.dataperfect.nl/mailman/listinfo/dataperf
