convert to date

[Andy Gill]

Im sending a report to open in pdf.
Im trying to filter the data depending on the date i select.
"Pickup_Date" is the field i want to use as a filter.
i get this error when i try to execute it:

Errors in: event.AfterValidate code:
Invalid report filter expression: 'Pickup_Date = "03/14/2005"'.

Help.
"-----------this is the code-----------"
dim filter as C
dim order as C
dim invnum as C
dim filename as C

'since InvoiceNumber is a multi-select DropDownBox, it is actually an array that contains the selected values.
invnum = invoiceNumber.dump()
invnum = remove_blank_lines(invnum)
invnum = *for_each(x,"Pickup_Date = " + quote(x),invnum)
filter = stritran(alltrim(invnum), crlf()," .or. ")
Order = ""

filename = session.session_folder + chr(92) + "tempreport.pdf"

filename = report.saveas("pickup@"adb_path"\order_all.set", "pdf", filter, order, filename,.f.)
if file.exists(filename)
currentform.RedirectTarget = session.session_url + "tempreport.pdf?" + rand_string(5)
end if
Skipreport:

[Selwyn Rabins]

RE: convert to date

'Pickup_Date = "03/14/2005"'

is not valid because pickup_date is (presumably) a date field and "03/14/2005" is a character value.



Pickup_date = "{" + "03/14/2005" + "}"

is a valid expression because date values in a5 are enclosed in "{}". e.g. {1/1/2005}

if your date value is a variable, called say whatDate, then the filter is:

pickup_date = "{" + whatDate + "}"

[Andy Gill]

RE: convert to date

Selwyn,
Where exactly would i put that code?
i've tried this:
invnum = *for_each(x,"Pickup_Date = " + quote(x),"{"+invnum+"}").
but it doesn't work.

Andy.

[Lenny Forziati]

RE: convert to date

You need

invnum = *for_each(x,"Pickup_Date = " + quote("{"+x+"}"),invnum)

[Ren� Stout]

RE: convert to date

Selwyn,

If I want to use this for my dutch settings on a WAS server that has (for me) unknown date setting, can I than "denationalize" this, f.i. using the ctod() function or so?
(still fighting against national standards....)


Greetings, Ren�

[Selwyn Rabins]

RE: convert to date

you could use this function to get the date format on the server:

? a5_get_local_info(1024,31)
= "M/d/yyyy"

[Ren� Stout]

RE: convert to date

Thanks, but this doesn't help much.
Here's the situation:
On my local development station I define (in Windows) date as dd-mm-yy. In A5 I cannot find a place where I can define the date format. Neither in table structure or fields rules or in view,settings or in database settings.

In the Grid Builder, when I change nothing, the date will be displayed the Windows way, in my case: dd-mm-yy or dd-mm-yyyy.

Now I host the database on the alpha5host.com site. There the default date format is mm/dd/yyyy.
By setting the display format in the grid fields to time("dd-MM-yy","value"), I can force the WAS to display dd-mm-yy.
BUT: when I define a Search field, I must enter the mm/dd/yy format, else the search won't work. I cannot find a way to neutralize this.
I think I managed my built in filters by using cdate(Fieldname).

So: the Search in grids is still a problem because I don't see a way to translate the dutch search command dd-mm-yy into a US internal search command.

Ren�

[Andy Gill]

RE: convert to date

I still get the same error.

Andy

[Lenny Forziati]

RE: convert to date

Since you are doing other things to the filter as well, I would suggest looking at the final value just before your report.saveas(). Try adding something like

save_to_file(filter,"c:\filter.txt")

Then look at the contents of that file after running your code.

[Andy Gill]

RE: convert to date

I followed Lenny's tip, to create a text file:
save_to_file(filter,"c:\filter.txt")

the content was:
Pickup_Date = "{03/14/2005}"

its still invalid, how to i go from that, to what selwin suggested in earlier comments, this:
Pickup_date = "{" + "03/14/2005" + "}"

Andy.

[Peter.Wayne]

RE: convert to date

You need
invnum = *for_each(x,"Pickup_Date = " + "{"+x+"}",invnum)

You don't need the quote() function unless Pickup_Date is a character field.

[Andy Gill]

RE: convert to date

Thanks Peter, that worked. One last question about this matter, how come when i submit the form i have to reload in order to see the pdf file?

Andy.

[Peter.Wayne]

RE: convert to date

Try response.redirect() instead of currentform.redirectTarget(), but I don't know if that will make a difference. I don't know why you have to reload.

[Andy Gill]

RE: convert to date

It didn't work, it just reloaded to the same page.

Andy.

[Selwyn Rabins]

RE: convert to date



I have thought of a solution to the problem you have mentioned.

To summarize the problem, you have a grid with a search component. The search component has a date field.
The WAS server is located on a machine where the regional settings are set for US dates (i.e. MM/DD/YYYY)
The Users, however, wants to search using date formats of DD/MM/YYYY


Here is how you can solve the problem.

You need to edit the .a5w page that displays the component.

Assume that your component name is "grid1" and that the date field is called "date1"
Note that component name is actually set in this code block with the command
with tmpl
componentname = "grid1"
end with

Switch to source mode.
In the "%A5 codeblock that loads the component, you need to add this code BEFORE the component is actually run


if eval_valid("grid1_s_date1") then
if grid1_s_date1 "" "" then
grid1_s_date1 = "" + date_value(val(word(grid1_s_date1,3,"-")),val(word(grid1_s_date1,2,"-")),val(word(grid1_s_date1,1,"-")))
end if
end if


The way this code works is as follows:
if the user clicked the search button, then variables are submitted with the page. the variable names of the form:
componentName_s_fieldname

if the user entered a value in the search field, it will be non-blank and will look like (say) 31/12/2005

you need to intercept this and change it to 12/31/2005 before the grid component sees it.

this is what the above code does.