Access2000: Datetime value in query condition

[Arvi Laanemets]

Hi

I have a problem with function below. How to construct varQstr so, that
exact time is taken into account in WHERE clause, not only date. With
current syntax, I get same value for all routes on same day, but I need the
value from preivious route to be returned (unless it's the first route in
table for this car)


Thabnks in advance
Arvi Laanemets



****
Public Function PrevSpidVal(parDate As Date, parCar As String)
Dim dbs As Database
Dim rs As Recordset
Dim rs2 As Recordset

' parDate is a datetime value, like 03.01.2005 14:00:00
' The function looks for entries in table Soidud, where SoidukID=parCar
and Aeg0<parDate.
' When found, the value if SpidomX from latest found entry is returned
' (NB! there can be several entries for same car in day, so time MUST be
taken to account!).
' When not found, the latest value for SpidomNait is returned from table
Fix,
' where SoidukID=parCar and Kuupaev <= DateValue(parDate) (field Kuupaev
is a date, i.e. no hours there)

Set dbs = CurrentDb
On Error GoTo Err_PrevSpidVal
varQStr = "SELECT Aeg0, SpidomX FROM Soidud" & _
" WHERE Aeg0 < DateValue('" & parDate & _
"') AND SoidukID = " & parCar & _
" Order By Aeg0 DESC"
Set rs = dbs.OpenRecordset(varQStr)
If rs.EOF And rs.BOF Then
varQStr2 = "SELECT Kuupaev, SpidomNait FROM Fix" & _
" WHERE Kuupaev <= DateValue('" & parDate & _
"') AND SoidukID = " & parCar & _
" Order By Kuupaev DESC"
Set rs2 = dbs.OpenRecordset(varQStr2)
rs2.AbsolutePosition = 0
PrevSpidVal = rs2.Fields(1).Value
rs2.Close
Else
rs.AbsolutePosition = 0
PrevSpidVal = rs.Fields(1).Value
End If


Err_PrevSpidVal:
rs.Close
dbs.Close
Set rs = Nothing
Set rs2 = Nothing
Set dbs = Nothing

End Function

 

[Allen Browne]

This string formats the literal string with the formatting that Access
itself uses when you type a literal date/time value into the Criteria row of
a query:

Const strcJetDateTime = "\#mm\/dd\/yyyy hh\:nn\:ss\#"

varQStr = "SELECT Aeg0, SpidomX FROM Soidud" & _
" WHERE (Aeg0 < " & Format(parDate, strcJetDateTime) & _
") AND SoidukID = " & parCar & _
" Order By Aeg0 DESC"

 

[Arvi Laanemets]

Hi

Thanks! It looks like it's OK now! One never kows with those US date formats
)


Arvi Laanemets