Re: Follow-up : Parameters using 'Between Date 1 & Date 2' - Need to use 'Like' too!

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: MGFoster (me_at_privacy.com)
Date: 03/10/04 

Date: Wed, 10 Mar 2004 22:12:13 GMT

Kahuna wrote:

> As a follow-up I have tried using the following code
>
> Between (([tblAnom_Register].[anom_date_raised]) Like IIf(IsNull([Enter
> First Possible Date Raised]),"*",[Enter First Possible Date Raised])) And
> (([tblAnom_Register].[anom_date_raised]) Like IIf(IsNull([Enter Second
> Possible Date Raised]),"*",[Enter Second Possible Date Raised]))
>
> Though the syntax seems to be correct the query returns no records with
> theithr blank parameters entered or qualified dates.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You could probably try using the Nz() function. E.g.:

Syntax: Nz(test value, substitute value)

If the "test value" is NULL then the "substitute value" is used.

SQL WHERE clause:

WHERE tblAnom_Register.anom_date_raised BETWEEN Nz([Enter First Date
Raised], #1/1/100#) And Nz([Enter Second Possible Date Raised],
#1/1/9999#)

If the user doesn't enter a date the default dates are substituted. I
used the highest & lowest default date values that can be stored in an
Access db. Therefore, if the user doesn't enter any dates in the
parameter prompts, all records should be shown.

Caveat: Any anom_date_raised that is NULL will not be selected.

HTH,

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQE+SvoechKqOuFEgEQJXawCg71bUANdieBz/ZNQrzwzidc6b2xwAmwR6
KDZhWNUIUe0ZKqBtul/sLA7q
=oDhM
-----END PGP SIGNATURE-----

Relevant Pages

  • Re: [OFFTOPIC] "smack the penguin"
    ... > Hash: SHA1 ... Attitude is no substitute for competence. ... send the line "unsubscribe linux-kernel" in ...
    (Linux-Kernel)
  • Re: delete oldest dates
    ... > Hash: SHA1 ... Be sure to substitute your table name for "Accounts." ...
    (microsoft.public.access.queries)
  • Re: Conversion from int to char
    ... Gary Scott wrote: ... It's an abstract concept...the character variable is a substitute for a ... that while they might then be able to find the syntax, ... There's a lot of different io in fortran. ...
    (comp.lang.fortran)
  • Re: Conversion from int to char
    ... It's an abstract concept...the character variable is a substitute for a ... I don't quite follow execution here. ... that while they might then be able to find the syntax, ... you want them to think of it as internal I/O ...
    (comp.lang.fortran)
  • Re: FAQ 4.41 How can I remove duplicate elements from a list or array?
    ... I can't quite make out this syntax. ... in the hash), the grep will see the result as logic "true"; ... then added to the array. ... The fault is that Perl hash is heavily wieghted with the underlying ...
    (comp.lang.perl.misc)