Function Using Select Case for Query

C

Cydney

Using the function as shown below, I'm trying to create a field in my query
that will give me the correct title for the rows of data. I'm calling it in
my query using: Expr1: SetInvBrkdwn(). When I run the query, the function
just loops around opening the recordset.

Can someone please help me put this function in proper syntax?

Function SetInvBrkdwn() As String
Dim MyExpT As String
Dim MyODC As String
Dim MyExpType As String
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("qCostBreakdowns")
With rs
MyExpT = .Fields("ExpT")
MyODC = .Fields("ODC")
MyExpType = .Fields("ExpType")

If MyExpType Like "*Fringe*" Then MyExpType = "Fringe"
If MyExpType Like "*Overhead*" Then MyExpType = "Overhead"

Select Case MyExpType
Case Is = "G&A" Or "Fixed Fee": .Fields("InvBrkDwn") = MyExpType
Case Is = "Fringe"
Select Case MyODC
Case Is = "LB": If MyExpT = "DL" Then .Fields("InvBrkDwn") =
"Headquarters Fringe Rate"
Case Is = "SC" Or "LB": If MyExpT = "FL" Then
..Fields("InvBrkDwn") = "Field / Contract Fringe Rate"
End Select
Case Is = "Overhead"
Select Case MyODC
Case Is = "LB": If MyExpT = "DL" Then .Fields("InvBrkDwn") =
"Headquarters Overhead Rate"
Case Is = "SC" Or "LB": If MyExpT = "FL" Then
..Fields("InvBrkDwn") = "Field / Contract Overhead Rate"
End Select
Case Is <> "Overhead", Is <> "Fringe"
Select Case MyODC
Case Is = "LB": If MyExpT = "DL" Then .Fields("InvBrkDwn") =
"Labor: Headquarters"
Case Is = "SC" Or "LB": If MyExpT = "FL" Then
..Fields("InvBrkDwn") = "Labor: Field / Contract"
End Select
Case Else
Exit Function
End Select
End With
rs.close
End Function
--
Thank you, cs
~~~~~~~~~~~~~~~~~~~~~~~~
"What lies behind us and what lies before us are tiny matters compared to
what lies within us."
~ Ralph Waldo Emerson
 
P

pietlinden

Okay, I'm completely baffled. You're trying to use this function to
name a column in your query, but you never manipulate a query's SQL
property?

And your function, although it is declared to return a string value,
never actually returns ANY value. Where is the "SetInvBrkdwn=????" in
your code that passes the calculated value back to your function name
so it can be passed out of the routine?
 
C

Cydney

Sorry <embarrassed>...

Hopefully with your knowledge and my feeble attempt at explanation we can
figure this out. (Thank you for taking on the challenge by responding!)

First, I'm not sure what you mean by "manipulating a query's SQL property".

My IF statements were meant to populate the value. I suppose what I need is
to replace the '.Fields("InvBrkDwn")' with 'SetInvBrkdwn'?? I've never used
the "Fields" command before, but I found it somewhere and thought it might
populate my query field for me.

In essence, I want it to look at the values of the 3 fields and depending on
their value, populate the query field known as "InvBrkDwn" with the
appropriate string.

I know.. it's pretty messed up.. help?
 
J

John Spencer

Call the following function in your query
EXPR1: SetInvBrkDown([ExpT],[ODC],[ExpType])


Save the UNTESTED function below into a VBA module. Do not name the module
with the same name as the function


Public Function SetInvBrkdwn(myExpT, MyODC, MyExpType) As String

If MyExpType Like "*Fringe*" Then MyExpType = "Fringe"
If MyExpType Like "*Overhead*" Then MyExpType = "Overhead"

Select Case MyExpType
Case "G&A", "Fixed Fee"
SetInvBrkdwn = MyExpType

Case "Fringe"
Select Case MyODC
Case "LB"
If myExpT = "DL" Then
SetInvBrkdwn = "Headquarters Fringe Rate"
ElseIf myExpT = "FL" Then
SetInvBrkdwn = "Field / Contract Fringe Rate"

End If
Case "SC"
If myExpT = "FL" Then
SetInvBrkdwn = "Field / Contract Fringe Rate"
End If
End Select

Case "Overhead"
Select Case MyODC
Case "LB"
If myExpT = "DL" Then
SetInvBrkdwn = "Headquarters Overhead Rate"
ElseIf myExpT = "FL" Then
SetInvBrkdwn = "Field / Contract Overhead Rate"
End If
Case "SC", "LB"
If myExpT = "FL" Then
SetInvBrkdwn = "Field / Contract Overhead Rate"
End If
End Select

Case Is <> "Overhead", Is <> "Fringe"
Select Case MyODC
Case "LB"
If myExpT = "DL" Then
SetInvBrkdwn = "Labor: Headquarters"
ElseIf myExpT = "FL" Then
SetInvBrkdwn = "Labor: Field / Contract"
End If
Case "SC", "LB"
If myExpT = "FL" Then
SetInvBrkdwn = "Labor: Field / Contract"
End If
End Select

Case Else
SetInvBrkdwn = "UNKNOWN"
End Select

End Function


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
C

Cydney

Yes, yes, YES! That worked, John!
--
Thank you, cs
~~~~~~~~~~~~~~~~~~~~~~~~
"What lies behind us and what lies before us are tiny matters compared to
what lies within us."
~ Ralph Waldo Emerson


John Spencer said:
Call the following function in your query
EXPR1: SetInvBrkDown([ExpT],[ODC],[ExpType])


Save the UNTESTED function below into a VBA module. Do not name the module
with the same name as the function


Public Function SetInvBrkdwn(myExpT, MyODC, MyExpType) As String

If MyExpType Like "*Fringe*" Then MyExpType = "Fringe"
If MyExpType Like "*Overhead*" Then MyExpType = "Overhead"

Select Case MyExpType
Case "G&A", "Fixed Fee"
SetInvBrkdwn = MyExpType

Case "Fringe"
Select Case MyODC
Case "LB"
If myExpT = "DL" Then
SetInvBrkdwn = "Headquarters Fringe Rate"
ElseIf myExpT = "FL" Then
SetInvBrkdwn = "Field / Contract Fringe Rate"

End If
Case "SC"
If myExpT = "FL" Then
SetInvBrkdwn = "Field / Contract Fringe Rate"
End If
End Select

Case "Overhead"
Select Case MyODC
Case "LB"
If myExpT = "DL" Then
SetInvBrkdwn = "Headquarters Overhead Rate"
ElseIf myExpT = "FL" Then
SetInvBrkdwn = "Field / Contract Overhead Rate"
End If
Case "SC", "LB"
If myExpT = "FL" Then
SetInvBrkdwn = "Field / Contract Overhead Rate"
End If
End Select

Case Is <> "Overhead", Is <> "Fringe"
Select Case MyODC
Case "LB"
If myExpT = "DL" Then
SetInvBrkdwn = "Labor: Headquarters"
ElseIf myExpT = "FL" Then
SetInvBrkdwn = "Labor: Field / Contract"
End If
Case "SC", "LB"
If myExpT = "FL" Then
SetInvBrkdwn = "Labor: Field / Contract"
End If
End Select

Case Else
SetInvBrkdwn = "UNKNOWN"
End Select

End Function


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Sorry <embarrassed>...

Hopefully with your knowledge and my feeble attempt at explanation we can
figure this out. (Thank you for taking on the challenge by responding!)

First, I'm not sure what you mean by "manipulating a query's SQL property".

My IF statements were meant to populate the value. I suppose what I need is
to replace the '.Fields("InvBrkDwn")' with 'SetInvBrkdwn'?? I've never used
the "Fields" command before, but I found it somewhere and thought it might
populate my query field for me.

In essence, I want it to look at the values of the 3 fields and depending on
their value, populate the query field known as "InvBrkDwn" with the
appropriate string.

I know.. it's pretty messed up.. help?
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Print dependent on Select Case value 3
type mismatch error in macro in excel 0
If Then / Case Else Not Working 6
If then and Case Select 1
Error 3061: Too Fee Parameters 2
Using the "Select Case..." function 6
exporting xml from access 2000 query 3
Upsize to SQL ODBC error on rs!update 3

Top