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

double if condition in MS access query

P: 21
hey guys,
I want to do some mathwork in a MS access query. But i tried a couple of syntax to get this to work but i can quite get my condition translated for a MS access querry Can someone tell me how i get get two IF conditions into the query.
My psudo-code is as follows
Expand|Select|Wrap|Line Numbers
  1. iF (Priority='AOG')
  2.   if (Service = 'D2D')
  3.     timeA = time2-time1
  4.   else 
  5.     timeA = time3-time1
  6. else
  7.   if (Service = 'D2D')
  8.     timeA = time2-time4
  9.   else 
  10.     timeA = time5-time4
  11.  
Jul 19 '10 #1

✓ answered by NeoPa

Assuming you have all the fields specified available, then you would need to use the IIf() function. In this case it would have to be stacked :
Expand|Select|Wrap|Line Numbers
  1. timeA: IIf([Priority]='AOG',
  2.            IIf([Service]='D2D',[time2]-[time1],[time3]-[time1]),
  3.            IIf([Service]='D2D',[time2]-[time4],[time5]-[time4]))
Welcome to Bytes!

PS. Linq brings up an important point about dealing with dates & times. I cannot guarantee your maths will work. I only duplicate what you already had to illustrate the structure of the instruction.

Share this Question
Share on Google+
3 Replies


missinglinq
Expert 2.5K+
P: 3,532
I think that first off we need to know what kind of Datatypes are we dealing with here. Are Time1, Time2, TimeA, etc. actual Date/Time fields, as their names imply, or something else, such as Numbers or Text? If Date/Time fields, do they contain Date and Time or just Time?

Or are these Lapsed Times, i.e. hours, minutes, seconds?

All these things make a difference in how you do 'math' with 'times.'

Welcome to Bytes!

Linq ;0)>
Jul 19 '10 #2

NeoPa
Expert Mod 15k+
P: 31,769
Assuming you have all the fields specified available, then you would need to use the IIf() function. In this case it would have to be stacked :
Expand|Select|Wrap|Line Numbers
  1. timeA: IIf([Priority]='AOG',
  2.            IIf([Service]='D2D',[time2]-[time1],[time3]-[time1]),
  3.            IIf([Service]='D2D',[time2]-[time4],[time5]-[time4]))
Welcome to Bytes!

PS. Linq brings up an important point about dealing with dates & times. I cannot guarantee your maths will work. I only duplicate what you already had to illustrate the structure of the instruction.
Jul 19 '10 #3

P: 21
Thanks guys!!!
It worked...the "times" were formatted as date/time... did some minor *24 etc manipulations to get the result int he desired output... the main problem i had was in the double IF conditions :)
Thanks again!!!
Jul 20 '10 #4

Post your reply

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