By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,696 Members | 1,383 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,696 IT Pros & Developers. It's quick & easy.

If Then Statement Help

P: 12
What I am looking to do is create an if then statement for a particular access query, here are the facts

Tables used
Mainframe Report List

Fields Within the table that are used for the if then statement
GENERATIONS
FREQUENCY
EXTENDED

what i would like is to say if FREQUENCY = Daily5 then Divide value in GENERATIONS by 5 and Place the Result in EXTENDED ELSE IF FREQUENCY = WEEKLY then Divide value in GENERATIONS by 1 etc etc for the following values

Daily5
Daily7
Weekly
Monthly
Quarterly
Annualy
On Demand


ELSE IF NULL then EXTENDED = NULL
Any help on this would be greatly appreciated as this has a deadline of tomorrow morning :( Thanks
Jun 12 '07 #1
Share this Question
Share on Google+
1 Reply

ADezii
Expert 5K+
P: 8,750
What I am looking to do is create an if then statement for a particular access query, here are the facts

Tables used
Mainframe Report List

Fields Within the table that are used for the if then statement
GENERATIONS
FREQUENCY
EXTENDED

what i would like is to say if FREQUENCY = Daily5 then Divide value in GENERATIONS by 5 and Place the Result in EXTENDED ELSE IF FREQUENCY = WEEKLY then Divide value in GENERATIONS by 1 etc etc for the following values

Daily5
Daily7
Weekly
Monthly
Quarterly
Annualy
On Demand


ELSE IF NULL then EXTENDED = NULL
Any help on this would be greatly appreciated as this has a deadline of tomorrow morning :( Thanks
In my opinion, this can only be done in code and I have posted the partial solution and logic below:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database, MyRS As DAO.Recordset
  2.  
  3. Set MyDB = CurrentDb()
  4. Set MyRS = MyDB.OpenRecordset("Mainframe Report List", dbOpenDynaset)
  5.  
  6. MyRS.MoveFirst
  7.  
  8. Do While Not MyRS.EOF
  9.   MyRS.Edit
  10.     Select Case MyRS![FREQUENCY]
  11.       Case "Daily5"
  12.         MyRS![EXTENDED] = MyRS![GENERATIONS] / 5
  13.       Case "Weekly"
  14.         'Here is where I get confused...
  15.       Case Else
  16.         MyRS![EXTENDED] = Null
  17.     End Select
  18.   MyRS.Update
  19.     MyRS.MoveNext
  20. Loop
  21.  
  22. MyRS.Close
This is where I totally got confused. You'll have to fill in the missing code yourself.
IF FREQUENCY = WEEKLY then Divide value in GENERATIONS by 1 etc etc for the following values

Daily5
Daily7
Weekly
Monthly
Quarterly
Annualy
On Demand
Jun 14 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.