Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

TIME field with VB6 on a SQL statement

Status
Not open for further replies.

wakkara

Programmer
Jun 15, 2005
31
MX
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!
 
put single quotes around the time

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

and see if that helps.
 
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:

________________________________________________________________
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
 
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?
 

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") & "#"
 
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
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top