Thank you for the reply,
I might need to clarify my desired output from this recordset.
This may be easiest for me to explain like this.... I hope it stays
like I typed it....
Query def produced this needs this criteria this
equation this output
Start Time End Time Start time end
Time
10:45:00 AM 4:00:00 PM <#12:00:00 PM# >#12:30:00 PM#
Datediff("n", [Start Time],[End Time])/60-.5 4.75
7:30:00 AM 4:00:00 PM <#12:00:00 PM# >#12:30:00 PM#
Datediff("n", [Start Time],[End Time])/60-.5 8
8:00:00 AM 9:45:00 AM <=#12:00:00 PM# <=#12:00:00 PM#
Datediff("n", [Start Time],[End Time])/60 1.75
7:30:00 AM 4:00:00 PM <#12:00:00 PM# >#12:30:00 PM#
Datediff("n", [Start Time],[End Time])/60-.5 8
7:30:00 AM 11:30:00 AM <=#12:00:00 PM# <=#12:00:00 PM#
Datediff("n", [Start Time],[End Time])/60 4
1:00:00 PM 4:00:00 PM >=#12:30:00 PM# <=#4:00:00PM#
Datediff("n", [Start Time],[End Time])/60 3
The best I could do with Queries is: a morning Query, minus lunch Query,
Afternoon Query and hour sum query for 4 queries total.
I thought something like looping through the recordset, passing the
criteria and applying
the equation through "IF THEN" and storing the output in a
string/integer(?) and then using the final total in a text box.
I have tried several different ways to get this to work but I cannot get
the recordset to see the [end time]
produced by the querydef .
Is this something that can't be done within a recordset?
Douglas J. Steele said:
I think Piet's point was that you don't need to loop through a recordset
to get what you want: you should be able to create a simple query that
returns a single row containing the answer. Unless we've both
misinterpretted what you're trying to do: after all, you're only going to
have a single value for Hours representing the difference for the last
entry in the recordset since you're constantly resetting Hours in your
code.
Note that there are some errors in your code. If you're trying to use
date/time constants in VBA, you must delimit them with #:
If [Start Time] < #12:00:00pm# and [End Time] > #12:30:00PM# Then
The DateDiff function returns a number, so it really doesn't make sense
for Hour to be declared as a String variable.
Also, you should never name a field Date. It's a reserved word, and you
should avoid using reserved words for your own purposes. For a
comprehensive list of names to avoid (as well as a link to a free utility
to check your application for compliance), see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
I really don't have a good reason why, but this going to be used over a
network with one or two users...I guess to see if I can get it to work
without multiple querys.
I got this to work:
strsql = "SELECT[Start Time] , [End Time] FROM [Work Time Log] WHERE
[Date] Between #" & dt1 & "# And #" & dt2 & "# ;"
Set qdf1 = dbs.CreateQueryDef("test", strsql)
But I don't know how to pull the information from the recordset...but
since I added "test" in the createQuerydef I can see the information is
there.
Paul
Hello There,
My form has [Date], [Start Time], [End Time] from this
table -[Work
Time Log].
I would like to get the total hours from a user specified date range.
I am having trouble getting the correct format using date and time
with the
Select string.
Dim dbs As DAO. Database
Dim qdf1 As DAO.QueryDef
Dim rs1 As Recordset
Dim strsql As String
Dim dt1 As Date ' value from a text box
Dim dt2 As Date ' value from a text box
Dim Hours As String
dt1 = me.text6.value
dt2 = me.text8.value
Set dbs = CurrentDb
strsql = "SELECT [Start Time] , [End Time] FROM [Work Time Log] WHERE
[Date]
Between #" & dt1 & "# And #" & dt2 & "# ;" 'will not work
Set qdf1 = dbs.CreateQueryDef("", strsql)
Set rs1 = qdf1.OpenRecordset
If [Start Time] < 12:00:00pm and [End Time] > 12:30:00PM
then
Hours = Datediff("n",[Start Time],[End
Time])/60 - 0.5 ' Would like to use something like
this in a
loop to get a total of hours worked.
else
' but not sure how to code this within a recordset
Hours = Datediff("n",[Start Time],[End
Time])/60
end if
Thank you,
Paul
Is there a reason you can't just use a parameteri query? Just point
the criteria to controls on your form... then run the query. Why are
you looping through a recordset when SQL is a set-oriented language?
If you need to, create a query that gets the number of minutes worked
and then in your summary query that would be based on the first query,
do the totals and maybe divide by 60...
