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

Report based on checkbox selection

100+
P: 106
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

Expand|Select|Wrap|Line Numbers
  1. 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
  2. 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
  3. WHERE (((queues_members.cell)="301,302....etc"));;
Thanks,
Richard
Sep 20 '07 #1
Share this Question
Share on Google+
29 Replies


Scott Price
Expert 100+
P: 1,384
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

Expand|Select|Wrap|Line Numbers
  1. 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
  2. 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
  3. 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
Sep 20 '07 #2

NeoPa
Expert Mod 15k+
P: 31,186
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)
Sep 21 '07 #3

100+
P: 106
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.
Sep 21 '07 #4

100+
P: 106
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:
Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM queues_members INNER JOIN cypress_facility ON queues_members.members_id = cypress_facility.ID
  3. 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]))
  4. GROUP BY cypress_facility.Name, cypress_facility.Initials, queues_members.cell, cypress_facility.ID
  5. HAVING (((queues_members.cell)=[cell numbers here]) AND ((cypress_facility.ID) Like [Forms]![rpt_selector]![cbotech]))
  6. ORDER BY cypress_facility.Name, Max(Format([date],"m/d/yyyy"));
and here it's yours:
Expand|Select|Wrap|Line Numbers
  1.  SELECT tblCustomer.CustomerID, tblCustomer.CustomerFirstName, tblCustomer.CustomerLastName, tblCustomer.Damaged FROM tblCustomer
  2. 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
Sep 21 '07 #5

Scott Price
Expert 100+
P: 1,384
I'll have a look at this late tonight or tomorrow!

Have a good weekend!

Regards,
Scott
Sep 21 '07 #6

NeoPa
Expert Mod 15k+
P: 31,186
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 ('))
    Expand|Select|Wrap|Line Numbers
    1. WHERE (queues_members.cell In('301','302',...)
I hope this is some help, and I'll leave you in Scott's capable hands.
Sep 22 '07 #7

Scott Price
Expert 100+
P: 1,384
Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM queues_members INNER JOIN cypress_facility ON queues_members.members_id = cypress_facility.ID
  3. 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,""))
  4. GROUP BY cypress_facility.Name, cypress_facility.Initials, queues_members.cell, cypress_facility.ID
  5. HAVING (((queues_members.cell)=[cell numbers here]) AND ((cypress_facility.ID) LIKE [Forms]![rpt_selector]![cbotech]))
  6. 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
Sep 22 '07 #8

100+
P: 106
Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM queues_members INNER JOIN cypress_facility ON queues_members.members_id = cypress_facility.ID
  3. 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,""))
  4. GROUP BY cypress_facility.Name, cypress_facility.Initials, queues_members.cell, cypress_facility.ID
  5. HAVING (((queues_members.cell)=[cell numbers here]) AND ((cypress_facility.ID) LIKE [Forms]![rpt_selector]![cbotech]))
  6. 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
Sep 24 '07 #9

100+
P: 106
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"
Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM queues_members INNER JOIN cypress_facility ON queues_members.members_id = cypress_facility.ID
  3. 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,"")))
  4. GROUP BY cypress_facility.Name, cypress_facility.Initials, queues_members.cell, cypress_facility.ID
  5. HAVING (((cypress_facility.ID) Like [Forms]![rpt_selector]![cbotech]))
  6. ORDER BY cypress_facility.Name, Max(Format([date],"m/d/yyyy"));
  7.  
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
Sep 24 '07 #10

Scott Price
Expert 100+
P: 1,384
I'm a little under the weather today, sorry :-(

I'll try to look at this later on.

Regards,
Scott
Sep 24 '07 #11

100+
P: 106
I'm a little under the weather today, sorry :-(

I'll try to look at this later on.

Regards,
Scott

No problem...feel better
Sep 24 '07 #12

100+
P: 106
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:
Expand|Select|Wrap|Line Numbers
  1.  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
  2. FROM queues_members INNER JOIN cypress_facility ON queues_members.members_id = cypress_facility.ID
  3. 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","")))
  4. GROUP BY cypress_facility.Name, cypress_facility.Initials, queues_members.cell, cypress_facility.ID
  5. HAVING (((cypress_facility.ID) Like [Forms]![rpt_selector]![cbotech]))
  6. ORDER BY cypress_facility.Name, Max(Format([date],"m/d/yyyy"));
  7.  
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
Sep 24 '07 #13

NeoPa
Expert Mod 15k+
P: 31,186
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.
Sep 24 '07 #14

100+
P: 106
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
Sep 24 '07 #15

NeoPa
Expert Mod 15k+
P: 31,186
No problem Rickster.
&BTW we all ask n00by questions from time-to-time. I doubt you'll find anyone here who hasn't ;)
Sep 24 '07 #16

100+
P: 106
good, I am stumped with the error message which is generated when I run the sql statement
Sep 24 '07 #17

Scott Price
Expert 100+
P: 1,384
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
Sep 24 '07 #18

NeoPa
Expert Mod 15k+
P: 31,186
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.
Sep 25 '07 #19

100+
P: 106
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
Sep 25 '07 #20

100+
P: 106
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.

Expand|Select|Wrap|Line Numbers
  1.  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
  2. FROM cypress_facility INNER JOIN queues_members ON cypress_facility.ID = queues_members.members_id
  3. 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
  4. 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]));
  5.  

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:
Expand|Select|Wrap|Line Numbers
  1. SELECT  id, name from qryCypressFacility_AgentsOnly 
  2. 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!
Sep 26 '07 #21

Scott Price
Expert 100+
P: 1,384
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 :-)
Sep 26 '07 #22

100+
P: 106
You're Welcome....

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

Richard
Sep 26 '07 #23

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

Richard

Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM queues_members INNER JOIN cypress_facility ON queues_members.members_id = cypress_facility.ID
  3. 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","")))
  4. GROUP BY cypress_facility.Name, cypress_facility.Initials, queues_members.cell, cypress_facility.ID
  5. HAVING (((cypress_facility.ID) Like [Forms]![rpt_selector]![cbotech]))
  6. ORDER BY cypress_facility.Name, Max(Format([date],"m/d/yyyy"));
Sep 26 '07 #24

Scott Price
Expert 100+
P: 1,384
Try taking your Format([date]... BETWEEN... And ...[cboEndDate] Line out of the WHERE clause, and instead put it in the HAVING clause.

Regards,
Scott
Sep 26 '07 #25

NeoPa
Expert Mod 15k+
P: 31,186
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).
Sep 27 '07 #26

100+
P: 106
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:
Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM queues_members INNER JOIN cypress_facility ON queues_members.members_id = cypress_facility.ID
  3. 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","")))
  4. GROUP BY cypress_facility.Name, cypress_facility.Initials, queues_members.cell, cypress_facility.ID, Format([date],"m/d/yyyy")
  5. 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]))
  6. ORDER BY cypress_facility.Name, Max(Format([date],"m/d/yyyy"));
  7.  
I can't find why it's giving me that error. Help.....!
Sep 28 '07 #27

Scott Price
Expert 100+
P: 1,384
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
Sep 28 '07 #28

100+
P: 106
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....

Expand|Select|Wrap|Line Numbers
  1. 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")
  2. FROM queues_members INNER JOIN cypress_facility ON queues_members.members_id = cypress_facility.ID
  3. 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","")))
  4. GROUP BY queues_members.cell, Format([date],"m/d/yyyy")
  5. 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]))
  6. ORDER BY queues_members.cell, Max(Format([date],"m/d/yyyy"));
Richard
Oct 4 '07 #29

Scott Price
Expert 100+
P: 1,384
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
Oct 4 '07 #30

Post your reply

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