Convert Access SQL Query to Excel MS Query

[Jeff Gross]

I have a query that works in Access but when I convert it to Excel, I get
errors. Can someone help me with converting this to MS Query SQL Excel 2003
language?

Here is the original Access coding:

SELECT HS_Incident.[Plant Name], HS_Incident.[Incident Date],
HS_Incident.[Incident Desc], HS_Incident.[Lost Time Case],
HS_Incident.[Report Date]
FROM HS_Incident
WHERE (Datediff('d',[HS_Incident].[Incident Date],date())<=7) and
[HS_Incident].[Lost Time Case] like 'Y%' and [HS_Incident].[Plant Name]
<>'MARKHAM ' or (Datevalue([HS_Incident].[Report Date])>datevalue(date())-7)



Any help would be greatly appreciated.

Jeff

 

[MGFoster]

I have a query that works in Access but when I convert it to Excel, I get
errors. Can someone help me with converting this to MS Query SQL Excel 2003
language?

Here is the original Access coding:

SELECT HS_Incident.[Plant Name], HS_Incident.[Incident Date],
HS_Incident.[Incident Desc], HS_Incident.[Lost Time Case],
HS_Incident.[Report Date]
FROM HS_Incident
WHERE (Datediff('d',[HS_Incident].[Incident Date],date())<=7) and
[HS_Incident].[Lost Time Case] like 'Y%' and [HS_Incident].[Plant Name]
<>'MARKHAM ' or (Datevalue([HS_Incident].[Report Date])>datevalue(date())-7)

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

When using the query function in Excel it is best not to use Access
built-in functions (like DateDiff() & DateValue()). Luckily, you are
doing date math, which can be done w/o the date functions. Like this:

SELECT [Plant Name], [Incident Date], [Incident Desc], [Lost Time Case],

[Report Date]
FROM HS_Incident
WHERE ([Incident Date] - Date() <= 7
AND [Lost Time Case] LIKE 'Y*'
AND [Plant Name] <> 'MARKHAM ')
OR [Report Date] > Date()-7

I'm not sure about your Boolean criteria - OR is usually separate from a
succession of ANDs, or is associated w/ only one of the ANDs. Since I
don't know your true requirements I put parens around the ANDs and left
the OR'd criteria separate.

I changed the LIKE wild-card to the asterisk (*), since that is the
usual JET (Access) wild-card for "one, or more characters." If that
doesn't work change it back to the percent sign (%). Is this an Access
project (.adp) DB? If so you'll probably have to use T-SQL instead of
Access SQL.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

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

iQA/AwUBR8A6lIechKqOuFEgEQI/UwCfSErwMXu+Uk3tBRgp+ck7wJmrB8wAn1xx
KJtRMCp2Z/jqbBhm8h3nE91E
=wvvJ
-----END PGP SIGNATURE-----