Need help with Date Query

[Guest]

This is the query that I have
PARAMETERS Forms!frm_low_Test!cboService Text, Forms!frm_low_Test!txtEffective DateTime
SELECT DI.
FROM tbl_delivery_info AS DI INNER JOIN [ SELECT Dest_Unit, SHIPPER, Max(Eff_Date) AS Max_Eff_Dat
FROM tbl_delivery_inf
WHERE (((tbl_delivery_info.Service_Level)=IIf(Forms!frm_low_Test!cboService=" ",(tbl_delivery_info.Service_Level),Forms!frm_low_Test!cboService)) AND ((tbl_delivery_info.Eff_Date)=IIf(IsNull(Forms!frm_low_Test!txtEffective),(tbl_delivery_info.Eff_Date),<= (Forms!frm_low_Test!txtEffective))) AND ((tbl_delivery_info.End_Date) Is Null Or (tbl_delivery_info.End_Date)>Now())
GROUP BY Dest_Unit, SHIPPER]. AS T ON (DI.SHIPPER = T.SHIPPER) AND (DI.Eff_Date = T.Max_Eff_Date) AND (DI.Dest_Unit = T.Dest_Unit)

The query work when I did not have the <= sign in the query...but as soon as I put the <= sign I got the following messag
Syntax error( missing operator ) in query expression. Please help me. I've been stuck on this for a while. Thanks in advance.

 

[MGFoster]

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

Your syntax of the IIf() function is incorrect it should be:

IIf( IsNull(Forms!frm_low_Test!txtEffective),
tbl_delivery_info.Eff_Date <= Forms!frm_low_Test!txtEffective)

Which translates to:

If the value of txtEffective Is Null then return the evaluation of the
expression: Is the Eff_Date of table tbl_delivery_info less than, or
equal to, the value of txtEffective; otherwise, return NULL.

This is obviously a logic error.

Your whole query needs to be re-written. I'm guessing that you want: if
the criteria on the form "frm_low_Test" is null ignore it and get all
"whatever." Correct?

A try at fixing your query:

PARAMETERS Forms!frm_low_Test!cboService Text,
Forms!frm_low_Test!txtEffective DateTime;
SELECT DI.*
FROM tbl_delivery_info AS DI INNER JOIN

[ SELECT Dest_Unit, SHIPPER, Max(Eff_Date) AS Max_Eff_Date
FROM tbl_delivery_info
WHERE (Forms!frm_low_Test!cboService IS NULL
OR (Forms!frm_low_Test!cboService IS NOT NULL
AND Service_Level = Forms!frm_low_Test!cboService))
AND (Forms!frm_low_Test!txtEffective IS NULL
OR (Forms!frm_low_Test!txtEffective IS NOT NULL
AND Eff_Date = Forms!frm_low_Test!txtEffective))
AND ( End_Date Is Null OR End_Date > Now() )
GROUP BY Dest_Unit, SHIPPER]. AS T

ON DI.SHIPPER = T.SHIPPER AND DI.Eff_Date = T.Max_Eff_Date
AND DI.Dest_Unit = T.Dest_Unit

If you have trouble w/ the cboService evaluation you could change it to
this:

(Len(Trim(Forms!frm_low_Test!cboService))=0
OR (Len(Trim(Forms!frm_low_Test!cboService))>0 AND ... etc.

To avoid the "blank" in the ComboBox error, change the property
LimitToList to Yes.

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

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

iQA/AwUBQIg85IechKqOuFEgEQJEDgCg2a42S2ieEULtG+2grKZzMyNG/9AAoNDL
GrjPsdEkL/knEDC6LDBbjIym
=xJOf
-----END PGP SIGNATURE-----

This is the query that I have:
PARAMETERS Forms!frm_low_Test!cboService Text, Forms!frm_low_Test!txtEffective DateTime;
SELECT DI.*
FROM tbl_delivery_info AS DI INNER JOIN [ SELECT Dest_Unit, SHIPPER, Max(Eff_Date) AS Max_Eff_Date
FROM tbl_delivery_info
WHERE (((tbl_delivery_info.Service_Level)=IIf(Forms!frm_low_Test!cboService=" ",(tbl_delivery_info.Service_Level),Forms!frm_low_Test!cboService)) AND ((tbl_delivery_info.Eff_Date)=IIf(IsNull(Forms!frm_low_Test!txtEffective),(tbl_delivery_info.Eff_Date),<= (Forms!frm_low_Test!txtEffective))) AND ((tbl_delivery_info.End_Date) Is Null Or (tbl_delivery_info.End_Date)>Now()))
GROUP BY Dest_Unit, SHIPPER]. AS T ON (DI.SHIPPER = T.SHIPPER) AND (DI.Eff_Date = T.Max_Eff_Date) AND (DI.Dest_Unit = T.Dest_Unit);

The query work when I did not have the <= sign in the query...but as soon as I put the <= sign I got the following message
Syntax error( missing operator ) in query expression. Please help me. I've been stuck on this for a while. Thanks in advance.

 

[MGFoster]

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

Yes. Instead of using the equal sign (=) use the less-than or equal
sign (<=).

.... Eff_Date <= Forms!frm_low_Test!txtEffective ...

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

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

iQA/AwUBQIiHboechKqOuFEgEQJFcwCgtjO6/LMdpHgd1v65oQMJ/dWZ8ssAoN0P
+zelPCxtXe4UtlJGSumcTsxu
=jLgp
-----END PGP SIGNATURE-----

Thanks Meg for giving this a try, but what I really want is all the records in the table that are less than or equal to the paramater date entered(Forms!frm_low_Test!txtEffective). The query without the <= operator returns all records in the table if they enter nothing into the parameter field(Forms!frm_low_Test!txtEffective), else it returns all the records in the table that have the date entered in the parameter(Forms!frm_low_Test!txtEffective). Do you have any idea on how I can achieve the result of getting those records in the database that are less than or equal to the date I enter in the parameter?
----- MGFoster wrote: -----

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

Your syntax of the IIf() function is incorrect it should be:

IIf( IsNull(Forms!frm_low_Test!txtEffective),
tbl_delivery_info.Eff_Date <= Forms!frm_low_Test!txtEffective)

Which translates to:

If the value of txtEffective Is Null then return the evaluation of the
expression: Is the Eff_Date of table tbl_delivery_info less than, or
equal to, the value of txtEffective; otherwise, return NULL.

This is obviously a logic error.

Your whole query needs to be re-written. I'm guessing that you want: if
the criteria on the form "frm_low_Test" is null ignore it and get all
"whatever." Correct?

A try at fixing your query:

PARAMETERS Forms!frm_low_Test!cboService Text,
Forms!frm_low_Test!txtEffective DateTime;
SELECT DI.*
FROM tbl_delivery_info AS DI INNER JOIN

[ SELECT Dest_Unit, SHIPPER, Max(Eff_Date) AS Max_Eff_Date
FROM tbl_delivery_info
WHERE (Forms!frm_low_Test!cboService IS NULL
OR (Forms!frm_low_Test!cboService IS NOT NULL
AND Service_Level = Forms!frm_low_Test!cboService))
AND (Forms!frm_low_Test!txtEffective IS NULL
OR (Forms!frm_low_Test!txtEffective IS NOT NULL
AND Eff_Date = Forms!frm_low_Test!txtEffective))
AND ( End_Date Is Null OR End_Date > Now() )
GROUP BY Dest_Unit, SHIPPER]. AS T

ON DI.SHIPPER = T.SHIPPER AND DI.Eff_Date = T.Max_Eff_Date
AND DI.Dest_Unit = T.Dest_Unit

If you have trouble w/ the cboService evaluation you could change it to
this:

(Len(Trim(Forms!frm_low_Test!cboService))=0
OR (Len(Trim(Forms!frm_low_Test!cboService))>0 AND ... etc.

To avoid the "blank" in the ComboBox error, change the property
LimitToList to Yes.

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

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

iQA/AwUBQIg85IechKqOuFEgEQJEDgCg2a42S2ieEULtG+2grKZzMyNG/9AAoNDL
GrjPsdEkL/knEDC6LDBbjIym
=xJOf
-----END PGP SIGNATURE-----

This is the query that I have:
PARAMETERS Forms!frm_low_Test!cboService Text, Forms!frm_low_Test!txtEffective DateTime;
SELECT DI.*
FROM tbl_delivery_info AS DI INNER JOIN [ SELECT Dest_Unit, SHIPPER, Max(Eff_Date) AS Max_Eff_Date
FROM tbl_delivery_info

The query work when I did not have the <= sign in the query...but as soon as I put the <= sign I got the following message

Syntax error( missing operator ) in query expression. Please help me. I've been stuck on this for a while. Thanks in advance.