Displaying records from today on (filtering out the old)

I am trying to filter out old records from a search. I tried this but
it still seems to display the old records.
gofind is the input box text query and it passes through fine. It
searches perfectly, just doesn't filter out old records.
It should show everything from today and into the future.
dDate = Date()-1
SELECT * FROM events WHERE title LIKE '%" & gofind & "%' AND date >= "
& dDate & " AND active='yes' OR comments LIKE '%" & gofind & "%' AND
date >= " & dDate & " AND active='yes' ORDER BY date ASC
Can someone help please?You need to enclose the two sections of your where clause in
parentheses. Try something like this:
SELECT * FROM events WHERE (title LIKE '%" & gofind & "%' AND date >= "
& dDate & " AND active='yes') OR (comments LIKE '%" & gofind & "%' AND
date >= " & dDate & " AND active='yes') ORDER BY date ASC
As it was written simply a match with the comments LIKE '%" & gofind &
"%' would result in an evaluation of true.
james.shearer@.gmail.com wrote:
> I am trying to filter out old records from a search. I tried this but
> it still seems to display the old records.
> gofind is the input box text query and it passes through fine. It
> searches perfectly, just doesn't filter out old records.
> It should show everything from today and into the future.
> dDate = Date()-1
> SELECT * FROM events WHERE title LIKE '%" & gofind & "%' AND date >= "
> & dDate & " AND active='yes' OR comments LIKE '%" & gofind & "%' AND
> date >= " & dDate & " AND active='yes' ORDER BY date ASC
> Can someone help please?
>|||Thanks Steve but that doesn't seem to make any difference.
The query works fine apart from the part:
date >= " & dDate & "
That doesn't seem to make any difference at all.
It should show records dated from a date greater than yesterday but it
just shows everything that meets the rest of the criteria ignoring the
date request.
Any idea?|||On 8 Nov 2005 20:05:09 -0800, james.shearer@.gmail.com wrote:

>Thanks Steve but that doesn't seem to make any difference.
>The query works fine apart from the part:
>date >= " & dDate & "
>That doesn't seem to make any difference at all.
>It should show records dated from a date greater than yesterday but it
>just shows everything that meets the rest of the criteria ignoring the
>date request.
>Any idea?
Hi James,
I don't know what front end you are using. Best would be to pass the
date as a parameter instead of passing it as text. That would prevent
any possible conversion problems, since native date/time datatypes will
be used.
If you must pass it as text, then the best way to troubleshoot this is
to display the query string instead of executing it. The part with the
date comparison should look like this:
date >= '20051109'
or
date >= '2005-11-09T22:40:34.550'
That is: the date string must be enclosed in single quotes, and it must
adhere to one of the unambigous date formats, which are:
* yyyymmdd for date only
(note: no dashes, slashes, dots, or other punctuation)
* yyyy-mm-ddThh:mm:ss for date and time
(note: dashes in the date, colons in the time and an uppercase T [not
a space!] between the two parts)
* yyyy-mm-ddThh:mm:ss.mmm for date and time with millisecond precision
(note: same as above; add a dot and then the milliseconds)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Display N/A Something When Data Does Not Exist

Hi I have a dataset with 2 columns Col1 is a list of dates and col2 is a list
of Values
On my report I put a filter on a list filtering out the dates with
expressions. Such as = (colDate.value = myDate)
But some time myDate does not exist in the dataset, so the after the filter
the list has no results.
What i want to achieve in this case is to display an N/A. i tried something
like this in the textbox contorl inside the list
=IIF(IsNothing(col2Value),"N/A",col2Value) but that does not work.
Any help is appreciated. ThanksHi,
Why not try something within your stored procedure
Select Col1(ISNULL, 'N/A') FROM TABLE
"gMaster" wrote:
> Hi I have a dataset with 2 columns Col1 is a list of dates and col2 is a list
> of Values
> On my report I put a filter on a list filtering out the dates with
> expressions. Such as = (colDate.value = myDate)
> But some time myDate does not exist in the dataset, so the after the filter
> the list has no results.
> What i want to achieve in this case is to display an N/A. i tried something
> like this in the textbox contorl inside the list
> =IIF(IsNothing(col2Value),"N/A",col2Value) but that does not work.
> Any help is appreciated. Thanks|||Is this in an actual "list" control? There is a property in the properties
panel, called "NoRows" .. you simply type the text you want to display when
there are no rows in the list. Hope that fixes your problem.
"gMaster" wrote:
> Hi I have a dataset with 2 columns Col1 is a list of dates and col2 is a list
> of Values
> On my report I put a filter on a list filtering out the dates with
> expressions. Such as = (colDate.value = myDate)
> But some time myDate does not exist in the dataset, so the after the filter
> the list has no results.
> What i want to achieve in this case is to display an N/A. i tried something
> like this in the textbox contorl inside the list
> =IIF(IsNothing(col2Value),"N/A",col2Value) but that does not work.
> Any help is appreciated. Thanks|||thanks that worked.
"isamu" wrote:
> Is this in an actual "list" control? There is a property in the properties
> panel, called "NoRows" .. you simply type the text you want to display when
> there are no rows in the list. Hope that fixes your problem.
> "gMaster" wrote:
> > Hi I have a dataset with 2 columns Col1 is a list of dates and col2 is a list
> > of Values
> > On my report I put a filter on a list filtering out the dates with
> > expressions. Such as = (colDate.value = myDate)
> >
> > But some time myDate does not exist in the dataset, so the after the filter
> > the list has no results.
> >
> > What i want to achieve in this case is to display an N/A. i tried something
> > like this in the textbox contorl inside the list
> > =IIF(IsNothing(col2Value),"N/A",col2Value) but that does not work.
> >
> > Any help is appreciated. Thanks