I have the following command that works to pull data from an Access Database but am having problems creating an automated range:
SELECT
`ProductType`.`TypeName`,
`Entry_Details`.`RecordID`,
`Entry_Details`.`Date`,
`Entry_Details`.`TransInitials`,
`Products`.`ProductDesc`,
`Entry_Details`.`PRodTypeID`,
`Entry_Details`.`ProdID`,
`Entry_Details`.`EntryPrsnID`
`Employees`.`FullName`,
`Employees`.`Status`,
`Employees`.`PrsnID`
FROM
`Entry_Details`,
`Products`,
`Employees`,
`ProductType`
WHERE
(`Entry_Details`.`Date`>= {?BeginDate} AND `Entry_Details`.`Date`<
{?EndDate})
And
`Entry_Details`.`EntryPrsnID`=`Employees`.`PrsnID`
And `Entry_Details`.`ProdID` = `Products`.`ProductID`
And `Products`.`ProdTypeID`=`ProductType`.`TypeID`
I have used this format preceding this type of code when running SQL against an SQL server environment for other reports:
Declare
@start smalldatetime,
@end smalldatetime
declare
@start smalldatetime,
@end smalldatetime
if {?BeginDate}={?EndDate} begin
select
@end = getdate(),
select
@start = dateadd('d',-30,@end)
end
else begin
select
@start = {?BeginDate},
@end = {?EndDate}
end
Then my select statement would apply the date range based on the values for the @start and @end. I get the following error message:
Database Connector Error 'DAO Error Code: 0xc06
Source: DAO.Database
Description: The Microsoft Jet database engine cannot find the input table or query 'declare @start smalldatetime, @end smalldatetime
If #2015-01-19#=#2015-01-19# begin
select
@end = getdate(),
select
@start = dateadd('d',-30,@end)
end
else begin
etc...
Is there a syntax format that works the same and I just don't have that right based on reading from an Access DB? Any suggestions is greatly appreciated.