Qry replaces , with > when saved

V

Vyki

I have what i hope is a simple question, maybe something to do with
Access 2003 specifically...and I'm not finding anything useful via
google searching.

I'm writing a query, (its not finished yet/doesn't capture all the
data i need) see below. It seems to
work, I see the data I want to, displayed as I want to see it.

SELECT DISTINCT Location.LocationCode & " TR " & Transect.Transect AS
Location, [tblSpecies.Species Code] AS [Four Letter Code],
tblSpecies.Species AS [Genus Species], Layer.Layer, tblSpecies!Native
& " " & tblSpecies!Perennial AS Lifeform, IIf([Query1.Year]=2007,-1,0)
AS 2007, IIf([Query1.Year]=2008,-1,0) AS 2008, IIf([Query1.Year]
=2009,-1,0) AS 2009, IIf([Query1.Year]=2010,-1,0) AS 2010
FROM (Query1 INNER JOIN (Layer INNER JOIN tblSpecies ON Layer.LayerID
= tblSpecies.[Layer Code]) ON Query1.[Species Code] =
tblSpecies.SpeciesID) INNER JOIN (Location INNER JOIN (Transect INNER
JOIN AddlSpecies ON Transect.TransectID = AddlSpecies.TransectID) ON
Location.LocationID = Transect.Location) ON Query1.TransectID =
AddlSpecies.TransectID;



But after viewing the datasheet view when I return to the sql it looks
a little different ">" substituted for the commas in the IIf
statements. And I receive an error msg when I try to view the
datasheet again " Wrong number of arguments used with function in
query expression 'IIf([Query.1Year]=2007>-1>0' "

SELECT DISTINCT Location.LocationCode & " TR " & Transect.Transect AS
Location, [tblSpecies.Species Code] AS [Four Letter Code],
tblSpecies.Species AS [Genus Species], Layer.Layer, tblSpecies!Native
& " " & tblSpecies!Perennial AS Lifeform, IIf([Query1.Year]=2007>-1>0)
AS 2007, IIf([Query1.Year]=2008>-1>0) AS 2008, IIf([Query1.Year]
=2009>-1>0) AS 2009, IIf([Query1.Year]=2010>-1>0) AS 2010
FROM (Query1 INNER JOIN (Layer INNER JOIN tblSpecies ON Layer.LayerID
= tblSpecies.[Layer Code]) ON Query1.[Species Code] =
tblSpecies.SpeciesID) INNER JOIN (Location INNER JOIN (Transect INNER
JOIN AddlSpecies ON Transect.TransectID = AddlSpecies.TransectID) ON
Location.LocationID = Transect.Location) ON Query1.TransectID =
AddlSpecies.TransectID;

If I replace the ">" with my original commas, it works again, once...

Help? What am I missing here?
 
V

Vyki

I have what i hope is a simple question, maybe something to do with
Access 2003 specifically...and I'm not finding anything useful via
google searching.

I'm writing a query, (its not finished yet/doesn't capture all the
data i need) see below.  It seems to
work, I see the data I want to, displayed as I want to see it.

SELECT DISTINCT Location.LocationCode & " TR " & Transect.Transect AS
Location, [tblSpecies.Species Code] AS [Four Letter Code],
tblSpecies.Species AS [Genus Species], Layer.Layer, tblSpecies!Native
& " " & tblSpecies!Perennial AS Lifeform, IIf([Query1.Year]=2007,-1,0)
AS 2007, IIf([Query1.Year]=2008,-1,0) AS 2008, IIf([Query1.Year]
=2009,-1,0) AS 2009, IIf([Query1.Year]=2010,-1,0) AS 2010
FROM (Query1 INNER JOIN (Layer INNER JOIN tblSpecies ON Layer.LayerID
= tblSpecies.[Layer Code]) ON Query1.[Species Code] =
tblSpecies.SpeciesID) INNER JOIN (Location INNER JOIN (Transect INNER
JOIN AddlSpecies ON Transect.TransectID = AddlSpecies.TransectID) ON
Location.LocationID = Transect.Location) ON Query1.TransectID =
AddlSpecies.TransectID;

But after viewing the datasheet view when I return to the sql it looks
a little different ">" substituted for the commas in the IIf
statements.  And I receive an error msg when I try to view the
datasheet again " Wrong number of arguments used with function in
query expression 'IIf([Query.1Year]=2007>-1>0' "

SELECT DISTINCT Location.LocationCode & " TR " & Transect.Transect AS
Location, [tblSpecies.Species Code] AS [Four Letter Code],
tblSpecies.Species AS [Genus Species], Layer.Layer, tblSpecies!Native
& " " & tblSpecies!Perennial AS Lifeform, IIf([Query1.Year]=2007>-1>0)
AS 2007, IIf([Query1.Year]=2008>-1>0) AS 2008, IIf([Query1.Year]
=2009>-1>0) AS 2009, IIf([Query1.Year]=2010>-1>0) AS 2010
FROM (Query1 INNER JOIN (Layer INNER JOIN tblSpecies ON Layer.LayerID
= tblSpecies.[Layer Code]) ON Query1.[Species Code] =
tblSpecies.SpeciesID) INNER JOIN (Location INNER JOIN (Transect INNER
JOIN AddlSpecies ON Transect.TransectID = AddlSpecies.TransectID) ON
Location.LocationID = Transect.Location) ON Query1.TransectID =
AddlSpecies.TransectID;

If I replace the ">" with my original commas, it works again, once...

Help? What am I missing here?
Click to expand...

Turning off Name AutoCorrect seemed to solve my problem.
vy
 
J

John Spencer

Do you really have a field named Query1.Year? Or is that supposed to be a
field named Year that is in Query1?

If the latter then your IIF should read
IIf([Query1].[Year]=2008,-1,0) AS 2008

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I have what i hope is a simple question, maybe something to do with
Access 2003 specifically...and I'm not finding anything useful via
google searching.

I'm writing a query, (its not finished yet/doesn't capture all the
data i need) see below. It seems to
work, I see the data I want to, displayed as I want to see it.

SELECT DISTINCT Location.LocationCode & " TR " & Transect.Transect AS
Location, [tblSpecies.Species Code] AS [Four Letter Code],
tblSpecies.Species AS [Genus Species], Layer.Layer, tblSpecies!Native
& " " & tblSpecies!Perennial AS Lifeform, IIf([Query1.Year]=2007,-1,0)
AS 2007, IIf([Query1.Year]=2008,-1,0) AS 2008, IIf([Query1.Year]
=2009,-1,0) AS 2009, IIf([Query1.Year]=2010,-1,0) AS 2010
FROM (Query1 INNER JOIN (Layer INNER JOIN tblSpecies ON Layer.LayerID
= tblSpecies.[Layer Code]) ON Query1.[Species Code] =
tblSpecies.SpeciesID) INNER JOIN (Location INNER JOIN (Transect INNER
JOIN AddlSpecies ON Transect.TransectID = AddlSpecies.TransectID) ON
Location.LocationID = Transect.Location) ON Query1.TransectID =
AddlSpecies.TransectID;

But after viewing the datasheet view when I return to the sql it looks
a little different ">" substituted for the commas in the IIf
statements. And I receive an error msg when I try to view the
datasheet again " Wrong number of arguments used with function in
query expression 'IIf([Query.1Year]=2007>-1>0' "

SELECT DISTINCT Location.LocationCode & " TR " & Transect.Transect AS
Location, [tblSpecies.Species Code] AS [Four Letter Code],
tblSpecies.Species AS [Genus Species], Layer.Layer, tblSpecies!Native
& " " & tblSpecies!Perennial AS Lifeform, IIf([Query1.Year]=2007>-1>0)
AS 2007, IIf([Query1.Year]=2008>-1>0) AS 2008, IIf([Query1.Year]
=2009>-1>0) AS 2009, IIf([Query1.Year]=2010>-1>0) AS 2010
FROM (Query1 INNER JOIN (Layer INNER JOIN tblSpecies ON Layer.LayerID
= tblSpecies.[Layer Code]) ON Query1.[Species Code] =
tblSpecies.SpeciesID) INNER JOIN (Location INNER JOIN (Transect INNER
JOIN AddlSpecies ON Transect.TransectID = AddlSpecies.TransectID) ON
Location.LocationID = Transect.Location) ON Query1.TransectID =
AddlSpecies.TransectID;

If I replace the ">" with my original commas, it works again, once...

Help? What am I missing here?
Click to expand...

Turning off Name AutoCorrect seemed to solve my problem.
vy
Click to expand...
 
Top