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

[Chris W via AccessMonster.com]

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.

 

[Tom Ellison]

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

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.

 

[Ken Snell [MVP]]

Dear Chris:

Tom, it's good to see you back here in the ngs!!!

 

[Chris W via AccessMonster.com]

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.

 

[Chris W via AccessMonster.com]

Hi ken

Sorry if I offended you in any way I really do appreciate any help that you
can offer

 

[Ken Snell [MVP]]

I'm not offended

Tom is a better master of SQL than I, so I will let him have first chance at
your problem.

 

[Chris W via AccessMonster.com]

Thanks mate

 

[Tom Ellison]

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

 

[Tom Ellison]

Ken,

That's a huge complement coming from the likes of you. I hope I can fulfill
that to a reasonable degree!

Tom

I'm not offended

Tom is a better master of SQL than I, so I will let him have first chance at
your problem.

 

[Tom Ellison]

Dear Chris:

See responses inserted in your text below:

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.

 

[Ken Snell [MVP]]

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!

 

[Chris W via AccessMonster.com]

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

 

[Chris W via AccessMonster.com]

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.

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

 

[Tom Ellison]

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

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.

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

 

[Chris W via AccessMonster.com]

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

 

[Tom Ellison]

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:

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

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

 

[Chris W via AccessMonster.com]

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

 

[Tom Ellison]

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 are:

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

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

 

[Chris W via AccessMonster.com]

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.

 

[Tom Ellison]

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