Crosstab qry

[Guest]

Hi:

Is there any way for a crosstab qry to enter a date criteria like: between
now() and now()-14? with dates is working but with now(0 is not working.

Thanks,

Dan

TRANSFORM Sum(IIf([dly_sls_am]<>0,1,Null)) AS Expr1
SELECT ODSDBA_WATF1SIT.NATL_STR_NBR, [CanaComp for holes].[Owner Type],
[CanaComp for holes].[Owner Type], Sum(IIf([dly_sls_am]<>0,1,Null)) AS TOTAL
FROM (ODSDBA_WATF1SIT INNER JOIN ODSDBA_WATS5DLS ON
ODSDBA_WATF1SIT.NATL_STR_NBR = ODSDBA_WATS5DLS.NATL_STR_NBR) INNER JOIN
[CanaComp for holes] ON ODSDBA_WATS5DLS.NATL_STR_NBR = [CanaComp for
holes].[Natl#]
WHERE (((ODSDBA_WATF1SIT.NATL_STR_NBR)>0) AND ((ODSDBA_WATS5DLS.DLY_SLS_DT)
Between Now() And Now()-14) AND ((ODSDBA_WATF1SIT.STR_CLS_DT) Is Null) AND
((ODSDBA_WATF1SIT.SITE_STS)="A") AND ((ODSDBA_WATF1SIT.STR_OPN_DT) Is Not
Null))
GROUP BY ODSDBA_WATF1SIT.NATL_STR_NBR, ODSDBA_WATS5DLS.REST_OWN_TYP,
ODSDBA_WATF1SIT.STR_CLS_DT, ODSDBA_WATF1SIT.SITE_STS,
ODSDBA_WATF1SIT.STR_OPN_DT, [CanaComp for holes].[Owner Type], [CanaComp for
holes].[Owner Type], ODSDBA_WATS5DLS.DLY_SLS_DT
ORDER BY ODSDBA_WATF1SIT.NATL_STR_NBR, ODSDBA_WATF1SIT.STR_CLS_DT DESC
PIVOT ODSDBA_WATS5DLS.DLY_SLS_DT;

 

[Duane Hookom]

Now() should work in the criteria for the crosstab. User-defined
references/criteria need to have their data type defined in the
Query->Parameters dialog.

 

[Guest]

Hi Duane!

Thanks! but this is a liked table from DB2; I get an error: enterd a text in
other data type"

Thanks,

Dan

Now() should work in the criteria for the crosstab. User-defined
references/criteria need to have their data type defined in the
Query->Parameters dialog.

--
Duane Hookom
MS Access MVP

Hi:

Is there any way for a crosstab qry to enter a date criteria like: between
now() and now()-14? with dates is working but with now(0 is not working.

Thanks,

Dan

TRANSFORM Sum(IIf([dly_sls_am]<>0,1,Null)) AS Expr1
SELECT ODSDBA_WATF1SIT.NATL_STR_NBR, [CanaComp for holes].[Owner Type],
[CanaComp for holes].[Owner Type], Sum(IIf([dly_sls_am]<>0,1,Null)) AS
TOTAL
FROM (ODSDBA_WATF1SIT INNER JOIN ODSDBA_WATS5DLS ON
ODSDBA_WATF1SIT.NATL_STR_NBR = ODSDBA_WATS5DLS.NATL_STR_NBR) INNER JOIN
[CanaComp for holes] ON ODSDBA_WATS5DLS.NATL_STR_NBR = [CanaComp for
holes].[Natl#]
WHERE (((ODSDBA_WATF1SIT.NATL_STR_NBR)>0) AND
((ODSDBA_WATS5DLS.DLY_SLS_DT)
Between Now() And Now()-14) AND ((ODSDBA_WATF1SIT.STR_CLS_DT) Is Null) AND
((ODSDBA_WATF1SIT.SITE_STS)="A") AND ((ODSDBA_WATF1SIT.STR_OPN_DT) Is Not
Null))
GROUP BY ODSDBA_WATF1SIT.NATL_STR_NBR, ODSDBA_WATS5DLS.REST_OWN_TYP,
ODSDBA_WATF1SIT.STR_CLS_DT, ODSDBA_WATF1SIT.SITE_STS,
ODSDBA_WATF1SIT.STR_OPN_DT, [CanaComp for holes].[Owner Type], [CanaComp
for
holes].[Owner Type], ODSDBA_WATS5DLS.DLY_SLS_DT
ORDER BY ODSDBA_WATF1SIT.NATL_STR_NBR, ODSDBA_WATF1SIT.STR_CLS_DT DESC
PIVOT ODSDBA_WATS5DLS.DLY_SLS_DT;

 

[Duane Hookom]

I must have missed any early suggestion that these weren't Access tables.
When you view the linked table, is the date value left or right aligned?

--
Duane Hookom
MS Access MVP

Hi Duane!

Thanks! but this is a liked table from DB2; I get an error: enterd a text
in
other data type"

Thanks,

Dan

Now() should work in the criteria for the crosstab. User-defined
references/criteria need to have their data type defined in the
Query->Parameters dialog.

--
Duane Hookom
MS Access MVP

Hi:

Is there any way for a crosstab qry to enter a date criteria like:
between
now() and now()-14? with dates is working but with now(0 is not
working.

Thanks,

Dan

TRANSFORM Sum(IIf([dly_sls_am]<>0,1,Null)) AS Expr1
SELECT ODSDBA_WATF1SIT.NATL_STR_NBR, [CanaComp for holes].[Owner Type],
[CanaComp for holes].[Owner Type], Sum(IIf([dly_sls_am]<>0,1,Null)) AS
TOTAL
FROM (ODSDBA_WATF1SIT INNER JOIN ODSDBA_WATS5DLS ON
ODSDBA_WATF1SIT.NATL_STR_NBR = ODSDBA_WATS5DLS.NATL_STR_NBR) INNER JOIN
[CanaComp for holes] ON ODSDBA_WATS5DLS.NATL_STR_NBR = [CanaComp for
holes].[Natl#]
WHERE (((ODSDBA_WATF1SIT.NATL_STR_NBR)>0) AND
((ODSDBA_WATS5DLS.DLY_SLS_DT)
Between Now() And Now()-14) AND ((ODSDBA_WATF1SIT.STR_CLS_DT) Is Null)
AND
((ODSDBA_WATF1SIT.SITE_STS)="A") AND ((ODSDBA_WATF1SIT.STR_OPN_DT) Is
Not
Null))
GROUP BY ODSDBA_WATF1SIT.NATL_STR_NBR, ODSDBA_WATS5DLS.REST_OWN_TYP,
ODSDBA_WATF1SIT.STR_CLS_DT, ODSDBA_WATF1SIT.SITE_STS,
ODSDBA_WATF1SIT.STR_OPN_DT, [CanaComp for holes].[Owner Type],
[CanaComp
for
holes].[Owner Type], ODSDBA_WATS5DLS.DLY_SLS_DT
ORDER BY ODSDBA_WATF1SIT.NATL_STR_NBR, ODSDBA_WATF1SIT.STR_CLS_DT DESC
PIVOT ODSDBA_WATS5DLS.DLY_SLS_DT;

 

[Salah Omer via AccessMonster.com]

You could try to base the crosstab query on a simple select query that
retrieves records for the desired criteria