Date/Time

[Geetika Malik]

Dim dteValue as date

dteValue=date

"SELECT CODE FROM PLAN WHERE
(DATEVALUE(""" dteValue """) BETWEEN
[StartDate] AND [EndDate])"





This query gives me all the codes in which Today's Date falls between their StartDate and EndDate.

Now, i have two more fields [StartTime] and [EndTime]. So, I want to write a query that gives me all the codes in which Today's Date including Time falls between their StartDate + StartTime and EndDate + EndTime.

I have to write this query for MS Access, in Visual Basic.

The query I have written is as follows:


Dim sDate as string

sDate=dteToday & FORMAT(time,hh:mm)

"SELECT CODE FROM PLAN WHERE
(DateValue(""" & sDate & """)] BETWEEN
[StartDate] & [StartTime] AND
[EndDate] & [EndTime])"





The above query works but does not give right value because DateValue function excludes the Time part from Date. So, Please suggest the function which solves
the purpose or
another way of writing this.

Thanks

[Iouri]

DOn't use function DateValue.
dim MyaData as Date
and use it instead of sDate.
Instead if quotes around date use #

"SELECT CODE FROM PLAN WHERE #" & MyDate & "# BETWEEN " & StartDate & StartTime & " AND " & EndDate & EndTime





Iouri Boutchkine
iouri@hotsheet.com

[Cakkie]

Make sure to give the statement proper formatting. In your example, if the date was 01/07/2001 and the time was 11:00, you statement would read 01/07/200111:00, which will fail. You must leave a space between date and time, and should always give the complete time (including seconds). You statement should look like 01/07/2001 11:00:00

Tom Cannaerts
slisse@planetinternet.be

Programming today is a race between software engineers striving to build bigger and better idot-proof programs, and the universe trying to produce bigger and better idiots. So far, the universe is winning -- Rich Cook