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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
daniel_cAuthor Commented:
I know it's hard!
That's why I ask to you guys... ;-)
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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
guntherdsCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.