If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Integrating the code for a max.date function with a check box controlling it and
Hi all
I have a problem with integrating some code I received via a previous post for linking a check box to a max.date function: this is the max date function code: (note: this is only the WHERE statement from the SQL code as the full code is very long and not all together necessary, when ever I put it in a post it seems to scare people off) WHERE (((Cost.[Date of Research])=(select max([Date of Research]) from cost where cost.[Plant and Machinery ID]=Transaction.[Plant and Machinery ID]))) OR (((Cost.[Date of Research])=(select max([Date of Research]) from cost where cost.[Plant and Machinery ID]=Transaction.[Plant and Machinery ID])) OR [Forms]![FormName]![CheckBoxName] = True) The problem that I am having now is that it fails to integrate with another feature of the query, which is another check box ‘basket’. The function says that when basket = TRUE (on the form) return only the records containing a TRUE basket check box but when ‘basket’ on the form = false the query is to return all records wether they contain a TRUE value or a FALSE. My code as it is prior to the addition of the second checkbox is, WHERE ((([forms]![multi query]![check60])=False) AND ((Cost.[Date of Research] )=(select max([Date of Research]) from cost where cost.[Plant and Machinery ID]=Transaction.[Plant and Machinery ID]))) OR (((Cost.[Date of Research])= (select max([Date of Research]) from cost where cost.[Plant and Machinery ID] =Transaction.[Plant and Machinery ID])) AND ((Transaction.Basket)=True)) So I need it to operate together when basket is selected only records where basket=true are returned and when basket and the checkbox to disable max. date function is true it returns all date for records where basket = true and so on….for all the combinations of the two check box’s I would dearly love to know how to integrate them to create a statement that works, thanks for any help that you can offer I really appreciate it. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200510/1 |
#2
|
|||
|
|||
Dear Chris:
I've reformatted your second WHERE clause here, removing some of the unnecessary parentheses (probably those added by Access when it mangles your query: WHERE ([forms]![multi query]![check60] = False AND Cost.[Date of Research] = (select max([Date of Research]) from cost where cost.[Plant and Machinery ID] = Transaction.[Plant and Machinery ID])) OR (Cost.[Date of Research] = (select max([Date of Research]) from cost where cost.[Plant and Machinery ID] = Transaction.[Plant and Machinery ID]) AND Transaction.Basket = True) Sometimes, seeing the logic of what you've done can have an effect on your view of what might be wrong. Now, you say: when basket = TRUE (on the form) return only the records containing a TRUE basket check box but when 'basket' on the form = false the query is to return all records wether they contain a TRUE value or a FALSE. I do not see where you have tested "Basket on the form". Perhaps this is chekc60? When you have a control you want to reference elsewhere, it's good to name the control according to its function, not only so that others (like myself) can tell what you intend, but to avoid making some mistake yourself. Just a suggestion, eh. Now, I assume next that "records containing a TRUE basket check box" refers to a boolean column in the table Cost. Is that correct? If so, where in this query fragment did you test that column? The logic I would use to test this would look something like this: WHERE (Cost.Basket = -1 OR [forms]![multi query]![check60] = 0) AND . . . This construction is not immediately intuitive, but I believe it is correct and is fairly minimal. If the Basket column is checked (true), then the test is true, whether or not the Basket on the form is checked. If the Basket on the form is unchecked, the result is true whether the column in the table is true or not. The only time this will exclude a row is when the column is unchecked and the check box on the form IS checked. Isn't that just what you want? By the way, the value of TRUE is -1 for Access Jet databases, with respect to both columns in tables and checkboxes on forms. Please let me know if this helped, and if I can be of any other assistance. Tom Ellison "Chris W via AccessMonster.com" u12677@uwe wrote in message news:55b37e8318c02@uwe... Hi all I have a problem with integrating some code I received via a previous post for linking a check box to a max.date function: this is the max date function code: (note: this is only the WHERE statement from the SQL code as the full code is very long and not all together necessary, when ever I put it in a post it seems to scare people off) WHERE (((Cost.[Date of Research])=(select max([Date of Research]) from cost where cost.[Plant and Machinery ID]=Transaction.[Plant and Machinery ID]))) OR (((Cost.[Date of Research])=(select max([Date of Research]) from cost where cost.[Plant and Machinery ID]=Transaction.[Plant and Machinery ID])) OR [Forms]![FormName]![CheckBoxName] = True) The problem that I am having now is that it fails to integrate with another feature of the query, which is another check box 'basket'. The function says that when basket = TRUE (on the form) return only the records containing a TRUE basket check box but when 'basket' on the form = false the query is to return all records wether they contain a TRUE value or a FALSE. My code as it is prior to the addition of the second checkbox is, WHERE ((([forms]![multi query]![check60])=False) AND ((Cost.[Date of Research] )=(select max([Date of Research]) from cost where cost.[Plant and Machinery ID]=Transaction.[Plant and Machinery ID]))) OR (((Cost.[Date of Research])= (select max([Date of Research]) from cost where cost.[Plant and Machinery ID] =Transaction.[Plant and Machinery ID])) AND ((Transaction.Basket)=True)) So I need it to operate together when basket is selected only records where basket=true are returned and when basket and the checkbox to disable max. date function is true it returns all date for records where basket = true and so on..for all the combinations of the two check box's I would dearly love to know how to integrate them to create a statement that works, thanks for any help that you can offer I really appreciate it. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200510/1 |
#3
|
|||
|
|||
"Tom Ellison" wrote in message
... Dear Chris: Tom, it's good to see you back here in the ngs!!! -- Ken Snell MS ACCESS MVP |
#4
|
|||
|
|||
Tom
Thank you very much for your reply, I was being assisted by Ken Snell but unfortunately I fear I may have offended him by starting this thread. Both you and he made the same suggestion around how I name the check boxes and fields and as a result I have now changed the name of check60 to ‘basket’ I am aware that TRUE = -1 and FALSE = 0. however when I initially wrote or had assistance to write this code, for some reason true worked when -1 and 0 didn’t, I am not sure why, though no doubt it was due to my own error, so this is just the way I got it to work, not necessarily correct. I have entered in the WHERE statement that you gave me which when compiled looks like this: WHERE ([forms]![multi query]![basket] = False AND Cost.[Date of Research] = (select max([Date of Research]) from cost where cost.[Plant and Machinery ID] = Transaction.[Plant and Machinery ID])) OR (Cost.[Date of Research] = (select max([Date of Research]) from cost where cost.[Plant and Machinery ID] = Transaction.[Plant and Machinery ID]) AND Transaction.Basket = True) This works fine in terms it displays the correct ‘basket’ record. When the ‘basket’ checkbox on the form = true or -1, the query returns only those records that contain a value of true or -1 in their record/table. And conversely when the checkbox ‘basket’ on the form is not selected therefore = False or 0 the query returns all records wether ‘basket’ on the record/table = -1 or 0, true or false. This is perfect Now what I need it to do further to this is that when a second check box is selected it disables the max date function. Let’s call this check box ‘no_max. date’. I don’t know how to put this into the where function so that the basket function still operates exactly like it does now but with the addition of being able to show all costing/valuation records for an individual asset. It should operate so that when ‘basket’ on the form is displayed only assets with basket = -1 in their records are displayed and if no_max.date is selected then all costing information is displayed and via versa when ‘basket on the form = false and no_max.date = true therefore selected it should return all records whither basket on the table or record = true or false and all the costing date for each record. It is the addition of the no_max.date function to the code you offered in your previous post, in a way that has it operating in conjunction with basket function that I really would appreciate your help with. Sorry about the long reply but a I fear that I may not have explained myself properly in previous posts. Tom Ellison wrote: Dear Chris: -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200510/1 |
#5
|
|||
|
|||
Hi ken
Sorry if I offended you in any way I really do appreciate any help that you can offer -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200510/1 |
#6
|
|||
|
|||
I'm not offended :-)
Tom is a better master of SQL than I, so I will let him have first chance at your problem. -- Ken Snell MS ACCESS MVP "Chris W via AccessMonster.com" u12677@uwe wrote in message news:55b4f5f086acb@uwe... Hi ken Sorry if I offended you in any way I really do appreciate any help that you can offer -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200510/1 |
#7
|
|||
|
|||
Thanks mate
-- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200510/1 |
#8
|
|||
|
|||
Dear Ken,
It's also very good to hear from you. Bad news today is, I'm not back in the other sense (MVP). No big surprise there. My enforced absense of about 8 months has taken its toll. Hope to see you all again some time soon! Tom Ellison "Ken Snell [MVP]" wrote in message ... "Tom Ellison" wrote in message ... Dear Chris: Tom, it's good to see you back here in the ngs!!! -- Ken Snell MS ACCESS MVP |
#9
|
|||
|
|||
Ken,
That's a huge complement coming from the likes of you. I hope I can fulfill that to a reasonable degree! Tom "Ken Snell [MVP]" wrote in message ... I'm not offended :-) Tom is a better master of SQL than I, so I will let him have first chance at your problem. -- Ken Snell MS ACCESS MVP "Chris W via AccessMonster.com" u12677@uwe wrote in message news:55b4f5f086acb@uwe... Hi ken Sorry if I offended you in any way I really do appreciate any help that you can offer -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200510/1 |
#10
|
|||
|
|||
Dear Chris:
See responses inserted in your text below: "Chris W via AccessMonster.com" u12677@uwe wrote in message news:55b4ef4ed9b3b@uwe... Tom Thank you very much for your reply, I was being assisted by Ken Snell but unfortunately I fear I may have offended him by starting this thread. Both you and he made the same suggestion around how I name the check boxes and fields and as a result I have now changed the name of check60 to 'basket' I am aware that TRUE = -1 and FALSE = 0. however when I initially wrote or had assistance to write this code, for some reason true worked when -1 and 0 didn't, I am not sure why, though no doubt it was due to my own error, so this is just the way I got it to work, not necessarily correct. I have entered in the WHERE statement that you gave me which when compiled looks like this: WHERE ([forms]![multi query]![basket] = False AND Cost.[Date of Research] = (select max([Date of Research]) from cost where cost.[Plant and Machinery ID] = Transaction.[Plant and Machinery ID])) OR (Cost.[Date of Research] = (select max([Date of Research]) from cost where cost.[Plant and Machinery ID] = Transaction.[Plant and Machinery ID]) AND Transaction.Basket = True) This works fine in terms it displays the correct 'basket' record. When the 'basket' checkbox on the form = true or -1, the query returns only those records that contain a value of true or -1 in their record/table. And conversely when the checkbox 'basket' on the form is not selected therefore = False or 0 the query returns all records wether 'basket' on the record/table = -1 or 0, true or false. This is perfect Now what I need it to do further to this is that when a second check box is selected it disables the max date function. Let's call this check box 'no_max. date'. I don't know how to put this into the where function so that the basket function still operates exactly like it does now but with the addition of being able to show all costing/valuation records for an individual asset. If you will build both these functions as I suggested, the logic will be minimized. If you don't it gets extremely complex. The way I suggested it amounts to a "building block" that can be implemented independently with respect to the coding of your logic. If you don't use the simplest logic possible, then the difficulty of writing logic code will multiply exponentially. It may be working as you have it written, but if the logic is not kept minimal then "extensibility" will suffer. It becomes rather difficult to add more logic - that's the very point. So did you test my initial suggestion? Does it work as you wish? Can you see that this keeps a piece of your logic separate and independent from other logic that needs to be added later? It should operate so that when 'basket' on the form is displayed only assets with basket = -1 in their records are displayed and if no_max.date is selected then all costing information is displayed and via versa when 'basket on the form = false and no_max.date = true therefore selected it should return all records whither basket on the table or record = true or false and all the costing date for each record. It is the addition of the no_max.date function to the code you offered in your previous post, in a way that has it operating in conjunction with basket function that I really would appreciate your help with. As I propose the logic, the no_max date would be written in a way similar to how I wrote the Basket logic. Because of the extreme difficulty of working with logic that is not optimized, my mind rebels at the thought of modifying your existing code in the way you suggest. I believe most experts would agree. You must keep your logic minimal and "logical" - that is, so it can be read easily and it's meaning as clear as possible. Sorry about the long reply but a I fear that I may not have explained myself properly in previous posts. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
More Duplicate E-mails Outlook 2003 | John Smith | General Discussion | 36 | January 8th, 2007 05:36 PM |
Attn: Ken Snell - Displaying chemical structures in Access | Michele | General Discussion | 11 | May 13th, 2005 07:19 PM |
Automatically up date time in a cell | Mark | General Discussion | 5 | May 12th, 2005 12:26 AM |
*Another* OLK 2002 sendmail/SMTP problem | steviegb | General Discussion | 11 | March 23rd, 2005 12:59 AM |
OLE Object- the real question | Michelle | Using Forms | 18 | February 28th, 2005 04:04 AM |