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

"Tom Ellison" wrote in message
...
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


Good!
--

Ken Snell
MS ACCESS MVP


  #12  
Old October 12th, 2005, 06:53 AM
Chris W via AccessMonster.com
external usenet poster
 
Posts: n/a
Default

WHERE ([forms]![multi query]![basket] = False AND [forms]![multi query]!
[no_max.date] = 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)

AND

([forms]![multi query]![no_max.date] = TRUE AND Cost.[Date of Research] = ((
[Date of Research]) from cost where cost.[Plant and Machinery ID] =
Transaction.[Plant and Machinery ID]) AND Transaction.Basket = True)

Please persevere with me I know that I am requiring a lot of guidence


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200510/1
  #13  
Old October 12th, 2005, 07:08 AM
Chris W via AccessMonster.com
external usenet poster
 
Posts: n/a
Default

sorry the last post should have had this above it, sorry not sure what
happend there


Hi Tom thanks for your response

I completely understand that the logic must be optimised and fluent so that
it can be easily understood. However I think that you may over estimate my
knowledge of SQL data base coding, pretty much none of it seems logical to me
so I apologise for how thick I must seem to someone who clearly has done a
great deal of this.

I have attempted to follow you original response and construct it in the way
that you suggest but I now get a syntax error when I try to save if you could,
though it doesn’t vary a lot from the code that you suggest it simply adds an
element to it for the no_max.date combo box.


Chris W wrote:
WHERE ([forms]![multi query]![basket] = False AND [forms]![multi query]!
[no_max.date] = 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)

AND

([forms]![multi query]![no_max.date] = TRUE AND Cost.[Date of Research] = ((
[Date of Research]) from cost where cost.[Plant and Machinery ID] =
Transaction.[Plant and Machinery ID]) AND Transaction.Basket = True)

Please persevere with me I know that I am requiring a lot of guidence



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

Dear Chris:

I'm back to work here now.

There is a challenge here beyond just knowing how to code for a specific
language and application framework. The challenge is that of logic. A bit
of logic training is invaluable at this point. But much more important is
just the ability to think logically.

Let's start with a description of the problem.

Is it the case that you want to EXCLUDE rows from the query whenever the
row's column Basket is not checked if the "basket" check box on the form is
checked? If the "basket" check box on the form is not checked, then all
rows will be included regardless of the Basket column in the table. Is this
correct? If so, let's write the query to do just this one thing and test
that much. Let the following be the entire filter for your query:

WHERE (Cost.Basket = -1 OR [forms]![multi query]![basket] = 0)

Just a note here. Programmers are strongly advised to use a prefix on
controls. Otherwise, there can be a conflict between the name of a control
and the name of a column in the recordsource of a bound form. Calling your
control "basket" when you have a column "Basket" is just such a case. When
you later reference the control, the software will reference the column
instead in ways that are totally mysterious.

Personally, I do not prefer prefixes. When working with an alphabetical
list of controls this creates difficulty for me. I want to look at controls
sorted by their name knowing what name I have made for them, not by some
standard prefix. I therefore choose to suffix this instead of prefixing it.
I've been consistently glad that I do so, but the principle is the same. Do
not use control names that can duplicate column names. I follow that
necessity rigorously, and strongly recommend you do so also.

Can we call the control BasketChk instead. At this point it is important
that you change your thinking as well, to avoid ambiguity there, too. What
I suggest here is that you NEVER refer to a column as a check box. The
datatype used in columns is not check box, but boolean. Using such
terminology in the newsgroup and in your own mind will avoid confusion.

Perhaps you may think I'm nit picking. There are reasons for my strongly
recommending this. As we discuss the logic you are trying to build, it will
be a great advantage to avoid confusion between us AND within your mind (and
mine!) Constructing complex logic is difficult enough without adding even
the smallest bit of confusion.

I propose to build the additional logic you require one piece at a time on
top of this beginning piece. It is important that this be tested well. One
good test I have found is to reverse the logic to be able to see the rows
that will be excluded. Briefly try this variation:

WHERE NOT (Cost.Basket = -1 OR [forms]![multi query]![BasketChk] = 0)

Observe the total number of rows in the table. Observe the number of rows
returned by the query with each of the two filters above. Do they add up to
the total number of rows in the table? If not, you probably have a problem
with NULLs, and we can adjust for that. Important Note: If there is no
problem with NULLs at this time, there may come a time when there IS a NULL
in this column. If so, we must handle that in the query now, or change the
table design to eliminate that possibility.

Please get back to me on how this is working for you, and any questions you
may have. When we are ready to proceed from this point, we can implement
another independent piece of your logic.

Tom Ellison


"Chris W via AccessMonster.com" u12677@uwe wrote in message
news:55b6d9bc59957@uwe...
sorry the last post should have had this above it, sorry not sure what
happend there


Hi Tom thanks for your response

I completely understand that the logic must be optimised and fluent so

that
it can be easily understood. However I think that you may over estimate my
knowledge of SQL data base coding, pretty much none of it seems logical to

me
so I apologise for how thick I must seem to someone who clearly has done a
great deal of this.

I have attempted to follow you original response and construct it in the

way
that you suggest but I now get a syntax error when I try to save if you

could,
though it doesn't vary a lot from the code that you suggest it simply adds

an
element to it for the no_max.date combo box.


Chris W wrote:
WHERE ([forms]![multi query]![basket] = False AND [forms]![multi query]!
[no_max.date] = 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)

AND

([forms]![multi query]![no_max.date] = TRUE AND Cost.[Date of Research] =

((
[Date of Research]) from cost where cost.[Plant and Machinery ID] =
Transaction.[Plant and Machinery ID]) AND Transaction.Basket = True)

Please persevere with me I know that I am requiring a lot of guidence



--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200510/1



  #15  
Old October 13th, 2005, 12:01 AM
Chris W via AccessMonster.com
external usenet poster
 
Posts: n/a
Default

Hi Tom thanks for sticking with me and guiding me through this

Is it the case that you want to EXCLUDE rows from the query whenever the
row's column Basket is not checked if the "basket" check box on the form is
checked? If the "basket" check box on the form is not checked, then all
rows will be included regardless of the Basket column in the table. Is this
correct?

Bingo

WHERE (Cost.Basket = -1 OR [forms]![multi query]![basket] = 0)


Works a treat, no problem with nulls nor do I really envisage there being one.
The way that the data is entered does not allow nulls.

I therefore choose to suffix this instead of prefixing it.


OK yep I can understand the potential for confusion, therefore [forms]![multi
query]![basket] now = [forms]![multi query]![basketchk]

Thanks again, look forward to hearing from you as soon as it’s convenient
P.s. sorry for the late reply I have just got in to work


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

Dear Chris:

Sounds good now. I think this is progress. One additional note: Recommend
you use "Hungarian Notation" in which the first letter of each word is
capitalized. Do not put spaces between words. This improves readability
while following syntax rules. Recommend "BasketChk" instead of all lower
case.

In a previous post you stated:

[quote]
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.
[end quote]

This could be coded as:

WHERE (Cost.MaxDate = ??? OR [forms]![multi query]![NoMaxDateChk] = 0)

Now I don't know from where you get the value where I used ???. Is this on
a form? If so, I'd recommend you have just one control to do this. On a
form, place a text box MaxDateTxt. If the user places a date in it, it's
the maximum for the MaxDate column in Cost. If not, then any date in
Cost.MaxDate is acceptable. I would code this as follows:

WHERE (Cost.MaxDate = [forms]![multi query]![MaxDateTxt] OR
Nz([forms]![multi query]![MaxDateTxt], "" ""))

The little trick here is to change a null value in the control (which
happens under certain conditions, but looks to the user just like an empty
string, so it should be treated by the programmer the same as an empty
string) to the empty string, then compare to empty string.

The way I write applications, this is not sufficient. There's another case.
What if the MaxDateTxt control has a value in it, but it's not a date? I
would test this before running the query and display a message box. I would
also consider whether there is a safety range of dates, perhaps, say,
today's date or up to one year prior. If the value is a date, but is out of
range, give a different message box. Users mustn't be allowed to guess why
things down work correctly. You have to wake them up!

Please let me know how you want to implement this step if you are having any
difficulty with it. I recommend you get this part working SEPARATELY from
what we've just accomplished, and then combine the two.

After you've tried this, and when we need to proceed to another step of your
project, please explain what it is to be implemented next. This time, I was
able to guess at one, based on what you'd said before, but it is best if you
drive this process according to your needs, rather than having me try to do
that. OK?

Tom Ellison


"Chris W via AccessMonster.com" u12677@uwe wrote in message
news:55bfb23ef7b8f@uwe...
Hi Tom thanks for sticking with me and guiding me through this

Is it the case that you want to EXCLUDE rows from the query whenever the
row's column Basket is not checked if the "basket" check box on the form

is
checked? If the "basket" check box on the form is not checked, then all
rows will be included regardless of the Basket column in the table. Is

this
correct?

Bingo

WHERE (Cost.Basket = -1 OR [forms]![multi query]![basket] = 0)


Works a treat, no problem with nulls nor do I really envisage there being

one.
The way that the data is entered does not allow nulls.

I therefore choose to suffix this instead of prefixing it.


OK yep I can understand the potential for confusion, therefore

[forms]![multi
query]![basket] now = [forms]![multi query]![basketchk]

Thanks again, look forward to hearing from you as soon as it's convenient
P.s. sorry for the late reply I have just got in to work


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200510/1



  #17  
Old October 13th, 2005, 02:12 AM
Chris W via AccessMonster.com
external usenet poster
 
Posts: n/a
Default

Ok this is what I implemented:


WHERE (transaction.Basket = -1 OR [forms]![multi query]![BasketChk] = 0) AND
((Cost.[Date of Research])=(select max([Date of Research]) from cost where
cost.[Plant and Machinery ID]=Transaction.[Plant and Machinery ID]) OR [forms]
![multi query]![NoMaxDateChk] = -1)

I am not quite sure why but it actually works perfectly, beautifully and
superbly

However, looking at this code do you think that I may experience any problems
in the future does it not contain something that it should.

Going on what you suggested their could be issues with incorrect dates being
imputed or not with in a range. The purpose of this feature is to pull the
most recent date from all dates attributable to the record on the transaction
table via the cost table, this could be over any range. The way I have got
around incorrect dates being entered is to have criteria on the entry of the
data, so I don’t really see this as a problem. Also null fields are not going
to be a problem because I have everything as a required field even is N/A is
entered.

Do you foresee any problems with this method, if so how do I get around
having null values in the table, I have all the query criteria fields set to
IS NULL, but that still only returns data when the filed contains data and
the IS NULL refers to the field through which the criteria is being entered
on the form.

Thank you so much for your help, mate it is good to have someone spend the
time, even if it does mean that it get to my boss a bit late


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

Dear Chris:

As you first ran each of the two pieces separately, that is your best chance
to learn how these work. Divide and conquer.

Here's how it works. You have two binary conditions. A binary condition
has two possible values, true or false. All the combinations of two value
each having two possible values is 2 X 2 = 4 combinations. These a

Basket True BasketChk True
Basket True BasketChk False
Basket False BasketChk True
Basket False BasketChk False

You want to include rows in all the above cases except when the checkbox is
checked (BasketChk = True) and the Basket column is false. Correct? So, of
the above combinations, the only one you exclude is row 3 above. Right?

Look at it again. The other 3 rows are the ones wher Basket is False of
BasketChk is True. That is, rows 1, 2, and 4. All but 3. The logic I used
is a very simple way of saying just what you want.

Another way of saying it that may be more logical to you is:

NOT ([forms]![multi query]![BasketChk] = -1 AND transaction.Basket = 0)

That is, any case in which the check box on the form is checked and in which
the column in that row is not checked. There is a distributive property in
logic that says the following two propositions are equivalent:

A OR B
not A AND not B

That is:

The check box on the form is unchecked OR the Basket in the row is checked
NOT (the check box on the form is checked AND the Basket in the row is
unchecked)

Two ways of saying the exact same thing. Clear?

A quick course in logic can be most especially helpful. This is an
"algebra" that many people find difficult. But grasping these fundamentals
is extremely useful when the programming gets going.

The other snippet was the same kind of idea, and implemented similarly.

As you can see, I really do want you to understand the thinking. I intend
to use the simplest logic and create the simplest, clearest, most
MAINTAINABLE code to go into any application.

If you have nulls in a column, you can use Nz() to specify how you want
nulls to be treated. If you want nulls to be treated as though the check
box were not checked (which is exactly how they appear to a user) then have
Nz() convert the nulls to 0.

I'll try to be responsive, but right now it's almost bed time.

You did not cover what portion of the query to work on next. If this much
is clear, perhaps you don't even need any more help. If you do, try to
carefully and explicitly explain the next segment of the logic to implement.

Let me know if this helped. I'll be up for an hour or so yet, and I'll
check back.

Tom Ellison


"Chris W via AccessMonster.com" u12677@uwe wrote in message
news:55c0d5da6f053@uwe...
Ok this is what I implemented:


WHERE (transaction.Basket = -1 OR [forms]![multi query]![BasketChk] = 0)

AND
((Cost.[Date of Research])=(select max([Date of Research]) from cost where
cost.[Plant and Machinery ID]=Transaction.[Plant and Machinery ID]) OR

[forms]
![multi query]![NoMaxDateChk] = -1)

I am not quite sure why but it actually works perfectly, beautifully and
superbly

However, looking at this code do you think that I may experience any

problems
in the future does it not contain something that it should.

Going on what you suggested their could be issues with incorrect dates

being
imputed or not with in a range. The purpose of this feature is to pull the
most recent date from all dates attributable to the record on the

transaction
table via the cost table, this could be over any range. The way I have got
around incorrect dates being entered is to have criteria on the entry of

the
data, so I don't really see this as a problem. Also null fields are not

going
to be a problem because I have everything as a required field even is N/A

is
entered.

Do you foresee any problems with this method, if so how do I get around
having null values in the table, I have all the query criteria fields set

to
IS NULL, but that still only returns data when the filed contains data and
the IS NULL refers to the field through which the criteria is being

entered
on the form.

Thank you so much for your help, mate it is good to have someone spend the
time, even if it does mean that it get to my boss a bit late


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200510/1



  #19  
Old October 13th, 2005, 05:40 AM
Chris W via AccessMonster.com
external usenet poster
 
Posts: n/a
Default

That really does make a lot more sense I hadn’t really though of the WHERE
clause of the SQL as EXCEPT WHERE or as you put it NOT clause, though I
don’t think I am ready for in depth programming.

In terms of the next part of the query I really have it operating the way it
should at the moment and I am becoming extremely restricted for time as this
was only ever meant to be a side project that has progressed to a full blown
obsession.

Having said that the only other thing that I am interested in creating,
though again I don’t even know if this is even possible to do let alone in a
timely fashion.

I am interested in creating a ‘shopping basket’ feature to a query, where the
retuned records from the query are viewed in a cascading form view. Then the
records that the user desires to be reported, (as not all records will be,
regardless of the depth of search criteria) can be selected and then when the
user proceeds to a report only the selected records are reported.

I have made numerous posts, seeking guidance on this issue and have received
no assistance. Do to the lack of interest and the timeliness of the
requirement I had a small play around myself and then gave up.

The problem than I found insurmountable is that I could not develop a unique
checkbox for each record on the cascading form that can be referenced by the
report. Even if this was possible my next challenge was going to be trying to
link the unique check box to the record that is being displayed on the
cascading form and so that a link can be made by the report to that record
and then have that record displayed in a report.

The responses I received to previous posts essentially revolved around
generating a more accurate query to remove unwanted records being returned.
This is a perfectly viable method if it wasn’t that the basis for the
database is to make the finding of specific detail from brief/limited detail
easier (I hope that makes sense). So to make the query even more specific is
not desirable and creates unnecessary fields that no one will use.

Although I did get one that talked about adopting a sub-query but I feel that
there must be an easier way.

So to have a way for the user to self filter the records return and decide on
those that should be required in a report would seem very logical to me and
why it is so hard remains a mystery.

Again I stress I don’t know if this is even possible, if it is I may have to
make the database available to users and provide an updated version down the
track.

For any additional detail that I may not have included in this post please
refer to a previous post “Identifying records from a query to be reported”

If you know of a method of doing it or someone that has, I would appreciate
some guidance.
Thanks for all your help you have really got me out of a sticky spot thanks
again.


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

Dear Chris:

Your next project sounds somewhat interesting.

I believe I am leaning toward having a local table to store the user's
selected check boxes (booleans). This local table would be created or
cleared when the form is entered. It would need whatever columns uniquely
identify the row being selected.

You would clear and repopulate this table when the form is entered, or
whenever the set of rows displayed on the form changes, depending on how the
design functions in this respect.

By using a local table, two different users could create reports on
different computers independently.

Sounds like a moderate challenge to me.

Tom Ellison


"Chris W via AccessMonster.com" u12677@uwe wrote in message
news:55c2a7bd97aae@uwe...
That really does make a lot more sense I hadn't really though of the WHERE
clause of the SQL as EXCEPT WHERE or as you put it NOT clause, though I
don't think I am ready for in depth programming.

In terms of the next part of the query I really have it operating the way

it
should at the moment and I am becoming extremely restricted for time as

this
was only ever meant to be a side project that has progressed to a full

blown
obsession.

Having said that the only other thing that I am interested in creating,
though again I don't even know if this is even possible to do let alone in

a
timely fashion.

I am interested in creating a 'shopping basket' feature to a query, where

the
retuned records from the query are viewed in a cascading form view. Then

the
records that the user desires to be reported, (as not all records will be,
regardless of the depth of search criteria) can be selected and then when

the
user proceeds to a report only the selected records are reported.

I have made numerous posts, seeking guidance on this issue and have

received
no assistance. Do to the lack of interest and the timeliness of the
requirement I had a small play around myself and then gave up.

The problem than I found insurmountable is that I could not develop a

unique
checkbox for each record on the cascading form that can be referenced by

the
report. Even if this was possible my next challenge was going to be trying

to
link the unique check box to the record that is being displayed on the
cascading form and so that a link can be made by the report to that record
and then have that record displayed in a report.

The responses I received to previous posts essentially revolved around
generating a more accurate query to remove unwanted records being

returned.
This is a perfectly viable method if it wasn't that the basis for the
database is to make the finding of specific detail from brief/limited

detail
easier (I hope that makes sense). So to make the query even more specific

is
not desirable and creates unnecessary fields that no one will use.

Although I did get one that talked about adopting a sub-query but I feel

that
there must be an easier way.

So to have a way for the user to self filter the records return and decide

on
those that should be required in a report would seem very logical to me

and
why it is so hard remains a mystery.

Again I stress I don't know if this is even possible, if it is I may have

to
make the database available to users and provide an updated version down

the
track.

For any additional detail that I may not have included in this post please
refer to a previous post "Identifying records from a query to be reported"

If you know of a method of doing it or someone that has, I would

appreciate
some guidance.
Thanks for all your help you have really got me out of a sticky spot

thanks
again.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200510/1



 




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 10:20 AM.


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