RE: build an if statement with conditional parameters

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" wrote:

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" wrote:

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" wrote:

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" wrote:

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...

.

Relevant Pages