Syntax Error in Query

[Doozer1979]

Hello,

I got the 'syntax error' in the following query. The query works fine when i look for items that don't appera in the list on ONE particular day, but when i look for items that don't appear in the list within a given date range, the query falls over


SELECT StationGroup.StationGroupName, Stations.StationName
FROM Stations LEFT JOIN StationGroup ON Stations.StationGroupID = StationGroup.StationGroupID
WHERE
(((Stations.StationID) Not In
(SELECT Station FROM AlertsAM
WHERE
DateOfAction => Forms![frmReports]![cboUncheckedStations] AND <= Forms![frmReports]![cboUncheckedStations2] )))
ORDER BY StationGroup.StationGroupID;


Not sure what i'm doing wrong. Can anyone help?

thanks

 

[jal]

Hello,

I got the 'syntax error' in the following query. The query works fine when i look for items that don't appera in the list on ONE particular day, but when i look for items that don't appear in the list within a given date range, the query falls over


SELECT StationGroup.StationGroupName, Stations.StationName
FROM Stations LEFT JOIN StationGroup ON Stations.StationGroupID = StationGroup.StationGroupID
WHERE
(((Stations.StationID) Not In
(SELECT Station FROM AlertsAM
WHERE
DateOfAction => Forms![frmReports]![cboUncheckedStations] AND <= Forms![frmReports]![cboUncheckedStations2] )))
ORDER BY StationGroup.StationGroupID;


Not sure what i'm doing wrong. Can anyone help?

thanks

Well yes, you're right, clearly the date range area is where the problem is, but since I don't use Access forms, I don't know how to fix it.

Is there a way to get DateOfAction in there twice like this?

DateOfAction => Forms![frmReports]![cboUncheckedStations] AND DateOfAction <= Forms![frmReports]![cboUncheckedStations2]


Or maybe the BETWEEN keyword like this:

DateOfAction BETWEEN Forms![frmReports]![cboUncheckedStations] AND Forms![frmReports]![cboUncheckedStations2]

 

[khawar]

try this

DateOfAction between Forms![frmReports]![cboUncheckedStations] AND Forms![frmReports]![cboUncheckedStations2] )))

 

[Doozer1979]

That work's but now i've struck another problem.

I want the query to return results grouped by Date within the date range that i've sepecified. I tried putting order by dateofaction within the subquery, like below.

SELECT StationGroup.StationGroupName, Stations.StationName
FROM Stations LEFT JOIN StationGroup ON Stations.StationGroupID = StationGroup.StationGroupID
WHERE (((Stations.StationID) Not In (SELECT
StationID, DateofAction FROM AlertsAM WHERE DateOfAction between Forms![frmReports]![cboUncheckedStations] AND Forms![frmReports]![cboUncheckedStations2] ORDER by dateofaction )))
ORDER BY StationGroupName;


but then it pops up with the error "you have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause."

Any ideas?