Re: [dba-users] Problem filtering a form on a date field

[Andrew Jensen]

Walter A. March wrote:

sub onSearchDate( oEv )
dim dtSearch
dim oForm
oForm = oEv.Source.Model.Parent
dtSearch= oForm.getByName( "dtDateFilter" )
oForm.Filter = "startDate = " & "{D '" & dtSearch.text & "' }"
oForm.ApplyFilter = True
oForm.Reload
end sub
The error (assuming I choose July 30, 2006 from dtDateFilter):
OpenOffice.org Base
The data content could not be updated
Wrong data type in statement [SELECT * FROM "Appointments" WHERE 
"startDate"=  '07/30/06'  ORDER BY "startDate" ASC, "startTime" ASC, 
"endDate" ASC]

oh... did I mention the form is sorted? ;)

Doesn't matter for these purposes, I don't think.


As a test, I tried the following line of code:
oForm.Filter = "startDate = " & "{D '" & "2006-07-30" & "' }"
This worked fine!!

Right, note the difference in the date format, 2006-07-30 and 07/03/06. 
Here is the catch that has you caught, so to speak. The date control's 
text property returns you the short date format and what need is the 
date formatted YYY-MM-DD. So the question how to get that.

Assuming the control is bound to a database column of type date you can 
do this

sub onSearchDate( oEv )
dim dtSearch
dim oForm
oForm = oEv.Source.Model.Parent
dtSearch= oForm.getByName( "dtDateFilter" )
dtVal = dtSearch.BoundField.getDate   REM using getdate from the
oForm.Filter = "startDate = " & "{D '" &  dtVal.Year & "-" & dtVal.Month 
& "-" & dtVal.Day  & "' }"
oForm.ApplyFilter = True
oForm.Reload
end sub



---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]