473,326 Members | 2,148 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Can IF or CASE statements be used to alter the criteria of a query?

6
I am developing quite a large database in MS Access 2003.
I need to allow the information to be filtered and displayed in a subform.

I have several combo boxes that filter this query. These compare the combo box variable with the corresponding fields in another table. These work fine.

The next step that I would like to do is allow the user to choose to show records if a certain field is blank (DatePaymentReceived). This is because the user will want to filter the data as normal but may also choose to see the records of any outstanding payments.

I can get the query to filter using the SQL below:

Expand|Select|Wrap|Line Numbers
  1. WHERE ((CandidateInformation.Trust Like ([Forms]![MainForm]![cmbTrust])) 
  2. AND 
  3. (Qualification.DueProgrammeStartDate Between ([Forms]![MainForm]![cmbStartDate]) AND ([Forms]![MainForm]![cmbEndDate])) 
  4. AND
  5.  (Qualification.CompletionStatusID Like ([Forms]![MainForm]![cmbStatus]))
  6. AND
  7. Qualification.DatePaymentReceived IS NULL)

Where the "Qualification.DatePaymentReceived IS NULL" I would like to only filter this if another combo box is set to "yes"
Is this possible? I have tried inserting an IF statement to the query to check IF (cmbOverdue = "yes") THEN ...perform payment filter

Are there any suggestions on how/if this can be done?

Your advice would be greatly appreciated, thanks
Sep 2 '10 #1

✓ answered by TheSmileyCoder

I believe this should work. You can find information on the IIF function in the help.
Expand|Select|Wrap|Line Numbers
  1. ...
  2. AND 
  3. IIF(Forms!MainForm!cmbOverdue ,Qualification.DatePaymentReceived IS NULL,nz(Qualification.DatePaymentReceived,0) =*)) 

4 1593
TheSmileyCoder
2,322 Expert Mod 2GB
I believe this should work. You can find information on the IIF function in the help.
Expand|Select|Wrap|Line Numbers
  1. ...
  2. AND 
  3. IIF(Forms!MainForm!cmbOverdue ,Qualification.DatePaymentReceived IS NULL,nz(Qualification.DatePaymentReceived,0) =*)) 
Sep 2 '10 #2
Nick H
6
Thank you. That looks like what I need!

But should the expression part be...

Expand|Select|Wrap|Line Numbers
  1. IIF(Forms!MainForm!cmbOverdue = "Yes",
...so it can calculate true/false?

I am also getting a syntax error (missing operator) in query with the "*" at the end.

Thank you for your help
Sep 2 '10 #3
Nick H
6
It's ok, I've sorted it!

It's working great, thank you very much for your help, much appreciated!
Sep 2 '10 #4
Oralloy
985 Expert 512MB
Efficency wise, you might want to lose the IF.

You can use a clause of the form:
Expand|Select|Wrap|Line Numbers
  1. ((Forms!MainForm!cmbOverdue <> "Yes") OR (guarded condition))
The trick is to use "OR".

Better still is to simply guard whether you add the condition to your SQL statement. It all depends on what you're doing.

Cheers!
Sep 2 '10 #5

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

Similar topics

2
by: Heist | last post by:
Hi, I just want to know to turn this: CREATE TABLE . ( NOT NULL , (50) COLLATE French_CI_AS NULL , NOT NULL , (50) COLLATE French_CI_AS NOT NULL , NULL , NULL ) ON into this:
1
by: db2sysc | last post by:
All. We have LOT of variables declared in the Oracle package as ORACLE CONSTANTS like, v_test CONSTANT INTEGER=1; When converting to DB2, MTK changes each of these CONSTANTs into...
3
by: Fanofmsu | last post by:
Hi - from my limited expereince in DB2, it appears that you can't include a case statement in a group by clause - is this correct? For example, my SQL looked like this: SELECT CASE WHEN...
10
by: Lyle Fairfield | last post by:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaac11/html/acfctNZ_HV05186465.asp "If the value of the variant argument is Null, the Nz function returns the number zero or a...
5
by: alacrite | last post by:
If I have code like this: int main() { a=1; b=2; x=0; switch(a) {
6
by: Fir5tSight | last post by:
Hi All, I have a "SELECT" statement in a stored procedure that looks like the follows: ...
6
by: kronecker | last post by:
Is there any way to make case statements case independent? ie Case Is = "dim the light" or Case Is = "Dim the light" making them the same outcome without putting every possibility. eg ...
1
by: bharathi228 | last post by:
How to write two or more select statements in a single query here my requirement is SELECT dbo.SYS_PARAMS.PARAMETER_NAME, dbo.SYS_PARAMS.PARAMETER_UNITS, SYSTEM2.dbo.CALIBRATION.CAL_TYPE, ...
1
by: weefrenchie | last post by:
Hi, I have a SQL query that looks like this: , CASE WHEN object 1 LIKE '%first%' OR object 1 LIKE '%second%' OR object 2 LIKE '%first%' OR object 2 LIKE '%second%' THEN object 3...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.