build an if statement with conditional parameters

D

David Pelizzari

I am attempting to query a table based on two parameters from a form, I have
a date field and a site field. I would like to have the user be able to
input one or the other, or both and query the data. Without any additional
code, I can specify just the date and get all site data back. Here is my
current query code:

SELECT dbo_ISD_Lease.*, dbo_ISD_Lease.epend_dt,
dbo_ISD_Lease.epasset_status, dbo_ISD_Lease.[rStatus*], dbo_ISD_Lease.rSite,
dbo_ISD_Lease.rSite
FROM dbo_ISD_Lease
WHERE (((dbo_ISD_Lease.epend_dt) Like [Forms]![Parameters]![StartDate]) AND
((dbo_ISD_Lease.epasset_status)="Active") AND ((dbo_ISD_Lease.rSite) Like
[Forms]![Parameters]![Site Code] & "*"));

I was thinking I could use an If Else statement, or nested iif, but I got
lost in the code...
 
K

Klatuu

Here is how I do that. I use the Nz function to replace a Null value with *
so that if no entry is made in the text box, it will translate to Like "*";
otherwise, it will translate to the value in the text box.

WHERE dbo_ISD_Lease.epend_dt Like Nz([Forms]![Parameters]![StartDate],*) AND
dbo_ISD_Lease.epasset_status="Active" AND dbo_ISD_Lease.rSite Like
Nz([Forms]![Parameters]![Site Code], *);
 
D

David Pelizzari

Hmm, I copied and pasted the Where statement and got errors, when I attempted
to build the expression using Nz, I get just about the same results, I can
specify a date, and leave the site blank, and get nothing back, if I specify
the date and site, I get the expected data, if I specify the site, I get the
specified data. Does Nz not like working with date fields?

Klatuu said:
Here is how I do that. I use the Nz function to replace a Null value with *
so that if no entry is made in the text box, it will translate to Like "*";
otherwise, it will translate to the value in the text box.

WHERE dbo_ISD_Lease.epend_dt Like Nz([Forms]![Parameters]![StartDate],*) AND
dbo_ISD_Lease.epasset_status="Active" AND dbo_ISD_Lease.rSite Like
Nz([Forms]![Parameters]![Site Code], *);

--
Dave Hargis, Microsoft Access MVP


David Pelizzari said:
I am attempting to query a table based on two parameters from a form, I have
a date field and a site field. I would like to have the user be able to
input one or the other, or both and query the data. Without any additional
code, I can specify just the date and get all site data back. Here is my
current query code:

SELECT dbo_ISD_Lease.*, dbo_ISD_Lease.epend_dt,
dbo_ISD_Lease.epasset_status, dbo_ISD_Lease.[rStatus*], dbo_ISD_Lease.rSite,
dbo_ISD_Lease.rSite
FROM dbo_ISD_Lease
WHERE (((dbo_ISD_Lease.epend_dt) Like [Forms]![Parameters]![StartDate]) AND
((dbo_ISD_Lease.epasset_status)="Active") AND ((dbo_ISD_Lease.rSite) Like
[Forms]![Parameters]![Site Code] & "*"));

I was thinking I could use an If Else statement, or nested iif, but I got
lost in the code...
Click to expand...
Click to expand...
 
K

Klatuu

No, it worked fine when I tested it, including using a date field.
It may be your syntax, but I don't know. I did take out the useless (), but
maybe I didn't get them all or got something wrong in your origninal code.

Here is my code from testing with a date field and a text field:

SELECT tblClient.FirstName, tblClient.MainName, tblClient.AddressL1,
tblClient.EnteredOn
FROM tblClient
WHERE (((tblClient.MainName) Like Nz([forms]![form5]![text2],"*")) AND
((tblClient.EnteredOn) Like Nz([forms]![form5]![text0],"*")));

Here is what I Actually put in the criteria row for the fields:

Like Nz([forms]![form5]![text2],"*")

Like Nz([forms]![form5]![text0],"*")
--
Dave Hargis, Microsoft Access MVP


David Pelizzari said:
Hmm, I copied and pasted the Where statement and got errors, when I attempted
to build the expression using Nz, I get just about the same results, I can
specify a date, and leave the site blank, and get nothing back, if I specify
the date and site, I get the expected data, if I specify the site, I get the
specified data. Does Nz not like working with date fields?

Klatuu said:
Here is how I do that. I use the Nz function to replace a Null value with *
so that if no entry is made in the text box, it will translate to Like "*";
otherwise, it will translate to the value in the text box.

WHERE dbo_ISD_Lease.epend_dt Like Nz([Forms]![Parameters]![StartDate],*) AND
dbo_ISD_Lease.epasset_status="Active" AND dbo_ISD_Lease.rSite Like
Nz([Forms]![Parameters]![Site Code], *);

--
Dave Hargis, Microsoft Access MVP


David Pelizzari said:
I am attempting to query a table based on two parameters from a form, I have
a date field and a site field. I would like to have the user be able to
input one or the other, or both and query the data. Without any additional
code, I can specify just the date and get all site data back. Here is my
current query code:

SELECT dbo_ISD_Lease.*, dbo_ISD_Lease.epend_dt,
dbo_ISD_Lease.epasset_status, dbo_ISD_Lease.[rStatus*], dbo_ISD_Lease.rSite,
dbo_ISD_Lease.rSite
FROM dbo_ISD_Lease
WHERE (((dbo_ISD_Lease.epend_dt) Like [Forms]![Parameters]![StartDate]) AND
((dbo_ISD_Lease.epasset_status)="Active") AND ((dbo_ISD_Lease.rSite) Like
[Forms]![Parameters]![Site Code] & "*"));

I was thinking I could use an If Else statement, or nested iif, but I got
lost in the code...
Click to expand...
Click to expand...
Click to expand...
 
D

David Pelizzari

This is my modified code, no errors, but like I said, I can query with just
the site code or site & date
WHERE (((dbo_ISD_Lease.epend_dt) Like
Nz([Forms]![Parameters]![StartDate],"*")) AND
((dbo_ISD_Lease.epasset_status)="Active") AND
((dbo_ISD_Lease.rSite)=Nz([Forms]![Parameters]![Site Code],"*")));

did I mention I am querying an SQL 2005 server with Access 2007?

Klatuu said:
No, it worked fine when I tested it, including using a date field.
It may be your syntax, but I don't know. I did take out the useless (), but
maybe I didn't get them all or got something wrong in your origninal code.

Here is my code from testing with a date field and a text field:

SELECT tblClient.FirstName, tblClient.MainName, tblClient.AddressL1,
tblClient.EnteredOn
FROM tblClient
WHERE (((tblClient.MainName) Like Nz([forms]![form5]![text2],"*")) AND
((tblClient.EnteredOn) Like Nz([forms]![form5]![text0],"*")));

Here is what I Actually put in the criteria row for the fields:

Like Nz([forms]![form5]![text2],"*")

Like Nz([forms]![form5]![text0],"*")
--
Dave Hargis, Microsoft Access MVP


David Pelizzari said:
Hmm, I copied and pasted the Where statement and got errors, when I attempted
to build the expression using Nz, I get just about the same results, I can
specify a date, and leave the site blank, and get nothing back, if I specify
the date and site, I get the expected data, if I specify the site, I get the
specified data. Does Nz not like working with date fields?

Klatuu said:
Here is how I do that. I use the Nz function to replace a Null value with *
so that if no entry is made in the text box, it will translate to Like "*";
otherwise, it will translate to the value in the text box.

WHERE dbo_ISD_Lease.epend_dt Like Nz([Forms]![Parameters]![StartDate],*) AND
dbo_ISD_Lease.epasset_status="Active" AND dbo_ISD_Lease.rSite Like
Nz([Forms]![Parameters]![Site Code], *);

--
Dave Hargis, Microsoft Access MVP


:

I am attempting to query a table based on two parameters from a form, I have
a date field and a site field. I would like to have the user be able to
input one or the other, or both and query the data. Without any additional
code, I can specify just the date and get all site data back. Here is my
current query code:

SELECT dbo_ISD_Lease.*, dbo_ISD_Lease.epend_dt,
dbo_ISD_Lease.epasset_status, dbo_ISD_Lease.[rStatus*], dbo_ISD_Lease.rSite,
dbo_ISD_Lease.rSite
FROM dbo_ISD_Lease
WHERE (((dbo_ISD_Lease.epend_dt) Like [Forms]![Parameters]![StartDate]) AND
((dbo_ISD_Lease.epasset_status)="Active") AND ((dbo_ISD_Lease.rSite) Like
[Forms]![Parameters]![Site Code] & "*"));

I was thinking I could use an If Else statement, or nested iif, but I got
lost in the code...
Click to expand...
Click to expand...
Click to expand...
 
K

Klatuu

Can you query with just the date?
It should not matter in this case that the database is SQL Server.
--
Dave Hargis, Microsoft Access MVP


David Pelizzari said:
This is my modified code, no errors, but like I said, I can query with just
the site code or site & date
WHERE (((dbo_ISD_Lease.epend_dt) Like
Nz([Forms]![Parameters]![StartDate],"*")) AND
((dbo_ISD_Lease.epasset_status)="Active") AND
((dbo_ISD_Lease.rSite)=Nz([Forms]![Parameters]![Site Code],"*")));

did I mention I am querying an SQL 2005 server with Access 2007?

Klatuu said:
No, it worked fine when I tested it, including using a date field.
It may be your syntax, but I don't know. I did take out the useless (), but
maybe I didn't get them all or got something wrong in your origninal code.

Here is my code from testing with a date field and a text field:

SELECT tblClient.FirstName, tblClient.MainName, tblClient.AddressL1,
tblClient.EnteredOn
FROM tblClient
WHERE (((tblClient.MainName) Like Nz([forms]![form5]![text2],"*")) AND
((tblClient.EnteredOn) Like Nz([forms]![form5]![text0],"*")));

Here is what I Actually put in the criteria row for the fields:

Like Nz([forms]![form5]![text2],"*")

Like Nz([forms]![form5]![text0],"*")
--
Dave Hargis, Microsoft Access MVP


David Pelizzari said:
Hmm, I copied and pasted the Where statement and got errors, when I attempted
to build the expression using Nz, I get just about the same results, I can
specify a date, and leave the site blank, and get nothing back, if I specify
the date and site, I get the expected data, if I specify the site, I get the
specified data. Does Nz not like working with date fields?

:

Here is how I do that. I use the Nz function to replace a Null value with *
so that if no entry is made in the text box, it will translate to Like "*";
otherwise, it will translate to the value in the text box.

WHERE dbo_ISD_Lease.epend_dt Like Nz([Forms]![Parameters]![StartDate],*) AND
dbo_ISD_Lease.epasset_status="Active" AND dbo_ISD_Lease.rSite Like
Nz([Forms]![Parameters]![Site Code], *);

--
Dave Hargis, Microsoft Access MVP


:

I am attempting to query a table based on two parameters from a form, I have
a date field and a site field. I would like to have the user be able to
input one or the other, or both and query the data. Without any additional
code, I can specify just the date and get all site data back. Here is my
current query code:

SELECT dbo_ISD_Lease.*, dbo_ISD_Lease.epend_dt,
dbo_ISD_Lease.epasset_status, dbo_ISD_Lease.[rStatus*], dbo_ISD_Lease.rSite,
dbo_ISD_Lease.rSite
FROM dbo_ISD_Lease
WHERE (((dbo_ISD_Lease.epend_dt) Like [Forms]![Parameters]![StartDate]) AND
((dbo_ISD_Lease.epasset_status)="Active") AND ((dbo_ISD_Lease.rSite) Like
[Forms]![Parameters]![Site Code] & "*"));

I was thinking I could use an If Else statement, or nested iif, but I got
lost in the code...
Click to expand...
Click to expand...
Click to expand...
 
D

David Pelizzari

Yes, I can do just date, so that works, it appears when I try to do a "null"
that it gets unhappy.

Klatuu said:
Can you query with just the date?
It should not matter in this case that the database is SQL Server.
--
Dave Hargis, Microsoft Access MVP


David Pelizzari said:
This is my modified code, no errors, but like I said, I can query with just
the site code or site & date
WHERE (((dbo_ISD_Lease.epend_dt) Like
Nz([Forms]![Parameters]![StartDate],"*")) AND
((dbo_ISD_Lease.epasset_status)="Active") AND
((dbo_ISD_Lease.rSite)=Nz([Forms]![Parameters]![Site Code],"*")));

did I mention I am querying an SQL 2005 server with Access 2007?

Klatuu said:
No, it worked fine when I tested it, including using a date field.
It may be your syntax, but I don't know. I did take out the useless (), but
maybe I didn't get them all or got something wrong in your origninal code.

Here is my code from testing with a date field and a text field:

SELECT tblClient.FirstName, tblClient.MainName, tblClient.AddressL1,
tblClient.EnteredOn
FROM tblClient
WHERE (((tblClient.MainName) Like Nz([forms]![form5]![text2],"*")) AND
((tblClient.EnteredOn) Like Nz([forms]![form5]![text0],"*")));

Here is what I Actually put in the criteria row for the fields:

Like Nz([forms]![form5]![text2],"*")

Like Nz([forms]![form5]![text0],"*")
--
Dave Hargis, Microsoft Access MVP


:

Hmm, I copied and pasted the Where statement and got errors, when I attempted
to build the expression using Nz, I get just about the same results, I can
specify a date, and leave the site blank, and get nothing back, if I specify
the date and site, I get the expected data, if I specify the site, I get the
specified data. Does Nz not like working with date fields?

:

Here is how I do that. I use the Nz function to replace a Null value with *
so that if no entry is made in the text box, it will translate to Like "*";
otherwise, it will translate to the value in the text box.

WHERE dbo_ISD_Lease.epend_dt Like Nz([Forms]![Parameters]![StartDate],*) AND
dbo_ISD_Lease.epasset_status="Active" AND dbo_ISD_Lease.rSite Like
Nz([Forms]![Parameters]![Site Code], *);

--
Dave Hargis, Microsoft Access MVP


:

I am attempting to query a table based on two parameters from a form, I have
a date field and a site field. I would like to have the user be able to
input one or the other, or both and query the data. Without any additional
code, I can specify just the date and get all site data back. Here is my
current query code:

SELECT dbo_ISD_Lease.*, dbo_ISD_Lease.epend_dt,
dbo_ISD_Lease.epasset_status, dbo_ISD_Lease.[rStatus*], dbo_ISD_Lease.rSite,
dbo_ISD_Lease.rSite
FROM dbo_ISD_Lease
WHERE (((dbo_ISD_Lease.epend_dt) Like [Forms]![Parameters]![StartDate]) AND
((dbo_ISD_Lease.epasset_status)="Active") AND ((dbo_ISD_Lease.rSite) Like
[Forms]![Parameters]![Site Code] & "*"));

I was thinking I could use an If Else statement, or nested iif, but I got
lost in the code...
Click to expand...
Click to expand...
Click to expand...
 
K

Klatuu

What do you mean by "try to do a Null"?

--
Dave Hargis, Microsoft Access MVP


David Pelizzari said:
Yes, I can do just date, so that works, it appears when I try to do a "null"
that it gets unhappy.

Klatuu said:
Can you query with just the date?
It should not matter in this case that the database is SQL Server.
--
Dave Hargis, Microsoft Access MVP


David Pelizzari said:
This is my modified code, no errors, but like I said, I can query with just
the site code or site & date
WHERE (((dbo_ISD_Lease.epend_dt) Like
Nz([Forms]![Parameters]![StartDate],"*")) AND
((dbo_ISD_Lease.epasset_status)="Active") AND
((dbo_ISD_Lease.rSite)=Nz([Forms]![Parameters]![Site Code],"*")));

did I mention I am querying an SQL 2005 server with Access 2007?

:

No, it worked fine when I tested it, including using a date field.
It may be your syntax, but I don't know. I did take out the useless (), but
maybe I didn't get them all or got something wrong in your origninal code.

Here is my code from testing with a date field and a text field:

SELECT tblClient.FirstName, tblClient.MainName, tblClient.AddressL1,
tblClient.EnteredOn
FROM tblClient
WHERE (((tblClient.MainName) Like Nz([forms]![form5]![text2],"*")) AND
((tblClient.EnteredOn) Like Nz([forms]![form5]![text0],"*")));

Here is what I Actually put in the criteria row for the fields:

Like Nz([forms]![form5]![text2],"*")

Like Nz([forms]![form5]![text0],"*")
--
Dave Hargis, Microsoft Access MVP


:

Hmm, I copied and pasted the Where statement and got errors, when I attempted
to build the expression using Nz, I get just about the same results, I can
specify a date, and leave the site blank, and get nothing back, if I specify
the date and site, I get the expected data, if I specify the site, I get the
specified data. Does Nz not like working with date fields?

:

Here is how I do that. I use the Nz function to replace a Null value with *
so that if no entry is made in the text box, it will translate to Like "*";
otherwise, it will translate to the value in the text box.

WHERE dbo_ISD_Lease.epend_dt Like Nz([Forms]![Parameters]![StartDate],*) AND
dbo_ISD_Lease.epasset_status="Active" AND dbo_ISD_Lease.rSite Like
Nz([Forms]![Parameters]![Site Code], *);

--
Dave Hargis, Microsoft Access MVP


:

I am attempting to query a table based on two parameters from a form, I have
a date field and a site field. I would like to have the user be able to
input one or the other, or both and query the data. Without any additional
code, I can specify just the date and get all site data back. Here is my
current query code:

SELECT dbo_ISD_Lease.*, dbo_ISD_Lease.epend_dt,
dbo_ISD_Lease.epasset_status, dbo_ISD_Lease.[rStatus*], dbo_ISD_Lease.rSite,
dbo_ISD_Lease.rSite
FROM dbo_ISD_Lease
WHERE (((dbo_ISD_Lease.epend_dt) Like [Forms]![Parameters]![StartDate]) AND
((dbo_ISD_Lease.epasset_status)="Active") AND ((dbo_ISD_Lease.rSite) Like
[Forms]![Parameters]![Site Code] & "*"));

I was thinking I could use an If Else statement, or nested iif, but I got
lost in the code...
Click to expand...
Click to expand...
Click to expand...
 
D

David Pelizzari

sorry, I meant if I leave the date field blank, and I leave the site field
blank, I get nothing

Klatuu said:
What do you mean by "try to do a Null"?

--
Dave Hargis, Microsoft Access MVP


David Pelizzari said:
Yes, I can do just date, so that works, it appears when I try to do a "null"
that it gets unhappy.

Klatuu said:
Can you query with just the date?
It should not matter in this case that the database is SQL Server.
--
Dave Hargis, Microsoft Access MVP


:

This is my modified code, no errors, but like I said, I can query with just
the site code or site & date
WHERE (((dbo_ISD_Lease.epend_dt) Like
Nz([Forms]![Parameters]![StartDate],"*")) AND
((dbo_ISD_Lease.epasset_status)="Active") AND
((dbo_ISD_Lease.rSite)=Nz([Forms]![Parameters]![Site Code],"*")));

did I mention I am querying an SQL 2005 server with Access 2007?

:

No, it worked fine when I tested it, including using a date field.
It may be your syntax, but I don't know. I did take out the useless (), but
maybe I didn't get them all or got something wrong in your origninal code.

Here is my code from testing with a date field and a text field:

SELECT tblClient.FirstName, tblClient.MainName, tblClient.AddressL1,
tblClient.EnteredOn
FROM tblClient
WHERE (((tblClient.MainName) Like Nz([forms]![form5]![text2],"*")) AND
((tblClient.EnteredOn) Like Nz([forms]![form5]![text0],"*")));

Here is what I Actually put in the criteria row for the fields:

Like Nz([forms]![form5]![text2],"*")

Like Nz([forms]![form5]![text0],"*")
--
Dave Hargis, Microsoft Access MVP


:

Hmm, I copied and pasted the Where statement and got errors, when I attempted
to build the expression using Nz, I get just about the same results, I can
specify a date, and leave the site blank, and get nothing back, if I specify
the date and site, I get the expected data, if I specify the site, I get the
specified data. Does Nz not like working with date fields?

:

Here is how I do that. I use the Nz function to replace a Null value with *
so that if no entry is made in the text box, it will translate to Like "*";
otherwise, it will translate to the value in the text box.

WHERE dbo_ISD_Lease.epend_dt Like Nz([Forms]![Parameters]![StartDate],*) AND
dbo_ISD_Lease.epasset_status="Active" AND dbo_ISD_Lease.rSite Like
Nz([Forms]![Parameters]![Site Code], *);

--
Dave Hargis, Microsoft Access MVP


:

I am attempting to query a table based on two parameters from a form, I have
a date field and a site field. I would like to have the user be able to
input one or the other, or both and query the data. Without any additional
code, I can specify just the date and get all site data back. Here is my
current query code:

SELECT dbo_ISD_Lease.*, dbo_ISD_Lease.epend_dt,
dbo_ISD_Lease.epasset_status, dbo_ISD_Lease.[rStatus*], dbo_ISD_Lease.rSite,
dbo_ISD_Lease.rSite
FROM dbo_ISD_Lease
WHERE (((dbo_ISD_Lease.epend_dt) Like [Forms]![Parameters]![StartDate]) AND
((dbo_ISD_Lease.epasset_status)="Active") AND ((dbo_ISD_Lease.rSite) Like
[Forms]![Parameters]![Site Code] & "*"));

I was thinking I could use an If Else statement, or nested iif, but I got
lost in the code...
Click to expand...
Click to expand...
Click to expand...
 
K

Klatuu

I tested it again and it worked with both fields filled in, no fields filled
in, and either of the fields filled in and got the expected results in all
cases. I even added a third condition so it would be more like your
situation.
--
Dave Hargis, Microsoft Access MVP


David Pelizzari said:
sorry, I meant if I leave the date field blank, and I leave the site field
blank, I get nothing

Klatuu said:
What do you mean by "try to do a Null"?

--
Dave Hargis, Microsoft Access MVP


David Pelizzari said:
Yes, I can do just date, so that works, it appears when I try to do a "null"
that it gets unhappy.

:

Can you query with just the date?
It should not matter in this case that the database is SQL Server.
--
Dave Hargis, Microsoft Access MVP


:

This is my modified code, no errors, but like I said, I can query with just
the site code or site & date
WHERE (((dbo_ISD_Lease.epend_dt) Like
Nz([Forms]![Parameters]![StartDate],"*")) AND
((dbo_ISD_Lease.epasset_status)="Active") AND
((dbo_ISD_Lease.rSite)=Nz([Forms]![Parameters]![Site Code],"*")));

did I mention I am querying an SQL 2005 server with Access 2007?

:

No, it worked fine when I tested it, including using a date field.
It may be your syntax, but I don't know. I did take out the useless (), but
maybe I didn't get them all or got something wrong in your origninal code.

Here is my code from testing with a date field and a text field:

SELECT tblClient.FirstName, tblClient.MainName, tblClient.AddressL1,
tblClient.EnteredOn
FROM tblClient
WHERE (((tblClient.MainName) Like Nz([forms]![form5]![text2],"*")) AND
((tblClient.EnteredOn) Like Nz([forms]![form5]![text0],"*")));

Here is what I Actually put in the criteria row for the fields:

Like Nz([forms]![form5]![text2],"*")

Like Nz([forms]![form5]![text0],"*")
--
Dave Hargis, Microsoft Access MVP


:

Hmm, I copied and pasted the Where statement and got errors, when I attempted
to build the expression using Nz, I get just about the same results, I can
specify a date, and leave the site blank, and get nothing back, if I specify
the date and site, I get the expected data, if I specify the site, I get the
specified data. Does Nz not like working with date fields?

:

Here is how I do that. I use the Nz function to replace a Null value with *
so that if no entry is made in the text box, it will translate to Like "*";
otherwise, it will translate to the value in the text box.

WHERE dbo_ISD_Lease.epend_dt Like Nz([Forms]![Parameters]![StartDate],*) AND
dbo_ISD_Lease.epasset_status="Active" AND dbo_ISD_Lease.rSite Like
Nz([Forms]![Parameters]![Site Code], *);

--
Dave Hargis, Microsoft Access MVP


:

I am attempting to query a table based on two parameters from a form, I have
a date field and a site field. I would like to have the user be able to
input one or the other, or both and query the data. Without any additional
code, I can specify just the date and get all site data back. Here is my
current query code:

SELECT dbo_ISD_Lease.*, dbo_ISD_Lease.epend_dt,
dbo_ISD_Lease.epasset_status, dbo_ISD_Lease.[rStatus*], dbo_ISD_Lease.rSite,
dbo_ISD_Lease.rSite
FROM dbo_ISD_Lease
WHERE (((dbo_ISD_Lease.epend_dt) Like [Forms]![Parameters]![StartDate]) AND
((dbo_ISD_Lease.epasset_status)="Active") AND ((dbo_ISD_Lease.rSite) Like
[Forms]![Parameters]![Site Code] & "*"));

I was thinking I could use an If Else statement, or nested iif, but I got
lost in the code...
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Try the following WHERE clause:

WHERE (dbo_ISD_Lease.epend_dt=[Forms]![Parameters]![StartDate]
OR [Forms]![Parameters]![StartDate] Is Null)

AND dbo_ISD_Lease.epasset_status)="Active"

AND (dbo_ISD_Lease.rSite=[Forms]![Parameters]![Site Code]
OR [Forms]![Parameters]![Site Code] is Null)

Note that Access query grid will make this more complex when you save
the query, but it should still work.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


David said:
This is my modified code, no errors, but like I said, I can query with just
the site code or site & date
WHERE (((dbo_ISD_Lease.epend_dt) Like
Nz([Forms]![Parameters]![StartDate],"*")) AND
((dbo_ISD_Lease.epasset_status)="Active") AND
((dbo_ISD_Lease.rSite)=Nz([Forms]![Parameters]![Site Code],"*")));

did I mention I am querying an SQL 2005 server with Access 2007?

Klatuu said:
No, it worked fine when I tested it, including using a date field.
It may be your syntax, but I don't know. I did take out the useless (), but
maybe I didn't get them all or got something wrong in your origninal code.

Here is my code from testing with a date field and a text field:

SELECT tblClient.FirstName, tblClient.MainName, tblClient.AddressL1,
tblClient.EnteredOn
FROM tblClient
WHERE (((tblClient.MainName) Like Nz([forms]![form5]![text2],"*")) AND
((tblClient.EnteredOn) Like Nz([forms]![form5]![text0],"*")));

Here is what I Actually put in the criteria row for the fields:

Like Nz([forms]![form5]![text2],"*")

Like Nz([forms]![form5]![text0],"*")
--
Dave Hargis, Microsoft Access MVP


David Pelizzari said:
Hmm, I copied and pasted the Where statement and got errors, when I attempted
to build the expression using Nz, I get just about the same results, I can
specify a date, and leave the site blank, and get nothing back, if I specify
the date and site, I get the expected data, if I specify the site, I get the
specified data. Does Nz not like working with date fields?

:

Here is how I do that. I use the Nz function to replace a Null value with *
so that if no entry is made in the text box, it will translate to Like "*";
otherwise, it will translate to the value in the text box.

WHERE dbo_ISD_Lease.epend_dt Like Nz([Forms]![Parameters]![StartDate],*) AND
dbo_ISD_Lease.epasset_status="Active" AND dbo_ISD_Lease.rSite Like
Nz([Forms]![Parameters]![Site Code], *);

--
Dave Hargis, Microsoft Access MVP


:

I am attempting to query a table based on two parameters from a form, I have
a date field and a site field. I would like to have the user be able to
input one or the other, or both and query the data. Without any additional
code, I can specify just the date and get all site data back. Here is my
current query code:

SELECT dbo_ISD_Lease.*, dbo_ISD_Lease.epend_dt,
dbo_ISD_Lease.epasset_status, dbo_ISD_Lease.[rStatus*], dbo_ISD_Lease.rSite,
dbo_ISD_Lease.rSite
FROM dbo_ISD_Lease
WHERE (((dbo_ISD_Lease.epend_dt) Like [Forms]![Parameters]![StartDate]) AND
((dbo_ISD_Lease.epasset_status)="Active") AND ((dbo_ISD_Lease.rSite) Like
[Forms]![Parameters]![Site Code] & "*"));

I was thinking I could use an If Else statement, or nested iif, but I got
lost in the code...
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

Changing the parameters of a form 16
Query with count, but what am I counting?! 4
Parameters for query from form 5
crosstab with conditional SUM() 1
optional parameter query 1
Building Complex Criteria for Queries in Access 2007 1
Query using date range entered by user 2
optional paramters to a query 1

Top