A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Integrating the code for a max.date function with a check box controlling it and



 
 
Thread Tools Display Modes
  #1  
Old October 12th, 2005, 12:44 AM
Chris W via AccessMonster.com
external usenet poster
 
Posts: n/a
Default 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  
Old October 12th, 2005, 02:13 AM
Tom Ellison
external usenet poster
 
Posts: n/a
Default

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  
Old October 12th, 2005, 03:07 AM
Ken Snell [MVP]
external usenet poster
 
Posts: n/a
Default

"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  
Old October 12th, 2005, 03:29 AM
Chris W via AccessMonster.com
external usenet poster
 
Posts: n/a
Default

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  
Old October 12th, 2005, 03:32 AM
Chris W via AccessMonster.com
external usenet poster
 
Posts: n/a
Default

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  
Old October 12th, 2005, 03:52 AM
Ken Snell [MVP]
external usenet poster
 
Posts: n/a
Default

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  
Old October 12th, 2005, 04:05 AM
Chris W via AccessMonster.com
external usenet poster
 
Posts: n/a
Default

Thanks mate


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200510/1
  #8  
Old October 12th, 2005, 04:44 AM
Tom Ellison
external usenet poster
 
Posts: n/a
Default

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  
Old October 12th, 2005, 04:45 AM
Tom Ellison
external usenet poster
 
Posts: n/a
Default

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  
Old October 12th, 2005, 04:55 AM
Tom Ellison
external usenet poster
 
Posts: n/a
Default

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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
More Duplicate E-mails Outlook 2003 John Smith General Discussion 36 January 8th, 2007 06: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 01:59 AM
OLE Object- the real question Michelle Using Forms 18 February 28th, 2005 05:04 AM


All times are GMT +1. The time now is 11:52 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.