Page 1 of 1

dbpQuery on time field

PostPosted: Wed Nov 06, 2013 5:53 am
by paeon09
Hi all

I have a table with a time field, and in the time field I have a value 08:16 which represents 16 minutes past 8 am, now
I have a variable with this value 08:16 and wish to find the value in the time field.

dbpCreateTable "TM" "user_times" "id AutoInc;user_id Integer;c_date DateTime;n_date Integer;at_on Time;at_off Time;at_time Time"

[usertime] holds 08:16
user_times is my table name
user_times.at_on is my field name

I have tried::
dbpQuery "TM" "user_times" "user_times.at_on = #[usertime]#"
and
dbpQuery "TM" "user_times" "at_on = #[usertime]#"
and
dbpQuery "TM" "user_times" "user_times.at_on = [usertime]"

but I get::
dbpError=Parameter object is improperly defined. Inconsistent or incomplete information was provided

I know its me, but having trouble working it out.

The idea of the table is to have a time in and a time out then calculate the time difference
of the two.

I am not sure if a time field is strictly necessary here?

any help greatly appreciated

Tony

Re: dbpQuery on time field

PostPosted: Wed Nov 06, 2013 6:01 am
by mishem
should work

dbpQuery "TM" "user_times" "user_times.at_on = [#34][usertime][#34]"

Or can not return to a variable 08:16 and 8:16.

I have not tested it, just conjecture.

Re: dbpQuery on time field

PostPosted: Wed Nov 06, 2013 6:19 am
by Gaev
paeon09:

If your database is of the MS Access kind, note this information in the Help file related to dbpCreateTable ...
Time Time. This type is not supported by MS Access, use DateTime instead.**


If you need to query "time of day" in your application, consider storing it in an Integer field with a value representing "minutes from midnight" e.g. 8:16 (am) would be stored (and queried) as 496.

Re: dbpQuery on time field

PostPosted: Wed Nov 06, 2013 12:28 pm
by Neosoft Support
You may have to format the time in a specific way. For example:

SetVar "[usertime]" "8:00:00 AM"

Then perform a query like this:

dbpQuery "TM" "user_times" "at_on = #[usertime]#"

Re: dbpQuery on time field

PostPosted: Thu Nov 07, 2013 4:48 am
by paeon09
Many thanks everyone

Gaev was spot on, I was using a access db(did not rtfm) also used your suggestion Gaev
to use int field with mins from midnight, thank you.

Tony