2 parameters query: trouble when specifying query criteria

[limperger]

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.

[Delerna]

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

[limperger]

The SQL statements:
[CODE=SQL]SELECT REGISTRO.SURNAM E, REGISTRO.NAME, REGISTRO.MONTH, REGISTRO.YEAR
FROM REGISTRO
WHERE (((REGISTRO.MON TH)=[Please provide month]) AND ((REGISTRO.YEAR )=[Please provide year])) OR (((REGISTRO.YEA R)=[Please provide year]))
ORDER BY REGISTRO.SURNAM E;[/CODE]
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!

[Delerna]

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
[code=sql]
SELECT REGISTRO.SURNAM E,
REGISTRO.NAME,
REGISTRO.MONTH,
REGISTRO.YEAR
FROM REGISTRO
WHERE REGISTRO.MONTH=
([Please provide month] AND REGISTRO.YEAR=[Please provide year])
OR



(REGISTRO.YEAR=[Please provide year] AND [Please provide month] is null)




ORDER BY REGISTRO.SURNAM E;

[/code]

[NeoPa]

@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.

[NeoPa]

My version to try :
[CODE=SQL]SELECT [Surname],
[Name],
[Month],
[Year]
FROM [Registro]
WHERE ((([Please provide month] Is Null) OR ([Month]=[Please provide month]))
AND (([Please provide Year] Is Null) OR ([Year]=[Please provide year])))
ORDER BY [Surname][/CODE]

[Delerna]

@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.

[limperger]

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

[NeoPa]

No worries.

As I said in my post it was clear you were trying to do it as helpfully as possible :)