This is really sloppy -- help please

[Pwyd]

I have a form named average form. On current, it puts two short dates into
two textboxes. The form has 3 hidden list boxes. Two have queries attached
to them, and the third calculutes the number of work days between the two
dates in the text boxes previously mentioned. I'd like to do all of this
programatically without using the invisible listboxes, it seems really
sloppy, i just dont have the experience in VB and SQL to do it properly. I
also don't know how to properly extract a single value from a query, or run a
query programatically. When i try do.cmd and run it that way, it won't
allow it. If any other information or a copy of the form or queries in
question is required, please, ask, and i'll be happy to provide. Thank you
in advance.

 

[roger]

1st I think you're in over your head.

I have NO idea what you're doing with THREE hidden listboxes.

Look these things up in Help: (or go get a good book)

to calc days:
=DateDiff("d", date1, date2)
although if its time worked should you be counting hours?

To RUN a Query
Docmd.runquery (qryname)

To get one value from a qry (or table) (that has many rows and cols)
=Dlookup(fieldname, datasource, where-condition)

hth

 

[Pwyd]

the dlookup is useful. But the docmd.openquery doesn't function properly
when you're using an if/then statement. I'll state the error if you wish.
as for your date diff, that won't work for workdays. i already have a
function for htat. it looks like this
Function smsQtyOfWorkingDays(pvarStartDate As Variant, pvarEndDate As
Variant) As Integer

End Function
On Error GoTo smsQtyOfWorkingDays_Err

Dim lngStartDate As Long, lngEndDate As Long

lngStartDate = CLng(CVDate(pvarStartDate))
lngEndDate = CLng(CVDate(pvarEndDate))
If lngStartDate <= lngEndDate Then
smsQtyOfWorkingDays = DateDiff("w", lngStartDate, lngEndDate) * 5 +
smsQtyOfWorkingDaysBetween2WeekDays(Weekday(lngStartDate),
Weekday(lngEndDate))
Else
lngStartDate = CLng(CVDate(pvarEndDate))
lngEndDate = CLng(CVDate(pvarStartDate))
smsQtyOfWorkingDays = -(DateDiff("w", lngStartDate, lngEndDate) * 5 +
smsQtyOfWorkingDaysBetween2WeekDays(Weekday(lngStartDate),
Weekday(lngEndDate)))
End If

smsQtyOfWorkingDays_Done:
Exit Function
smsQtyOfWorkingDays_Err:
Resume smsQtyOfWorkingDays_Done
End Function

Function smsQtyOfWorkingDaysBetween2WeekDays(intFirstWeekDay As Integer,
intSecondWeekDay As Integer)
On Error GoTo smsQtyOfWorkingDaysBetween2WeekDays_Err

smsQtyOfWorkingDaysBetween2WeekDays = 0
Dim intForIdx As Integer, intCycle2 As Integer, intCnt As Integer

intCnt = 0
If intFirstWeekDay <> intSecondWeekDay Then
If intFirstWeekDay < intSecondWeekDay Then
intCycle2 = intSecondWeekDay
Else
intCycle2 = intSecondWeekDay + 7
End If

For intForIdx = intFirstWeekDay To intCycle2 - 1
Select Case intForIdx Mod 7
Case 1, 7:
Case 2, 3, 4, 5, 6: intCnt = intCnt + 1
Case Else
End Select
Next intForIdx
End If
smsQtyOfWorkingDaysBetween2WeekDays = intCnt

smsQtyOfWorkingDaysBetween2WeekDays_Done:
Exit Function
smsQtyOfWorkingDaysBetween2WeekDays_Err:
Resume smsQtyOfWorkingDaysBetween2WeekDays_Done
End Function

Function WorkDayDiff( _
DateFrom As Date, _
DateTo As Date _
) As Long
WorkDayDiff = DateDiff("d", DateFrom, DateTo) - _
DateDiff("ww", DateFrom, DateTo, 1) * 2 - _
IIf(Weekday(DateTo, 1) = 7, _
IIf(Weekday(DateFrom, 1) = 7, 0, 1), _
IIf(Weekday(DateFrom, 1) = 7, -1, 0))
End Function

End Function

and works just dandy.
However. I need to run a query on a button click that extracts data from
two text boxes and then takes the queries numerical result and divides it by
the number of working days, to get an average over time.
So the button click essentially does this, right now:
clears the listboxes, runs a query through a macro, puts the valu einto a
listbox, takes listbox.listindex(1) and divides that by the result of another
query, in a different listbox. As i said. sloppy. I want to do all of this
programmatically and can't get it working. Here's what it looks like:


Private Sub Command49_Click()
Me.RunMacro (QtyWorkingdaysMacro)
Me.Requery
If Check51.Value = 0 Then Text55 = List69 / List63 Else Text55 = List71 /
List63
End Sub

Private Sub Command50_Click()
[Forms]![AverageForm].Requery
[Forms]![AverageForm].Refresh
If Check51.Value = 0 Then Text55 = List69.ListIndex(1) Else Text55 =
List71.ListIndex(1)
End Sub

here are the queries in question:
SELECT
Sum(MailLog.Itemsfororg100+[Itemsfororg107]+[Itemsfororg109]+[Itemsfororg113]+[Itemsfororg114]+[Itemsfororg119]+[Itemsfororg144]+[Itemsfororg185]+[Itemsfororg195]+[Itemsfororg528]+[CSCExpensereport]+[ATDCheckreq]+[ODCCorrections]+[Diner]+[Investigators]+[Credits]+[Freight]+[Tax]+[Fedex]+[Reversals])
AS [Total Processing Batch]
FROM MailLog
WHERE (((MailLog.ProcessingAssignedto) Like [Forms]![AverageForm]![Combo14])
AND ((MailLog.ProcessingAssignedDate)>=[Forms]![AverageForm]![Text7] And
(MailLog.ProcessingAssignedDate)<=[Forms]![AverageForm]![Text2]) AND
((MailLog.ProcessingCompleteDate)>=[Forms]![AverageForm]![Text7] And
(MailLog.ProcessingCompleteDate)<=[Forms]![AverageForm]![Text2]));


and the second query looks like this:
SELECT MailLog.ProcessingAssignedto,
(Sum(MailLog.Itemsfororg100+[Itemsfororg107]+[Itemsfororg109]+[Itemsfororg113]+[Itemsfororg114]+[Itemsfororg119]+[Itemsfororg144]+[Itemsfororg185]+[Itemsfororg195]+[Itemsfororg528]+[CSCExpensereport]+[ATDCheckreq]+[ODCCorrections]+[Diner]+[Investigators]+[Credits]+[Freight]+[Tax]+[Fedex]+[Reversals]))
AS [Total Processed Batches]
FROM MailLog
WHERE (((MailLog.ProcessingAssignedDate)>=[Forms]![AverageForm]![Text7] And
(MailLog.ProcessingAssignedDate)<=[Forms]![AverageForm]![Text2]) AND
((MailLog.ProcessingCompleteDate)>=[Forms]![AverageForm]![Text7] And
(MailLog.ProcessingCompleteDate)<=[Forms]![AverageForm]![Text2]))
GROUP BY MailLog.ProcessingAssignedto
HAVING (((MailLog.ProcessingAssignedto)=[Forms]![AverageForm]![Combo14]));

 

[Pwyd]

What i'd really like to do is run the whole thing programmatically. Have the
button click take the two dates, find the workdays between the two, and run a
query with the criteria shown in those two queries. But when i change the
queries to sql view and paste teh "where" contents into a query to run, it
just doesn't work, and i simply do not have the knowledge to make it work

the dlookup is useful. But the docmd.openquery doesn't function properly
when you're using an if/then statement. I'll state the error if you wish.
as for your date diff, that won't work for workdays. i already have a
function for htat. it looks like this
Function smsQtyOfWorkingDays(pvarStartDate As Variant, pvarEndDate As
Variant) As Integer

End Function
On Error GoTo smsQtyOfWorkingDays_Err

Dim lngStartDate As Long, lngEndDate As Long

lngStartDate = CLng(CVDate(pvarStartDate))
lngEndDate = CLng(CVDate(pvarEndDate))
If lngStartDate <= lngEndDate Then
smsQtyOfWorkingDays = DateDiff("w", lngStartDate, lngEndDate) * 5 +
smsQtyOfWorkingDaysBetween2WeekDays(Weekday(lngStartDate),
Weekday(lngEndDate))
Else
lngStartDate = CLng(CVDate(pvarEndDate))
lngEndDate = CLng(CVDate(pvarStartDate))
smsQtyOfWorkingDays = -(DateDiff("w", lngStartDate, lngEndDate) * 5 +
smsQtyOfWorkingDaysBetween2WeekDays(Weekday(lngStartDate),
Weekday(lngEndDate)))
End If

smsQtyOfWorkingDays_Done:
Exit Function
smsQtyOfWorkingDays_Err:
Resume smsQtyOfWorkingDays_Done
End Function

Function smsQtyOfWorkingDaysBetween2WeekDays(intFirstWeekDay As Integer,
intSecondWeekDay As Integer)
On Error GoTo smsQtyOfWorkingDaysBetween2WeekDays_Err

smsQtyOfWorkingDaysBetween2WeekDays = 0
Dim intForIdx As Integer, intCycle2 As Integer, intCnt As Integer

intCnt = 0
If intFirstWeekDay <> intSecondWeekDay Then
If intFirstWeekDay < intSecondWeekDay Then
intCycle2 = intSecondWeekDay
Else
intCycle2 = intSecondWeekDay + 7
End If

For intForIdx = intFirstWeekDay To intCycle2 - 1
Select Case intForIdx Mod 7
Case 1, 7:
Case 2, 3, 4, 5, 6: intCnt = intCnt + 1
Case Else
End Select
Next intForIdx
End If
smsQtyOfWorkingDaysBetween2WeekDays = intCnt

smsQtyOfWorkingDaysBetween2WeekDays_Done:
Exit Function
smsQtyOfWorkingDaysBetween2WeekDays_Err:
Resume smsQtyOfWorkingDaysBetween2WeekDays_Done
End Function

Function WorkDayDiff( _
DateFrom As Date, _
DateTo As Date _
) As Long
WorkDayDiff = DateDiff("d", DateFrom, DateTo) - _
DateDiff("ww", DateFrom, DateTo, 1) * 2 - _
IIf(Weekday(DateTo, 1) = 7, _
IIf(Weekday(DateFrom, 1) = 7, 0, 1), _
IIf(Weekday(DateFrom, 1) = 7, -1, 0))
End Function

End Function

and works just dandy.
However. I need to run a query on a button click that extracts data from
two text boxes and then takes the queries numerical result and divides it by
the number of working days, to get an average over time.
So the button click essentially does this, right now:
clears the listboxes, runs a query through a macro, puts the valu einto a
listbox, takes listbox.listindex(1) and divides that by the result of another
query, in a different listbox. As i said. sloppy. I want to do all of this
programmatically and can't get it working. Here's what it looks like:


Private Sub Command49_Click()
Me.RunMacro (QtyWorkingdaysMacro)
Me.Requery
If Check51.Value = 0 Then Text55 = List69 / List63 Else Text55 = List71 /
List63
End Sub

Private Sub Command50_Click()
[Forms]![AverageForm].Requery
[Forms]![AverageForm].Refresh
If Check51.Value = 0 Then Text55 = List69.ListIndex(1) Else Text55 =
List71.ListIndex(1)
End Sub

here are the queries in question:
SELECT
Sum(MailLog.Itemsfororg100+[Itemsfororg107]+[Itemsfororg109]+[Itemsfororg113]+[Itemsfororg114]+[Itemsfororg119]+[Itemsfororg144]+[Itemsfororg185]+[Itemsfororg195]+[Itemsfororg528]+[CSCExpensereport]+[ATDCheckreq]+[ODCCorrections]+[Diner]+[Investigators]+[Credits]+[Freight]+[Tax]+[Fedex]+[Reversals])
AS [Total Processing Batch]
FROM MailLog
WHERE (((MailLog.ProcessingAssignedto) Like [Forms]![AverageForm]![Combo14])
AND ((MailLog.ProcessingAssignedDate)>=[Forms]![AverageForm]![Text7] And
(MailLog.ProcessingAssignedDate)<=[Forms]![AverageForm]![Text2]) AND
((MailLog.ProcessingCompleteDate)>=[Forms]![AverageForm]![Text7] And
(MailLog.ProcessingCompleteDate)<=[Forms]![AverageForm]![Text2]));


and the second query looks like this:
SELECT MailLog.ProcessingAssignedto,
(Sum(MailLog.Itemsfororg100+[Itemsfororg107]+[Itemsfororg109]+[Itemsfororg113]+[Itemsfororg114]+[Itemsfororg119]+[Itemsfororg144]+[Itemsfororg185]+[Itemsfororg195]+[Itemsfororg528]+[CSCExpensereport]+[ATDCheckreq]+[ODCCorrections]+[Diner]+[Investigators]+[Credits]+[Freight]+[Tax]+[Fedex]+[Reversals]))
AS [Total Processed Batches]
FROM MailLog
WHERE (((MailLog.ProcessingAssignedDate)>=[Forms]![AverageForm]![Text7] And
(MailLog.ProcessingAssignedDate)<=[Forms]![AverageForm]![Text2]) AND
((MailLog.ProcessingCompleteDate)>=[Forms]![AverageForm]![Text7] And
(MailLog.ProcessingCompleteDate)<=[Forms]![AverageForm]![Text2]))
GROUP BY MailLog.ProcessingAssignedto
HAVING (((MailLog.ProcessingAssignedto)=[Forms]![AverageForm]![Combo14]));

1st I think you're in over your head.

I have NO idea what you're doing with THREE hidden listboxes.

Look these things up in Help: (or go get a good book)

to calc days:
=DateDiff("d", date1, date2)
although if its time worked should you be counting hours?

To RUN a Query
Docmd.runquery (qryname)

To get one value from a qry (or table) (that has many rows and cols)
=Dlookup(fieldname, datasource, where-condition)

hth

 

[roger]

Sorry dude, I can't wade through all that code.

I do that openquery doesn't function works just fine in or out of an if/then
statement
although maybe your query doesn't.

and its not "my" datediff function.

and if you think ANYTHING works in a macro but not in VB, just save the
macro AS a VB module, and got read it. you probebly just have a syntax error.

the dlookup is useful. But the docmd.openquery doesn't function properly
when you're using an if/then statement. I'll state the error if you wish.
as for your date diff, that won't work for workdays. i already have a
function for htat. it looks like this
Function smsQtyOfWorkingDays(pvarStartDate As Variant, pvarEndDate As
Variant) As Integer

End Function
On Error GoTo smsQtyOfWorkingDays_Err

Dim lngStartDate As Long, lngEndDate As Long

lngStartDate = CLng(CVDate(pvarStartDate))
lngEndDate = CLng(CVDate(pvarEndDate))
If lngStartDate <= lngEndDate Then
smsQtyOfWorkingDays = DateDiff("w", lngStartDate, lngEndDate) * 5 +
smsQtyOfWorkingDaysBetween2WeekDays(Weekday(lngStartDate),
Weekday(lngEndDate))
Else
lngStartDate = CLng(CVDate(pvarEndDate))
lngEndDate = CLng(CVDate(pvarStartDate))
smsQtyOfWorkingDays = -(DateDiff("w", lngStartDate, lngEndDate) * 5 +
smsQtyOfWorkingDaysBetween2WeekDays(Weekday(lngStartDate),
Weekday(lngEndDate)))
End If

smsQtyOfWorkingDays_Done:
Exit Function
smsQtyOfWorkingDays_Err:
Resume smsQtyOfWorkingDays_Done
End Function

Function smsQtyOfWorkingDaysBetween2WeekDays(intFirstWeekDay As Integer,
intSecondWeekDay As Integer)
On Error GoTo smsQtyOfWorkingDaysBetween2WeekDays_Err

smsQtyOfWorkingDaysBetween2WeekDays = 0
Dim intForIdx As Integer, intCycle2 As Integer, intCnt As Integer

intCnt = 0
If intFirstWeekDay <> intSecondWeekDay Then
If intFirstWeekDay < intSecondWeekDay Then
intCycle2 = intSecondWeekDay
Else
intCycle2 = intSecondWeekDay + 7
End If

For intForIdx = intFirstWeekDay To intCycle2 - 1
Select Case intForIdx Mod 7
Case 1, 7:
Case 2, 3, 4, 5, 6: intCnt = intCnt + 1
Case Else
End Select
Next intForIdx
End If
smsQtyOfWorkingDaysBetween2WeekDays = intCnt

smsQtyOfWorkingDaysBetween2WeekDays_Done:
Exit Function
smsQtyOfWorkingDaysBetween2WeekDays_Err:
Resume smsQtyOfWorkingDaysBetween2WeekDays_Done
End Function

Function WorkDayDiff( _
DateFrom As Date, _
DateTo As Date _
) As Long
WorkDayDiff = DateDiff("d", DateFrom, DateTo) - _
DateDiff("ww", DateFrom, DateTo, 1) * 2 - _
IIf(Weekday(DateTo, 1) = 7, _
IIf(Weekday(DateFrom, 1) = 7, 0, 1), _
IIf(Weekday(DateFrom, 1) = 7, -1, 0))
End Function

End Function

and works just dandy.
However. I need to run a query on a button click that extracts data from
two text boxes and then takes the queries numerical result and divides it by
the number of working days, to get an average over time.
So the button click essentially does this, right now:
clears the listboxes, runs a query through a macro, puts the valu einto a
listbox, takes listbox.listindex(1) and divides that by the result of another
query, in a different listbox. As i said. sloppy. I want to do all of this
programmatically and can't get it working. Here's what it looks like:


Private Sub Command49_Click()
Me.RunMacro (QtyWorkingdaysMacro)
Me.Requery
If Check51.Value = 0 Then Text55 = List69 / List63 Else Text55 = List71 /
List63
End Sub

Private Sub Command50_Click()
[Forms]![AverageForm].Requery
[Forms]![AverageForm].Refresh
If Check51.Value = 0 Then Text55 = List69.ListIndex(1) Else Text55 =
List71.ListIndex(1)
End Sub

here are the queries in question:
SELECT
Sum(MailLog.Itemsfororg100+[Itemsfororg107]+[Itemsfororg109]+[Itemsfororg113]+[Itemsfororg114]+[Itemsfororg119]+[Itemsfororg144]+[Itemsfororg185]+[Itemsfororg195]+[Itemsfororg528]+[CSCExpensereport]+[ATDCheckreq]+[ODCCorrections]+[Diner]+[Investigators]+[Credits]+[Freight]+[Tax]+[Fedex]+[Reversals])
AS [Total Processing Batch]
FROM MailLog
WHERE (((MailLog.ProcessingAssignedto) Like [Forms]![AverageForm]![Combo14])
AND ((MailLog.ProcessingAssignedDate)>=[Forms]![AverageForm]![Text7] And
(MailLog.ProcessingAssignedDate)<=[Forms]![AverageForm]![Text2]) AND
((MailLog.ProcessingCompleteDate)>=[Forms]![AverageForm]![Text7] And
(MailLog.ProcessingCompleteDate)<=[Forms]![AverageForm]![Text2]));


and the second query looks like this:
SELECT MailLog.ProcessingAssignedto,
(Sum(MailLog.Itemsfororg100+[Itemsfororg107]+[Itemsfororg109]+[Itemsfororg113]+[Itemsfororg114]+[Itemsfororg119]+[Itemsfororg144]+[Itemsfororg185]+[Itemsfororg195]+[Itemsfororg528]+[CSCExpensereport]+[ATDCheckreq]+[ODCCorrections]+[Diner]+[Investigators]+[Credits]+[Freight]+[Tax]+[Fedex]+[Reversals]))
AS [Total Processed Batches]
FROM MailLog
WHERE (((MailLog.ProcessingAssignedDate)>=[Forms]![AverageForm]![Text7] And
(MailLog.ProcessingAssignedDate)<=[Forms]![AverageForm]![Text2]) AND
((MailLog.ProcessingCompleteDate)>=[Forms]![AverageForm]![Text7] And
(MailLog.ProcessingCompleteDate)<=[Forms]![AverageForm]![Text2]))
GROUP BY MailLog.ProcessingAssignedto
HAVING (((MailLog.ProcessingAssignedto)=[Forms]![AverageForm]![Combo14]));

1st I think you're in over your head.

I have NO idea what you're doing with THREE hidden listboxes.

Look these things up in Help: (or go get a good book)

to calc days:
=DateDiff("d", date1, date2)
although if its time worked should you be counting hours?

To RUN a Query
Docmd.runquery (qryname)

To get one value from a qry (or table) (that has many rows and cols)
=Dlookup(fieldname, datasource, where-condition)

hth

 

[Pwyd]

Whats kind of value does a query need to be assigned to. a Recordset? then
dlookup the recordset for some value? or does the dlookup perform the query
based on the criteria.

Sorry dude, I can't wade through all that code.

I do that openquery doesn't function works just fine in or out of an if/then
statement
although maybe your query doesn't.

and its not "my" datediff function.

and if you think ANYTHING works in a macro but not in VB, just save the
macro AS a VB module, and got read it. you probebly just have a syntax error.

the dlookup is useful. But the docmd.openquery doesn't function properly
when you're using an if/then statement. I'll state the error if you wish.
as for your date diff, that won't work for workdays. i already have a
function for htat. it looks like this
Function smsQtyOfWorkingDays(pvarStartDate As Variant, pvarEndDate As
Variant) As Integer

End Function
On Error GoTo smsQtyOfWorkingDays_Err

Dim lngStartDate As Long, lngEndDate As Long

lngStartDate = CLng(CVDate(pvarStartDate))
lngEndDate = CLng(CVDate(pvarEndDate))
If lngStartDate <= lngEndDate Then
smsQtyOfWorkingDays = DateDiff("w", lngStartDate, lngEndDate) * 5 +
smsQtyOfWorkingDaysBetween2WeekDays(Weekday(lngStartDate),
Weekday(lngEndDate))
Else
lngStartDate = CLng(CVDate(pvarEndDate))
lngEndDate = CLng(CVDate(pvarStartDate))
smsQtyOfWorkingDays = -(DateDiff("w", lngStartDate, lngEndDate) * 5 +
smsQtyOfWorkingDaysBetween2WeekDays(Weekday(lngStartDate),
Weekday(lngEndDate)))
End If

smsQtyOfWorkingDays_Done:
Exit Function
smsQtyOfWorkingDays_Err:
Resume smsQtyOfWorkingDays_Done
End Function

Function smsQtyOfWorkingDaysBetween2WeekDays(intFirstWeekDay As Integer,
intSecondWeekDay As Integer)
On Error GoTo smsQtyOfWorkingDaysBetween2WeekDays_Err

smsQtyOfWorkingDaysBetween2WeekDays = 0
Dim intForIdx As Integer, intCycle2 As Integer, intCnt As Integer

intCnt = 0
If intFirstWeekDay <> intSecondWeekDay Then
If intFirstWeekDay < intSecondWeekDay Then
intCycle2 = intSecondWeekDay
Else
intCycle2 = intSecondWeekDay + 7
End If

For intForIdx = intFirstWeekDay To intCycle2 - 1
Select Case intForIdx Mod 7
Case 1, 7:
Case 2, 3, 4, 5, 6: intCnt = intCnt + 1
Case Else
End Select
Next intForIdx
End If
smsQtyOfWorkingDaysBetween2WeekDays = intCnt

smsQtyOfWorkingDaysBetween2WeekDays_Done:
Exit Function
smsQtyOfWorkingDaysBetween2WeekDays_Err:
Resume smsQtyOfWorkingDaysBetween2WeekDays_Done
End Function

Function WorkDayDiff( _
DateFrom As Date, _
DateTo As Date _
) As Long
WorkDayDiff = DateDiff("d", DateFrom, DateTo) - _
DateDiff("ww", DateFrom, DateTo, 1) * 2 - _
IIf(Weekday(DateTo, 1) = 7, _
IIf(Weekday(DateFrom, 1) = 7, 0, 1), _
IIf(Weekday(DateFrom, 1) = 7, -1, 0))
End Function

End Function

and works just dandy.
However. I need to run a query on a button click that extracts data from
two text boxes and then takes the queries numerical result and divides it by
the number of working days, to get an average over time.
So the button click essentially does this, right now:
clears the listboxes, runs a query through a macro, puts the valu einto a
listbox, takes listbox.listindex(1) and divides that by the result of another
query, in a different listbox. As i said. sloppy. I want to do all of this
programmatically and can't get it working. Here's what it looks like:


Private Sub Command49_Click()
Me.RunMacro (QtyWorkingdaysMacro)
Me.Requery
If Check51.Value = 0 Then Text55 = List69 / List63 Else Text55 = List71 /
List63
End Sub

Private Sub Command50_Click()
[Forms]![AverageForm].Requery
[Forms]![AverageForm].Refresh
If Check51.Value = 0 Then Text55 = List69.ListIndex(1) Else Text55 =
List71.ListIndex(1)
End Sub

here are the queries in question:
SELECT
Sum(MailLog.Itemsfororg100+[Itemsfororg107]+[Itemsfororg109]+[Itemsfororg113]+[Itemsfororg114]+[Itemsfororg119]+[Itemsfororg144]+[Itemsfororg185]+[Itemsfororg195]+[Itemsfororg528]+[CSCExpensereport]+[ATDCheckreq]+[ODCCorrections]+[Diner]+[Investigators]+[Credits]+[Freight]+[Tax]+[Fedex]+[Reversals])
AS [Total Processing Batch]
FROM MailLog
WHERE (((MailLog.ProcessingAssignedto) Like [Forms]![AverageForm]![Combo14])
AND ((MailLog.ProcessingAssignedDate)>=[Forms]![AverageForm]![Text7] And
(MailLog.ProcessingAssignedDate)<=[Forms]![AverageForm]![Text2]) AND
((MailLog.ProcessingCompleteDate)>=[Forms]![AverageForm]![Text7] And
(MailLog.ProcessingCompleteDate)<=[Forms]![AverageForm]![Text2]));


and the second query looks like this:
SELECT MailLog.ProcessingAssignedto,
(Sum(MailLog.Itemsfororg100+[Itemsfororg107]+[Itemsfororg109]+[Itemsfororg113]+[Itemsfororg114]+[Itemsfororg119]+[Itemsfororg144]+[Itemsfororg185]+[Itemsfororg195]+[Itemsfororg528]+[CSCExpensereport]+[ATDCheckreq]+[ODCCorrections]+[Diner]+[Investigators]+[Credits]+[Freight]+[Tax]+[Fedex]+[Reversals]))
AS [Total Processed Batches]
FROM MailLog
WHERE (((MailLog.ProcessingAssignedDate)>=[Forms]![AverageForm]![Text7] And
(MailLog.ProcessingAssignedDate)<=[Forms]![AverageForm]![Text2]) AND
((MailLog.ProcessingCompleteDate)>=[Forms]![AverageForm]![Text7] And
(MailLog.ProcessingCompleteDate)<=[Forms]![AverageForm]![Text2]))
GROUP BY MailLog.ProcessingAssignedto
HAVING (((MailLog.ProcessingAssignedto)=[Forms]![AverageForm]![Combo14]));

1st I think you're in over your head.

I have NO idea what you're doing with THREE hidden listboxes.

Look these things up in Help: (or go get a good book)

to calc days:
=DateDiff("d", date1, date2)
although if its time worked should you be counting hours?

To RUN a Query
Docmd.runquery (qryname)

To get one value from a qry (or table) (that has many rows and cols)
=Dlookup(fieldname, datasource, where-condition)

hth




:

I have a form named average form. On current, it puts two short dates into
two textboxes. The form has 3 hidden list boxes. Two have queries attached
to them, and the third calculutes the number of work days between the two
dates in the text boxes previously mentioned. I'd like to do all of this
programatically without using the invisible listboxes, it seems really
sloppy, i just dont have the experience in VB and SQL to do it properly. I
also don't know how to properly extract a single value from a query, or run a
query programatically. When i try do.cmd and run it that way, it won't
allow it. If any other information or a copy of the form or queries in
question is required, please, ask, and i'll be happy to provide. Thank you
in advance.

 

[roger]

it IS a select query right?

dlookup executes the query (or opens the table)
and THEN selects a record based on the criteria,
and returns the contents of the named field,

look it up in help

Whats kind of value does a query need to be assigned to. a Recordset? then
dlookup the recordset for some value? or does the dlookup perform the query
based on the criteria.

Sorry dude, I can't wade through all that code.

I do that openquery doesn't function works just fine in or out of an if/then
statement
although maybe your query doesn't.

and its not "my" datediff function.

and if you think ANYTHING works in a macro but not in VB, just save the
macro AS a VB module, and got read it. you probebly just have a syntax error.

the dlookup is useful. But the docmd.openquery doesn't function properly
when you're using an if/then statement. I'll state the error if you wish.
as for your date diff, that won't work for workdays. i already have a
function for htat. it looks like this
Function smsQtyOfWorkingDays(pvarStartDate As Variant, pvarEndDate As
Variant) As Integer

End Function
On Error GoTo smsQtyOfWorkingDays_Err

Dim lngStartDate As Long, lngEndDate As Long

lngStartDate = CLng(CVDate(pvarStartDate))
lngEndDate = CLng(CVDate(pvarEndDate))
If lngStartDate <= lngEndDate Then
smsQtyOfWorkingDays = DateDiff("w", lngStartDate, lngEndDate) * 5 +
smsQtyOfWorkingDaysBetween2WeekDays(Weekday(lngStartDate),
Weekday(lngEndDate))
Else
lngStartDate = CLng(CVDate(pvarEndDate))
lngEndDate = CLng(CVDate(pvarStartDate))
smsQtyOfWorkingDays = -(DateDiff("w", lngStartDate, lngEndDate) * 5 +
smsQtyOfWorkingDaysBetween2WeekDays(Weekday(lngStartDate),
Weekday(lngEndDate)))
End If

smsQtyOfWorkingDays_Done:
Exit Function
smsQtyOfWorkingDays_Err:
Resume smsQtyOfWorkingDays_Done
End Function

Function smsQtyOfWorkingDaysBetween2WeekDays(intFirstWeekDay As Integer,
intSecondWeekDay As Integer)
On Error GoTo smsQtyOfWorkingDaysBetween2WeekDays_Err

smsQtyOfWorkingDaysBetween2WeekDays = 0
Dim intForIdx As Integer, intCycle2 As Integer, intCnt As Integer

intCnt = 0
If intFirstWeekDay <> intSecondWeekDay Then
If intFirstWeekDay < intSecondWeekDay Then
intCycle2 = intSecondWeekDay
Else
intCycle2 = intSecondWeekDay + 7
End If

For intForIdx = intFirstWeekDay To intCycle2 - 1
Select Case intForIdx Mod 7
Case 1, 7:
Case 2, 3, 4, 5, 6: intCnt = intCnt + 1
Case Else
End Select
Next intForIdx
End If
smsQtyOfWorkingDaysBetween2WeekDays = intCnt

smsQtyOfWorkingDaysBetween2WeekDays_Done:
Exit Function
smsQtyOfWorkingDaysBetween2WeekDays_Err:
Resume smsQtyOfWorkingDaysBetween2WeekDays_Done
End Function

Function WorkDayDiff( _
DateFrom As Date, _
DateTo As Date _
) As Long
WorkDayDiff = DateDiff("d", DateFrom, DateTo) - _
DateDiff("ww", DateFrom, DateTo, 1) * 2 - _
IIf(Weekday(DateTo, 1) = 7, _
IIf(Weekday(DateFrom, 1) = 7, 0, 1), _
IIf(Weekday(DateFrom, 1) = 7, -1, 0))
End Function

End Function

and works just dandy.
However. I need to run a query on a button click that extracts data from
two text boxes and then takes the queries numerical result and divides it by
the number of working days, to get an average over time.
So the button click essentially does this, right now:
clears the listboxes, runs a query through a macro, puts the valu einto a
listbox, takes listbox.listindex(1) and divides that by the result of another
query, in a different listbox. As i said. sloppy. I want to do all of this
programmatically and can't get it working. Here's what it looks like:


Private Sub Command49_Click()
Me.RunMacro (QtyWorkingdaysMacro)
Me.Requery
If Check51.Value = 0 Then Text55 = List69 / List63 Else Text55 = List71 /
List63
End Sub

Private Sub Command50_Click()
[Forms]![AverageForm].Requery
[Forms]![AverageForm].Refresh
If Check51.Value = 0 Then Text55 = List69.ListIndex(1) Else Text55 =
List71.ListIndex(1)
End Sub

here are the queries in question:
SELECT
Sum(MailLog.Itemsfororg100+[Itemsfororg107]+[Itemsfororg109]+[Itemsfororg113]+[Itemsfororg114]+[Itemsfororg119]+[Itemsfororg144]+[Itemsfororg185]+[Itemsfororg195]+[Itemsfororg528]+[CSCExpensereport]+[ATDCheckreq]+[ODCCorrections]+[Diner]+[Investigators]+[Credits]+[Freight]+[Tax]+[Fedex]+[Reversals])
AS [Total Processing Batch]
FROM MailLog
WHERE (((MailLog.ProcessingAssignedto) Like [Forms]![AverageForm]![Combo14])
AND ((MailLog.ProcessingAssignedDate)>=[Forms]![AverageForm]![Text7] And
(MailLog.ProcessingAssignedDate)<=[Forms]![AverageForm]![Text2]) AND
((MailLog.ProcessingCompleteDate)>=[Forms]![AverageForm]![Text7] And
(MailLog.ProcessingCompleteDate)<=[Forms]![AverageForm]![Text2]));


and the second query looks like this:
SELECT MailLog.ProcessingAssignedto,
(Sum(MailLog.Itemsfororg100+[Itemsfororg107]+[Itemsfororg109]+[Itemsfororg113]+[Itemsfororg114]+[Itemsfororg119]+[Itemsfororg144]+[Itemsfororg185]+[Itemsfororg195]+[Itemsfororg528]+[CSCExpensereport]+[ATDCheckreq]+[ODCCorrections]+[Diner]+[Investigators]+[Credits]+[Freight]+[Tax]+[Fedex]+[Reversals]))
AS [Total Processed Batches]
FROM MailLog
WHERE (((MailLog.ProcessingAssignedDate)>=[Forms]![AverageForm]![Text7] And
(MailLog.ProcessingAssignedDate)<=[Forms]![AverageForm]![Text2]) AND
((MailLog.ProcessingCompleteDate)>=[Forms]![AverageForm]![Text7] And
(MailLog.ProcessingCompleteDate)<=[Forms]![AverageForm]![Text2]))
GROUP BY MailLog.ProcessingAssignedto
HAVING (((MailLog.ProcessingAssignedto)=[Forms]![AverageForm]![Combo14]));


:

1st I think you're in over your head.

I have NO idea what you're doing with THREE hidden listboxes.

Look these things up in Help: (or go get a good book)

to calc days:
=DateDiff("d", date1, date2)
although if its time worked should you be counting hours?

To RUN a Query
Docmd.runquery (qryname)

To get one value from a qry (or table) (that has many rows and cols)
=Dlookup(fieldname, datasource, where-condition)

hth




:

I have a form named average form. On current, it puts two short dates into
two textboxes. The form has 3 hidden list boxes. Two have queries attached
to them, and the third calculutes the number of work days between the two
dates in the text boxes previously mentioned. I'd like to do all of this
programatically without using the invisible listboxes, it seems really
sloppy, i just dont have the experience in VB and SQL to do it properly. I
also don't know how to properly extract a single value from a query, or run a
query programatically. When i try do.cmd and run it that way, it won't
allow it. If any other information or a copy of the form or queries in
question is required, please, ask, and i'll be happy to provide. Thank you
in advance.

 

[Steve Sanford]

PMFJI, it's not that hard to do the calculations you want in code. But you
have to be able to write down the steps necessary.

The first step is to write the pseudo code.
Then go back and add more detail.
Repeat until the code is written.

Example:

Sub DoCalc()

get two dates from MyForm
calc work days
get the sum of the $$ amount between two dates

divide amount by work days

store result on MyForm

End Sub


Before I go much further, I have to say (IMO) the structure for the table
"MailLog" should be changed. It looks like you have committed "Spreadsheet".
You have a field for each catagory. What happens if you have to add
"Itemsfororg607"? EVERY form, query and report will/might have to be changed.
If your structure for "MailLog" looked like:

ProcessingAssignedto - Text(?)
curAmount - Currency
lngCatagoryFK - Long
ProcessingAssignedDate - date
ProcessingCompleteDate - date

 

[Pwyd]

These are government contracts, there are no other orgs, or changes to them.
haven't been in many, many years. But should they come, i won't have to
update the old records. I remove them after some months and they're
archived, never to be looked at again. The form that they are viewed through
simply shows a null when they look at a form with the "new" field in it, and
an old record. Thank you very much for all your help. i'll give that code a
try. i know how to code in c++ and am familiar with pseudocode, thanks. I
haven't used VB in many, many years. I only remember bits and pieces of it,
and only enoguh to get me by. i'll fiddle with what you've given me here and
see if i can't get it working. Thanks again.

PMFJI, it's not that hard to do the calculations you want in code. But you
have to be able to write down the steps necessary.

The first step is to write the pseudo code.
Then go back and add more detail.
Repeat until the code is written.

Example:

Sub DoCalc()

get two dates from MyForm
calc work days
get the sum of the $$ amount between two dates

divide amount by work days

store result on MyForm

End Sub


Before I go much further, I have to say (IMO) the structure for the table
"MailLog" should be changed. It looks like you have committed "Spreadsheet".
You have a field for each catagory. What happens if you have to add
"Itemsfororg607"? EVERY form, query and report will/might have to be changed.
If your structure for "MailLog" looked like:

ProcessingAssignedto - Text(?)
curAmount - Currency
lngCatagoryFK - Long
ProcessingAssignedDate - date
ProcessingCompleteDate - date
.
.
.


and a table tblCatagory:

lngCatagoryID - Auto (PK)
strCatagory - Text


as long as you were filtering (querying) by ProcessingAssignedto,
ProcessingAssignedDate & ProcessingCompleteDate, you wouldn't have to change
anything. You could add as many catagories as you wanted.

Without knowing a lot more about your mdb and your calculations, I can't
give you all of the code. Here is an example of (or at least a start) how to
convert your first (?)query to code:

(watch for line wrap)
'---------------------
Private Sub Command3_Click()
On Error GoTo Handle_Err

Dim db As DAO.Database
Dim rst As DAO.Recordset

Dim strSQL As String

Dim mvAssignedto As String
Dim mvAssignedDate As Date
Dim mvCompleteDate As Date

Dim msg As String

Set db = CurrentDb

mvAssignedto = [Forms]![AverageForm]![Combo14]
mvBeginDate = [Forms]![AverageForm]![Text7]
mvEndDate = [Forms]![AverageForm]![Text2]


strSQL = "SELECT Sum(MailLog.Itemsfororg100+[Itemsfororg107]+"
strSQL = strSQL & " [Itemsfororg109]+[Itemsfororg113]+[Itemsfororg114]+"
strSQL = strSQL & " [Itemsfororg119]+[Itemsfororg144]+[Itemsfororg185]+"
strSQL = strSQL & " [Itemsfororg195]+[Itemsfororg528]+[CSCExpensereport]+"
strSQL = strSQL & " [ATDCheckreq]+[ODCCorrections]+[Diner]+"
strSQL = strSQL & " [Investigators]+[Credits]+[Freight]+[Tax]+[Fedex]+"
strSQL = strSQL & " [Reversals]) AS [Total Processing Batch]"

strSQL = strSQL & " FROM MailLog"

strSQL = strSQL & " WHERE MailLog.ProcessingAssignedto Like '" &
mvAssignedto & "' AND"
strSQL = strSQL & " MailLog.ProcessingAssignedDate >= #" & mvBeginDate &
"# AND"
strSQL = strSQL & " MailLog.ProcessingAssignedDate <= #" & mvEndDate & "#
AND"
strSQL = strSQL & " ((MailLog.ProcessingCompleteDate)>= #" & mvBeginDate
& "# AND"
strSQL = strSQL & " MailLog.ProcessingCompleteDate<= #" & mvEndDate & "# ;"

Set rst = db.OpenRecordset(strSQL)

If Not rst.BOF And Not rst.EOF Then
rst.MoveLast
rst.MoveFirst
'for debugging
MsgBox "Records in query = " & rst.RecordCount
MsgBox "The first record sum is " & rst.Fields(0)


' do calculations here
' wkDays = smsQtyOfWorkingDays(mvBeginDate,mvEndDate)
'
'

Else
msg = "No records found for Assigned to Like " & mvAssignedto & vbCrLf
msg = msg & "Begin date >= " & mvBeginDate & vbCrLf
msg = msg & "End date >= " & mvEndDate & vbCrLf
MsgBox msg
End If

Handle_Err_Exit:
On Error Resume Next
'cleanup
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

Handle_Err:
MsgBox Err.Description
Resume Handle_Err_Exit

End Sub
'-------------------------


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

the dlookup is useful. But the docmd.openquery doesn't function properly
when you're using an if/then statement. I'll state the error if you wish.
as for your date diff, that won't work for workdays. i already have a
function for htat. it looks like this
Function smsQtyOfWorkingDays(pvarStartDate As Variant, pvarEndDate As
Variant) As Integer

End Function
On Error GoTo smsQtyOfWorkingDays_Err

Dim lngStartDate As Long, lngEndDate As Long

lngStartDate = CLng(CVDate(pvarStartDate))
lngEndDate = CLng(CVDate(pvarEndDate))
If lngStartDate <= lngEndDate Then
smsQtyOfWorkingDays = DateDiff("w", lngStartDate, lngEndDate) * 5 +
smsQtyOfWorkingDaysBetween2WeekDays(Weekday(lngStartDate),
Weekday(lngEndDate))
Else
lngStartDate = CLng(CVDate(pvarEndDate))
lngEndDate = CLng(CVDate(pvarStartDate))
smsQtyOfWorkingDays = -(DateDiff("w", lngStartDate, lngEndDate) * 5 +
smsQtyOfWorkingDaysBetween2WeekDays(Weekday(lngStartDate),
Weekday(lngEndDate)))
End If

smsQtyOfWorkingDays_Done:
Exit Function
smsQtyOfWorkingDays_Err:
Resume smsQtyOfWorkingDays_Done
End Function

Function smsQtyOfWorkingDaysBetween2WeekDays(intFirstWeekDay As Integer,
intSecondWeekDay As Integer)
On Error GoTo smsQtyOfWorkingDaysBetween2WeekDays_Err

smsQtyOfWorkingDaysBetween2WeekDays = 0
Dim intForIdx As Integer, intCycle2 As Integer, intCnt As Integer

intCnt = 0
If intFirstWeekDay <> intSecondWeekDay Then
If intFirstWeekDay < intSecondWeekDay Then
intCycle2 = intSecondWeekDay
Else
intCycle2 = intSecondWeekDay + 7
End If

For intForIdx = intFirstWeekDay To intCycle2 - 1
Select Case intForIdx Mod 7
Case 1, 7:
Case 2, 3, 4, 5, 6: intCnt = intCnt + 1
Case Else
End Select
Next intForIdx
End If
smsQtyOfWorkingDaysBetween2WeekDays = intCnt

smsQtyOfWorkingDaysBetween2WeekDays_Done:
Exit Function
smsQtyOfWorkingDaysBetween2WeekDays_Err:
Resume smsQtyOfWorkingDaysBetween2WeekDays_Done
End Function

Function WorkDayDiff( _
DateFrom As Date, _
DateTo As Date _
) As Long
WorkDayDiff = DateDiff("d", DateFrom, DateTo) - _
DateDiff("ww", DateFrom, DateTo, 1) * 2 - _
IIf(Weekday(DateTo, 1) = 7, _
IIf(Weekday(DateFrom, 1) = 7, 0, 1), _
IIf(Weekday(DateFrom, 1) = 7, -1, 0))
End Function

End Function

and works just dandy.
However. I need to run a query on a button click that extracts data from
two text boxes and then takes the queries numerical result and divides it by
the number of working days, to get an average over time.
So the button click essentially does this, right now:
clears the listboxes, runs a query through a macro, puts the valu einto a
listbox, takes listbox.listindex(1) and divides that by the result of another
query, in a different listbox. As i said. sloppy. I want to do all of this
programmatically and can't get it working. Here's what it looks like:


Private Sub Command49_Click()
Me.RunMacro (QtyWorkingdaysMacro)
Me.Requery
If Check51.Value = 0 Then Text55 = List69 / List63 Else Text55 = List71 /
List63
End Sub

Private Sub Command50_Click()
[Forms]![AverageForm].Requery
[Forms]![AverageForm].Refresh
If Check51.Value = 0 Then Text55 = List69.ListIndex(1) Else Text55 =
List71.ListIndex(1)
End Sub

here are the queries in question:
SELECT
Sum(MailLog.Itemsfororg100+[Itemsfororg107]+[Itemsfororg109]+[Itemsfororg113]+[Itemsfororg114]+[Itemsfororg119]+[Itemsfororg144]+[Itemsfororg185]+[Itemsfororg195]+[Itemsfororg528]+[CSCExpensereport]+[ATDCheckreq]+[ODCCorrections]+[Diner]+[Investigators]+[Credits]+[Freight]+[Tax]+[Fedex]+[Reversals])
AS [Total Processing Batch]
FROM MailLog
WHERE (((MailLog.ProcessingAssignedto) Like [Forms]![AverageForm]![Combo14])
AND ((MailLog.ProcessingAssignedDate)>=[Forms]![AverageForm]![Text7] And
(MailLog.ProcessingAssignedDate)<=[Forms]![AverageForm]![Text2]) AND
((MailLog.ProcessingCompleteDate)>=[Forms]![AverageForm]![Text7] And
(MailLog.ProcessingCompleteDate)<=[Forms]![AverageForm]![Text2]));


and the second query looks like this:
SELECT MailLog.ProcessingAssignedto,
(Sum(MailLog.Itemsfororg100+[Itemsfororg107]+[Itemsfororg109]+[Itemsfororg113]+[Itemsfororg114]+[Itemsfororg119]+[Itemsfororg144]+[Itemsfororg185]+[Itemsfororg195]+[Itemsfororg528]+[CSCExpensereport]+[ATDCheckreq]+[ODCCorrections]+[Diner]+[Investigators]+[Credits]+[Freight]+[Tax]+[Fedex]+[Reversals]))
AS [Total Processed Batches]
FROM MailLog
WHERE (((MailLog.ProcessingAssignedDate)>=[Forms]![AverageForm]![Text7] And
(MailLog.ProcessingAssignedDate)<=[Forms]![AverageForm]![Text2]) AND
((MailLog.ProcessingCompleteDate)>=[Forms]![AverageForm]![Text7] And
(MailLog.ProcessingCompleteDate)<=[Forms]![AverageForm]![Text2]))
GROUP BY MailLog.ProcessingAssignedto
HAVING (((MailLog.ProcessingAssignedto)=[Forms]![AverageForm]![Combo14]));

1st I think you're in over your head.

I have NO idea what you're doing with THREE hidden listboxes.

Look these things up in Help: (or go get a good book)

to calc days:
=DateDiff("d", date1, date2)
although if its time worked should you be counting hours?

To RUN a Query
Docmd.runquery (qryname)

To get one value from a qry (or table) (that has many rows and cols)
=Dlookup(fieldname, datasource, where-condition)

hth




:

I have a form named average form. On current, it puts two short dates into
two textboxes. The form has 3 hidden list boxes. Two have queries attached
to them, and the third calculutes the number of work days between the two
dates in the text boxes previously mentioned. I'd like to do all of this
programatically without using the invisible listboxes, it seems really
sloppy, i just dont have the experience in VB and SQL to do it properly. I

 

[Pwyd]

Though, on a side note -- the reason i didn't use one field for all the orgs
then try to sort them out for each record is multiple. one, the person
putting in the data didn't want to type it out. they wnated a simple
numerical entry ,and, i don't have the knowledge necessary to parse from
which field they entered the data, and enter the appropriately parsed values
into the single field, then extract them later. that would all require lots
of code. I don't have time to do any of it

PMFJI, it's not that hard to do the calculations you want in code. But you
have to be able to write down the steps necessary.

The first step is to write the pseudo code.
Then go back and add more detail.
Repeat until the code is written.

Example:

Sub DoCalc()

get two dates from MyForm
calc work days
get the sum of the $$ amount between two dates

divide amount by work days

store result on MyForm

End Sub


Before I go much further, I have to say (IMO) the structure for the table
"MailLog" should be changed. It looks like you have committed "Spreadsheet".
You have a field for each catagory. What happens if you have to add
"Itemsfororg607"? EVERY form, query and report will/might have to be changed.
If your structure for "MailLog" looked like:

ProcessingAssignedto - Text(?)
curAmount - Currency
lngCatagoryFK - Long
ProcessingAssignedDate - date
ProcessingCompleteDate - date
.
.
.


and a table tblCatagory:

lngCatagoryID - Auto (PK)
strCatagory - Text


as long as you were filtering (querying) by ProcessingAssignedto,
ProcessingAssignedDate & ProcessingCompleteDate, you wouldn't have to change
anything. You could add as many catagories as you wanted.

Without knowing a lot more about your mdb and your calculations, I can't
give you all of the code. Here is an example of (or at least a start) how to
convert your first (?)query to code:

(watch for line wrap)
'---------------------
Private Sub Command3_Click()
On Error GoTo Handle_Err

Dim db As DAO.Database
Dim rst As DAO.Recordset

Dim strSQL As String

Dim mvAssignedto As String
Dim mvAssignedDate As Date
Dim mvCompleteDate As Date

Dim msg As String

Set db = CurrentDb

mvAssignedto = [Forms]![AverageForm]![Combo14]
mvBeginDate = [Forms]![AverageForm]![Text7]
mvEndDate = [Forms]![AverageForm]![Text2]


strSQL = "SELECT Sum(MailLog.Itemsfororg100+[Itemsfororg107]+"
strSQL = strSQL & " [Itemsfororg109]+[Itemsfororg113]+[Itemsfororg114]+"
strSQL = strSQL & " [Itemsfororg119]+[Itemsfororg144]+[Itemsfororg185]+"
strSQL = strSQL & " [Itemsfororg195]+[Itemsfororg528]+[CSCExpensereport]+"
strSQL = strSQL & " [ATDCheckreq]+[ODCCorrections]+[Diner]+"
strSQL = strSQL & " [Investigators]+[Credits]+[Freight]+[Tax]+[Fedex]+"
strSQL = strSQL & " [Reversals]) AS [Total Processing Batch]"

strSQL = strSQL & " FROM MailLog"

strSQL = strSQL & " WHERE MailLog.ProcessingAssignedto Like '" &
mvAssignedto & "' AND"
strSQL = strSQL & " MailLog.ProcessingAssignedDate >= #" & mvBeginDate &
"# AND"
strSQL = strSQL & " MailLog.ProcessingAssignedDate <= #" & mvEndDate & "#
AND"
strSQL = strSQL & " ((MailLog.ProcessingCompleteDate)>= #" & mvBeginDate
& "# AND"
strSQL = strSQL & " MailLog.ProcessingCompleteDate<= #" & mvEndDate & "# ;"

Set rst = db.OpenRecordset(strSQL)

If Not rst.BOF And Not rst.EOF Then
rst.MoveLast
rst.MoveFirst
'for debugging
MsgBox "Records in query = " & rst.RecordCount
MsgBox "The first record sum is " & rst.Fields(0)


' do calculations here
' wkDays = smsQtyOfWorkingDays(mvBeginDate,mvEndDate)
'
'

Else
msg = "No records found for Assigned to Like " & mvAssignedto & vbCrLf
msg = msg & "Begin date >= " & mvBeginDate & vbCrLf
msg = msg & "End date >= " & mvEndDate & vbCrLf
MsgBox msg
End If

Handle_Err_Exit:
On Error Resume Next
'cleanup
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

Handle_Err:
MsgBox Err.Description
Resume Handle_Err_Exit

End Sub
'-------------------------


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

the dlookup is useful. But the docmd.openquery doesn't function properly
when you're using an if/then statement. I'll state the error if you wish.
as for your date diff, that won't work for workdays. i already have a
function for htat. it looks like this
Function smsQtyOfWorkingDays(pvarStartDate As Variant, pvarEndDate As
Variant) As Integer

End Function
On Error GoTo smsQtyOfWorkingDays_Err

Dim lngStartDate As Long, lngEndDate As Long

lngStartDate = CLng(CVDate(pvarStartDate))
lngEndDate = CLng(CVDate(pvarEndDate))
If lngStartDate <= lngEndDate Then
smsQtyOfWorkingDays = DateDiff("w", lngStartDate, lngEndDate) * 5 +
smsQtyOfWorkingDaysBetween2WeekDays(Weekday(lngStartDate),
Weekday(lngEndDate))
Else
lngStartDate = CLng(CVDate(pvarEndDate))
lngEndDate = CLng(CVDate(pvarStartDate))
smsQtyOfWorkingDays = -(DateDiff("w", lngStartDate, lngEndDate) * 5 +
smsQtyOfWorkingDaysBetween2WeekDays(Weekday(lngStartDate),
Weekday(lngEndDate)))
End If

smsQtyOfWorkingDays_Done:
Exit Function
smsQtyOfWorkingDays_Err:
Resume smsQtyOfWorkingDays_Done
End Function

Function smsQtyOfWorkingDaysBetween2WeekDays(intFirstWeekDay As Integer,
intSecondWeekDay As Integer)
On Error GoTo smsQtyOfWorkingDaysBetween2WeekDays_Err

smsQtyOfWorkingDaysBetween2WeekDays = 0
Dim intForIdx As Integer, intCycle2 As Integer, intCnt As Integer

intCnt = 0
If intFirstWeekDay <> intSecondWeekDay Then
If intFirstWeekDay < intSecondWeekDay Then
intCycle2 = intSecondWeekDay
Else
intCycle2 = intSecondWeekDay + 7
End If

For intForIdx = intFirstWeekDay To intCycle2 - 1
Select Case intForIdx Mod 7
Case 1, 7:
Case 2, 3, 4, 5, 6: intCnt = intCnt + 1
Case Else
End Select
Next intForIdx
End If
smsQtyOfWorkingDaysBetween2WeekDays = intCnt

smsQtyOfWorkingDaysBetween2WeekDays_Done:
Exit Function
smsQtyOfWorkingDaysBetween2WeekDays_Err:
Resume smsQtyOfWorkingDaysBetween2WeekDays_Done
End Function

Function WorkDayDiff( _
DateFrom As Date, _
DateTo As Date _
) As Long
WorkDayDiff = DateDiff("d", DateFrom, DateTo) - _
DateDiff("ww", DateFrom, DateTo, 1) * 2 - _
IIf(Weekday(DateTo, 1) = 7, _
IIf(Weekday(DateFrom, 1) = 7, 0, 1), _
IIf(Weekday(DateFrom, 1) = 7, -1, 0))
End Function

End Function

and works just dandy.
However. I need to run a query on a button click that extracts data from
two text boxes and then takes the queries numerical result and divides it by
the number of working days, to get an average over time.
So the button click essentially does this, right now:
clears the listboxes, runs a query through a macro, puts the valu einto a
listbox, takes listbox.listindex(1) and divides that by the result of another
query, in a different listbox. As i said. sloppy. I want to do all of this
programmatically and can't get it working. Here's what it looks like:


Private Sub Command49_Click()
Me.RunMacro (QtyWorkingdaysMacro)
Me.Requery
If Check51.Value = 0 Then Text55 = List69 / List63 Else Text55 = List71 /
List63
End Sub

Private Sub Command50_Click()
[Forms]![AverageForm].Requery
[Forms]![AverageForm].Refresh
If Check51.Value = 0 Then Text55 = List69.ListIndex(1) Else Text55 =
List71.ListIndex(1)
End Sub

here are the queries in question:
SELECT
Sum(MailLog.Itemsfororg100+[Itemsfororg107]+[Itemsfororg109]+[Itemsfororg113]+[Itemsfororg114]+[Itemsfororg119]+[Itemsfororg144]+[Itemsfororg185]+[Itemsfororg195]+[Itemsfororg528]+[CSCExpensereport]+[ATDCheckreq]+[ODCCorrections]+[Diner]+[Investigators]+[Credits]+[Freight]+[Tax]+[Fedex]+[Reversals])
AS [Total Processing Batch]
FROM MailLog
WHERE (((MailLog.ProcessingAssignedto) Like [Forms]![AverageForm]![Combo14])
AND ((MailLog.ProcessingAssignedDate)>=[Forms]![AverageForm]![Text7] And
(MailLog.ProcessingAssignedDate)<=[Forms]![AverageForm]![Text2]) AND
((MailLog.ProcessingCompleteDate)>=[Forms]![AverageForm]![Text7] And
(MailLog.ProcessingCompleteDate)<=[Forms]![AverageForm]![Text2]));


and the second query looks like this:
SELECT MailLog.ProcessingAssignedto,
(Sum(MailLog.Itemsfororg100+[Itemsfororg107]+[Itemsfororg109]+[Itemsfororg113]+[Itemsfororg114]+[Itemsfororg119]+[Itemsfororg144]+[Itemsfororg185]+[Itemsfororg195]+[Itemsfororg528]+[CSCExpensereport]+[ATDCheckreq]+[ODCCorrections]+[Diner]+[Investigators]+[Credits]+[Freight]+[Tax]+[Fedex]+[Reversals]))
AS [Total Processed Batches]
FROM MailLog
WHERE (((MailLog.ProcessingAssignedDate)>=[Forms]![AverageForm]![Text7] And
(MailLog.ProcessingAssignedDate)<=[Forms]![AverageForm]![Text2]) AND
((MailLog.ProcessingCompleteDate)>=[Forms]![AverageForm]![Text7] And
(MailLog.ProcessingCompleteDate)<=[Forms]![AverageForm]![Text2]))
GROUP BY MailLog.ProcessingAssignedto
HAVING (((MailLog.ProcessingAssignedto)=[Forms]![AverageForm]![Combo14]));

1st I think you're in over your head.

I have NO idea what you're doing with THREE hidden listboxes.

Look these things up in Help: (or go get a good book)

to calc days:
=DateDiff("d", date1, date2)
although if its time worked should you be counting hours?

To RUN a Query
Docmd.runquery (qryname)

To get one value from a qry (or table) (that has many rows and cols)
=Dlookup(fieldname, datasource, where-condition)

hth




:

I have a form named average form. On current, it puts two short dates into
two textboxes. The form has 3 hidden list boxes. Two have queries attached
to them, and the third calculutes the number of work days between the two
dates in the text boxes previously mentioned. I'd like to do all of this
programatically without using the invisible listboxes, it seems really
sloppy, i just dont have the experience in VB and SQL to do it properly. I

 

[John W. Vinson]

Though, on a side note -- the reason i didn't use one field for all the orgs
then try to sort them out for each record is multiple. one, the person
putting in the data didn't want to type it out. they wnated a simple
numerical entry ,and, i don't have the knowledge necessary to parse from
which field they entered the data, and enter the appropriately parsed values
into the single field, then extract them later. that would all require lots
of code. I don't have time to do any of it

How about no code at all, and no need to memorize numbers? Just use a Combo
Box on the form. The user can start typing the first two or three letters of
the organization's name and it will autocomplete.

 

[Pwyd]

i did that on the last form i built for a different branch of the same
company. They really didn't like it. The other problem, which perhaps i
should have mentioned, is that this form is then printed out and used as a
batch cover sheet -- so i'd have to also code what "prints" and/or build a
seperate form just for printing stuff out. I'm not making excuses. its
slapdash, sloppy, and frankly, this giant collection of small companies
doesn't deserve any better for asking me to build the database while still
completing all of my other duties

 

[Steve Sanford]

OK, sometimes you have to run with what you are given.

As I said, if you can do the calculation by hand, you can write code to do
it. Getting the data off of the form is easy. The hard part is getting the
queries to function. The example I gave you should help. It gets easier....

It helps to put

Debug.print strSQL

or

Msgbox strSQL


before you try to execute the SQL to check if the SQL is formed correctly.
Comment it out after you have the query working.


Post back if you have problems getting the code to work.

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

These are government contracts, there are no other orgs, or changes to them.
haven't been in many, many years. But should they come, i won't have to
update the old records. I remove them after some months and they're
archived, never to be looked at again. The form that they are viewed through
simply shows a null when they look at a form with the "new" field in it, and
an old record. Thank you very much for all your help. i'll give that code a
try. i know how to code in c++ and am familiar with pseudocode, thanks. I
haven't used VB in many, many years. I only remember bits and pieces of it,
and only enoguh to get me by. i'll fiddle with what you've given me here and
see if i can't get it working. Thanks again.

PMFJI, it's not that hard to do the calculations you want in code. But you
have to be able to write down the steps necessary.

The first step is to write the pseudo code.
Then go back and add more detail.
Repeat until the code is written.

Example:

Sub DoCalc()

get two dates from MyForm
calc work days
get the sum of the $$ amount between two dates

divide amount by work days

store result on MyForm

End Sub


Before I go much further, I have to say (IMO) the structure for the table
"MailLog" should be changed. It looks like you have committed "Spreadsheet".
You have a field for each catagory. What happens if you have to add
"Itemsfororg607"? EVERY form, query and report will/might have to be changed.
If your structure for "MailLog" looked like:

ProcessingAssignedto - Text(?)
curAmount - Currency
lngCatagoryFK - Long
ProcessingAssignedDate - date
ProcessingCompleteDate - date
.
.
.


and a table tblCatagory:

lngCatagoryID - Auto (PK)
strCatagory - Text


as long as you were filtering (querying) by ProcessingAssignedto,
ProcessingAssignedDate & ProcessingCompleteDate, you wouldn't have to change
anything. You could add as many catagories as you wanted.

Without knowing a lot more about your mdb and your calculations, I can't
give you all of the code. Here is an example of (or at least a start) how to
convert your first (?)query to code:

(watch for line wrap)
'---------------------
Private Sub Command3_Click()
On Error GoTo Handle_Err

Dim db As DAO.Database
Dim rst As DAO.Recordset

Dim strSQL As String

Dim mvAssignedto As String
Dim mvAssignedDate As Date
Dim mvCompleteDate As Date

Dim msg As String

Set db = CurrentDb

mvAssignedto = [Forms]![AverageForm]![Combo14]
mvBeginDate = [Forms]![AverageForm]![Text7]
mvEndDate = [Forms]![AverageForm]![Text2]


strSQL = "SELECT Sum(MailLog.Itemsfororg100+[Itemsfororg107]+"
strSQL = strSQL & " [Itemsfororg109]+[Itemsfororg113]+[Itemsfororg114]+"
strSQL = strSQL & " [Itemsfororg119]+[Itemsfororg144]+[Itemsfororg185]+"
strSQL = strSQL & " [Itemsfororg195]+[Itemsfororg528]+[CSCExpensereport]+"
strSQL = strSQL & " [ATDCheckreq]+[ODCCorrections]+[Diner]+"
strSQL = strSQL & " [Investigators]+[Credits]+[Freight]+[Tax]+[Fedex]+"
strSQL = strSQL & " [Reversals]) AS [Total Processing Batch]"

strSQL = strSQL & " FROM MailLog"

strSQL = strSQL & " WHERE MailLog.ProcessingAssignedto Like '" &
mvAssignedto & "' AND"
strSQL = strSQL & " MailLog.ProcessingAssignedDate >= #" & mvBeginDate &
"# AND"
strSQL = strSQL & " MailLog.ProcessingAssignedDate <= #" & mvEndDate & "#
AND"
strSQL = strSQL & " ((MailLog.ProcessingCompleteDate)>= #" & mvBeginDate
& "# AND"
strSQL = strSQL & " MailLog.ProcessingCompleteDate<= #" & mvEndDate & "# ;"

Set rst = db.OpenRecordset(strSQL)

If Not rst.BOF And Not rst.EOF Then
rst.MoveLast
rst.MoveFirst
'for debugging
MsgBox "Records in query = " & rst.RecordCount
MsgBox "The first record sum is " & rst.Fields(0)


' do calculations here
' wkDays = smsQtyOfWorkingDays(mvBeginDate,mvEndDate)
'
'

Else
msg = "No records found for Assigned to Like " & mvAssignedto & vbCrLf
msg = msg & "Begin date >= " & mvBeginDate & vbCrLf
msg = msg & "End date >= " & mvEndDate & vbCrLf
MsgBox msg
End If

Handle_Err_Exit:
On Error Resume Next
'cleanup
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

Handle_Err:
MsgBox Err.Description
Resume Handle_Err_Exit

End Sub
'-------------------------


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

the dlookup is useful. But the docmd.openquery doesn't function properly
when you're using an if/then statement. I'll state the error if you wish.
as for your date diff, that won't work for workdays. i already have a
function for htat. it looks like this
Function smsQtyOfWorkingDays(pvarStartDate As Variant, pvarEndDate As
Variant) As Integer

End Function
On Error GoTo smsQtyOfWorkingDays_Err

Dim lngStartDate As Long, lngEndDate As Long

lngStartDate = CLng(CVDate(pvarStartDate))
lngEndDate = CLng(CVDate(pvarEndDate))
If lngStartDate <= lngEndDate Then
smsQtyOfWorkingDays = DateDiff("w", lngStartDate, lngEndDate) * 5 +
smsQtyOfWorkingDaysBetween2WeekDays(Weekday(lngStartDate),
Weekday(lngEndDate))
Else
lngStartDate = CLng(CVDate(pvarEndDate))
lngEndDate = CLng(CVDate(pvarStartDate))
smsQtyOfWorkingDays = -(DateDiff("w", lngStartDate, lngEndDate) * 5 +
smsQtyOfWorkingDaysBetween2WeekDays(Weekday(lngStartDate),
Weekday(lngEndDate)))
End If

smsQtyOfWorkingDays_Done:
Exit Function
smsQtyOfWorkingDays_Err:
Resume smsQtyOfWorkingDays_Done
End Function

Function smsQtyOfWorkingDaysBetween2WeekDays(intFirstWeekDay As Integer,
intSecondWeekDay As Integer)
On Error GoTo smsQtyOfWorkingDaysBetween2WeekDays_Err

smsQtyOfWorkingDaysBetween2WeekDays = 0
Dim intForIdx As Integer, intCycle2 As Integer, intCnt As Integer

intCnt = 0
If intFirstWeekDay <> intSecondWeekDay Then
If intFirstWeekDay < intSecondWeekDay Then
intCycle2 = intSecondWeekDay
Else
intCycle2 = intSecondWeekDay + 7
End If

For intForIdx = intFirstWeekDay To intCycle2 - 1
Select Case intForIdx Mod 7
Case 1, 7:
Case 2, 3, 4, 5, 6: intCnt = intCnt + 1
Case Else
End Select
Next intForIdx
End If
smsQtyOfWorkingDaysBetween2WeekDays = intCnt

smsQtyOfWorkingDaysBetween2WeekDays_Done:
Exit Function
smsQtyOfWorkingDaysBetween2WeekDays_Err:
Resume smsQtyOfWorkingDaysBetween2WeekDays_Done
End Function

Function WorkDayDiff( _
DateFrom As Date, _
DateTo As Date _
) As Long
WorkDayDiff = DateDiff("d", DateFrom, DateTo) - _
DateDiff("ww", DateFrom, DateTo, 1) * 2 - _
IIf(Weekday(DateTo, 1) = 7, _
IIf(Weekday(DateFrom, 1) = 7, 0, 1), _
IIf(Weekday(DateFrom, 1) = 7, -1, 0))
End Function

End Function

and works just dandy.
However. I need to run a query on a button click that extracts data from
two text boxes and then takes the queries numerical result and divides it by
the number of working days, to get an average over time.
So the button click essentially does this, right now:
clears the listboxes, runs a query through a macro, puts the valu einto a
listbox, takes listbox.listindex(1) and divides that by the result of another
query, in a different listbox. As i said. sloppy. I want to do all of this
programmatically and can't get it working. Here's what it looks like:


Private Sub Command49_Click()
Me.RunMacro (QtyWorkingdaysMacro)
Me.Requery
If Check51.Value = 0 Then Text55 = List69 / List63 Else Text55 = List71 /
List63
End Sub

Private Sub Command50_Click()
[Forms]![AverageForm].Requery
[Forms]![AverageForm].Refresh
If Check51.Value = 0 Then Text55 = List69.ListIndex(1) Else Text55 =
List71.ListIndex(1)
End Sub

here are the queries in question:
SELECT
Sum(MailLog.Itemsfororg100+[Itemsfororg107]+[Itemsfororg109]+[Itemsfororg113]+[Itemsfororg114]+[Itemsfororg119]+[Itemsfororg144]+[Itemsfororg185]+[Itemsfororg195]+[Itemsfororg528]+[CSCExpensereport]+[ATDCheckreq]+[ODCCorrections]+[Diner]+[Investigators]+[Credits]+[Freight]+[Tax]+[Fedex]+[Reversals])
AS [Total Processing Batch]
FROM MailLog
WHERE (((MailLog.ProcessingAssignedto) Like [Forms]![AverageForm]![Combo14])
AND ((MailLog.ProcessingAssignedDate)>=[Forms]![AverageForm]![Text7] And
(MailLog.ProcessingAssignedDate)<=[Forms]![AverageForm]![Text2]) AND
((MailLog.ProcessingCompleteDate)>=[Forms]![AverageForm]![Text7] And
(MailLog.ProcessingCompleteDate)<=[Forms]![AverageForm]![Text2]));


and the second query looks like this:
SELECT MailLog.ProcessingAssignedto,
(Sum(MailLog.Itemsfororg100+[Itemsfororg107]+[Itemsfororg109]+[Itemsfororg113]+[Itemsfororg114]+[Itemsfororg119]+[Itemsfororg144]+[Itemsfororg185]+[Itemsfororg195]+[Itemsfororg528]+[CSCExpensereport]+[ATDCheckreq]+[ODCCorrections]+[Diner]+[Investigators]+[Credits]+[Freight]+[Tax]+[Fedex]+[Reversals]))
AS [Total Processed Batches]
FROM MailLog
WHERE (((MailLog.ProcessingAssignedDate)>=[Forms]![AverageForm]![Text7] And
(MailLog.ProcessingAssignedDate)<=[Forms]![AverageForm]![Text2]) AND
((MailLog.ProcessingCompleteDate)>=[Forms]![AverageForm]![Text7] And
(MailLog.ProcessingCompleteDate)<=[Forms]![AverageForm]![Text2]))
GROUP BY MailLog.ProcessingAssignedto
HAVING (((MailLog.ProcessingAssignedto)=[Forms]![AverageForm]![Combo14]));


:

1st I think you're in over your head.

I have NO idea what you're doing with THREE hidden listboxes.

Look these things up in Help: (or go get a good book)

to calc days:
=DateDiff("d", date1, date2)
although if its time worked should you be counting hours?

To RUN a Query
Docmd.runquery (qryname)

To get one value from a qry (or table) (that has many rows and cols)
=Dlookup(fieldname, datasource, where-condition)

 

[Pwyd]

Okay, so i was out sick yesterday. I've pasted the code under the average
button for command 49 click.

I removed the line-wrapping, but perhaps i've done it incorrectly. i get
this as a syntax error (run time. no red in the VB module shown)

Syntax error in query expression 'MailLog.ProcessingAssignedto Like 'Joseph'
AND Maillog.processingAssigneddate >= #2/7/2008 12:12:54 PM# and
maillog.processingAssignedDate <=#2/14/2008 12:12:54PM# AND
((Maillog.ProcessingCompleteDate)>=#2/7/2008 12:12:54 PM# and
Maillog.processingCompelteDate<=#2/14/2008 12:12:54 PM# ;'.

(isn't there a close paren missing there somewhere?)

 

[Steve Sanford]

Yes, remove all parens in the WHERE clause.

Also be aware that any records between 2/7/2008 00:00:01 AM (midnight) and
2/7/2008 12:12:54 PM *will not* be in the recordset.

Also, records between 2/14/2008 12:12:54PM and 2/14/2008 12:59:59 PM *will
not* be in the recordset.

How are the dates for [Forms]![AverageForm]![Text7] and
[Forms]![AverageForm]![Text2] being entered?


Maybe this doesn't matter, but if you need to get all records from just
after midnight on 2/7/2008 until just before midnight 2/14/2008, try:


mvBeginDate = Int([Forms]![AverageForm]![Text7])
mvEndDate = Int([Forms]![AverageForm]![Text2]) +1

The "+1" adds one day to the end date which would be midnight between 2/14
and 2/15. Try it with and without to see what records are returned.


HTH

 

[Pwyd]

Yeah i see what you mean. I was actually going to instruct the person
involved with this to simply move the "start" date back one day, and push the
"end" date back one day, so that it would pick up the records, exclusive of
each of those days. Your solution works just as well. And is one less
thing to document so that the next person who has to flub through this won't
screw it up

Yes, remove all parens in the WHERE clause.

Also be aware that any records between 2/7/2008 00:00:01 AM (midnight) and
2/7/2008 12:12:54 PM *will not* be in the recordset.

Also, records between 2/14/2008 12:12:54PM and 2/14/2008 12:59:59 PM *will
not* be in the recordset.

How are the dates for [Forms]![AverageForm]![Text7] and
[Forms]![AverageForm]![Text2] being entered?


Maybe this doesn't matter, but if you need to get all records from just
after midnight on 2/7/2008 until just before midnight 2/14/2008, try:


mvBeginDate = Int([Forms]![AverageForm]![Text7])
mvEndDate = Int([Forms]![AverageForm]![Text2]) +1

The "+1" adds one day to the end date which would be midnight between 2/14
and 2/15. Try it with and without to see what records are returned.


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Okay, so i was out sick yesterday. I've pasted the code under the average
button for command 49 click.

I removed the line-wrapping, but perhaps i've done it incorrectly. i get
this as a syntax error (run time. no red in the VB module shown)

Syntax error in query expression 'MailLog.ProcessingAssignedto Like 'Joseph'
AND Maillog.processingAssigneddate >= #2/7/2008 12:12:54 PM# and
maillog.processingAssignedDate <=#2/14/2008 12:12:54PM# AND
((Maillog.ProcessingCompleteDate)>=#2/7/2008 12:12:54 PM# and
Maillog.processingCompelteDate<=#2/14/2008 12:12:54 PM# ;'.

(isn't there a close paren missing there somewhere?)

 

[Pwyd]

Excellent, it works like a charm. Thanks everyone. Now when i'm not so sick,
i'll put it into the form properly, and remove those ugly useless invisible
textboxes

Yes, remove all parens in the WHERE clause.

Also be aware that any records between 2/7/2008 00:00:01 AM (midnight) and
2/7/2008 12:12:54 PM *will not* be in the recordset.

Also, records between 2/14/2008 12:12:54PM and 2/14/2008 12:59:59 PM *will
not* be in the recordset.

How are the dates for [Forms]![AverageForm]![Text7] and
[Forms]![AverageForm]![Text2] being entered?


Maybe this doesn't matter, but if you need to get all records from just
after midnight on 2/7/2008 until just before midnight 2/14/2008, try:


mvBeginDate = Int([Forms]![AverageForm]![Text7])
mvEndDate = Int([Forms]![AverageForm]![Text2]) +1

The "+1" adds one day to the end date which would be midnight between 2/14
and 2/15. Try it with and without to see what records are returned.


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Okay, so i was out sick yesterday. I've pasted the code under the average
button for command 49 click.

I removed the line-wrapping, but perhaps i've done it incorrectly. i get
this as a syntax error (run time. no red in the VB module shown)

Syntax error in query expression 'MailLog.ProcessingAssignedto Like 'Joseph'
AND Maillog.processingAssigneddate >= #2/7/2008 12:12:54 PM# and
maillog.processingAssignedDate <=#2/14/2008 12:12:54PM# AND
((Maillog.ProcessingCompleteDate)>=#2/7/2008 12:12:54 PM# and
Maillog.processingCompelteDate<=#2/14/2008 12:12:54 PM# ;'.

(isn't there a close paren missing there somewhere?)