Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 6 of 6 total
Thread Using locate against a TDateTime field?
Thu, Oct 30 2008 2:55 PMPermanent Link

"aroberts"
I am trying to use the Locate method in a query against a DateTime field.
I'm using the standard syntax:

SDat := DateTimePicker1.Date;
qryOrders.Locate('OrdDat',SDat,[loCaseInsensitive,loPartialKey]);

It doesn't work.  Even though the DateTimePicker doesn't return a
fully-formed DateTime, doesn't the loPartialKey compenstate for that?  Or
should SDat be a string var, and formatted in a special way?

Thanks.

Thu, Oct 30 2008 3:53 PMPermanent Link

"Robert"

"aroberts" <aroberts@roboticstech.com> wrote in message
news:E6A04938-0151-4651-96EA-F165F53E6173@news.elevatesoft.com...
>I am trying to use the Locate method in a query against a DateTime field.
>I'm using the standard syntax:
>
> SDat := DateTimePicker1.Date;
> qryOrders.Locate('OrdDat',SDat,[loCaseInsensitive,loPartialKey]);
>
> It doesn't work.  Even though the DateTimePicker doesn't return a
> fully-formed DateTime, doesn't the loPartialKey compenstate for that?  Or
> should SDat be a string var, and formatted in a special way?
>

If the OrdDat field is a database date field, then get rid of the parameters
in locate. A date is a float number, and your parameters only make sense for
a string. In fact, I have no idea what they do for a numeric field like
float or integer, but I suspect bad stuff. So if what you want is an exact
match,  just eliminating the parameters should do it.

Now if what you want is to position yourself at or near the date entered,
then you have a different problem. I would work with strings, select an
additional field, the date as string, and then do the locate on that field.
Maybe there are other solutions.

Robert


Fri, Oct 31 2008 3:00 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

aroberts


I'll support what Robert says but also add that if you field is storing a date and time Locate will never work (remember it stores down to milliseconds), and even if only a date there's a fair chance it won't.

I'd suggest either using sql to get at the records key and then using that to do the locate or having another table component and use range (a millisecond less to a millisecond more) or filter (>=)  to trap the desired date and then pick the top record and use the key to do a Locate.

Roy Lambert [Team Elevate]
Fri, Oct 31 2008 10:53 AMPermanent Link

"Robert"

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:2576C6E8-3688-4500-8924-6B762FE29730@news.elevatesoft.com...
> aroberts
>
>
> I'll support what Robert says but also add that if you field is storing a
> date and time Locate will never work (remember it stores down to
> milliseconds), and even if only a date there's a fair chance it won't.
>

IMO it is important to keep in mind that unless you require a live result
you are not limited in a select to the actual fields in the table, you can
construct other fields if necessary. Especially useful when dealing with
dates. For example is t is a timestamp

select t, cast(t as date) td, cast(t as char(18)) ts, cast(extract (year
from t) as integer) y,
cast(extract (year from t) as largeint) * 10000 + cast(extract (month from
t) as integer) * 100 + extract (day from t) lt
from sometable

Pull a column in the format that will be most useful to you.

Robert

Fri, Oct 31 2008 12:32 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com


<< I am trying to use the Locate method in a query against a DateTime field.
I'm using the standard syntax:

SDat := DateTimePicker1.Date;
qryOrders.Locate('OrdDat',SDat,[loCaseInsensitive,loPartialKey]);

What is the actual type of the field that you're searching on ?  Is it a
ftDate or ftDateTime field ?  And, as already indicated by others, you don't
need the Locate options for searching on a date/time field.

--
Tim Young
Elevate Software
www.elevatesoft.com

Fri, Oct 31 2008 2:36 PMPermanent Link

"aroberts"

"Tim Young [Elevate Software]" <timyoung@elevatesoft.com> wrote in message
news:C5B1A609-D064-4C70-B23B-B7172BBCD1EB@news.elevatesoft.com...
>
> << I am trying to use the Locate method in a query against a DateTime
> field. I'm using the standard syntax:
>
> SDat := DateTimePicker1.Date;
> qryOrders.Locate('OrdDat',SDat,[loCaseInsensitive,loPartialKey]);
>
> What is the actual type of the field that you're searching on ?  Is it a
> ftDate or ftDateTime field ?  And, as already indicated by others, you
> don't need the Locate options for searching on a date/time field.
>
> --
> Tim Young
> Elevate Software
> www.elevatesoft.com


The field is an ftDateTime field, and as mentioned by others, the time
portion is messing with it.  I am going to try sql to do this as was suggest
by those who know much more than I.  Thanks to all.


>
>

Image