Strict Date

[jjgraf]

OK i run into a bit of problem with VFP 7 and this strict date format and my ERP software Database.

I connect to the ERP software database which sends the date fields in the Year-Month-Date format and the cursor stores it in that format. Now when I try to do a select on the Cursor like so

SELECT * from ack WHERE entry_system_date >= {11/01/2001} AND entry_system_date <= {11/30/2001}

i get ambiguous Date Error message telling me to use {^2001-11-01} format Year-Month-Day. if i do that, it works.

I'm not against doing that way but how do i convert a TextBox with a date value into that format without doing this

ldFrom = &quot;{^&quot;+ STR(YEAR(goDates.txtFromDate.Value),4)+&quot;-&quot;+ RIGHT('00'+ALLTRIM(STR(MONTH(goDates.txtFromDate.Value))),2) +&quot;-&quot;+ RIGHT(&quot;00&quot;+ALLTRIM(STR(DAY(goDates.txtFromDate.Value))),2) + &quot;}&quot;

ldTo = &quot;{^&quot; + STR(YEAR(goDates.txtToDATE.Value),4)+&quot;-&quot;+ RIGHT('00'+ALLTRIM(STR(MONTH(goDates.txttoDate.Value))),2) +&quot;-&quot;+ RIGHT(&quot;00&quot;+ALLTRIM(STR(DAY(goDates.txttoDate.Value))),2) + &quot;}&quot;

BTW i did turn off Strict Date but to no avail

 

[marsh]

Check out the date() function (VFP 6.0).
If you have a character representation of a date in CCYY-MM-DD format, try this:

cFromdt = &quot;2001-11-30&quot;
cNewFromdt = strtran(cFromdt,&quot;-&quot;, &quot;,&quot
dFromDT = date(&cNewFromdt.)

Then use dFromDt in your query

If the input box uses the &quot;/&quot;, substitute it for the &quot;-&quot; in the string transformation funtion.

 

[jjgraf]

i like that, but you make the assumption its in CCYY/MM/DD

I will get skinned alive by the users if i make them type in YY/Month/Day instead of Month/day/Year and for the most part the users use the lose date feature of Foxpro by only typing in Month/day.

BTW i also played with Set Date where you change the display format but still get the ambiguous Date Error.

 

[ramani]

HI

In your form...

1. InitEvent
*********
PRIVATE myStartDate, myEndDate
STORE {} TO m.myStartDate, m.myEndDate

2. In the text box you use to accept the start date and end date.. make the control source as..
m.myStartDate and m.myEnddate respectively.

The advantage of (1) is .. the variables are available thru out this form and the called reports.The variables are initialised as date variables.. and st to your prefered SET DATE format. The advantag of (2) is that the date acceptance is restricted to date format.. errors cannot get in the format.. such as 31 Nov etc.

3. Validate suitably somewhere.. that
m.myStartDate NOT > m.myEndDate .. else set focu back to the textboxes of dates.

4.
SELECT * FROM ack WHERE ;
BETWEEN(entry_system_date,m.myStartDate,m.myEndDate)

This shall take you thru easily ramani :-9
(Subramanian.G),FoxAcc, ramani_g@yahoo.com