Query Based on 2 out of 4 criteria?

[romio]

I have table with filed with jobs that our IT’s have done for that day, the table is updated daily, based on my table I have created my query, I have set some criteria for some fields though, as follow:

Month = [Forms]![DailyWorkForm]![commonth]
Year = [Forms]![DailyWorkForm]![comyear]
ITName = [Forms]![DailyWorkForm]![comit]
Day = [Forms]![DailyWorkForm]![comday]

on my form I have commonth, comyear, comit, comday and a command button that will open a report based on the query which will be based on my 4 combo boxes on my form, ok I hope I didn’t make you all really confused, this is the question what if the user inputs on 2 combo only, meaning that if month = may and year = 2005 and left ITname and day empty, I still want the query to run, how can I do that?

Thanks a lot.

 

[JVermast]

Make the default for all 4 fields "*" and use "Like [Forms]![DailyWorkForm]![commonth]" etc for your criteria.

 

[romio]

Like [Forms]![DailyWorkForm]![comyear]

but I got an error:
"You tried to execute a query that does not include the specified expression "Year" as part of an aggregate function."

 

[ColinEssex]

Remember that Month, Year and Day are reserved words in Access and shouldn't be used for naming

Col

 

[romio]

I think I found where is the problem, on the same form I do also have 3 combo for day, month and year, but the comyear, commonth, comday and comit are unbound I only added them in order to create my query, the problem is that the query is trying to find the data not form comyear, commonth, comday and comit but from day, month and year, how can I fix that, basically I don’t need to save the field of comyear, commonth, comday and comit they only exists for the query, any ideas.

 

[JVermast]

How about a screenshot of your query and your tables.

 

[romio]

I have added 4 images that will expalin what am trying to do, my form, table and query.

Thanks.

 

[romio]

what i did is delete the query and create one from the scratch, this is the sql code:

SELECT Records.ID, Records.Name, Records.Description, Records.Department, Records.ProblemDesc, Records.SolutionDesc, Records.RTime, Records.AMPM, Records.ITName, Records.RDay, Records.RMonth, Records.RYear
FROM Records
WHERE (((Records.ITName)=[Forms]![DailyWorkForm]![comit]) AND ((Records.RDay)=[Forms]![DailyWorkForm]![comday]) AND ((Records.RMonth)=[Forms]![DailyWorkForm]![commonth]) AND ((Records.RYear)=[Forms]![DailyWorkForm]![comyear]));

for some reasons when i double click on the query then enter the data required then it works, but if i use the command button on my form to open that query based on the combo boxes then the query opens empty... why?

 

[romio]

I found where the problem comes from, when I deleted
[Forms]![DailyWorkForm]![comit] from my query it did work.

well
[Forms]![DailyWorkForm]![commonth]
and
[Forms]![DailyWorkForm]![comyear]
and
[Forms]![DailyWorkForm]![comit]
the row source type is value list
[Forms]![DailyWorkForm]![comday] its based on table/query. Not sure why this is happening, anyone knows why?