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

SQL Parameter Variable in IIF Statement

P: 22
I have a Make-Table Query running multiple IIF statements to evaluate a linked table for various criteria, and I would like to solicit user input to determine which table to reference at one point in the process.

I would like the following bit of code, where it says "Slots" to pop up a form asking for user input to determine which Slot table to use (FallWinterSlots or SpringSlots):
Expand|Select|Wrap|Line Numbers
  1. IIf([Start] In (select SlotStart from Slots)...
Right now I'm just writing the query twice, and changing the Slots table manually to match the report being analyzed:

Expand|Select|Wrap|Line Numbers
  1. SELECT CVReport.Title, CVReport.Course, CVReport.Sec, CVReport.Location, CVReport.Room, CVReport.Day, CVReport.Start, CVReport.End, CVReport.[SECT Start] AS Start1, CVReport.[SECT End] AS End1, CVReport.Credits,  CVReport.Contact,  CVReport.Weeks,
  3. IIf([Start1] In (select StandardDate from Dates),"","Check") AS StartDateCheck, IIf([End1] In (select StandardDate from Dates),"","Check") AS EndDateCheck, 
  5. IIf([Sec] In (select Section from RoomlessSections),"",IIf(Right([Course],1)="L","",IIf([Location] In (select OffcLoc from Roomless),"",IIf([Start] In (select SlotStart from Slots),"","Check")))) AS SlotStartCheck, 
  7. IIf([Sec] In (select Section from RoomlessSections),"",IIf(Right([Course],1)="L","",IIf([Location] In (select OffcLoc from Roomless),"",IIf([End] In (select SlotEnd from Slots),"","Check")))) AS SlotEndCheck, 
  9. IIf([Location]="MAIN",IIf([Room] Is Null,"Loc. MAIN No Room",""),"") AS LocationCheck, 
  11. IIf([StartDateCheck]="",IIf([EndDateCheck]="",IIf([SlotStartCheck]="",IIf([SlotEndCheck]="",IIf([LocationCheck]="","PASSED",""),""),""),""),"") AS CheckAll INTO [Test]
  13. FROM CVReport
  15. WHERE (((CVReport.Status)<>"C"));
I've tried placing [Forms]![SelectTerm]![Term] in where I have Slots, but it doesn't accept my syntax. I'm also unclear on how to make a form return specific values like "FallWinterSlots" or "SpringSlots."

What is the syntax for soliciting form input, and how would I link it in to my SQL?

Thank you,

Dec 12 '11 #1
Share this Question
Share on Google+
3 Replies

P: 579
I didn't see a field called "Slots" in the initial SELECT "Slots" an existing field in the CVReport table?

Assuming it is and I just missed it, you should be able to add code to capture the slot to your existing WHERE clause.

Changing the existing WHERE clause:
Expand|Select|Wrap|Line Numbers
  1. WHERE (((CVReport.Status)<>"C"));
To the following:
Expand|Select|Wrap|Line Numbers
  1. WHERE (((CVReport.Status)<>"C") AND ((CVReport.Slots)=[Forms]![SelectTerm]![Term]));
Then, just make sure that you've got a form that has either a textbox or a combobox with the field name "Term" and it should work. The query/SQL syntax should reference that field on the form when it gets to that line of code.

Hope this helps,
Dec 12 '11 #2

P: 22
Slots is a Table (columns: SlotStart and SlotEnd). Well, actually, two tables... Right now, I'm renaming two different tabled (FallWinterSlots and SpringSlots) as "Slots", depending on which one I want to use. Up to this point I haven't had to select the table in the main Select statement, as it appeared nested within the IIF statement as:
Expand|Select|Wrap|Line Numbers
  1. IIf([Start] In (select SlotStart from Slots)
My problem is that I'm trying to select from a specific table depending on which variable is returned, so I want something like:
Expand|Select|Wrap|Line Numbers
  1. IIF([Start] In (select SlotStart from IIF([Forms]![SelectTerm]![Term] = "Fall/Winter","FallWinterSlots",IIF([Forms]![SelectTerm]![Term] = "Spring","Spring","ERROR"))),"","Check Slot Start Time") AS SlotStartCheck,
So, I want "Slots" to be changed to "Go get the right table name from a form," where the table name is either "FallWinterSlots" or "SpringSlots," and I'm looking for "SlotStart" (and later, SlotEnd) anywhere in the table.
Dec 12 '11 #3

Expert Mod 10K+
P: 12,366
First off, why are they in separate tables? Unless they each contain very different fields, they should be in the same table with an extra field indicating the season. This would normalize the data and make querying less difficult.

Second, you can achieve what you want with a union subquery.

Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM (
  3.    SELECT 'fall' AS season, *
  4.    FROM table1
  6.    UNION
  8.    SELECT 'spring' AS season, *
  9.    FROM table2
  10. ) AS unionTable
  11. WHERE season = Forms!formName!controlName
I leave the specific implementation up to you.
Dec 12 '11 #4

Post your reply

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