Is it possible to run "If Statement" with DSum?

The actual code is:

Private Sub Form_Current()

Dim s As String
Dim rs As DAO.Recordset

s = "SELECT JobTitleName,DepartmentName,LOP" & _
" FROM [Service Record Query] WHERE EmployeeID = " _
& Nz(Me.EmployeeID, 0) & " AND DateEnd Is Null"

If Not Me.NewRecord Then
Set rs = CurrentDb.OpenRecordset(s, dbOpenDynaset)
If Not (rs.BOF And rs.EOF) Then
Me.Current_Job_Title_Name = rs!JobTitleName
Me.Current_Department_Name = rs!DepartmentName
Me.Current_LOP = rs!LOP
rs.Close
Set rs = Nothing
Else
Me.Current_Job_Title_Name = Null
Me.Current_Department_Name = Null
End If
End If
End Sub
--
Judy


"John W. Vinson" wrote:

On Wed, 17 Mar 2010 08:13:02 -0700, judyb
wrote:

Hi Steve,

Yes, I finally located that post and did as you suggested. It is now giving
me a Run-time error "3061": Too few parameters. Expected 2. When I select
the "Debug" button and my code opens, the following text is highlighted:
Set rs = CurrentDb. OpenRecordset (s, dbOpenDynaset)

I am lost. This is my first database so I am really green. Got any
suggestions?

Please post the actual code. It looks like your syntax is wrong! Click the
mouse on the word OpenRecordset and press F1 and study the help message for
the correct form of the OpenRecordset event.
--

John W. Vinson [MVP]
.

The actual code is:

Private Sub Form_Current()

Dim s As String
Dim rs As DAO.Recordset

s = "SELECT JobTitleName,DepartmentName,LOP" & _
" FROM [Service Record Query] WHERE EmployeeID = " _
& Nz(Me.EmployeeID, 0) & " AND DateEnd Is Null"

If Not Me.NewRecord Then
Set rs = CurrentDb.OpenRecordset(s, dbOpenDynaset)
If Not (rs.BOF And rs.EOF) Then
Me.Current_Job_Title_Name = rs!JobTitleName
Me.Current_Department_Name = rs!DepartmentName
Me.Current_LOP = rs!LOP
rs.Close
Set rs = Nothing
Else
Me.Current_Job_Title_Name = Null
Me.Current_Department_Name = Null
End If
End If
End Sub

This code worked fine until I made the following suggested changes:

Add an unbound textbox to the form where you get [Current_Department_Name}.
Name the textbox DepartmentNameCriteria and set its Visible property to No.
Put the following expression in its Controlsource property:
=IIF([Current_Department_Name] = "Reserves", Null,[Current_Department_Name])

Rather than use the DSum function, change your Service Record Query to a
Totals query. Click on the Sigma button (looks like a capital E) in the menu
at the top of the screen. Change Group By under WeeksService to Sum. Change
Group By under DepartName to Where. Put the following expression in the
criteria under DepartmentName:
Forms!NameOfYourForm!DepartmentNameCriteria Or
(Forms!NameOfYourFormDepartmentNameCriteria Is Null)



--
Judy


"J_Goddard via AccessMonster.com" wrote:

Either the syntax is wrong as suggested, or "s" (which must be a string
containing an SQL SELECT... statement) refers to a non-existant field
somewhere. What is "s", and what does it contain?

John G.


judyb wrote:
Hi Steve,

Yes, I finally located that post and did as you suggested. It is now giving
me a Run-time error "3061": Too few parameters. Expected 2. When I select
the "Debug" button and my code opens, the following text is highlighted:
Set rs = CurrentDb. OpenRecordset (s, dbOpenDynaset)

I am lost. This is my first database so I am really green. Got any
suggestions?
Judy,

[quoted text clipped - 43 lines]

.

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via http://www.accessmonster.com

.

On Wed, 17 Mar 2010 10:39:01 -0700, judyb
wrote:

The actual code is:

Private Sub Form_Current()

Dim s As String
Dim rs As DAO.Recordset

s = "SELECT JobTitleName,DepartmentName,LOP" & _
" FROM [Service Record Query] WHERE EmployeeID = " _
& Nz(Me.EmployeeID, 0) & " AND DateEnd Is Null"

If Not Me.NewRecord Then
Set rs = CurrentDb.OpenRecordset(s, dbOpenDynaset)
If Not (rs.BOF And rs.EOF) Then
Me.Current_Job_Title_Name = rs!JobTitleName
Me.Current_Department_Name = rs!DepartmentName
Me.Current_LOP = rs!LOP
rs.Close
Set rs = Nothing
Else
Me.Current_Job_Title_Name = Null
Me.Current_Department_Name = Null
End If
End If
End Sub

It sounds like either Service Record Query has two parameters that you're not
resolving, or that it does not include two of the fields you're selecting.
Could you post the SQL of this query?
--

John W. Vinson [MVP]

Hi Judy,

First, let's make sure [Service Record Query] is working properly. In the
code below, add the two new lines right after Private Sub .... The new code
will open the query and bypass everything else in the code.

Steve

Private Sub Form_Current()

DoCmd.OpenQuery "Service Record Query"
Exit Sub


Dim s As String
Dim rs As DAO.Recordset

s = "SELECT JobTitleName,DepartmentName,LOP" & _
" FROM [Service Record Query] WHERE EmployeeID = " _
& Nz(Me.EmployeeID, 0) & " AND DateEnd Is Null"

If Not Me.NewRecord Then
Set rs = CurrentDb.OpenRecordset(s, dbOpenDynaset)
If Not (rs.BOF And rs.EOF) Then
Me.Current_Job_Title_Name = rs!JobTitleName
Me.Current_Department_Name = rs!DepartmentName
Me.Current_LOP = rs!LOP
rs.Close
Set rs = Nothing
Else
Me.Current_Job_Title_Name = Null
Me.Current_Department_Name = Null
End If
End If
End Sub





"judyb" wrote in message
news
Hi Steve,

Yes, I finally located that post and did as you suggested. It is now
giving
me a Run-time error "3061": Too few parameters. Expected 2. When I
select
the "Debug" button and my code opens, the following text is highlighted:
Set rs = CurrentDb. OpenRecordset (s, dbOpenDynaset)

I am lost. This is my first database so I am really green. Got any
suggestions?
--
Judy


"Steve" wrote:

Judy,

Did you try changing your query to a totals query as I previously
suggested?

Steve


"judyb" wrote in message
...
John & Steve,

I apologize for reposting. I was unable to find my first post.
Anyway, I
thank you both for responding. I copied and pasted the new code, but
it
did
not provide the correct answer. In fact, the answer was way too high
(in
the
thousands) where it should have been below 100. Any other suggestions?
Thanks again!
--
Judy


"Steve" wrote:

I don't think your DSum function gives the OP the answer she wants!

If the current department name is millwright, she wants total
WeeksService
for Millwright. The same for Pipefitter. But if the current department
name
is Reserves, then she wants total WeeksService for MillWright and
Pipefitter.

Steve


"John W. Vinson" wrote in message
...
On Fri, 12 Mar 2010 10:56:01 -0800, judyb

wrote:

I hope this makes sense. In short, I need to keep the existing
code,
but need it to sum the Weeks Service slightly different IF the
Current
Department Name is equal to "Reserves". Thanks in advance!

Perhaps you missed the answers that Steve and I posted last week.
Here
you
go
again:

=DSum("[WeeksService]","Service Record Query","[EmployeeID] = " &
[EmployeeID] & " And [DepartmentName] = Current_Department_Name OR
[DepartmentName] = 'Reserves'")
--

John W. Vinson [MVP]


.



.

Hi Steve,
When you say "add the two new lines of code", are you referring to the
following code?

Forms!NameOfYourForm!DepartmentNameCriteria Or
(Forms!NameOfYourForm!DepartmentNameCriteria Is Null)
--
Judy


"Steve" wrote:

Hi Judy,

First, let's make sure [Service Record Query] is working properly. In the
code below, add the two new lines right after Private Sub .... The new code
will open the query and bypass everything else in the code.

Steve

Private Sub Form_Current()

DoCmd.OpenQuery "Service Record Query"
Exit Sub


Dim s As String
Dim rs As DAO.Recordset

s = "SELECT JobTitleName,DepartmentName,LOP" & _
" FROM [Service Record Query] WHERE EmployeeID = " _
& Nz(Me.EmployeeID, 0) & " AND DateEnd Is Null"

If Not Me.NewRecord Then
Set rs = CurrentDb.OpenRecordset(s, dbOpenDynaset)
If Not (rs.BOF And rs.EOF) Then
Me.Current_Job_Title_Name = rs!JobTitleName
Me.Current_Department_Name = rs!DepartmentName
Me.Current_LOP = rs!LOP
rs.Close
Set rs = Nothing
Else
Me.Current_Job_Title_Name = Null
Me.Current_Department_Name = Null
End If
End If
End Sub





"judyb" wrote in message
news
Hi Steve,

Yes, I finally located that post and did as you suggested. It is now
giving
me a Run-time error "3061": Too few parameters. Expected 2. When I
select
the "Debug" button and my code opens, the following text is highlighted:
Set rs = CurrentDb. OpenRecordset (s, dbOpenDynaset)

I am lost. This is my first database so I am really green. Got any
suggestions?
--
Judy


"Steve" wrote:

Judy,

Did you try changing your query to a totals query as I previously
suggested?

Steve


"judyb" wrote in message
...
John & Steve,

I apologize for reposting. I was unable to find my first post.
Anyway, I
thank you both for responding. I copied and pasted the new code, but
it
did
not provide the correct answer. In fact, the answer was way too high
(in
the
thousands) where it should have been below 100. Any other suggestions?
Thanks again!
--
Judy


"Steve" wrote:

I don't think your DSum function gives the OP the answer she wants!

If the current department name is millwright, she wants total
WeeksService
for Millwright. The same for Pipefitter. But if the current department
name
is Reserves, then she wants total WeeksService for MillWright and
Pipefitter.

Steve


"John W. Vinson" wrote in message
...
On Fri, 12 Mar 2010 10:56:01 -0800, judyb

wrote:

I hope this makes sense. In short, I need to keep the existing
code,
but need it to sum the Weeks Service slightly different IF the
Current
Department Name is equal to "Reserves". Thanks in advance!

Perhaps you missed the answers that Steve and I posted last week.
Here
you
go
again:

=DSum("[WeeksService]","Service Record Query","[EmployeeID] = " &
[EmployeeID] & " And [DepartmentName] = Current_Department_Name OR
[DepartmentName] = 'Reserves'")
--

John W. Vinson [MVP]


.



.



.

No! Add these two lines of code in Private Sub Form_Current() as shown below
......

DoCmd.OpenQuery "Service Record Query"
Exit Sub

Steve



"judyb" wrote in message
...
Hi Steve,
When you say "add the two new lines of code", are you referring to the
following code?

Forms!NameOfYourForm!DepartmentNameCriteria Or
(Forms!NameOfYourForm!DepartmentNameCriteria Is Null)
--
Judy


"Steve" wrote:

Hi Judy,

First, let's make sure [Service Record Query] is working properly. In the
code below, add the two new lines right after Private Sub .... The new
code
will open the query and bypass everything else in the code.

Steve

Private Sub Form_Current()

DoCmd.OpenQuery "Service Record Query" ---------------------
Exit Sub
--------------------


Dim s As String
Dim rs As DAO.Recordset

s = "SELECT JobTitleName,DepartmentName,LOP" & _
" FROM [Service Record Query] WHERE EmployeeID = " _
& Nz(Me.EmployeeID, 0) & " AND DateEnd Is Null"

If Not Me.NewRecord Then
Set rs = CurrentDb.OpenRecordset(s, dbOpenDynaset)
If Not (rs.BOF And rs.EOF) Then
Me.Current_Job_Title_Name = rs!JobTitleName
Me.Current_Department_Name = rs!DepartmentName
Me.Current_LOP = rs!LOP
rs.Close
Set rs = Nothing
Else
Me.Current_Job_Title_Name = Null
Me.Current_Department_Name = Null
End If
End If
End Sub





"judyb" wrote in message
news
Hi Steve,

Yes, I finally located that post and did as you suggested. It is now
giving
me a Run-time error "3061": Too few parameters. Expected 2. When I
select
the "Debug" button and my code opens, the following text is
highlighted:
Set rs = CurrentDb. OpenRecordset (s, dbOpenDynaset)

I am lost. This is my first database so I am really green. Got any
suggestions?
--
Judy


"Steve" wrote:

Judy,

Did you try changing your query to a totals query as I previously
suggested?

Steve


"judyb" wrote in message
...
John & Steve,

I apologize for reposting. I was unable to find my first post.
Anyway, I
thank you both for responding. I copied and pasted the new code,
but
it
did
not provide the correct answer. In fact, the answer was way too
high
(in
the
thousands) where it should have been below 100. Any other
suggestions?
Thanks again!
--
Judy


"Steve" wrote:

I don't think your DSum function gives the OP the answer she wants!

If the current department name is millwright, she wants total
WeeksService
for Millwright. The same for Pipefitter. But if the current
department
name
is Reserves, then she wants total WeeksService for MillWright and
Pipefitter.

Steve


"John W. Vinson" wrote in
message
...
On Fri, 12 Mar 2010 10:56:01 -0800, judyb

wrote:

I hope this makes sense. In short, I need to keep the existing
code,
but need it to sum the Weeks Service slightly different IF the
Current
Department Name is equal to "Reserves". Thanks in advance!

Perhaps you missed the answers that Steve and I posted last week.
Here
you
go
again:

=DSum("[WeeksService]","Service Record Query","[EmployeeID] = " &
[EmployeeID] & " And [DepartmentName] = Current_Department_Name
OR
[DepartmentName] = 'Reserves'")
--

John W. Vinson [MVP]


.



.



.

John,

I used the code below that you posted earlier in the form. It is
caluclating without any error messages, but it is giving me a number that is
far to high. The sum should be 83 and it is showing 30,987. Therefore, I
know that it is pulling Weeks Service from more records than it should.

Earlier Code Posted:
=DSum("[WeeksService]","Service Record Query","[EmployeeID] = " &
[EmployeeID] & " And [DepartmentName] = Current_Department_Name OR
[DepartmentName] = 'Reserves'")

Now for the SQL from the query (Service Record Query) that you requested in
this post:

SELECT [Service Record].ServiceRecordID, [Service Record].EmployeeID, [Job
Titles].LOP, [Service Record].JobTitleName, [Service Record].DepartmentName,
[Service Record].DateStart, [Service Record].DateEnd,
Sum(CalcWeeks([DateStart],[DateEnd])) AS WeeksService, [Service
Record].Reason, IIf(IsNull([DateEnd]),"Current","Previous") AS Status,
[Service Record].Comments
FROM [Service Record] INNER JOIN [Job Titles] ON [Service
Record].JobTitleName = [Job Titles].JobTitleName
GROUP BY [Service Record].ServiceRecordID, [Service Record].EmployeeID, [Job
Titles].LOP, [Service Record].JobTitleName, [Service Record].DepartmentName,
[Service Record].DateStart, [Service Record].DateEnd, [Service
Record].Reason, IIf(IsNull([DateEnd]),"Current","Previous"), [Service
Record].Comments
ORDER BY [Service Record].EmployeeID;

Thanks so much for your help!


--
Judy


"John W. Vinson" wrote:

On Wed, 17 Mar 2010 10:39:01 -0700, judyb
wrote:

The actual code is:

Private Sub Form_Current()

Dim s As String
Dim rs As DAO.Recordset

s = "SELECT JobTitleName,DepartmentName,LOP" & _
" FROM [Service Record Query] WHERE EmployeeID = " _
& Nz(Me.EmployeeID, 0) & " AND DateEnd Is Null"

If Not Me.NewRecord Then
Set rs = CurrentDb.OpenRecordset(s, dbOpenDynaset)
If Not (rs.BOF And rs.EOF) Then
Me.Current_Job_Title_Name = rs!JobTitleName
Me.Current_Department_Name = rs!DepartmentName
Me.Current_LOP = rs!LOP
rs.Close
Set rs = Nothing
Else
Me.Current_Job_Title_Name = Null
Me.Current_Department_Name = Null
End If
End If
End Sub

It sounds like either Service Record Query has two parameters that you're not
resolving, or that it does not include two of the fields you're selecting.
Could you post the SQL of this query?
--

John W. Vinson [MVP]
.

On Thu, 18 Mar 2010 10:58:02 -0700, judyb
wrote:

John,

I used the code below that you posted earlier in the form. It is
caluclating without any error messages, but it is giving me a number that is
far to high. The sum should be 83 and it is showing 30,987. Therefore, I
know that it is pulling Weeks Service from more records than it should.

Earlier Code Posted:
=DSum("[WeeksService]","Service Record Query","[EmployeeID] = " &
[EmployeeID] & " And [DepartmentName] = Current_Department_Name OR
[DepartmentName] = 'Reserves'")


Aha. You need some parentheses - as is it's finding ALL records ever for
Reserves, not just for this employee:

Earlier Code Posted:
=DSum("[WeeksService]","Service Record Query","[EmployeeID] = " &
[EmployeeID] & " And ([DepartmentName] = Current_Department_Name OR
[DepartmentName] = 'Reserves')")

That's a guess and may still not be correct.

--

John W. Vinson [MVP]

Judy:

I think you may have been following a red herring here and you can achieve
what you want with a simple extension of the expression. Going back to your
original expression:

=DSum("[WeeksService]","Service Record Query","[EmployeeID] = " &
[EmployeeID] & " And [DepartmentName] = Current_Department_Name")

I assume that Current_Department_Name is a string variable, so this would be
better expressed as the following, concatenating the value of the variable
into the expression:

=DSum("[WeeksService]","Service Record Query","[EmployeeID] = " &
[EmployeeID] & " And [DepartmentName] = """ & Current_Department_Name & """")

If we extend the logic to sum the WeeksService value for all rows regardless
of department where the value of the Current_Department_Name variable is
'Reserves' it becomes:

=DSum("[WeeksService]","Service Record Query","[EmployeeID] = " &
[EmployeeID] & " And ([DepartmentName] = """ & Current_Department_Name & """
Or """ & Current_Department_Name & """ = ""Reserves"")")

Say that the current EmployeeID value is 42 and the current value of the
Current_Department_Name variable is 'Millwright' the criteria expression
would evaluate as:

[EmployeeID] = 42 And ([DepartmentName] = "Millwright" Or "Millwright" =
"Reserves")

So the first part of the Boolean Or operation is True for any row where the
DepartmentName value is 'Millwright'. The second part of the operation is
always False, but as an Or operation only requires one part to be True all
rows where EmployeeID is 42 and the DepartmentName value is 'Millwright' will
be summed.

Say on the other hand that the current EmployeeID value is 42 and the current
value of the Current_Department_Name variable is 'Reserves' the criteria
expression would evaluate as:

[EmployeeID] = 42 And ([DepartmentName] = "Reserves" Or "Reserves" =
"Reserves")

In this case the second part of the Boolean Or operation is True under any
circumstances, so every row will be summed where EmployeeID = 42 regardless
of the DepartmentName value.

Ken Sheridan
Stafford, England

judyb wrote:
Hi Steve,
When you say "add the two new lines of code", are you referring to the
following code?

Forms!NameOfYourForm!DepartmentNameCriteria Or
(Forms!NameOfYourForm!DepartmentNameCriteria Is Null)
Hi Judy,

[quoted text clipped - 99 lines]

.

--
Message posted via http://www.accessmonster.com