About Query Filtering

About Query Filtering
---------------------
I have this 1 query component; just call it: qu_Browse.
The SQL statement of this query is like this:

SELECT
    QHEADER.QNOA,
    VENDORHEADER.VENDORCODE,
    VENDORHEADER.VENDORNAME,
    QHEADER.QDate,
    QHEADER.VendorQNum,
    QHEADER.Validity,
    QHEADER.TermOfPayment,
    QHEADER.Status
FROM
    QHEADER INNER JOIN
    VENDORHEADER ON
    QHEADER.VendorCode = VENDORHEADER.VENDORCODE
ORDER BY
    QHEADER.QNOA

From that query, I've used 'Fields Editor' and I set DISPLAY FORMAT for:
  * QDate: mmmm dd, yyyy
    So, if QDate = '01/30/1999', the display in dbgrid will be 'January 30, 1999'

  * Validity: ##0 days
    So, if Validity = '10', the display in dbgrid will be '10 days'
 
  * TermOfPayment: ##0 days
    Same with Validity.

My problem is this:
When I am in browsing mode (I've used DbGrid to show all data), I provide
a TEdit for searching data, and I use 'Filter' & 'FindFirst' for searching data.
When user wants to search based on QDate, he/she will type 'J', because in grid,
the display is 'January 30, 1999'. In fact, in the real data, it's not stored
like that, but '01/30/1999'. So, 'Filter' will be failed.
The error message is 'J*' is not valid date and time.
Oh yes, I always add '*' in the end of filter to make it more flexible.

This problem will occur too when user wants to search based on 'Validity' or 'TermOfPayment'.
How to solve this problem?
 

Like usual, need a help from any Delphi experts,

Daniel
LVL 7
daniel_cAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
guntherdsConnect With a Mentor Commented:
We've also had this problem (unfortunately), but we've solved it!
When you fill in the filter property to filter on dates you HAVE to use the format mm/dd/yyyy, you don't have to concern about the regional settings.
(I'm not 100% sure the format is mm/dd/yyyy because I can't check right now, I'm not at work)
But a better (and more professional solution) is to the OnFilter event of the query.
You could define a boolean field and this this field in the OnFilter if you have to filter on the dates.  In the OnFilter you can then reject records that don't suit the date limits.

(Or you can refetch the data from the DB, but this will cost you time and network traffic)

Have fun,
if any problems mail me at guntherds@sycron.be
0
 
RBertoraCommented:
Check out the datetimeformats supported by delphi with the shortdateformat variable:

So you can do something like this to
format all your date fields

procedure TForm1.FormCreate(Sender: TObject);
begin
  ShortDateFormat := 'DD/MM/YYYY';
  DateSeparator := '/';
end;

however you cannot use the longdateformat for input, only for display.. I suggest you use the TDateTimePicker Component for all your date inputs.

As for querying just a month say only january.. he he you will have to do that the hard way I'm afraid.

Rob ;-)
0
 
mhervaisCommented:
I am afraid you will have to do the conversion yourself, and then apply the correct sentence int the Filter property of your TQuery.

but other people might know more than me about this conversion problem

regards, marc
0
Take Control of Web Hosting For Your Clients

As a web developer or IT admin, successfully managing multiple client accounts can be challenging. In this webinar we will look at the tools provided by Media Temple and Plesk to make managing your clients’ hosting easier.

 
daniel_cAuthor Commented:
I know it's hard!
That's why I ask to you guys... ;-)
0
 
kretzschmarCommented:
hi rob ;-)
hi daniel,

you can use a calculated field (just add it in the fields editor)
in your oncalcfields-event transform your date into this calculated field as string in your recommended dateformat.

now filter on the calculated field

meikl
0
 
RBertoraCommented:
you can use a calculated field !!!
ayeeee!!!!!!! Talking about calculated
fields any progress on my question Meikl?

Based on that experience I tentatively recommend not to use calculated fields.

Rob  ;-)
0
 
kretzschmarCommented:
hi rob,

i've not forgotten your q,
sorry for the delay, but i was absent for some days.

but your experience with calculated fields should not reflect this q.

i will take a look on it today, i let you know.

meikl ;-)
0
 
RBertoraCommented:
Great thanks :-)
0
 
AndyDCommented:
One approach would be to place all your months in a TCombo and use the Index of the selected item to form your filter. Alternatively if you're serious about this kind of stuff you might want to check out Infopower 2000 by Woll2Woll software http://www.woll2woll.com which has filter dialogs (amongst many other things) that will make your life so much easier.
0
All Courses

From novice to tech pro — start learning today.