MS Query - Data between dates

[Jeff Gross]

I have the following MS Query inside my spreadsheet:

SELECT `HS_Incident$`.`Plant Name`, `HS_Incident$`.`Incident Desc`,
Format(`HS_Incident$`.`Incident Date`,'dd-mmm-yyyy'),
Format(`HS_Incident$`.`Report Date`,'dd-mmm-yyyy'), `HS_Incident$`.`Lost Time
Case`, `HS_Incident$`.`Incident Status`
FROM `C:\Reports\HS_Incident`.`HS_Incident$` `HS_Incident$`
WHERE (`HS_Incident$`.`Plant Name`<>'MARKHAM ') AND
(`HS_Incident$`.`Incident Status` Not Like 'SUBM%' And
`HS_Incident$`.`Incident Status` Not Like 'SAVE%' And
`HS_Incident$`.`Incident Status` Not Like 'REJE%')
ORDER BY `HS_Incident$`.`Report Date`

I would like to add another criteria that will only pull data from the last
7 days. I tried this:

AND (Datediff('d',[HS_Incident$].[Report Date],date())<=7) but it only
works sometimes. At other times, I get a data type mismatch error.

Does anyone have any ideas?

 

[Jason]

Hi Jeff,

From the SQL syntax I'd guess you're querying an Access database.
I know that RDBMSs like SQLServer allows for straightforward
mathematical operations with dates, without having to use datediff, but
I can't remember if Access does.

Anyway, try adding the following to the where clause:

date() - [HS_Incident$].[Report Date] <= 7

or try adding:

[HS_Incident$].[Report Date] IN
(
select DATE()-1 union
select DATE()-2 union
select DATE()-3 union
select DATE()-4 union
select DATE()-5 union
select DATE()-6 union
select DATE()-7
)

Jason

 

[Jeff Gross]

Thanks for the feedback Jason.

Actually, I'm querying data in an Excel spreadsheet. I was using some code
by a friend of mine who works alot in Access but he said it should work. It
usually does work but all of a sudden it stopped. I'm not sure if the source
(a website dump) has hidden characters causing it to act strange but at any
rate it stopped working.

I tried both of your suggestions but neither will work. The first gives me
the same error and the second says this type of code is not allowed in
subqueries (?).

Any other ideas or where I could go next?

Thanks again.

Hi Jeff,

From the SQL syntax I'd guess you're querying an Access database.
I know that RDBMSs like SQLServer allows for straightforward
mathematical operations with dates, without having to use datediff, but
I can't remember if Access does.

Anyway, try adding the following to the where clause:

date() - [HS_Incident$].[Report Date] <= 7

or try adding:

[HS_Incident$].[Report Date] IN
(
select DATE()-1 union
select DATE()-2 union
select DATE()-3 union
select DATE()-4 union
select DATE()-5 union
select DATE()-6 union
select DATE()-7
)

Jason

I have the following MS Query inside my spreadsheet:

SELECT `HS_Incident$`.`Plant Name`, `HS_Incident$`.`Incident Desc`,
Format(`HS_Incident$`.`Incident Date`,'dd-mmm-yyyy'),
Format(`HS_Incident$`.`Report Date`,'dd-mmm-yyyy'), `HS_Incident$`.`Lost Time
Case`, `HS_Incident$`.`Incident Status`
FROM `C:\Reports\HS_Incident`.`HS_Incident$` `HS_Incident$`
WHERE (`HS_Incident$`.`Plant Name`<>'MARKHAM ') AND
(`HS_Incident$`.`Incident Status` Not Like 'SUBM%' And
`HS_Incident$`.`Incident Status` Not Like 'SAVE%' And
`HS_Incident$`.`Incident Status` Not Like 'REJE%')
ORDER BY `HS_Incident$`.`Report Date`

I would like to add another criteria that will only pull data from the last
7 days. I tried this:

AND (Datediff('d',[HS_Incident$].[Report Date],date())<=7) but it only
works sometimes. At other times, I get a data type mismatch error.

Does anyone have any ideas?

 

[Jeff Gross]

A funny thing...

If I change the item from "Report" to "Incident" date, it runs fine. Both
the Report and Incident columns are formatted exactly alike in both the
original source file and the main spreadsheet that is getting queried.

Don't know if that has anything to do with it but it is interesting.

Jeff

Hi Jeff,

From the SQL syntax I'd guess you're querying an Access database.
I know that RDBMSs like SQLServer allows for straightforward
mathematical operations with dates, without having to use datediff, but
I can't remember if Access does.

Anyway, try adding the following to the where clause:

date() - [HS_Incident$].[Report Date] <= 7

or try adding:

[HS_Incident$].[Report Date] IN
(
select DATE()-1 union
select DATE()-2 union
select DATE()-3 union
select DATE()-4 union
select DATE()-5 union
select DATE()-6 union
select DATE()-7
)

Jason

I have the following MS Query inside my spreadsheet:

SELECT `HS_Incident$`.`Plant Name`, `HS_Incident$`.`Incident Desc`,
Format(`HS_Incident$`.`Incident Date`,'dd-mmm-yyyy'),
Format(`HS_Incident$`.`Report Date`,'dd-mmm-yyyy'), `HS_Incident$`.`Lost Time
Case`, `HS_Incident$`.`Incident Status`
FROM `C:\Reports\HS_Incident`.`HS_Incident$` `HS_Incident$`
WHERE (`HS_Incident$`.`Plant Name`<>'MARKHAM ') AND
(`HS_Incident$`.`Incident Status` Not Like 'SUBM%' And
`HS_Incident$`.`Incident Status` Not Like 'SAVE%' And
`HS_Incident$`.`Incident Status` Not Like 'REJE%')
ORDER BY `HS_Incident$`.`Report Date`

I would like to add another criteria that will only pull data from the last
7 days. I tried this:

AND (Datediff('d',[HS_Incident$].[Report Date],date())<=7) but it only
works sometimes. At other times, I get a data type mismatch error.

Does anyone have any ideas?