TIME field with VB6 on a SQL statement

[wakkara]

Hi there

i have a problem with this SQL statment




Public Sub dbventasturno()

strNombreDB = "D:\Mi escuela\proyecto extra aula\caja.mdb"
strNombreRS = "venta"


Set DB = DBEngine.OpenDatabase(strNombreDB)
Set RS = DB.OpenRecordset("select foliov & chr(9) & nomp & chr(9) & appp & chr(9) & apmp & chr(9) & sexo & chr(9) & nomM & chr(9) & fechanp & chr(9) & NomS & chr(9) & costo & chr(9) & horaV & chr(9) & billete & chr(9) & cambio & chr(9) & tpv as todo from venta where horav >= " & RS1.Fields("horaL") & " and horav <= " & Time & " ;")

End Sub


the error is here:

where horav >= " & 04:12:10 p.m.& " and horav <= " & Time & " ;")

it says , syntax error (missing operator) on the expresion
horav >= 12:25:15 p.m. and horav <= 04:12:10 p.m. '

its funny to me, cause 12:25:15 p.m. its whats on the DB is and 04:12:10 p.m. is the actual time.

ANYWAY,
the statment i want to make, is to select all the records that have the time between the time that is on an specific record ( RS1.Fields("horaL")) and the actual time (Time)that the Statement is runing.

SOME HELP WOULD BE NICE THANKS PEOPLE!

 

[stoolpigeon]

put single quotes around the time

where horav >= '12:25:15 p.m.'

and see if that helps.

 

[johnwm]

If you are using MSSQL or MSDE you're probably looking for the SQL Between operator. Your standard reference for SQL info for these platforms should be Books on Line, a free download from MS. Get it here:

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Essex Steam UK for steam enthusiasts

 

[wakkara]

Thanks man it really work that way, but i need to make that qerry dependind on the time on the record. cause its always diferent

The RS1.Fields("horaL") is a field that contains the time of a user login , so i pretty important to have a dynamic time =) thanks.

People any sugestions?

 

[LostInCode]

Is your time field a text field holding time, or date field?

Store the time as date-time in date field!
Or at least, store the time in 24 hour!! format in text field.
Date field is maybe better.

Use dao? Surround literal time with ##.

Text field:
If use dao then TimeValue(horav ) and TimeValue('12:48:25') or #12:48:25#
I think it will not work using "P.M.", I think you need "PM".

Date field:
If Date field with date-time, you have most chance of success, and less confusion and then you store NOW() as start and compare to NOW().

" Where DateTimeStart Between #" & Format$(Date & " 04:12:10","yyyy-mm-dd hh:nn:ss") & "# And #" & Format(Now(),"yyyy-mm-dd hh:nn:ss") & "#"

 

[wakkara]

thanks LostInCode

the issue here is that im making a direct comparison from access data base, wich holds date fields, with time format. so i think access saves the time fileds on a doferetn way than visual basic or something.

in fact i will like to compare time fotmat this 07/07/2005 05:37:59 p.m. ..is that possible, well possible using fileds direct from a database on a SQl statment??

like this

Set RS = DB.OpenRecordset("select * from venta where date1 >= "# date2 #";")

please some help here

 

[vyper78]

if date2 holds 07/07/2005 05:37:59 p.m.

Set RS = DB.OpenRecordset("Select * From venta Where date1 >= '"& date2 &"' ")

??? Worth a try I guess.

V.