Parameters using 'Between Date 1 & Date 2' - Need to use 'Like' too!

  • Thread starter Thread starter Kahuna
  • Start date Start date
K

Kahuna

Hi Folks

I have a parameter query feeding a report. Initially its asks for two dates
between which to source the data for the report:

Between [Enter First Likely Date] And [Enter Second Likely Date]

I'd like this report to show all records if there are no dates entered - had
the code once before but cant seem to get the syntax right now. Seem to
recall it was Like * & ......& * but that cant work with 'Between'.

Any help appreciated.

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

-----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-----
 
Thanks MG, that might just do it. I'll play with it this pm.

Cheers

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

-----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-----
Click to expand...
 
Back
Top