Full Version: Select or If Statement
UtterAccess Forums > Microsoft® Access > Access Forms
jmcwk
I have a Training Type Database that dependant on the type of Training there is a Frequency in regard to the Next Due Date of the Training.
blTraining
pkTrainingID (PK)
txtTrainingType
tblFrequency
pkFreqID (PK)
txtFrequency
tblTrainingDetail
pkDetailID (PK)
pkTrainingID(FK)
pkFreqID (FK)
dtTrainDt
txtFname
txtlname
Plan is to use a query and place a NDD field in the query and based on the pkFreqID of the Training place a date in the NDD field. Frequencys are Monthly,Yearly and BiAnnual
Thank You
argeedblu
Did you have a question?
Glenn
jmcwk
Sorry yes Would it be better to use a select case type statement function OR a number of If statements in the after update Event of the Training type
Thank You
argeedblu
John,
When there is a series of possible choices, select case is far easier to work with than nested if-then-elses. The code is also much easier to read and understand.
Glenn
jmcwk
Thank You Glenn maybe something like this?
Function NDD(FreqID)
If Nz(freqID, 0) = 0 Then Exit Function
Select Case FreqID
Case Is 1: NDD = DateAdd ('yyyy', 1, tradt)
Case Is 2: NDD =DateAdd ('m', 6, tradt)
Case Is 4: 3000 =DateAdd ('m', 3, tradt)
End Select
End Function
Jack Cowley
John -
I'm trying to see if Glenn is awake... What is 'tradt' and '3000' were do they come from? Note that the interval in DateAdd is a String (double quotes)...
Function NDD(FreqID)
If Nz(freqID, 0) = 0 Then Exit Function
Select Case FreqID
Case 1
NDD = DateAdd ("yyyy", 1, tradt )
Case 2
NDD =DateAdd ("m", 6, tradt )
Case 4
3000 =DateAdd ("m", 3, tradt )
End Select
End Function
OGlenn -o!
Jack
jmcwk
frown.gif Hello jack!

Would never say Glenn is asleep would not be good etiquette but you can say it

The 3000 is an error tradt is the initial date the training was held.

freqID is the frequency assigned to the Training 1=1Year,2=quarterly and 3 = Bi annually
Edited by: jmcwk on Fri May 5 18:01:07 EDT 2006.
Jack Cowley
John -
Glenn is a 'mature' man, much like myself, and a nap is not an unreasonable thing...
The quesition about 'tradt' is how does the function know what that variable is? If it is a global variable then your code should work.
Jack
jmcwk
As usual you are correct Variable not defined Tradt
Jack Cowley
John -
hew!
How about passing the variable along with the one you are already passing? That should do the trick...
Jack
jmcwk
Me Too Phew! Hope I am not wearing you out
Have this now but am getting dates like 12/30/1900 am putting in the Query Getthese:Ndd([freqid])
CODE
Function NDD(FreqID)
Dim tradt As Date
If Nz(FreqID, 0) = 0 Then Exit Function
Select Case FreqID
Case 1
NDD = DateAdd("m", 1, tradt)
Case 2
NDD = DateAdd("q", 3, tradt)
Case 3
NDD = DateAdd("yyyy", 3, tradt)
Case 4
NDD = DateAdd("m", 6, tradt)
End Select
End Function
Jack Cowley
John -
Try this:
Function NDD(FreqID As Integer, tradt As Date) As Date
...rest of code...
End Function
Getthese: NDD([freqid],[tradt])
Jack
jmcwk
I guess Function NDD(FreqID As Integer, tradt As Date) As Date is what you were trying to tell me when you said pass the variable??? and why the Function NDD(FreqID As Integer, tradt As Date) As Date
nyway It works fine
Thank You
Jack Cowley
John -
The variable I was talking about 'passing' to the function was the 'tradt' variable. The Getthese: NDD([freqid], [tradt]) is the code that passes the variable 'tradt' to the function. In the function I declared the Data Types with Integer, Date and As Date. The As Date means that the value coming back from the function is a Date and not a String. 1/2/2003 can be a string or a date so declaring it as a date means that it will be handled as a date in both how it is returned and how 'tradt' is treated in the function itself.
I hope that makes sense...
Jack
jmcwk
Perfectly clear Jack I appreciate the explanation learning as always.
Thank You As Always
Have A Good Evening!
Jack Cowley
John -
You are very welcome! By the way, have you heard from Rachel about the Animal db? Just curious is all....
You have a good evening and a good weekend. I hope you are not working all weekend....
Jack
jmcwk
Jack
Is there something I can place in the function that will clear up the #error when used in a form?
Thank You
Jack Cowley
John -
How are you calling the function from the form? Where are you getting the #Error error?
I am heading for the UA parking lot, but I will be around tomorrow...
Jack
jmcwk
I got it figured out however to answer your question I was using the same call =NDD([freqid],[tradt]) excluding the GetThese of course. Initially I was getting an error in the query because I had more than one freqid in the SQL and it did not know which I was referring to so after it was identified had no more problems.
Thank You Jack
argeedblu
I'm back. Did I miss anything? laugh.gif
I'm glad Jack was able to help you get on the right track John. Incidentally, as Jack surmised, I am prone to taking the odd nap but that wasn't why I was away this time.
In any case, have a great weekend.
Glenn
Jack Cowley
John -
h, the old 'two objects with the same name' problem! I'm glad you got it sorted out and all is now working as it should...
Jack
Jack Cowley
Glenn -
You did not miss much as you had gotten things going in the right direction early on. I just came in and give a slight ploish to the apple...
You have a great weekend too!!
Jack
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.