Re: Select query populating list box

Tech-Archive recommends: Fix windows errors by optimizing your registry

Thanks John worked just fine! Watch the coffee though, the caffeine can play
havoc with your brain :-)
Tony
"John Spencer" <spencer@xxxxxxxxx> wrote in message
news:e3c06$9gGHA.4404@xxxxxxxxxxxxxxxxxxxxxxx
Suggestions?

Yeah, I should have more coffee.

I reversed the greater than and less than signs. Which meant the code was
trying to find an ExpiryDate that was before the start date and after the
end date. An impossible condition unless you put the later date in the
start and the earlier date in the end control. Here is the corrected
version (I hope). Also I just posted a response to your earlier post with
another modification to it and I copied and pasted, so it has the same
error in the <= and >= signs.

SELECT ...
FROM ...
WHERE
( tbldocument.txtExpirydate >=[forms]![frmsearch3]![txtstartdate] OR
[forms]![frmsearch3]![txtstartdate] is Null )
AND
( tbldocument.txtExpirydate <= [forms]![frmsearch3]![txtenddate] OR
[forms]![frmsearch3]![txtenddate] Is Null )
AND ( tblInstitution1.txtlastname
Like [Forms]![frmsearch3]![txtname] & "*"
Or
tblInstitution1.txtInstitution
Like [Forms]![frmsearch3]![txtname] & "*")

"Tony Williams" <tw@xxxxxxxxxxx> wrote in message
news:e447gH9gGHA.1208@xxxxxxxxxxxxxxxxxxxxxxx
John tried your code . It works if I put say H* in textbox, gives me all
Txtnames and txtInstitution starting with H but when I put a date range
in the two date boxes I get no records and I have chosen a date range
where there should be.
Any ideas?
Tony
"John Spencer" <spencer4@xxxxxxxxxxx> wrote in message
news:447BA165.78BE262A@xxxxxxxxxxxxxx
Add one more set of parentheses

SELECT ...
FROM ...
WHERE
( tbldocument.txtExpirydate <=[forms]![frmsearch3]![txtstartdate] OR
[forms]![frmsearch3]![txtstartdate] is Null )
AND
( tbldocument.txtExpirydate >= [forms]![frmsearch3]![txtenddate] OR
[forms]![frmsearch3]![txtenddate] Is Null )
AND ( tblInstitution1.txtlastname
Like [Forms]![frmsearch3]![txtname] & "*"
Or
tblInstitution1.txtInstitution
Like [Forms]![frmsearch3]![txtname] & "*")

Additional assumption is that txtInstitution is never null.

Alert!! Every time you add one more criteria to the where clause,
Access will
redo the criteria and at some point the query will become too complex to
run.

You will reach the point where you will have to build the SQL using vba
(or at
least the where clause.)




Tony Williams wrote:

Thanks John there should never be Nulls but I think the second version
is
probably the safest.
However if you look at my original code the text control needs to
search two
fields txtlastname and txtinstitution (either field as an OR not both
as an
AND). This worked oK until I put the date parameters in. How do I add
the
extra choice to your statement?
This is looking promisiming I've spent 2 days on this so far.
Thanks again
Tony
"John Spencer" <spencer4@xxxxxxxxxxx> wrote in message
news:447B2907.63E9664@xxxxxxxxxxxxxx
IF txtExpirydate ALWAYS has a date value (never Null) And txtLastname
ALWAYS has
a value (never Null). Then you might be able to use the following.

Assumptions:
TxtLastName is never Null
TxtExpiryDate is never null and is within the date range Jan 1, 1900
to
Jan 1, 9999


SELECT tblInstitution1.ID, tbldocument.txtRefNbr,
tbldocument.txtExpirydate,
tblInstitution1.txtInstitution
, [txtFirstname] & " " & [txtlastname] AS Contact
FROM tbldocument LEFT JOIN tblInstitution1
ON tbldocument.txtRefNbr = tblInstitution1.txtRefNbr
WHERE tbldocument.txtExpirydate Between
Nz([forms]![frmsearch3]![txtstartdate],#1/1/1900#)
And NZ([forms]![frmsearch3]![txtenddate], #1/1/9999#)
AND tblInstitution1.txtlastname
Like [Forms]![frmsearch3]![txtname] & "*"


If you are dealing with nulls then there are other ways to modify the
where
clause. The following should work for you. The Parens are important
to
make
sure Access correctly understands the criteria.


SELECT ...
FROM ...
WHERE
( tbldocument.txtExpirydate <=[forms]![frmsearch3]![txtstartdate]
OR
[forms]![frmsearch3]![txtstartdate] is Null )
AND
( tbldocument.txtExpirydate >= [forms]![frmsearch3]![txtenddate]
OR
[forms]![frmsearch3]![txtenddate] Is Null )
AND tblInstitution1.txtlastname
Like [Forms]![frmsearch3]![txtname] & "*"


Access will rearrange the where criteria when you save the query.
Adding
several
additional sets of criteria.

Tony Williams wrote:

I have built a search form that has three unbound controls. On is a
text
control that the user can input the company name or contact and the
other
two are date controls where the user can put in a date range. Here
is the
sql
SELECT tblInstitution1.ID, tbldocument.txtRefNbr,
tbldocument.txtExpirydate,
tblInstitution1.txtInstitution, [txtFirstname] & " " & [txtlastname]
AS
Contact
FROM tbldocument LEFT JOIN tblInstitution1 ON tbldocument.txtRefNbr
=
tblInstitution1.txtRefNbr
WHERE (((tbldocument.txtExpirydate) Between
[forms]![frmsearch3]![txtstartdate] And
[forms]![frmsearch3]![txtenddate])
AND ((tblInstitution1.txtlastname) Like
[Forms]![frmsearch3]![txtname] &
"*")) OR (((tbldocument.txtExpirydate) Between
[forms]![frmsearch3]![txtstartdate] And
[forms]![frmsearch3]![txtenddate])
AND ((tblInstitution1.txtInstitution) Like
[Forms]![frmsearch3]![txtname]
&
"*"));

However I want the user to the ability to search without putting in
a
date
range and changed the sql to this

SELECT tblInstitution1.ID, tbldocument.txtRefNbr,
tbldocument.txtExpirydate,
tblInstitution1.txtInstitution, [txtFirstname] & " " & [txtlastname]
AS
Contact
FROM tbldocument LEFT JOIN tblInstitution1 ON tbldocument.txtRefNbr
=
tblInstitution1.txtRefNbr
WHERE (((tbldocument.txtExpirydate) Between
[forms]![frmsearch3]![txtstartdate] And
[forms]![frmsearch3]![txtenddate])
AND ((tblInstitution1.txtlastname) Like
[Forms]![frmsearch3]![txtname] &
"*")) OR (((tbldocument.txtExpirydate) Between
[forms]![frmsearch3]![txtstartdate] And
[forms]![frmsearch3]![txtenddate])
AND ((tblInstitution1.txtInstitution) Like
[Forms]![frmsearch3]![txtname]
&
"*")) OR (((tblInstitution1.txtInstitution) Like
[Forms]![frmsearch3]![txtname] & "*")) OR
(((tblInstitution1.txtlastname)
Like [Forms]![frmsearch3]![txtname] & "*"));

But this doesn't work it wont show any records with just the name
text
field
showing. Anyone help?
Thanks
Tony






.

Relevant Pages

  • Re: Select query populating list box
    ... Every time you add one more criteria to the where clause, ... However if you look at my original code the text control needs to search ... FROM tbldocument LEFT JOIN tblInstitution1 ON tbldocument.txtRefNbr = ...
    (microsoft.public.access.queries)
  • Re: Select query populating list box
    ... Additional assumption is that txtInstitution is never null. ... redo the criteria and at some point the query will become too complex to run. ... However if you look at my original code the text control needs to search two ... FROM tbldocument LEFT JOIN tblInstitution1 ON tbldocument.txtRefNbr = ...
    (microsoft.public.access.queries)
  • Re: Select query populating list box
    ... Thanks John there should never be Nulls but I think the second version is ... However if you look at my original code the text control needs to search two ... Access will rearrange the where criteria when you save the query. ... FROM tbldocument LEFT JOIN tblInstitution1 ON tbldocument.txtRefNbr = ...
    (microsoft.public.access.queries)
  • Re: Conditional formatting changes field enabled property?
    ... The CF routine can only handle 3 criteria. ... Your conditional formatting VB code almost ... "Stephen Lebans" wrote: ... applied, on a Control by Control basis, based on the criteria YOU set. ...
    (microsoft.public.access.forms)
  • Re: Iterative Technique for PID Controller Tuning
    ... You and Pieter stepped in front of Tim's and Joreg's pot of coffee comment, with your gain tweaking comment. ... Just because you don't have the need to know about better PID tuning techniques does meant that the next guys doesn't need to know more. ... I saw no reason to blame the integrator for any level control problems mentioned by John. ...
    (sci.engr.control)