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

2 parameters query: trouble when specifying query criteria

P: 66
Hello everyone!

First and foremost, my apologies for the title of the post. It is not very clarifying of what the problem is about, but I didn't know how to put it...
My problem is as follows: I have a parameter query consisting of 2 fields (month and year). The parameter prompts the user to enter the month and the year, that is, [Please enter the month/year]. When both are entered, there is no problem. Nevertheless, users may want only to enter the year, so as to display all records for that year. Then, I have specified the query criteria as follows:

Fields: Month, Year
Display: both displayed
Criteria 1: [Please enter month] and [Please enter year] :::> Month and yr provided
Criteria 2: [Please enter year] :::> Only yr provided.

But in the first case (entering month and year), those records matching with the year but not with the month will also be displayed.
Is there a way to block the query in a way that if month and year are provided, only those records with the matching month and year will be displayed and not those only matching the year? Is code needed?
I feel I am doing something wrong here, with Access itself (without using any code...).

Best regards.
Apr 30 '08 #1
Share this Question
Share on Google+
8 Replies


Delerna
Expert 100+
P: 1,134
can you post your query, it might help clarify the result you are after
I thought I understood during the first half but then I got confused in the second half of the question
Apr 30 '08 #2

P: 66
The SQL statements:
Expand|Select|Wrap|Line Numbers
  1. SELECT REGISTRO.SURNAME, REGISTRO.NAME, REGISTRO.MONTH, REGISTRO.YEAR
  2. FROM REGISTRO
  3. WHERE (((REGISTRO.MONTH)=[Please provide month]) AND ((REGISTRO.YEAR)=[Please provide year])) OR (((REGISTRO.YEAR)=[Please provide year]))
  4. ORDER BY REGISTRO.SURNAME;
The problem is that if an user provides the month and year the records matching with the month and year provided will display but so will do the records in which only the year matches with the data provided.
My question is whether it is possible to block the query, in a way that when an user provides the month and year, the query will only display those records in which the month AND the year match, and not ALL the records in which the year match. Obviously, I could make 2 different queries but I would like to know if what I outline here is possible (without using code??).

I hope this time I will be understandable. In any case, it is my fault. I tend to explain myself badly... Sorry!

Best regards!
Apr 30 '08 #3

Delerna
Expert 100+
P: 1,134
Got it now. It wasn't so badly explained at all. The Query helped as I could compare it with your question to understand your meaning

try this, you just need to check that the entered month is null in the second scenario
see below
Expand|Select|Wrap|Line Numbers
  1. SELECT REGISTRO.SURNAME, 
  2.      REGISTRO.NAME, 
  3.      REGISTRO.MONTH, 
  4.      REGISTRO.YEAR
  5. FROM REGISTRO
  6. WHERE REGISTRO.MONTH=
  7. ([Please provide month] AND REGISTRO.YEAR=[Please provide year])
  8. OR 
  9.  
  10.  
  11.  
  12. (REGISTRO.YEAR=[Please provide year] AND [Please provide month] is null)
  13.  
  14.  
  15.  
  16.  
  17. ORDER BY REGISTRO.SURNAME;
  18.  
  19.  
Apr 30 '08 #4

NeoPa
Expert Mod 15k+
P: 31,186
@Delerna - Nice work. I particularly like how you're laying out your SQL in a visible way. Very easy to follow :)

@Limperger - I won't criticise too much as it's clear you're making every effort to post sensibly, helpfully and within the rules. I will just make the point though, to remind you to use [ CODE ] tags whenever you post code. It's much easier to read when posted that way.
May 1 '08 #5

NeoPa
Expert Mod 15k+
P: 31,186
My version to try :
Expand|Select|Wrap|Line Numbers
  1. SELECT [Surname],
  2.        [Name],
  3.        [Month],
  4.        [Year]
  5. FROM [Registro]
  6. WHERE ((([Please provide month] Is Null) OR ([Month]=[Please provide month]))
  7.   AND  (([Please provide Year] Is Null) OR ([Year]=[Please provide year])))
  8. ORDER BY [Surname]
May 1 '08 #6

Delerna
Expert 100+
P: 1,134
@Delerna - Nice work. I particularly like how you're laying out your SQL in a visible way. Very easy to follow :)
Thanks NeoPa. I do this out of habit for myself. Bugs and logic faults are difficult enough to find as it is without hiding them in messy code.
Its nice to know that at least one other person finds it easy to read.
That means when I retire my replacement will hopefully be able to follow my code and thats a bonus.
May 2 '08 #7

P: 66
Hello everyone!

I have just tried Delerna SQL's code and it works fine. Thank you very much!!
NeoPa: It is the first time I post SQL code and didn't know the rules. My fault entirely! Please accept my apologies for any inconveniences caused.

Best regards
May 2 '08 #8

NeoPa
Expert Mod 15k+
P: 31,186
No worries.

As I said in my post it was clear you were trying to do it as helpfully as possible :)
May 2 '08 #9

Post your reply

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