Producing a report based on a query with 2+ criteria

[Rach1978]

I'm quite new to Access, and have searched through my many manuals to try and solve this problem and have reached a brick wall.

Basically, I have made a form in Access 2000 which produces a report. Currently, the user selects and option in a Combo box, presses a button and the report opens (preview mode).

As I'm still new to Access, I can't work out to produce a report based on two criteria (e.g. a user wants to create a report based on Project Manager AND Client Name).

I'd be very grateful if anyone could help me.

Thanks so much,

Rachael

 

[hjgoldstein]

The easiest way is to create the report based on a query. You can set as many criteria in the query as you like.
The criteria in the query should be something like

Like "*" & [Enter MyCriteria] & *

to allow for matches from partial input.

Hope this helps.

 

[Rach1978]

Thanks so much for the reply.

I'm still not quite sure how and where I should enter this query. I always design my queries in design view (as I'm not up on SQL) but I've just copied my query from the SQL, so maybe this could give you an idea of where my problems lie:

SELECT tblMain.ProjectNumber, tblMain.ProjectTypeCode, tblMain.ProjectName, tblMain.ClientCode, tblMain.ClientCompanyContact, tblMain.SectorName, tblMain.ProjectManager, tblMain.TargetCompanies, tblMain.Keywords, tblMain.Notes, tblMain.EndDate
FROM tblMain
WHERE (((tblMain.ClientCode)=[forms]![frmTest]![ClientCode])) AND (((tblMain.ProjectManager)=[forms]![frmTest]![ProjectManager]));

I should mention that this query (with a single criterion) works fine:

SELECT tblMain.ProjectNumber, tblMain.ProjectTypeCode, tblMain.ProjectName, tblMain.ClientCode, tblMain.ClientCompanyContact, tblMain.SectorName, tblMain.ProjectManager, tblMain.TargetCompanies, tblMain.Keywords, tblMain.Notes, tblMain.EndDate
FROM tblMain
WHERE (((tblMain.ClientCode)=[forms]![frmClientName]![ClientCode]));

Many, many thanks,

Rachael

 

[hjgoldstein]

Hi, Rachael.
I'm not too hot on SQL either, but I think that you might have a parentheses problem.

I've got a report which runs on a multiple criteria query and the SQL is as follows:

SELECT TblWrkLogs.[Entity No], TblMainTable.MainEntityName, TblWrkLogs.Comments, TblWrkLogs.Identifier, TblWrkLogs.Year, TblWrkLogs.[Completed to], TblMainTable.MainAdmin
FROM TblMonths INNER JOIN (TblMainTable INNER JOIN TblWrkLogs ON TblMainTable.MainEntityNumber = TblWrkLogs.[Entity No]) ON TblMonths.Month = TblWrkLogs.[Completed to]
WHERE (((TblMainTable.MainBookKeeper) Like "*" & [Enter Book Keeper Name] & "*&quot AND ((TblMainTable.MainAdmin) Like "*" & [Enter Admin Name] & "*&quot AND ((TblMainTable.MainStatus) Like "Open&quot)
GROUP BY TblWrkLogs.[Entity No], TblMainTable.MainEntityName, TblWrkLogs.Comments, TblWrkLogs.Identifier, TblWrkLogs.Year, TblWrkLogs.[Completed to], TblMainTable.MainAdmin, TblWrkLogs.[OS FROM], TblMonths.MonthNumber
HAVING (((TblWrkLogs.Identifier) Not Like "Completed&quot)
ORDER BY TblWrkLogs.Year, TblMonths.MonthNumber;

As you can see, the AND clause in each case is part of the main WHERE clause as there is only one closing parenthesis before the AND. It's worth giving it a shot.

Hope this helps.

 

[NaoTriste]

There are many ways of doing this but since you say you are relatively new to access I will give you the simplest ways.
Solution 1 - When you do a call to your report, there is a parameter you can use wich is "Where clause" and in there you use the string that contains your criteria.

E.g.
Dim strCriteria as string
strCriteria= "Manager = " & me!txtManager & "Client_Name = " & me!txtClientName
DoCmd.OpenReport Me.cboReports, acViewNormal, , strCriteria

Here I am assuming that that "me!txtManager" and "me!txtClientName" are items on the current screen but it could that they are global variables that you accept from a user prompt (popup , message box, etc).

Solution 2 - You can use the "Filter on" and "Filter" properties on the Report's data properties to include the criteria which you would get from the screen (form).

 

[Rach1978]

Just wanted to say thank you to both of you for your excellent advice and taking the time to answer my question.

My database now works perfectly and produces reports based on 6 criteria! :-D:-D:-D

I really am very grateful.

Thanks again,

Rachael