switch statement to calculate field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have just learned about using switch statements and have found them helpful
except for the following:
(a) I seem to be limited to 12 variations. If I use more than 12, I get a
"too complex" error statement. Ironically, the variations can be quite
complex or very simple but the 12 variations holds.
(b) I must re-enter the switch statement for every instance -- in the form,
in the query, in the report, etc.
I know there must be a better way. I am not a programmer but if someone
could help me know how to enter the information in as code, I would be very,
very grateful.
Many thanks for your attention!
 
Nancy,

Never heard of a switch statement. Is this something newer than A2K? I
have not worked in anything new. If you can illucidate, I would appreciate :)
 
Klatuu said:
Nancy,

Never heard of a switch statement. Is this something newer than A2K?
I
have not worked in anything new. If you can illucidate, I would
appreciate :)
Click to expand...

It's in the Access 97 Help file.
 
Thanks, you think after 6 years in Access, I would have noticed it. Looking
at it, however, I am not sure I would use it. I'm thinking Debug nightmare
 
Sounds like I'm not getting any help -- just negative reactions. It does
work and I found it exploring a database someone else designed using Access
2000. It's helpful because it permits users to identify the variables and
then produces the correct registration fee.

I will provide more information and hope for a better answer.

My database is for conference registrations. Registration fees vary on
several factors -- membership status, time of registration (early bird, pre
registration, on site), registration selections (1 day, 2 days, special
sessions), etc. An example of an expression using the "switch" function is:

=Switch([RegStat]="Member" And [RegDeadline]="EB" And [RegDays]="Both
Days",50,[RegStat]="Non Member" And [RegDeadline]="EB" And [RegDays]="Both
Days",95,[RegStat]="Student" And [RegDeadline]="EB" And [RegDays]="Both
Days",45,[RegStat]="Member" And [RegDeadline]="PR" And [RegDays]="Both
Days",75,[RegStat]="Non Member" And [RegDeadline]="PR" And [RegDays]="Both
Days",120,[RegStat]="Student" And [RegDeadline]="PR" And [RegDays]="Both
Days",60,[RegStat]="Member" And [RegDeadline]="OS" And [RegDays]="Both
Days",95,[RegStat]="Non Member" And [RegDeadline]="OS" And [RegDays]="Both
Days",140,[RegStat]="Student" And [RegDeadline]="OS" And [RegDays]="Both
Days",75,[RegStat]="Complimentary",0)

Notice that the expression covers only a "both days" option. I cannot
include the other options because of the limit of 12 variations.

My sense is that this would be more efficiently done in code -- I just don't
know how to do that.

I'm hoping for better help and would be very grateful.
 
Nancy said:
Sounds like I'm not getting any help -- just negative reactions. It
does work and I found it exploring a database someone else designed
using Access 2000. It's helpful because it permits users to identify
the variables and then produces the correct registration fee.

I will provide more information and hope for a better answer.

My database is for conference registrations. Registration fees vary
on several factors -- membership status, time of registration (early
bird, pre registration, on site), registration selections (1 day, 2
days, special sessions), etc. An example of an expression using the
"switch" function is:

=Switch([RegStat]="Member" And [RegDeadline]="EB" And [RegDays]="Both
Days",50,[RegStat]="Non Member" And [RegDeadline]="EB" And
[RegDays]="Both Days",95,[RegStat]="Student" And [RegDeadline]="EB"
And [RegDays]="Both Days",45,[RegStat]="Member" And
[RegDeadline]="PR" And [RegDays]="Both Days",75,[RegStat]="Non
Member" And [RegDeadline]="PR" And [RegDays]="Both
Days",120,[RegStat]="Student" And [RegDeadline]="PR" And
[RegDays]="Both Days",60,[RegStat]="Member" And [RegDeadline]="OS"
And [RegDays]="Both Days",95,[RegStat]="Non Member" And
[RegDeadline]="OS" And [RegDays]="Both Days",140,[RegStat]="Student"
And [RegDeadline]="OS" And [RegDays]="Both
Days",75,[RegStat]="Complimentary",0)

Notice that the expression covers only a "both days" option. I cannot
include the other options because of the limit of 12 variations.

My sense is that this would be more efficiently done in code -- I
just don't know how to do that.
Click to expand...

You're absolutely correct that anytime a standard expression gets too
complex it is better to move to a custom function. It's actually pretty easy
to build. Here is a simplified example.

Function MyFunction(varRegStat As String, _
varRegDeadline As String _
varRegDays As String) As Integer

If varRegStat = "Member" _
And varRegDeadline = "EB" _
And varRegDays = "Both Days" Then
MyFunction = 50
ElseIf varRegStat = "Non Member" _
And varRegDeadline = "EB" _
And varRegDays = "Both Days" Then
MyFunction = 95
Elseif...

End Function

You would use the function in your query with...

MyFunction([RegStat], [RegDeadline], [RegDays])
 
Nancy,

I don't know where you got any negative attitude. I only asked what a
Switch statement is and later said I didn't think much of it. Still don't.
In any case, I have looked at a lot of possibilties. In a perfect world, it
would be best to have a table with the fees structure in it so you don't have
to change your code every time you have a different event. If you consider
that negative, then live with it. Anyway, after about 4 hours of playing
with options, here is what I think will work for you. Since there are more
options than "Both Days", you will have to add some to the code, but I think
you can figure it out.

Function FindFee(strStat As String, strDeadline As String, strDays As
String) As Currency
'Compliments of Dave Hargis

Dim rstFees As ADODB.Recordset

If strStat = "Complimentary" Then
FindFee = 0
Exit Function
End If

'Create a recordset for the options
Set rstFees = New ADODB.Recordset
rstFees.Fields.Append "Stat", adVarChar, 10
rstFees.Fields.Append "Deadline", adVarChar, 2
rstFees.Fields.Append "Days", adVarChar, 20
rstFees.Fields.Append "Fee", adCurrency
'Open the recordset
rstFees.Open
'Load the values in the recordset
'Add Additional lines here for additional options
'Use the first line of the add new as is - it is the field names
'The second line is the values for each field

rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Member", "EB", "Both Days", 50)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Non Member", "EB", "Both Days", 95)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Student", "EB", "Both Days", 45)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Member", "PR", "Both Days", 75)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Non Member", "PR", "Both Days", 120)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Student", "PR", "Both Days", 60)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Member", "OS", "Both Days", 95)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Non Member", "OS", "Both Days", 140)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Student", "OS", "Both Days", 75)
rstFees.UpdateBatch

'Setting this filter will limit the recordset to 1 row with the correct fee
rstFees.Filter = "[stat] = '" & strStat & "' And [deadline] = '" _
& strDeadline & "' And [days] = '" & strDays & "'"

'Check for EOF to avoid an error in case we passed a bad value to the function
If rstFees.EOF Then
FindFee = -1 ' Return a value that says we could not find it
Else
rstFees.MoveFirst
FindFee = rstFees![fee]
End If

rstFees.Close
Set rstFees = Nothing
End Function


Nancy said:
Sounds like I'm not getting any help -- just negative reactions. It does
work and I found it exploring a database someone else designed using Access
2000. It's helpful because it permits users to identify the variables and
then produces the correct registration fee.

I will provide more information and hope for a better answer.

My database is for conference registrations. Registration fees vary on
several factors -- membership status, time of registration (early bird, pre
registration, on site), registration selections (1 day, 2 days, special
sessions), etc. An example of an expression using the "switch" function is:

=Switch([RegStat]="Member" And [RegDeadline]="EB" And [RegDays]="Both
Days",50,[RegStat]="Non Member" And [RegDeadline]="EB" And [RegDays]="Both
Days",95,[RegStat]="Student" And [RegDeadline]="EB" And [RegDays]="Both
Days",45,[RegStat]="Member" And [RegDeadline]="PR" And [RegDays]="Both
Days",75,[RegStat]="Non Member" And [RegDeadline]="PR" And [RegDays]="Both
Days",120,[RegStat]="Student" And [RegDeadline]="PR" And [RegDays]="Both
Days",60,[RegStat]="Member" And [RegDeadline]="OS" And [RegDays]="Both
Days",95,[RegStat]="Non Member" And [RegDeadline]="OS" And [RegDays]="Both
Days",140,[RegStat]="Student" And [RegDeadline]="OS" And [RegDays]="Both
Days",75,[RegStat]="Complimentary",0)

Notice that the expression covers only a "both days" option. I cannot
include the other options because of the limit of 12 variations.

My sense is that this would be more efficiently done in code -- I just don't
know how to do that.

I'm hoping for better help and would be very grateful.
--
nhb -- nc


Rick Brandt said:
It's in the Access 97 Help file.
Click to expand...
Click to expand...
 
Thanks to both Rick and Klatuu. I will try both options and get back to you.
--
nhb -- nc


Klatuu said:
Nancy,

I don't know where you got any negative attitude. I only asked what a
Switch statement is and later said I didn't think much of it. Still don't.
In any case, I have looked at a lot of possibilties. In a perfect world, it
would be best to have a table with the fees structure in it so you don't have
to change your code every time you have a different event. If you consider
that negative, then live with it. Anyway, after about 4 hours of playing
with options, here is what I think will work for you. Since there are more
options than "Both Days", you will have to add some to the code, but I think
you can figure it out.

Function FindFee(strStat As String, strDeadline As String, strDays As
String) As Currency
'Compliments of Dave Hargis

Dim rstFees As ADODB.Recordset

If strStat = "Complimentary" Then
FindFee = 0
Exit Function
End If

'Create a recordset for the options
Set rstFees = New ADODB.Recordset
rstFees.Fields.Append "Stat", adVarChar, 10
rstFees.Fields.Append "Deadline", adVarChar, 2
rstFees.Fields.Append "Days", adVarChar, 20
rstFees.Fields.Append "Fee", adCurrency
'Open the recordset
rstFees.Open
'Load the values in the recordset
'Add Additional lines here for additional options
'Use the first line of the add new as is - it is the field names
'The second line is the values for each field

rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Member", "EB", "Both Days", 50)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Non Member", "EB", "Both Days", 95)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Student", "EB", "Both Days", 45)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Member", "PR", "Both Days", 75)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Non Member", "PR", "Both Days", 120)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Student", "PR", "Both Days", 60)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Member", "OS", "Both Days", 95)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Non Member", "OS", "Both Days", 140)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Student", "OS", "Both Days", 75)
rstFees.UpdateBatch

'Setting this filter will limit the recordset to 1 row with the correct fee
rstFees.Filter = "[stat] = '" & strStat & "' And [deadline] = '" _
& strDeadline & "' And [days] = '" & strDays & "'"

'Check for EOF to avoid an error in case we passed a bad value to the function
If rstFees.EOF Then
FindFee = -1 ' Return a value that says we could not find it
Else
rstFees.MoveFirst
FindFee = rstFees![fee]
End If

rstFees.Close
Set rstFees = Nothing
End Function


Nancy said:
Sounds like I'm not getting any help -- just negative reactions. It does
work and I found it exploring a database someone else designed using Access
2000. It's helpful because it permits users to identify the variables and
then produces the correct registration fee.

I will provide more information and hope for a better answer.

My database is for conference registrations. Registration fees vary on
several factors -- membership status, time of registration (early bird, pre
registration, on site), registration selections (1 day, 2 days, special
sessions), etc. An example of an expression using the "switch" function is:

=Switch([RegStat]="Member" And [RegDeadline]="EB" And [RegDays]="Both
Days",50,[RegStat]="Non Member" And [RegDeadline]="EB" And [RegDays]="Both
Days",95,[RegStat]="Student" And [RegDeadline]="EB" And [RegDays]="Both
Days",45,[RegStat]="Member" And [RegDeadline]="PR" And [RegDays]="Both
Days",75,[RegStat]="Non Member" And [RegDeadline]="PR" And [RegDays]="Both
Days",120,[RegStat]="Student" And [RegDeadline]="PR" And [RegDays]="Both
Days",60,[RegStat]="Member" And [RegDeadline]="OS" And [RegDays]="Both
Days",95,[RegStat]="Non Member" And [RegDeadline]="OS" And [RegDays]="Both
Days",140,[RegStat]="Student" And [RegDeadline]="OS" And [RegDays]="Both
Days",75,[RegStat]="Complimentary",0)

Notice that the expression covers only a "both days" option. I cannot
include the other options because of the limit of 12 variations.

My sense is that this would be more efficiently done in code -- I just don't
know how to do that.

I'm hoping for better help and would be very grateful.
--
nhb -- nc


Rick Brandt said:
Klatuu wrote:
Nancy,

Never heard of a switch statement. Is this something newer than A2K?
I
have not worked in anything new. If you can illucidate, I would
appreciate :)

It's in the Access 97 Help file.
Click to expand...
Click to expand...
Click to expand...
 
Good luck, Nancy. I'm sure either approach will get you where you need to
go. I would interested to know which way you go with this.

Nancy said:
Thanks to both Rick and Klatuu. I will try both options and get back to you.
--
nhb -- nc


Klatuu said:
Nancy,

I don't know where you got any negative attitude. I only asked what a
Switch statement is and later said I didn't think much of it. Still don't.
In any case, I have looked at a lot of possibilties. In a perfect world, it
would be best to have a table with the fees structure in it so you don't have
to change your code every time you have a different event. If you consider
that negative, then live with it. Anyway, after about 4 hours of playing
with options, here is what I think will work for you. Since there are more
options than "Both Days", you will have to add some to the code, but I think
you can figure it out.

Function FindFee(strStat As String, strDeadline As String, strDays As
String) As Currency
'Compliments of Dave Hargis

Dim rstFees As ADODB.Recordset

If strStat = "Complimentary" Then
FindFee = 0
Exit Function
End If

'Create a recordset for the options
Set rstFees = New ADODB.Recordset
rstFees.Fields.Append "Stat", adVarChar, 10
rstFees.Fields.Append "Deadline", adVarChar, 2
rstFees.Fields.Append "Days", adVarChar, 20
rstFees.Fields.Append "Fee", adCurrency
'Open the recordset
rstFees.Open
'Load the values in the recordset
'Add Additional lines here for additional options
'Use the first line of the add new as is - it is the field names
'The second line is the values for each field

rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Member", "EB", "Both Days", 50)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Non Member", "EB", "Both Days", 95)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Student", "EB", "Both Days", 45)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Member", "PR", "Both Days", 75)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Non Member", "PR", "Both Days", 120)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Student", "PR", "Both Days", 60)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Member", "OS", "Both Days", 95)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Non Member", "OS", "Both Days", 140)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Student", "OS", "Both Days", 75)
rstFees.UpdateBatch

'Setting this filter will limit the recordset to 1 row with the correct fee
rstFees.Filter = "[stat] = '" & strStat & "' And [deadline] = '" _
& strDeadline & "' And [days] = '" & strDays & "'"

'Check for EOF to avoid an error in case we passed a bad value to the function
If rstFees.EOF Then
FindFee = -1 ' Return a value that says we could not find it
Else
rstFees.MoveFirst
FindFee = rstFees![fee]
End If

rstFees.Close
Set rstFees = Nothing
End Function


Nancy said:
Sounds like I'm not getting any help -- just negative reactions. It does
work and I found it exploring a database someone else designed using Access
2000. It's helpful because it permits users to identify the variables and
then produces the correct registration fee.

I will provide more information and hope for a better answer.

My database is for conference registrations. Registration fees vary on
several factors -- membership status, time of registration (early bird, pre
registration, on site), registration selections (1 day, 2 days, special
sessions), etc. An example of an expression using the "switch" function is:

=Switch([RegStat]="Member" And [RegDeadline]="EB" And [RegDays]="Both
Days",50,[RegStat]="Non Member" And [RegDeadline]="EB" And [RegDays]="Both
Days",95,[RegStat]="Student" And [RegDeadline]="EB" And [RegDays]="Both
Days",45,[RegStat]="Member" And [RegDeadline]="PR" And [RegDays]="Both
Days",75,[RegStat]="Non Member" And [RegDeadline]="PR" And [RegDays]="Both
Days",120,[RegStat]="Student" And [RegDeadline]="PR" And [RegDays]="Both
Days",60,[RegStat]="Member" And [RegDeadline]="OS" And [RegDays]="Both
Days",95,[RegStat]="Non Member" And [RegDeadline]="OS" And [RegDays]="Both
Days",140,[RegStat]="Student" And [RegDeadline]="OS" And [RegDays]="Both
Days",75,[RegStat]="Complimentary",0)

Notice that the expression covers only a "both days" option. I cannot
include the other options because of the limit of 12 variations.

My sense is that this would be more efficiently done in code -- I just don't
know how to do that.

I'm hoping for better help and would be very grateful.
--
nhb -- nc


:

Klatuu wrote:
Nancy,

Never heard of a switch statement. Is this something newer than A2K?
I
have not worked in anything new. If you can illucidate, I would
appreciate :)

It's in the Access 97 Help file.
Click to expand...
Click to expand...
Click to expand...
 
Thanks to both of you. I do understand the code and can expand it in each
case to include all the options. I still have a problem though because I do
not know how to make the procedure work. Where do I insert the function --
per the form properties or the field or what? When I was using the "switch"
statement I was inserting in the control source for the field to be filled
with the result. Control source used the "switch" expression but does not
accept code. I know this is lame and stupid but I am not a programmer. I am
very grateful for your attention and help. And, I did not mean to offend --
I apologize. That was my frustration. Now I need to know how to call the
procedure at the right place.
--
nhb -- nc


Klatuu said:
Good luck, Nancy. I'm sure either approach will get you where you need to
go. I would interested to know which way you go with this.

Nancy said:
Thanks to both Rick and Klatuu. I will try both options and get back to you.
--
nhb -- nc


Klatuu said:
Nancy,

I don't know where you got any negative attitude. I only asked what a
Switch statement is and later said I didn't think much of it. Still don't.
In any case, I have looked at a lot of possibilties. In a perfect world, it
would be best to have a table with the fees structure in it so you don't have
to change your code every time you have a different event. If you consider
that negative, then live with it. Anyway, after about 4 hours of playing
with options, here is what I think will work for you. Since there are more
options than "Both Days", you will have to add some to the code, but I think
you can figure it out.

Function FindFee(strStat As String, strDeadline As String, strDays As
String) As Currency
'Compliments of Dave Hargis

Dim rstFees As ADODB.Recordset

If strStat = "Complimentary" Then
FindFee = 0
Exit Function
End If

'Create a recordset for the options
Set rstFees = New ADODB.Recordset
rstFees.Fields.Append "Stat", adVarChar, 10
rstFees.Fields.Append "Deadline", adVarChar, 2
rstFees.Fields.Append "Days", adVarChar, 20
rstFees.Fields.Append "Fee", adCurrency
'Open the recordset
rstFees.Open
'Load the values in the recordset
'Add Additional lines here for additional options
'Use the first line of the add new as is - it is the field names
'The second line is the values for each field

rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Member", "EB", "Both Days", 50)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Non Member", "EB", "Both Days", 95)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Student", "EB", "Both Days", 45)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Member", "PR", "Both Days", 75)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Non Member", "PR", "Both Days", 120)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Student", "PR", "Both Days", 60)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Member", "OS", "Both Days", 95)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Non Member", "OS", "Both Days", 140)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Student", "OS", "Both Days", 75)
rstFees.UpdateBatch

'Setting this filter will limit the recordset to 1 row with the correct fee
rstFees.Filter = "[stat] = '" & strStat & "' And [deadline] = '" _
& strDeadline & "' And [days] = '" & strDays & "'"

'Check for EOF to avoid an error in case we passed a bad value to the function
If rstFees.EOF Then
FindFee = -1 ' Return a value that says we could not find it
Else
rstFees.MoveFirst
FindFee = rstFees![fee]
End If

rstFees.Close
Set rstFees = Nothing
End Function


:

Sounds like I'm not getting any help -- just negative reactions. It does
work and I found it exploring a database someone else designed using Access
2000. It's helpful because it permits users to identify the variables and
then produces the correct registration fee.

I will provide more information and hope for a better answer.

My database is for conference registrations. Registration fees vary on
several factors -- membership status, time of registration (early bird, pre
registration, on site), registration selections (1 day, 2 days, special
sessions), etc. An example of an expression using the "switch" function is:

=Switch([RegStat]="Member" And [RegDeadline]="EB" And [RegDays]="Both
Days",50,[RegStat]="Non Member" And [RegDeadline]="EB" And [RegDays]="Both
Days",95,[RegStat]="Student" And [RegDeadline]="EB" And [RegDays]="Both
Days",45,[RegStat]="Member" And [RegDeadline]="PR" And [RegDays]="Both
Days",75,[RegStat]="Non Member" And [RegDeadline]="PR" And [RegDays]="Both
Days",120,[RegStat]="Student" And [RegDeadline]="PR" And [RegDays]="Both
Days",60,[RegStat]="Member" And [RegDeadline]="OS" And [RegDays]="Both
Days",95,[RegStat]="Non Member" And [RegDeadline]="OS" And [RegDays]="Both
Days",140,[RegStat]="Student" And [RegDeadline]="OS" And [RegDays]="Both
Days",75,[RegStat]="Complimentary",0)

Notice that the expression covers only a "both days" option. I cannot
include the other options because of the limit of 12 variations.

My sense is that this would be more efficiently done in code -- I just don't
know how to do that.

I'm hoping for better help and would be very grateful.
--
nhb -- nc


:

Klatuu wrote:
Nancy,

Never heard of a switch statement. Is this something newer than A2K?
I
have not worked in anything new. If you can illucidate, I would
appreciate :)

It's in the Access 97 Help file.
Click to expand...
Click to expand...
Click to expand...
 
Nancy,

If you use the function I sent, it should be put in a VBA module or if it
will only be used by the one form, you can put it at the top of your form
module just after the declarations. If you choose to put it in the default
value of the text box, it should accept the function. The problem there is,
the data to do the calculation may not be available at the time. Without
knowing the form, it is hard to say exactly where the best place to put it
would be. Can you identify a place where all the other data is available?
you could pick an event that would fire at that point in time.

Nancy said:
Thanks to both of you. I do understand the code and can expand it in each
case to include all the options. I still have a problem though because I do
not know how to make the procedure work. Where do I insert the function --
per the form properties or the field or what? When I was using the "switch"
statement I was inserting in the control source for the field to be filled
with the result. Control source used the "switch" expression but does not
accept code. I know this is lame and stupid but I am not a programmer. I am
very grateful for your attention and help. And, I did not mean to offend --
I apologize. That was my frustration. Now I need to know how to call the
procedure at the right place.
--
nhb -- nc


Klatuu said:
Good luck, Nancy. I'm sure either approach will get you where you need to
go. I would interested to know which way you go with this.

Nancy said:
Thanks to both Rick and Klatuu. I will try both options and get back to you.
--
nhb -- nc


:

Nancy,

I don't know where you got any negative attitude. I only asked what a
Switch statement is and later said I didn't think much of it. Still don't.
In any case, I have looked at a lot of possibilties. In a perfect world, it
would be best to have a table with the fees structure in it so you don't have
to change your code every time you have a different event. If you consider
that negative, then live with it. Anyway, after about 4 hours of playing
with options, here is what I think will work for you. Since there are more
options than "Both Days", you will have to add some to the code, but I think
you can figure it out.

Function FindFee(strStat As String, strDeadline As String, strDays As
String) As Currency
'Compliments of Dave Hargis

Dim rstFees As ADODB.Recordset

If strStat = "Complimentary" Then
FindFee = 0
Exit Function
End If

'Create a recordset for the options
Set rstFees = New ADODB.Recordset
rstFees.Fields.Append "Stat", adVarChar, 10
rstFees.Fields.Append "Deadline", adVarChar, 2
rstFees.Fields.Append "Days", adVarChar, 20
rstFees.Fields.Append "Fee", adCurrency
'Open the recordset
rstFees.Open
'Load the values in the recordset
'Add Additional lines here for additional options
'Use the first line of the add new as is - it is the field names
'The second line is the values for each field

rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Member", "EB", "Both Days", 50)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Non Member", "EB", "Both Days", 95)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Student", "EB", "Both Days", 45)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Member", "PR", "Both Days", 75)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Non Member", "PR", "Both Days", 120)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Student", "PR", "Both Days", 60)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Member", "OS", "Both Days", 95)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Non Member", "OS", "Both Days", 140)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Student", "OS", "Both Days", 75)
rstFees.UpdateBatch

'Setting this filter will limit the recordset to 1 row with the correct fee
rstFees.Filter = "[stat] = '" & strStat & "' And [deadline] = '" _
& strDeadline & "' And [days] = '" & strDays & "'"

'Check for EOF to avoid an error in case we passed a bad value to the function
If rstFees.EOF Then
FindFee = -1 ' Return a value that says we could not find it
Else
rstFees.MoveFirst
FindFee = rstFees![fee]
End If

rstFees.Close
Set rstFees = Nothing
End Function


:

Sounds like I'm not getting any help -- just negative reactions. It does
work and I found it exploring a database someone else designed using Access
2000. It's helpful because it permits users to identify the variables and
then produces the correct registration fee.

I will provide more information and hope for a better answer.

My database is for conference registrations. Registration fees vary on
several factors -- membership status, time of registration (early bird, pre
registration, on site), registration selections (1 day, 2 days, special
sessions), etc. An example of an expression using the "switch" function is:

=Switch([RegStat]="Member" And [RegDeadline]="EB" And [RegDays]="Both
Days",50,[RegStat]="Non Member" And [RegDeadline]="EB" And [RegDays]="Both
Days",95,[RegStat]="Student" And [RegDeadline]="EB" And [RegDays]="Both
Days",45,[RegStat]="Member" And [RegDeadline]="PR" And [RegDays]="Both
Days",75,[RegStat]="Non Member" And [RegDeadline]="PR" And [RegDays]="Both
Days",120,[RegStat]="Student" And [RegDeadline]="PR" And [RegDays]="Both
Days",60,[RegStat]="Member" And [RegDeadline]="OS" And [RegDays]="Both
Days",95,[RegStat]="Non Member" And [RegDeadline]="OS" And [RegDays]="Both
Days",140,[RegStat]="Student" And [RegDeadline]="OS" And [RegDays]="Both
Days",75,[RegStat]="Complimentary",0)

Notice that the expression covers only a "both days" option. I cannot
include the other options because of the limit of 12 variations.

My sense is that this would be more efficiently done in code -- I just don't
know how to do that.

I'm hoping for better help and would be very grateful.
--
nhb -- nc


:

Klatuu wrote:
Nancy,

Never heard of a switch statement. Is this something newer than A2K?
I
have not worked in anything new. If you can illucidate, I would
appreciate :)

It's in the Access 97 Help file.
Click to expand...
Click to expand...
Click to expand...
 
Thanks Klatuu. I was out of town yesterday and today so I'm just getting to
your response. I will try putting the code into a module. What I'm hoping
is for it to work on a form or query or report if possible. I asked in the
form discussion area because I have created a form for users to enter the
data. After they make the selections of the different criteria in the fields
for registration status, deadline, days, etc., the correct fee pops into the
registration fee field. How will the form "know" to use the module?
--
nhb -- nc


Klatuu said:
Nancy,

If you use the function I sent, it should be put in a VBA module or if it
will only be used by the one form, you can put it at the top of your form
module just after the declarations. If you choose to put it in the default
value of the text box, it should accept the function. The problem there is,
the data to do the calculation may not be available at the time. Without
knowing the form, it is hard to say exactly where the best place to put it
would be. Can you identify a place where all the other data is available?
you could pick an event that would fire at that point in time.

Nancy said:
Thanks to both of you. I do understand the code and can expand it in each
case to include all the options. I still have a problem though because I do
not know how to make the procedure work. Where do I insert the function --
per the form properties or the field or what? When I was using the "switch"
statement I was inserting in the control source for the field to be filled
with the result. Control source used the "switch" expression but does not
accept code. I know this is lame and stupid but I am not a programmer. I am
very grateful for your attention and help. And, I did not mean to offend --
I apologize. That was my frustration. Now I need to know how to call the
procedure at the right place.
--
nhb -- nc


Klatuu said:
Good luck, Nancy. I'm sure either approach will get you where you need to
go. I would interested to know which way you go with this.

:

Thanks to both Rick and Klatuu. I will try both options and get back to you.
--
nhb -- nc


:

Nancy,

I don't know where you got any negative attitude. I only asked what a
Switch statement is and later said I didn't think much of it. Still don't.
In any case, I have looked at a lot of possibilties. In a perfect world, it
would be best to have a table with the fees structure in it so you don't have
to change your code every time you have a different event. If you consider
that negative, then live with it. Anyway, after about 4 hours of playing
with options, here is what I think will work for you. Since there are more
options than "Both Days", you will have to add some to the code, but I think
you can figure it out.

Function FindFee(strStat As String, strDeadline As String, strDays As
String) As Currency
'Compliments of Dave Hargis

Dim rstFees As ADODB.Recordset

If strStat = "Complimentary" Then
FindFee = 0
Exit Function
End If

'Create a recordset for the options
Set rstFees = New ADODB.Recordset
rstFees.Fields.Append "Stat", adVarChar, 10
rstFees.Fields.Append "Deadline", adVarChar, 2
rstFees.Fields.Append "Days", adVarChar, 20
rstFees.Fields.Append "Fee", adCurrency
'Open the recordset
rstFees.Open
'Load the values in the recordset
'Add Additional lines here for additional options
'Use the first line of the add new as is - it is the field names
'The second line is the values for each field

rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Member", "EB", "Both Days", 50)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Non Member", "EB", "Both Days", 95)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Student", "EB", "Both Days", 45)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Member", "PR", "Both Days", 75)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Non Member", "PR", "Both Days", 120)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Student", "PR", "Both Days", 60)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Member", "OS", "Both Days", 95)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Non Member", "OS", "Both Days", 140)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Student", "OS", "Both Days", 75)
rstFees.UpdateBatch

'Setting this filter will limit the recordset to 1 row with the correct fee
rstFees.Filter = "[stat] = '" & strStat & "' And [deadline] = '" _
& strDeadline & "' And [days] = '" & strDays & "'"

'Check for EOF to avoid an error in case we passed a bad value to the function
If rstFees.EOF Then
FindFee = -1 ' Return a value that says we could not find it
Else
rstFees.MoveFirst
FindFee = rstFees![fee]
End If

rstFees.Close
Set rstFees = Nothing
End Function


:

Sounds like I'm not getting any help -- just negative reactions. It does
work and I found it exploring a database someone else designed using Access
2000. It's helpful because it permits users to identify the variables and
then produces the correct registration fee.

I will provide more information and hope for a better answer.

My database is for conference registrations. Registration fees vary on
several factors -- membership status, time of registration (early bird, pre
registration, on site), registration selections (1 day, 2 days, special
sessions), etc. An example of an expression using the "switch" function is:

=Switch([RegStat]="Member" And [RegDeadline]="EB" And [RegDays]="Both
Days",50,[RegStat]="Non Member" And [RegDeadline]="EB" And [RegDays]="Both
Days",95,[RegStat]="Student" And [RegDeadline]="EB" And [RegDays]="Both
Days",45,[RegStat]="Member" And [RegDeadline]="PR" And [RegDays]="Both
Days",75,[RegStat]="Non Member" And [RegDeadline]="PR" And [RegDays]="Both
Days",120,[RegStat]="Student" And [RegDeadline]="PR" And [RegDays]="Both
Days",60,[RegStat]="Member" And [RegDeadline]="OS" And [RegDays]="Both
Days",95,[RegStat]="Non Member" And [RegDeadline]="OS" And [RegDays]="Both
Days",140,[RegStat]="Student" And [RegDeadline]="OS" And [RegDays]="Both
Days",75,[RegStat]="Complimentary",0)

Notice that the expression covers only a "both days" option. I cannot
include the other options because of the limit of 12 variations.

My sense is that this would be more efficiently done in code -- I just don't
know how to do that.

I'm hoping for better help and would be very grateful.
--
nhb -- nc


:

Klatuu wrote:
Nancy,

Never heard of a switch statement. Is this something newer than A2K?
I
have not worked in anything new. If you can illucidate, I would
appreciate :)

It's in the Access 97 Help file.
Click to expand...
Click to expand...
Click to expand...
 
Well -- I have tried and tried but I simply do not understand how to call the
procedure in the form. I know you are probably tired of trying to help me so
I will forgive if you want to tell me to go away. On the other hand, I am
convinced that I could make a giant leap if I could just do this. I have
created a very small database sample that I would be happy to send to anyone
who would be willing to look at it and show me how to insert the code so that
the procedure is called properly.
--
nhb -- nc


Nancy said:
Thanks Klatuu. I was out of town yesterday and today so I'm just getting to
your response. I will try putting the code into a module. What I'm hoping
is for it to work on a form or query or report if possible. I asked in the
form discussion area because I have created a form for users to enter the
data. After they make the selections of the different criteria in the fields
for registration status, deadline, days, etc., the correct fee pops into the
registration fee field. How will the form "know" to use the module?
--
nhb -- nc


Klatuu said:
Nancy,

If you use the function I sent, it should be put in a VBA module or if it
will only be used by the one form, you can put it at the top of your form
module just after the declarations. If you choose to put it in the default
value of the text box, it should accept the function. The problem there is,
the data to do the calculation may not be available at the time. Without
knowing the form, it is hard to say exactly where the best place to put it
would be. Can you identify a place where all the other data is available?
you could pick an event that would fire at that point in time.

Nancy said:
Thanks to both of you. I do understand the code and can expand it in each
case to include all the options. I still have a problem though because I do
not know how to make the procedure work. Where do I insert the function --
per the form properties or the field or what? When I was using the "switch"
statement I was inserting in the control source for the field to be filled
with the result. Control source used the "switch" expression but does not
accept code. I know this is lame and stupid but I am not a programmer. I am
very grateful for your attention and help. And, I did not mean to offend --
I apologize. That was my frustration. Now I need to know how to call the
procedure at the right place.
--
nhb -- nc


:

Good luck, Nancy. I'm sure either approach will get you where you need to
go. I would interested to know which way you go with this.

:

Thanks to both Rick and Klatuu. I will try both options and get back to you.
--
nhb -- nc


:

Nancy,

I don't know where you got any negative attitude. I only asked what a
Switch statement is and later said I didn't think much of it. Still don't.
In any case, I have looked at a lot of possibilties. In a perfect world, it
would be best to have a table with the fees structure in it so you don't have
to change your code every time you have a different event. If you consider
that negative, then live with it. Anyway, after about 4 hours of playing
with options, here is what I think will work for you. Since there are more
options than "Both Days", you will have to add some to the code, but I think
you can figure it out.

Function FindFee(strStat As String, strDeadline As String, strDays As
String) As Currency
'Compliments of Dave Hargis

Dim rstFees As ADODB.Recordset

If strStat = "Complimentary" Then
FindFee = 0
Exit Function
End If

'Create a recordset for the options
Set rstFees = New ADODB.Recordset
rstFees.Fields.Append "Stat", adVarChar, 10
rstFees.Fields.Append "Deadline", adVarChar, 2
rstFees.Fields.Append "Days", adVarChar, 20
rstFees.Fields.Append "Fee", adCurrency
'Open the recordset
rstFees.Open
'Load the values in the recordset
'Add Additional lines here for additional options
'Use the first line of the add new as is - it is the field names
'The second line is the values for each field

rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Member", "EB", "Both Days", 50)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Non Member", "EB", "Both Days", 95)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Student", "EB", "Both Days", 45)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Member", "PR", "Both Days", 75)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Non Member", "PR", "Both Days", 120)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Student", "PR", "Both Days", 60)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Member", "OS", "Both Days", 95)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Non Member", "OS", "Both Days", 140)
rstFees.AddNew Array("Stat", "Deadline", "Days", "Fee"), _
Array("Student", "OS", "Both Days", 75)
rstFees.UpdateBatch

'Setting this filter will limit the recordset to 1 row with the correct fee
rstFees.Filter = "[stat] = '" & strStat & "' And [deadline] = '" _
& strDeadline & "' And [days] = '" & strDays & "'"

'Check for EOF to avoid an error in case we passed a bad value to the function
If rstFees.EOF Then
FindFee = -1 ' Return a value that says we could not find it
Else
rstFees.MoveFirst
FindFee = rstFees![fee]
End If

rstFees.Close
Set rstFees = Nothing
End Function


:

Sounds like I'm not getting any help -- just negative reactions. It does
work and I found it exploring a database someone else designed using Access
2000. It's helpful because it permits users to identify the variables and
then produces the correct registration fee.

I will provide more information and hope for a better answer.

My database is for conference registrations. Registration fees vary on
several factors -- membership status, time of registration (early bird, pre
registration, on site), registration selections (1 day, 2 days, special
sessions), etc. An example of an expression using the "switch" function is:

=Switch([RegStat]="Member" And [RegDeadline]="EB" And [RegDays]="Both
Days",50,[RegStat]="Non Member" And [RegDeadline]="EB" And [RegDays]="Both
Days",95,[RegStat]="Student" And [RegDeadline]="EB" And [RegDays]="Both
Days",45,[RegStat]="Member" And [RegDeadline]="PR" And [RegDays]="Both
Days",75,[RegStat]="Non Member" And [RegDeadline]="PR" And [RegDays]="Both
Days",120,[RegStat]="Student" And [RegDeadline]="PR" And [RegDays]="Both
Days",60,[RegStat]="Member" And [RegDeadline]="OS" And [RegDays]="Both
Days",95,[RegStat]="Non Member" And [RegDeadline]="OS" And [RegDays]="Both
Days",140,[RegStat]="Student" And [RegDeadline]="OS" And [RegDays]="Both
Days",75,[RegStat]="Complimentary",0)

Notice that the expression covers only a "both days" option. I cannot
include the other options because of the limit of 12 variations.

My sense is that this would be more efficiently done in code -- I just don't
know how to do that.

I'm hoping for better help and would be very grateful.
--
nhb -- nc


:

Klatuu wrote:
Nancy,

Never heard of a switch statement. Is this something newer than A2K?
I
have not worked in anything new. If you can illucidate, I would
appreciate :)

It's in the Access 97 Help file.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top