Report based on checkbox selection

[Rickster66]

Hey there,

I have a reporting issue and any help is well appreciated. I have been requested to create a report based on cells (defined a groups of workers). So far, so good. The report is be be built based on checkboses that represent each cell. The user should be able to to select one checkbox for one cell, 2 checkboxes for 2 cells, 3,6, etc. The limit is 12 cells. I'm not sure how to go about it. I am enclosing the sql code below.

Richard

SELECT cypress_facility.Name, cypress_facility.username, cypress_facility.Initials, queues_members.queue, queues_members.cell, queues_members.DRRX, queues_members.PA, queues_members.DIABSVQ, queues_members.BIBr, queues_members.BIBn, queues_members.date AS Celldate
FROM (cypress_facility INNER JOIN queues_members ON cypress_facility.ID = queues_members.members_id) INNER JOIN queues ON queues_members.queues_id = queues.queue_id
WHERE (((queues_members.cell)="301,302....etc"));;

Thanks,
Richard

[Scott Price]

Hey there,

I have a reporting issue and any help is well appreciated. I have been requested to create a report based on cells (defined a groups of workers). So far, so good. The report is be be built based on checkboses that represent each cell. The user should be able to to select one checkbox for one cell, 2 checkboxes for 2 cells, 3,6, etc. The limit is 12 cells. I'm not sure how to go about it. I am enclosing the sql code below.

Richard

SELECT cypress_facility.Name, cypress_facility.username, cypress_facility.Initials, queues_members.queue, queues_members.cell, queues_members.DRRX, queues_members.PA, queues_members.DIABSVQ, queues_members.BIBr, queues_members.BIBn, queues_members.date AS Celldate
FROM (cypress_facility INNER JOIN queues_members ON cypress_facility.ID = queues_members.members_id) INNER JOIN queues ON queues_members.queues_id = queues.queue_id
WHERE (((queues_members.cell)="301,302....etc"));;

Thanks,
Richard

Good eve Richard,

Have a look at this thread, especially post #8, that discusses something very similar to what you are asking about.

http://www.thescripts.com/forum/thread708234.html

Hope this helps you out!

Regards,
Scott

[NeoPa]

If that's not what you're after, perhaps you could explain what you mean by cells. All we have so far is :

(defined a groups of workers)

[Rickster66]

If that's not what you're after, perhaps you could explain what you mean by cells. All we have so far is :

Yes, that is exactly what I'm after. A cell represents a numbers of workers. Each cell (total 12) make up the entire department.

[Rickster66]

Good eve Richard,

Have a look at this thread, especially post #8, that discusses something very similar to what you are asking about.

http://www.thescripts.com/forum/thread708234.html

Hope this helps you out!

Regards,
Scott

Hello Scott,

I have written the sql statement but included the statements that recommended at the end of the other thread. I'm a little confused as to what syntax should be used to incorporate the checkbox conditional statements with the already written code.

This is the one I wrote:

SELECT cypress_facility.Name, cypress_facility.Initials, queues_members.cell, Count(queues_members.DRRX) AS DRRX, Count(queues_members.COPAY) AS COPAY, Count(queues_members.BIB) AS BIB, Count(queues_members.BIBr) AS BIBr, Count(queues_members.BIBn) AS BIBn, Count(queues_members.MRD) AS MRD, Count(queues_members.DEXPND) AS DEXPND, Count(queues_members.CLAIMRJT) AS CLAIMRJT, Count(queues_members.PT) AS PT, Count(queues_members.DE) AS DE, Count(queues_members.DEr) AS DEr, Count(queues_members.FAXBACK) AS FAXBACK, Count(queues_members.COMP) AS [COMP], Count(queues_members.WF) AS WF, ([copay]+[bib]+[bibn]+[bibr]+[mrd]+[dexpnd]+[claimrjt]+[pt]+[de]+ [der]+[faxback]+[comp]+[wf]) AS Total, Max(Format([date],"m/d/yyyy")) AS datet
FROM queues_members INNER JOIN cypress_facility ON queues_members.members_id = cypress_facility.ID
WHERE (((Format([date],"m/d/yyyy")) Between [Forms]![rpt_selector]![cboStartDate] And [Forms]![rpt_selector]![cboEndDate] Or (Format([date],"m/d/yyyy"))=[Forms]![rpt_selector]![cboStartDate]))
GROUP BY cypress_facility.Name, cypress_facility.Initials, queues_members.cell, cypress_facility.ID
HAVING (((queues_members.cell)=[cell numbers here]) AND ((cypress_facility.ID) Like [Forms]![rpt_selector]![cbotech]))
ORDER BY cypress_facility.Name, Max(Format([date],"m/d/yyyy"));

and here it's yours:

 SELECT tblCustomer.CustomerID, tblCustomer.CustomerFirstName, tblCustomer.CustomerLastName, tblCustomer.Damaged FROM tblCustomer
WHERE (((tblCustomer.CustomerID) LIKE IIf([Forms]![form2]![CheckBox1]=-1,1,"") OR (tblCustomer.CustomerID) LIKE IIf([Forms]![form2]![checkbox2]=-1,2,"") OR (tblCustomer.CustomerID) LIKE IIf([Forms]![form2]![checkbox3]=-1,3,"")));

As always, your help is always appreciated because I'm not sure how to put these two together..........perhaps it's because it's eod Friday

[Scott Price]

I'll have a look at this late tonight or tomorrow!

Have a good weekend!

Regards,
Scott

[NeoPa]

Yes, that is exactly what I'm after. A cell represents a numbers of workers. Each cell (total 12) make up the entire department.

I can see communication is going to be a struggle, so I'll just jot down a couple of things I noticed in your code in the hope that it will help you somewhat, then sign off.

1. When selecting by date (Post #5, line #3) it is best to compare as date values (rather than as formatted strings). If you must use formatted strings (there are situations) then you should ensure that you use a format where the date elements are in descending order (EG. yyyy/mm/dd or yyyymmdd even). Otherwise your WHERE clause will give unintended results.
2. This you may know already but can trip people :-
   When you use Count() (as in Count(queues_members.BIBr) AS BIBr line #1) all records processed will be counted unless the field contains a Null value. Zeroes will be counted as will empty strings.
3. Going back to post #1 line #3 (WHERE (((queues_members.cell)="301,302....etc"))), if you want to select records if they are included in a list you would say (assuming string values - otherwise leave out the quotes ('))

   WHERE (queues_members.cell In('301','302',...)

I hope this is some help, and I'll leave you in Scott's capable hands.

[Scott Price]

SELECT cypress_facility.Name, cypress_facility.Initials, queues_members.cell, Count(queues_members.DRRX) AS DRRX, Count(queues_members.COPAY) AS COPAY, Count(queues_members.BIB) AS BIB, Count(queues_members.BIBr) AS BIBr, Count(queues_members.BIBn) AS BIBn, Count(queues_members.MRD) AS MRD, Count(queues_members.DEXPND) AS DEXPND, Count(queues_members.CLAIMRJT) AS CLAIMRJT, Count(queues_members.PT) AS PT, Count(queues_members.DE) AS DE, Count(queues_members.DEr) AS DEr, Count(queues_members.FAXBACK) AS FAXBACK, Count(queues_members.COMP) AS [COMP], Count(queues_members.WF) AS WF, ([copay]+[bib]+[bibn]+[bibr]+[mrd]+[dexpnd]+[claimrjt]+[pt]+[de]+ [der]+[faxback]+[comp]+[wf]) AS Total, Max(Format([date],"m/d/yyyy")) AS datet
FROM queues_members INNER JOIN cypress_facility ON queues_members.members_id = cypress_facility.ID
WHERE (((Format([date],"m/d/yyyy")) BETWEEN [Forms]![rpt_selector]![cboStartDate] AND [Forms]![rpt_selector]![cboEndDate] OR (Format([date],"m/d/yyyy"))=[Forms]![rpt_selector]![cboStartDate])) AND (queues_members.cell) LIKE IIf([Forms]![form2]![CheckBox1]=-1,1,"") OR (queues_members.cell) LIKE IIf([Forms]![form2]![checkbox2]=-1,2,"") OR (queues_members.cell) LIKE IIf([Forms]![form2]![checkbox3]=-1,3,""))
GROUP BY cypress_facility.Name, cypress_facility.Initials, queues_members.cell, cypress_facility.ID
HAVING (((queues_members.cell)=[cell numbers here]) AND ((cypress_facility.ID) LIKE [Forms]![rpt_selector]![cbotech]))
ORDER BY cypress_facility.Name, Max(Format([date],"m/d/yyyy"));

This should give you the general idea... The simplest way to do this is in query design view, just put your LIKE IIf([Forms]![form2]![CheckBox1]=-1,1,"") OR etc... criteria in the criteria grid box for the field queues_members.cell.

Regards,
Scott

[Rickster66]

SELECT cypress_facility.Name, cypress_facility.Initials, queues_members.cell, Count(queues_members.DRRX) AS DRRX, Count(queues_members.COPAY) AS COPAY, Count(queues_members.BIB) AS BIB, Count(queues_members.BIBr) AS BIBr, Count(queues_members.BIBn) AS BIBn, Count(queues_members.MRD) AS MRD, Count(queues_members.DEXPND) AS DEXPND, Count(queues_members.CLAIMRJT) AS CLAIMRJT, Count(queues_members.PT) AS PT, Count(queues_members.DE) AS DE, Count(queues_members.DEr) AS DEr, Count(queues_members.FAXBACK) AS FAXBACK, Count(queues_members.COMP) AS [COMP], Count(queues_members.WF) AS WF, ([copay]+[bib]+[bibn]+[bibr]+[mrd]+[dexpnd]+[claimrjt]+[pt]+[de]+[der]+[faxback]+[comp]+[wf]) AS Total, Max(Format([date],"m/d/yyyy")) AS datet
FROM queues_members INNER JOIN cypress_facility ON queues_members.members_id = cypress_facility.ID
WHERE (((Format([date],"m/d/yyyy")) BETWEEN [Forms]![rpt_selector]![cboStartDate] AND [Forms]![rpt_selector]![cboEndDate] OR (Format([date],"m/d/yyyy"))=[Forms]![rpt_selector]![cboStartDate])) AND (queues_members.cell) LIKE IIf([Forms]![form2]![CheckBox1]=-1,1,"") OR (queues_members.cell) LIKE IIf([Forms]![form2]![checkbox2]=-1,2,"") OR (queues_members.cell) LIKE IIf([Forms]![form2]![checkbox3]=-1,3,""))
GROUP BY cypress_facility.Name, cypress_facility.Initials, queues_members.cell, cypress_facility.ID
HAVING (((queues_members.cell)=[cell numbers here]) AND ((cypress_facility.ID) LIKE [Forms]![rpt_selector]![cbotech]))
ORDER BY cypress_facility.Name, Max(Format([date],"m/d/yyyy"));

This should give you the general idea... The simplest way to do this is in query design view, just put your LIKE IIf([Forms]![form2]![CheckBox1]=-1,1,"") OR etc... criteria in the criteria grid box for the field queues_members.cell.

Regards,
Scott

This is extremely helpful. I will check it out and let you guys know.....Thanks

Richard

[Rickster66]

I have another question,

If, on the report, or in the excel spreadsheet, I have a particular Id which represents a worker, and he or she has only worked on a few of the queues? how do I supress it so that only the queues with data show up?

this is the query from the last post"

SELECT cypress_facility.Name, cypress_facility.Initials, queues_members.cell, Count(queues_members.DRRX) AS DRRX, Count(queues_members.COPAY) AS COPAY, Count(queues_members.BIB) AS BIB, Count(queues_members.BIBr) AS BIBr, Count(queues_members.BIBn) AS BIBn, Count(queues_members.MRD) AS MRD, Count(queues_members.DEXPND) AS DEXPND, Count(queues_members.CLAIMRJT) AS CLAIMRJT, Count(queues_members.PT) AS PT, Count(queues_members.DE) AS DE, Count(queues_members.DEr) AS DEr, Count(queues_members.FAXBACK) AS FAXBACK, Count(queues_members.COMP) AS [COMP], Count(queues_members.WF) AS WF, ([copay]+[bib]+[bibn]+[bibr]+[mrd]+[dexpnd]+[claimrjt]+[pt]+[de]+ [der]+[faxback]+[comp]+[wf]) AS Total, Max(Format([date],"m/d/yyyy")) AS datet
FROM queues_members INNER JOIN cypress_facility ON queues_members.members_id = cypress_facility.ID
WHERE (((Format([date],"m/d/yyyy")) Between [Forms]![rpt_selector]![cboStartDate] And [Forms]![rpt_selector]![cboEndDate] Or (Format([date],"m/d/yyyy"))=[Forms]![rpt_selector]![cboStartDate]) AND ((queues_members.cell) Like IIf([Forms]![form2]![CheckBox1]=-1,1,""))) OR (((queues_members.cell) Like IIf([Forms]![form2]![checkbox2]=-1,2,""))) OR (((queues_members.cell) Like IIf([Forms]![form2]![checkbox3]=-1,3,"")))
GROUP BY cypress_facility.Name, cypress_facility.Initials, queues_members.cell, cypress_facility.ID
HAVING (((cypress_facility.ID) Like [Forms]![rpt_selector]![cbotech]))
ORDER BY cypress_facility.Name, Max(Format([date],"m/d/yyyy"));
 

On the report, (the sql) is there a way so that only the queue(Bib,Drrx,etc.) shows is the ID (the worker, has data? In other words, I would like to avoid empty columns. I've dabbled using the NZ function but I get weird errors. Thank you so much on again....you guys Rock!!

Richard

[Scott Price]

I'm a little under the weather today, sorry :-(

I'll try to look at this later on.

Regards,
Scott

[Rickster66]

I'm a little under the weather today, sorry :-(

I'll try to look at this later on.

Regards,
Scott

No problem...feel better

[Rickster66]

No problem...feel better

I hate to throw this at you now but I am under the gun right now. When you get a chance, I would appreciate it if you look at the sql code again. It's not working. Instead, It's displaying an error message. Here is the sql code:

 SELECT cypress_facility.Name, cypress_facility.Initials, queues_members.cell, Count(queues_members.DRRX) AS DRRX, Count(queues_members.COPAY) AS COPAY, Count(queues_members.BIB) AS BIB, Count(queues_members.BIBr) AS BIBr, Count(queues_members.BIBn) AS BIBn, Count(queues_members.MRD) AS MRD, Count(queues_members.DEXPND) AS DEXPND, Count(queues_members.CLAIMRJT) AS CLAIMRJT, Count(queues_members.PT) AS PT, Count(queues_members.DE) AS DE, Count(queues_members.DEr) AS DEr, Count(queues_members.FAXBACK) AS FAXBACK, Count(queues_members.COMP) AS [COMP], Count(queues_members.WF) AS WF, ([copay]+[bib]+[bibn]+[bibr]+[mrd]+[dexpnd]+[claimrjt]+[pt]+[de]+ [der]+[faxback]+[comp]+[wf]) AS Total, Max(Format([date],"m/d/yyyy")) AS datet
FROM queues_members INNER JOIN cypress_facility ON queues_members.members_id = cypress_facility.ID
WHERE (((Format([date],"m/d/yyyy")) Between [Forms]![rpt_selector]![cboStartDate] And [Forms]![rpt_selector]![cboEndDate] Or (Format([date],"m/d/yyyy"))=[Forms]![rpt_selector]![cboStartDate]) AND ((queues_members.cell) Like IIf([Forms]![rpt_selector]![CheckBox300]=-1,300,""))) OR (((queues_members.cell) Like IIf([Forms]![rpt_selector]![checkbox301]=-1,301,""))) OR (((queues_members.cell) Like IIf([Forms]![rpt_selector]![checkbox302]=-1,302,""))) OR (((queues_members.cell) Like IIf([Forms]![rpt_selector]![checkbox303]=-1,303,""))) OR (((queues_members.cell) Like IIf([Forms]![rpt_selector]![checkbox304]=-1,304,""))) OR (((queues_members.cell) Like IIf([Forms]![rpt_selector]![checkbox305]=-1,305,""))) OR (((queues_members.cell) Like IIf([Forms]![rpt_selector]![checkbox306]=-1,306,""))) OR (((queues_members.cell) Like IIf([Forms]![rpt_selector]![checkbox307]=-1,307,""))) OR (((queues_members.cell) Like IIf([Forms]![rpt_selector]![checkbox308]=-1,308,""))) OR (((queues_members.cell) Like IIf([Forms]![rpt_selector]![checkbox309]=-1,309,""))) OR (((queues_members.cell) Like IIf([Forms]![rpt_selector]![checkbox310]=-1,310,""))) OR (((queues_members.cell) Like IIf([Forms]![rpt_selector]![checkboxrt]=-1,"r-t",""))) OR (((queues_members.cell) Like IIf([Forms]![rpt_selector]![checkboxst]=-1,"s-t","")))
GROUP BY cypress_facility.Name, cypress_facility.Initials, queues_members.cell, cypress_facility.ID
HAVING (((cypress_facility.ID) Like [Forms]![rpt_selector]![cbotech]))
ORDER BY cypress_facility.Name, Max(Format([date],"m/d/yyyy"));
 

and here is the message:

The expression is typed incorrectly, or is too complex to be evaluated.For example, a numeric expression may contain too many complicated elements. Try simplifying the expression to variables.

I've been to figure out why it's not working to no avail. Once again, when you're able to, if you could take a look at this I would really appreciate it. Thanks again,

Richard

[NeoPa]

No problem...feel better

A SELECT query will always return the same number of columns (those defined in the SQL). It is possible to design it to return nulls, zeroes or empty strings in any of the columns for a particular record, but not to lose a column altogether (without changing the SQL itself).
HTH.
@Scott Get well soon my friend.

[Rickster66]

A SELECT query will always return the same number of columns (those defined in the SQL). It is possible to design it to return nulls, zeroes or empty strings in any of the columns for a particular record, but not to lose a column altogether (without changing the SQL itself).
HTH.
@Scott Get well soon my friend.

I understand...I should've known

Thanks,

Feel better Scott

Richard

[NeoPa]

No problem Rickster.
&BTW we all ask n00by questions from time-to-time. I doubt you'll find anyone here who hasn't ;)

[Rickster66]

good, I am stumped with the error message which is generated when I run the sql statement

[Scott Price]

Thanks, guys for the well-wishes...

Just a case of the good-ol' south american hyperzoodiacs that gave me a splitting headache, and had me running to the bathroom every 15 minutes :-( After spending most of the day in bed (between bathroom 'runs'), and taking a handful of ibuprofen I'm at least on my feet again.

Richard, when it gives you this error message, does it highlight any of the criteria?

I'm thinking at this point you might have a little syntax problem with the WHERE ... Format([date]...) BETWEEN clause.

I think when using a BETWEEN clause you have to write is as: HAVING [fieldname] BETWEEN [criteria] AND [criteria2]. Also I have had trouble before using the date functions in queries like this. If changing to HAVING doesn't help, try running the query without the date clause just to narrow down the error.

Regards,
Scott

[NeoPa]

FYI all - The HAVING & WHERE clauses in SQL are similar.
HAVING checks the results after any GROUP BY clause (If not grouped then no HAVING clause) and WHERE is used to check the input records.
Technically, it's also possible to have a grouped query without the GROUP BY clause. Having can also be used there I think.

[Rickster66]

Thanks, guys for the well-wishes...

Just a case of the good-ol' south american hyperzoodiacs that gave me a splitting headache, and had me running to the bathroom every 15 minutes :-( After spending most of the day in bed (between bathroom 'runs'), and taking a handful of ibuprofen I'm at least on my feet again.

Richard, when it gives you this error message, does it highlight any of the criteria?

I'm thinking at this point you might have a little syntax problem with the WHERE ... Format([date]...) BETWEEN clause.

I think when using a BETWEEN clause you have to write is as: HAVING [fieldname] BETWEEN [criteria] AND [criteria2]. Also I have had trouble before using the date functions in queries like this. If changing to HAVING doesn't help, try running the query without the date clause just to narrow down the error.

Regards,
Scott

Hi Scott, I wonder which specific hyperzoodiacs you could be refering to...but in any case, it's good that you're feeling better.

As far as as the error message is concerned, that is all I noticed. I will remove the date parameters and see what happens.

Thanks,

Richard

[Rickster66]

Hi Scott, the query is running - thank you ! Now, I have another question regarding date ranges as parameters in my queries. There are times when i need to select only one date and other times when to select a range. I was under the impression that by using "OR" the issue would be solved but it hasn't. I've had to create 2 separate quesries; one for the one time date selection and the other for the range. Something tells that this is not the best way to go about it.

 SELECT cypress_facility.ID, cypress_facility.Name, queues_members.username, queues_members.initials, queues_members.queue, queues_members.cell, queues_members.status, Count(queues_members.DRRX) AS CountOfDRRX, Count(queues_members.PA) AS CountOfPA, Count(queues_members.DIABSVQ) AS CountOfDIABSVQ, Count(queues_members.COPAY) AS CountOfCOPAY, Count(queues_members.BIB) AS BIB, Count(queues_members.BIBr) AS CountOfBIBr, Count(queues_members.BIBn) AS CountOfBIBn, Count(queues_members.MRD) AS CountOfMRD, Count(queues_members.DEXPND) AS CountOfDEXPND, Count(queues_members.CLAIMRJT) AS CountOfCLAIMRJT, Count(queues_members.PT) AS CountOfPT, Count(queues_members.DE) AS CountOfDE, Count(queues_members.DEr) AS CountOfDEr, Count(queues_members.WF) AS CountOfWF, Count(queues_members.RESOLVED) AS CountOfRESOLVED, TimeSerial(Hour([date]),Minute([date]),Second([date])) AS MemWorkTime, queues_members.date AS MemWorkDate
FROM cypress_facility INNER JOIN queues_members ON cypress_facility.ID = queues_members.members_id
GROUP BY cypress_facility.ID, cypress_facility.Name, queues_members.username, queues_members.initials, queues_members.queue, queues_members.cell, queues_members.status, TimeSerial(Hour([date]),Minute([date]),Second([date])), queues_members.date
HAVING (((cypress_facility.ID) Like [Forms]![frmReportSelectorDateTime]![cbotech]) AND ((TimeSerial(Hour([date]),Minute([date]),Second([date]))) Between [start time] And [end time]) AND ((queues_members.date) Between [start date] And [end date]));
 

Also the report should be able to retrive using one id or all Id's which are selected from a dropdown . I adapted a union query to make this happen:

SELECT  id, name from qryCypressFacility_AgentsOnly 
UNION SELECT "*","--All Agents--" from msysobjects;

Now, does this influence when I can retrive for 1 Id selected for one date or over a range.....? Ideally I would like to have one query where i can pick one user for one date or a range or all users for a date or a range....hope it's not just a pipe dream......Any suggestions? Many thanks,

Richard - as I said before..you guys Rock!

[Scott Price]

It should work to just enter the same date in both the Start Date and End Date parameters, obviating the need to add two queries. What happens if you try that?

As for the other question, I can't think of a reason it shouldn't work. What happens when you try it that way?

Regards,
Scott

p.s. thanks for the compliments :-) We like positive feedback and satisfied customers :-)

[Rickster66]

You're Welcome....

The date range is not working at all. I get all the dates no matter what....?

Richard

[Rickster66]

I am soo sorry, I placed the wrong sql. This sql code where the data ranges are not working....My apologies

Richard

SELECT cypress_facility.Name, cypress_facility.Initials, queues_members.cell, Count(queues_members.DRRX) AS DRRX, Count(queues_members.COPAY) AS COPAY, Count(queues_members.BIB) AS BIB, Count(queues_members.BIBr) AS BIBr, Count(queues_members.BIBn) AS BIBn, Count(queues_members.MRD) AS MRD, Count(queues_members.DEXPND) AS DEXPND, Count(queues_members.CLAIMRJT) AS CLAIMRJT, Count(queues_members.PT) AS PT, Count(queues_members.DE) AS DE, Count(queues_members.DEr) AS DEr, Count(queues_members.FAXBACK) AS FAXBACK, Count(queues_members.COMP) AS [COMP], Count(queues_members.WF) AS WF, ([copay]+[bib]+[bibn]+[bibr]+[mrd]+[dexpnd]+[claimrjt]+[pt]+[de]+ [der]+[faxback]+[comp]+[wf]) AS Total, Max(Format([date],"m/d/yyyy")) AS datet, cypress_facility.ID
FROM queues_members INNER JOIN cypress_facility ON queues_members.members_id = cypress_facility.ID
WHERE (((queues_members.cell) Like IIf([Forms]![rpt_selector]![CheckBox300]=-1,300,"")) AND ((Format([date],"m/d/yyyy")) Between [Forms]![rpt_selector]![cboStartDate] And [Forms]![rpt_selector]![cboEndDate] Or (Format([date],"m/d/yyyy"))=[Forms]![rpt_selector]![cboStartDate])) OR (((queues_members.cell) Like IIf([Forms]![rpt_selector]![checkbox301]=-1,301,""))) OR (((queues_members.cell) Like IIf([Forms]![rpt_selector]![checkbox302]=-1,302,""))) OR (((queues_members.cell) Like IIf([Forms]![rpt_selector]![checkbox303]=-1,303,""))) OR (((queues_members.cell) Like IIf([Forms]![rpt_selector]![checkbox304]=-1,304,""))) OR (((queues_members.cell) Like IIf([Forms]![rpt_selector]![checkbox305]=-1,305,""))) OR (((queues_members.cell) Like IIf([Forms]![rpt_selector]![checkbox306]=-1,306,""))) OR (((queues_members.cell) Like IIf([Forms]![rpt_selector]![checkbox307]=-1,307,""))) OR (((queues_members.cell) Like IIf([Forms]![rpt_selector]![checkbox308]=-1,308,""))) OR (((queues_members.cell) Like IIf([Forms]![rpt_selector]![checkbox309]=-1,309,""))) OR (((queues_members.cell) Like IIf([Forms]![rpt_selector]![checkbox310]=-1,310,""))) OR (((queues_members.cell) Like IIf([Forms]![rpt_selector]![checkboxrt]=-1,"r-t",""))) OR (((queues_members.cell) Like IIf([Forms]![rpt_selector]![checkboxst]=-1,"s-t","")))
GROUP BY cypress_facility.Name, cypress_facility.Initials, queues_members.cell, cypress_facility.ID
HAVING (((cypress_facility.ID) Like [Forms]![rpt_selector]![cbotech]))
ORDER BY cypress_facility.Name, Max(Format([date],"m/d/yyyy"));

[Scott Price]

Try taking your Format([date]... BETWEEN... And ...[cboEndDate] Line out of the WHERE clause, and instead put it in the HAVING clause.

Regards,
Scott

[NeoPa]

Do NOT use the current version of your [date] WHERE phrase as it is wrong in so many ways. Please refer back to post #7 about not comparing date strings formatted as d/m/y.
It will need to be in the HAVING clause if you need the Max() result checked. If not then it needs to be in the WHERE clause (where you have it now).

[Rickster66]

I made the change as indicated and I got this message:

The expression is typed incorrectly, or is too complex to be evaluated.For example, a numeric expression may contain too many complicated elements. Try simplifying the expression to variables.

Here is the sql code:

SELECT cypress_facility.Name, cypress_facility.Initials, queues_members.cell, Count(queues_members.DRRX) AS DRRX, Count(queues_members.COPAY) AS COPAY, Count(queues_members.BIB) AS BIB, Count(queues_members.BIBr) AS BIBr, Count(queues_members.BIBn) AS BIBn, Count(queues_members.MRD) AS MRD, Count(queues_members.DEXPND) AS DEXPND, Count(queues_members.CLAIMRJT) AS CLAIMRJT, Count(queues_members.PT) AS PT, Count(queues_members.DE) AS DE, Count(queues_members.DEr) AS DEr, Count(queues_members.FAXBACK) AS FAXBACK, Count(queues_members.COMP) AS [COMP], Count(queues_members.WF) AS WF, ([copay]+[bib]+[bibn]+[bibr]+[mrd]+[dexpnd]+[claimrjt]+[pt]+[de]+ [der]+[faxback]+[comp]+[wf]) AS Total, Max(Format([date],"m/d/yyyy")) AS datet, cypress_facility.ID
FROM queues_members INNER JOIN cypress_facility ON queues_members.members_id = cypress_facility.ID
WHERE (((queues_members.cell) Like IIf([Forms]![rpt_selector]![CheckBox300]=-1,300,""))) OR (((queues_members.cell) Like IIf([Forms]![rpt_selector]![checkbox301]=-1,301,""))) OR (((queues_members.cell) Like IIf([Forms]![rpt_selector]![checkbox302]=-1,302,""))) OR (((queues_members.cell) Like IIf([Forms]![rpt_selector]![checkbox303]=-1,303,""))) OR (((queues_members.cell) Like IIf([Forms]![rpt_selector]![checkbox304]=-1,304,""))) OR (((queues_members.cell) Like IIf([Forms]![rpt_selector]![checkbox305]=-1,305,""))) OR (((queues_members.cell) Like IIf([Forms]![rpt_selector]![checkbox306]=-1,306,""))) OR (((queues_members.cell) Like IIf([Forms]![rpt_selector]![checkbox307]=-1,307,""))) OR (((queues_members.cell) Like IIf([Forms]![rpt_selector]![checkbox308]=-1,308,""))) OR (((queues_members.cell) Like IIf([Forms]![rpt_selector]![checkbox309]=-1,309,""))) OR (((queues_members.cell) Like IIf([Forms]![rpt_selector]![checkbox310]=-1,310,""))) OR (((queues_members.cell) Like IIf([Forms]![rpt_selector]![checkboxrt]=-1,"r-t",""))) OR (((queues_members.cell) Like IIf([Forms]![rpt_selector]![checkboxst]=-1,"s-t","")))
GROUP BY cypress_facility.Name, cypress_facility.Initials, queues_members.cell, cypress_facility.ID, Format([date],"m/d/yyyy")
HAVING (((cypress_facility.ID) Like [Forms]![rpt_selector]![cbotech]) AND ((Format([date],"m/d/yyyy")) Between [Forms]![rpt_selector]![cboStartDate] And [Forms]![rpt_selector]![cboEndDate] Or (Format([date],"m/d/yyyy"))=[Forms]![rpt_selector]![cboStartDate]))
ORDER BY cypress_facility.Name, Max(Format([date],"m/d/yyyy"));
 

I can't find why it's giving me that error. Help.....!

[Scott Price]

What is being returned by your combo box start date value? Is this a formatted string, or a date literal, or an ID, or what? I assume you are wanting it to pass a date value, but you need to check carefully how many columns the combo box has, and which is the bound column to find out what value the combo box will pass.

If it's a formatted string, try passing it through the CDate() function. If a date literal, through the "#" & [ComboBoxName] & "#" treatment. Or a combination of both.

Regards,
Scott

[Rickster66]

What is being returned by your combo box start date value? Is this a formatted string, or a date literal, or an ID, or what? I assume you are wanting it to pass a date value, but you need to check carefully how many columns the combo box has, and which is the bound column to find out what value the combo box will pass.

If it's a formatted string, try passing it through the CDate() function. If a date literal, through the "#" & [ComboBoxName] & "#" treatment. Or a combination of both.

Regards,
Scott

Hey Scott,

I've been under the weather myself......

I am having sting problems with the query. I can't figure out how to pass the date parameters with the CDate() function or through the through the "#" & [ComboBoxName] & "#" treatment or both.....pretty clueless right now....

SELECT queues_members.cell, Count(queues_members.DRRX) AS DRRX, Count(queues_members.COPAY) AS COPAY, Count(queues_members.PSC) AS PSC, Count(queues_members.PSCr) AS PSCr, Count(queues_members.PSCn) AS PSCn, Count(queues_members.MRD) AS MRD, Count(queues_members.DEXPND) AS DEXPND, Count(queues_members.CLAIMRJT) AS CLAIMRJT, Count(queues_members.PT) AS PT, Count(queues_members.DE) AS DE, Count(queues_members.DEr) AS DEr, Count(queues_members.FAXBACK) AS FAXBACK, Count(queues_members.COMP) AS [COMP], Count(queues_members.WF) AS WF, ([copay]+[psc]+[pscn]+[pscr]+[mrd]+[dexpnd]+[claimrjt]+[pt]+[de]+[der]+[faxback]+[comp]+[wf]) AS Total, Max(Format([date],"m/d/yyyy")) AS datet, Format([date],"m/d/yyyy")
FROM queues_members INNER JOIN cypress_facility ON queues_members.members_id = cypress_facility.ID
WHERE (((queues_members.cell) Like IIf([Forms]![rpt_selector]![CheckBox300]=-1,300,"") Or (queues_members.cell) Like IIf([Forms]![rpt_selector]![checkbox301]=-1,301,"") Or (queues_members.cell) Like IIf([Forms]![rpt_selector]![checkbox302]=-1,302,"") Or (queues_members.cell) Like IIf([Forms]![rpt_selector]![checkbox303]=-1,303,"") Or (queues_members.cell) Like IIf([Forms]![rpt_selector]![checkbox304]=-1,304,"") Or (queues_members.cell) Like IIf([Forms]![rpt_selector]![checkbox305]=-1,305,"") Or (queues_members.cell) Like IIf([Forms]![rpt_selector]![checkbox306]=-1,306,"") Or (queues_members.cell) Like IIf([Forms]![rpt_selector]![checkbox307]=-1,307,"") Or (queues_members.cell) Like IIf([Forms]![rpt_selector]![checkbox308]=-1,308,"") Or (queues_members.cell) Like IIf([Forms]![rpt_selector]![checkbox309]=-1,309,"") Or (queues_members.cell) Like IIf([Forms]![rpt_selector]![checkbox310]=-1,310,"") Or (queues_members.cell) Like IIf([Forms]![rpt_selector]![checkboxrt]=-1,"r-t","") Or (queues_members.cell) Like IIf([Forms]![rpt_selector]![checkboxst]=-1,"s-t","")))
GROUP BY queues_members.cell, Format([date],"m/d/yyyy")
HAVING (((Format([date],"m/d/yyyy")) Between [Forms]![rpt_selector]![cboStartDate] And [Forms]![rpt_selector]![cboEndDate] Or (Format([date],"m/d/yyyy"))=[Forms]![rpt_selector]![cboStartDate]))
ORDER BY queues_members.cell, Max(Format([date],"m/d/yyyy"));

Richard

[Scott Price]

Make sure your combo box and form references are spelled exactly correct!

In my test db I tried to set this up, and I made the mistake of assuming that just because when I ran the combo box whizzard and got to the point where it asked me what label I wanted for the combo box, that I had named it! Wrong... It produced exactly the error you are seeing in the previous post... When I went to the form in design view and checked, the combo box was labeled cboStartDate, but Named Combo9 or some silly thing.

As long as you have some valid date being returned by your combo box, you shouldn't have to pass it through the cdate() or "#"'s...

Regards,
Scott