Select query populating list box

T

Tony Williams

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
 
T

Tony Williams

Sorry I forgot to say why it didn't work. What happens is this: if I put in
the date ranges then the query ignores the date range and I get all the
records where the names match the text in the text box.
Sorry
Tony
 
J

John Spencer

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 said:
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
Click to expand...
 
T

Tony Williams

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 said:
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 said:
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
Click to expand...
Click to expand...
 
T

Tony Williams

I've tried this but it doesn't work
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] &
"*" )

I've added tblInstitution1.txtlastname to the SELECT statement

Any ideas?
Tony

John Spencer said:
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 said:
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
Click to expand...
Click to expand...
 
J

John Spencer

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
T

Tony Williams

Thanks John. However it is possible that txtInstitution could be null which
is why the where needs to include both. It is unlikely (?) that the txtname
will be null.
Hoe does this change the where clause would it be
Or
tblInstitution1.txtInstitution
Like [Forms]![frmsearch3]![txtname] & "*"
Or
tblInstitution1.txtInstitution
[Forms]![frmsearch3]![txtname] is Null )

Is this getting complicated? I'm not too hot on VBA so I'm not sure how to
code this in VBA
Thanks again for your help
Tony
John Spencer said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
T

Tony Williams

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

If you are searching against the same value in either txtLastName and
txtInstitution (or both) and if at least one of those fields has a value
then the where clause should work for you.

Try it and see. If it gives you the "wrong" result, post back and explain
what you got and what you wanted (expected).

The following modification would let your query run and get results based on
any combination of the three controls on the form being filled in, including
NONE of them being filled in.
-- No criteria entered = all records
-- just start date = all records where expiry date is on or after the date
-- just end date = all records where expiry date in on or before the date
--Both dates = all records where expiry date occurs in the specified range
-- just txtName = all records where txtLastName or txtInstitution start with
txtName
-- three other combinations: definition left as an exercise for the student

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] & "*"
Or
[Forms]![frmsearch3]![txtname] is null)


Tony Williams said:
Thanks John. However it is possible that txtInstitution could be null
which is why the where needs to include both. It is unlikely (?) that the
txtname will be null.
Hoe does this change the where clause would it be
Or
tblInstitution1.txtInstitution
Like [Forms]![frmsearch3]![txtname] & "*"
Or
tblInstitution1.txtInstitution
[Forms]![frmsearch3]![txtname] is Null )

Is this getting complicated? I'm not too hot on VBA so I'm not sure how to
code this in VBA
Thanks again for your help
Tony
John Spencer said:
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 said:
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
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
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

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 said:
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 said:
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 said:
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
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
Click to expand...
Click to expand...
Click to expand...
 
T

Tony Williams

Thanks John worked just fine! Watch the coffee though, the caffeine can play
havoc with your brain :)
Tony
John Spencer said:
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 said:
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 said:
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
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Populating a listbox 10
List box question Part 2! 9
Parameterised query to return all records or those Between two val 2
Characters found at end of sql error-Repost 2
Parameter Criteria from a forms Text box - 2003 - not working 5
Is field between date range on form 2
Union Query Edit 1
SQL problem 9

Top