Date parameter queries

[KHogwood-Thompson]

Hi All,

I am going mad trying to extract the data from this query as the date
parameter does not seem to work!

The SQL is as follows:

SELECT [Job Transactions].[JOB REF], [Job Transactions].TRANSDATE,
Sum(-[VALUE]) AS SALES
FROM [Job Transactions]
WHERE ((([Job Transactions].TRANSDATE) Between [Start date] And [End date])
AND (([Job Transactions].CO)=2) AND (([Job Transactions].SOURCE)="I"))
GROUP BY [Job Transactions].[JOB REF], [Job Transactions].TRANSDATE;

The underlying query [Job Transactions] has the date field in the format of
General Date, the table that this query is based upon has the date as Text
format and so converts it using the following syntax:

TRANSDATE: IIf([JT_DATE]=" ",#01/01/1970#,FOUNDATIONDATE([JT_DATE]))

Running the query using the SQL above does not limit the record to those
between the date parameters. I have used >= and <= instead of the BETWEEN
command and it makes no difference.

Please can someone help??

 

[Dale Fye]

You probably need to declare your parameters. if you right click in the top
portion of the query grid, there is a parameters option in the popup menu.
You can enter your parameters (they must be exactly as indicated in your
query) and their data types. This is the easiest way, or you can just insert
the parameters line in the SQL view (it must be followed by a semi-colon).

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT [Job Transactions].[JOB REF],
[Job Transactions].TRANSDATE,
Sum(-[VALUE]) AS SALES
FROM [Job Transactions]
WHERE [Job Transactions].TRANSDATE Between [Start date] And [End date]
AND [Job Transactions].CO=2
AND [Job Transactions].SOURCE="I"
GROUP BY [Job Transactions].[JOB REF], [Job Transactions].TRANSDATE;

 

[KHogwood-Thompson]

Many thanks Dale,

I had to replace the BETWEEN with >= and <= but it worked fine.

Thanks again!
--
K Hogwood-Thompson

You probably need to declare your parameters. if you right click in the top
portion of the query grid, there is a parameters option in the popup menu.
You can enter your parameters (they must be exactly as indicated in your
query) and their data types. This is the easiest way, or you can just insert
the parameters line in the SQL view (it must be followed by a semi-colon).

PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT [Job Transactions].[JOB REF],
[Job Transactions].TRANSDATE,
Sum(-[VALUE]) AS SALES
FROM [Job Transactions]
WHERE [Job Transactions].TRANSDATE Between [Start date] And [End date]
AND [Job Transactions].CO=2
AND [Job Transactions].SOURCE="I"
GROUP BY [Job Transactions].[JOB REF], [Job Transactions].TRANSDATE;

--
Email address is not valid.
Please reply to newsgroup only.

Hi All,

I am going mad trying to extract the data from this query as the date
parameter does not seem to work!

The SQL is as follows:

SELECT [Job Transactions].[JOB REF], [Job Transactions].TRANSDATE,
Sum(-[VALUE]) AS SALES
FROM [Job Transactions]
WHERE ((([Job Transactions].TRANSDATE) Between [Start date] And [End date])
AND (([Job Transactions].CO)=2) AND (([Job Transactions].SOURCE)="I"))
GROUP BY [Job Transactions].[JOB REF], [Job Transactions].TRANSDATE;

The underlying query [Job Transactions] has the date field in the format of
General Date, the table that this query is based upon has the date as Text
format and so converts it using the following syntax:

TRANSDATE: IIf([JT_DATE]=" ",#01/01/1970#,FOUNDATIONDATE([JT_DATE]))

Running the query using the SQL above does not limit the record to those
between the date parameters. I have used >= and <= instead of the BETWEEN
command and it makes no difference.

Please can someone help??