Access 2003 query error -1, 0
in [Help]
|
Prev: Text box using DLookup is not updating unless I click on it.
Next: Access 2003 Listbox.AddItem w / 5 Columns Is Incredibly Slow
From: Vyki on 15 Sep 2009 15:17 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 15 Sep 2009 17:01 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 15 Sep 2009 17:15
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 |