From: Vyki on
I am new to access/sql and I am trying to write a report for my
supervisor (a botanist) to use in the field.
I'm writing a query, (its not finished yet) 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?
From: MGFoster on
Vyki wrote:
> I am new to access/sql and I am trying to write a report for my
> supervisor (a botanist) to use in the field.
> I'm writing a query, (its not finished yet) 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?


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

Make sure you have the most up-to-date Service Packs (I believe the last
SP was SP3) . Turn off the Name AutoCorrect option (main menu bar:
Tools > Options - under the General tab).

I've had weird problems like this before. What I did - when the query
worked I DID NOT look at the SQL or design grid again, I just saved the
query. I never looked at the design of the query again. That seemed to
prevent the weird changes Access had been making.

You, also, might consider putting a good copy of the SQL into a new
Query and see if the anomaly occurs again. It might be just a corrupt
query. Also, Compact & Repair the DB (main menu bar: Tools > Database
Utilities > Compact & Repair Database).

Google with these parameters:

"ms access 2003" problems

for more info.

HTH,
--
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/AwUBSrAAhYechKqOuFEgEQIFBQCglXVifLatgvFsS7b/vQEnSC74FXsAn1Em
TIanDBCBoSgXUkhYuBpIVUCm
=rq52
-----END PGP SIGNATURE-----
From: Vyki on
On Sep 15, 2:01 pm, MGFoster <m...(a)privacy.com> wrote:
> Vyki wrote:
> > I am new to access/sql and I am trying to write a report for my
> > supervisor (a botanist) to use in the field.
> > I'm writing a query, (its not finished yet) 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?
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Make sure you have the most up-to-date Service Packs (I believe the last
> SP was SP3) .  Turn off the Name AutoCorrect option (main menu bar:
> Tools > Options - under the General tab).
>
> I've had weird problems like this before.  What I did - when the query
> worked I DID NOT look at the SQL or design grid again, I just saved the
> query.  I never looked at the design of the query again.  That seemed to
> prevent the weird changes Access had been making.
>
> You, also, might consider putting a good copy of the SQL into a new
> Query and see if the anomaly occurs again.  It might be just a corrupt
> query.  Also, Compact & Repair the DB (main menu bar:  Tools > Database
> Utilities > Compact & Repair Database).
>
> Google with these parameters:
>
>    "ms access 2003" problems
>
> for more info.
>
> HTH,
> --
> 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/AwUBSrAAhYechKqOuFEgEQIFBQCglXVifLatgvFsS7b/vQEnSC74FXsAn1Em
> TIanDBCBoSgXUkhYuBpIVUCm
> =rq52
> -----END PGP SIGNATURE------ Hide quoted text -
>
> - Show quoted text -

Thanks, turning off Name AutoCorrect seemed to do the trick.
vy