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

query with an if/then?

AccessIdiot
100+
P: 493
Hi,

I have a table with a number field. I have on my form two radio buttons labeled "Actual" and "Approximate". If "Actual" is chosen "1" goes into the database and if "Approximate" is chosen "2" goes in.

Now I am writing a query but I don't want the resulting table to display "1" or "2", I want it to display "Actual" and "Approximate".

I'm guessing I need to write an if/then statement to display the appropriate text but I'm not sure where to do this?

Or maybe there is another way?

Thanks for any help!
Jul 26 '07 #1
Share this Question
Share on Google+
6 Replies


Expert 100+
P: 296
Hi,

I have a table with a number field. I have on my form two radio buttons labeled "Actual" and "Approximate". If "Actual" is chosen "1" goes into the database and if "Approximate" is chosen "2" goes in.

Now I am writing a query but I don't want the resulting table to display "1" or "2", I want it to display "Actual" and "Approximate".

I'm guessing I need to write an if/then statement to display the appropriate text but I'm not sure where to do this?

Or maybe there is another way?

Thanks for any help!
In your Select clause you can write
Expand|Select|Wrap|Line Numbers
  1. IIF([RadioFieldName]=1,"Actual","Approximate") AS AliasName
Jul 26 '07 #2

AccessIdiot
100+
P: 493
Hi can you tell me where in the Select statement to put it? I'm writing queries in design view but when I look at it in sql view it looks like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT tbl_Specimen_Replicate.Species_ID, Sum(tbl_Specimen_Replicate.SpecimenCount) AS SumOfSpecimenCount, tbl_Specimen_Replicate.ActualApproximate, tbl_Species_Freshwater.Fish, tbl_Species_Freshwater.Native
  2. FROM tbl_Species_Freshwater INNER JOIN (tbl_Replicate INNER JOIN tbl_Specimen_Replicate ON tbl_Replicate.Replicate_ID = tbl_Specimen_Replicate.Replicate_ID) ON tbl_Species_Freshwater.Species_ID = tbl_Specimen_Replicate.Species_ID
  3. GROUP BY tbl_Specimen_Replicate.Species_ID, tbl_Specimen_Replicate.ActualApproximate, tbl_Species_Freshwater.Fish, tbl_Species_Freshwater.Native;
  4.  
Can you tell me where in that mess to put your suggestion?

Thanks!
Jul 26 '07 #3

Expert 100+
P: 296
Hi can you tell me where in the Select statement to put it? I'm writing queries in design view but when I look at it in sql view it looks like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT tbl_Specimen_Replicate.Species_ID, Sum(tbl_Specimen_Replicate.SpecimenCount) AS SumOfSpecimenCount, tbl_Specimen_Replicate.ActualApproximate, tbl_Species_Freshwater.Fish, tbl_Species_Freshwater.Native
  2. FROM tbl_Species_Freshwater INNER JOIN (tbl_Replicate INNER JOIN tbl_Specimen_Replicate ON tbl_Replicate.Replicate_ID = tbl_Specimen_Replicate.Replicate_ID) ON tbl_Species_Freshwater.Species_ID = tbl_Specimen_Replicate.Species_ID
  3. GROUP BY tbl_Specimen_Replicate.Species_ID, tbl_Specimen_Replicate.ActualApproximate, tbl_Species_Freshwater.Fish, tbl_Species_Freshwater.Native;
  4.  
Can you tell me where in that mess to put your suggestion?

Thanks!
You can put it anywhere in the Select statement. Wherever you put it is the column it will show up in in datasheet view (i.e. - if you add it to the end of your select statement, it will be the last column, unless you switch it in datasheet view). Try this:
Expand|Select|Wrap|Line Numbers
  1. SELECT tbl_Specimen_Replicate.Species_ID, Sum(tbl_Specimen_Replicate.SpecimenCount) AS SumOfSpecimenCount, tbl_Specimen_Replicate.ActualApproximate, tbl_Species_Freshwater.Fish, tbl_Species_Freshwater.Native, IIF([tbl_Specimen_Replicate.ActualApproximate]=1,"Actual","Approximate") AS ActualApprox
  2. FROM tbl_Species_Freshwater INNER JOIN (tbl_Replicate INNER JOIN tbl_Specimen_Replicate ON tbl_Replicate.Replicate_ID = tbl_Specimen_Replicate.Replicate_ID) ON tbl_Species_Freshwater.Species_ID = tbl_Specimen_Replicate.Species_ID
  3. GROUP BY tbl_Specimen_Replicate.Species_ID, tbl_Specimen_Replicate.ActualApproximate, tbl_Species_Freshwater.Fish, tbl_Species_Freshwater.Native;
  4.  
I'm assuming tbl_Specimen_Replicate.ActualApproximate is the name of the field that contains the 1 or 2.
Jul 26 '07 #4

AccessIdiot
100+
P: 493
That's freakin awesome. Thank you! I ended up with a query like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT tbl_Specimen_Replicate.Species_ID, Sum(tbl_Specimen_Replicate.SpecimenCount) AS SumOfSpecimenCount, IIF([tbl_Specimen_Replicate.ActualApproximate]=1,"Actual","Approximate") AS ActualApprox, tbl_Species_Freshwater.Fish, tbl_Species_Freshwater.Native
  2. FROM tbl_Species_Freshwater INNER JOIN (tbl_Replicate INNER JOIN tbl_Specimen_Replicate ON tbl_Replicate.Replicate_ID=tbl_Specimen_Replicate.Replicate_ID) ON tbl_Species_Freshwater.Species_ID=tbl_Specimen_Replicate.Species_ID
  3. GROUP BY tbl_Specimen_Replicate.Species_ID, tbl_Specimen_Replicate.ActualApproximate, tbl_Species_Freshwater.Fish, tbl_Species_Freshwater.Native;
  4.  
and it works beautifully. :)
Jul 26 '07 #5

Expert 100+
P: 296
I should also add that the way I wrote that iif statement, if you have any values other than 1 or 2 in the field, or any nulls, this won't be entirely accurate.
If you have nulls, you would be better off to change this statement to
Expand|Select|Wrap|Line Numbers
  1. IIF([tbl_Specimen_Replicate.ActualApproximate]=1,"Actual", iif([tbl_Specimen_Replicate.ActualApproximate]=2,"Approximate","")) AS ActualApprox
In the above code, if the field equals something other than 1 or 2, it will display an empty string. Modify it to whatever suits your database.
Jul 26 '07 #6

Expert 100+
P: 296
That's freakin awesome. Thank you! I ended up with a query like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT tbl_Specimen_Replicate.Species_ID, Sum(tbl_Specimen_Replicate.SpecimenCount) AS SumOfSpecimenCount, IIF([tbl_Specimen_Replicate.ActualApproximate]=1,"Actual","Approximate") AS ActualApprox, tbl_Species_Freshwater.Fish, tbl_Species_Freshwater.Native
  2. FROM tbl_Species_Freshwater INNER JOIN (tbl_Replicate INNER JOIN tbl_Specimen_Replicate ON tbl_Replicate.Replicate_ID=tbl_Specimen_Replicate.Replicate_ID) ON tbl_Species_Freshwater.Species_ID=tbl_Specimen_Replicate.Species_ID
  3. GROUP BY tbl_Specimen_Replicate.Species_ID, tbl_Specimen_Replicate.ActualApproximate, tbl_Species_Freshwater.Fish, tbl_Species_Freshwater.Native;
  4.  
and it works beautifully. :)
Glad I could help! :)
Jul 26 '07 #7

Post your reply

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