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 digits 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.
1. Export all the Panel data with Shift-F7, Built in Short report, to a
disk file in WP format.
2. Delete all the Panel Data, Alt-F5, make sure only to delete the
panel data and do not empty the database
3. Add the new date field,. Put it adjacent to the current text field
holding the data. And use the format DMDY99-99-9999
4. Import all the data from the file you created in step 1. CTRL-F5
This should bring all the data back into the correct fields, and the new
date field should be empty
Create a report on the panel, to massage the field with the text date in it,
and then save it back to the field.
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 formula 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
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.
1. Change the edit order for the panel so that the Text Field P1F3 is
the last field in the edit list.
2. Export all the Panel data with Shift-F7, Built in Short report, to a
disk file in WP format.
3. Delete all the Panel Data, Alt-F5, make sure only to delete the
panel data and do not empty the database
4. Delete Field P1F3
5. 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
should 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: [email protected]
[mailto:[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
<http://www.stylelist.com/spring-fashion/spring-style/shopping-in-your-close
t?ncid=emlweusstyl00000001> !
_______________________________________________
Dataperf mailing list
[email protected]
http://lists.dataperfect.nl/mailman/listinfo/dataperf
_____
Can't afford a new spring wardrobe? Go
<http://www.stylelist.com/spring-fashion/spring-style/shopping-in-your-close
t?ncid=emlweusstyl00000001> shopping in your closet instead!
_______________________________________________
Dataperf mailing list
[email protected]
http://lists.dataperfect.nl/mailman/listinfo/dataperf
