Search and Add Time Problem

[vbcoder2]

Hi Friends

These days i m working on a s/w for cable operators . and i need to ask two Problems i m facing.........

Problem 1


In the Program i need to qry database with respect to time.....

Actually i m running a movie in media player displayed in form ...
Now i need to make it dynamic . Wat i mean to say is Program runs the movie , Song on the schedule .
Now when i qry the Access database for the movie between 08:30:00 Pm and 12:20:30 Am i get Two Results

1. i get all the records independent of time...
2. i get no Result..........

the Qry Does not seems to work.........

Here is the Qry ..........

Select * from table where MovTime is >= #"& startTime &"# and movietime <=#"& endtime &"#"




i have defined the field date/Time and in format long time....... in the database.........

Problem 2

i need to Add the time............

E.G i give movie the starting time... and i get the movie Total Time.... now i need to calculte the End Time ... How should i approach this...... one Solutio i have is

To convert hours and minutes into sec for starting time also the movie duration time........ and then add these seconds to obtain the max seconds and then return the hr and minutes back..........

at the same time i need to make sure that AM and PM are ok


Really in Tension

Plz do solve my Problem if u can ...........


Normal Vb Coder

[si_the_geek]

Problem 1 - you should use the Between comparison that SQL provides, eg:

sql = "Select * from table where MovTime between #" & startTime &"# and #"& endtime &"#"

Problem 2 - just add the dates, eg:

Dim startTime as Date, duration as Date
startTime = Now     '(just example data)
duration = "00:30"  '
 
endTime = startTime + duration

[vbcoder2]

I have Tried it 2 but same results

c i have saved Records in the database field with 07:00:00 Pm and then 07:30:00 Pm Now when i search for the record between 07:00:00 Am to 07:30:00 Am it gives me the same result mean i record found....
it does not differentiate bwtween Am and Pm .

[si_the_geek]

ah, I think I see the problem!

am I right in assuming that the data type of the date in the database is text/string rather than date?

if so the issue is caused by string comparisions, each character from the left of the strings is compared until a difference is found (so 07:00:00 Pm is < 07:30:00 Am )

there are two things I can think of that you could do. the first is to store it as a date instead of text - which would allow you to do date/time based searching (this is probably better, and is the way I would do it). alternatively you could store the times in 24 hour format instead of am/pm - which would allow string matching to work the way you want it to.

[vbcoder2]

Yeah Quite Right U R

i have Tried it both ways i mean string and date.........

in case of date it works fine but the Problem arises when i have to search records like this..........


c i m Playing a movie which starts at 10:00:00 Pm and finishes at 01:00:00 Am .Now when i search for any thing between this time it does not gives me any thing ....

I have tried both between and < and > Operators but cant find a sol....

it works fine when i search between 07:00:00 Am and 10:00:00 Am similarly with Pm . but when there is a change it gives me all the records in the table other than between this time....


and u r right i should have used time format with 1, 2 ,3 23,24 but the Client does not want it this way.... so i have to stick with AM and Pm

[dglienna]

LOL... ComCast had the same problem a few weeks ago!

You/They need to incorporate a DATE in the time or as a separate field. Use VB to properly fix things in the meantime.

[leinad31]

An access database? Try CDate() on the text

Other db types try CAST

[dglienna]

Add the checkmark to the first post in the thread to mark it as RESOLVED.

[si_the_geek]

As has been mentioned, you should be storing a proper date rather than just times, but then whether or not this is possible depends on the data (if the times are the same every day it might not be the best method).

and u r right i should have used time format with 1, 2 ,3 23,24 but the Client does not want it this way.... so i have to stick with AM and Pm

How you display the data does not affect how you store it, use functions such as Format to convert the data from how you want to store it to how you want to display it (and vice versa).