If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
access 2007 criteria in query
Greetings,
This has been an odd issue for this query with the given criteria in the WHERE statement: SELECT Parameters.ShortName, Parameters.LongName, Analyses.Value, Stations.ShortName, Samples.SampleDate_D, Samples.LabSampleID FROM Stations INNER JOIN (Samples INNER JOIN ([Parameters] INNER JOIN Analyses ON Parameters.ParameterNumber = Analyses.ParameterNumber) ON Samples.SampleNumber = Analyses.SampleNumber) ON Stations.StationNumber = Samples.StationNumber WHERE parameters.shortname = "tds" and stations.shortname like "sw*" and stations.shortname not like "*-*" and stations.shortname not like "*_*" and samples.sampledate_d #1/1/2004# and samples.labsampleid "tds"; If I try to add the criteria in design view, Access gives a syntax error. But when I add the criteria in SQL view it works fine. This query was originally designed in Access 2003. I converted the database, and it still has the problem. The date criteria ends up working fine, but as soon as I add text criteria it fails, and it doesn't matter which text criteria or what order I add it. In fact, if I open this SQL statement in design view and remove one of the text criteria it will fail. Other queries seem to work fine. The only difference appears to be that this query seems to indicate in design view that the joins are all one-many (by a 1 to infinity sign). The error states: Syntax error in query expression '(the WHERE expression above)'. Any ideas? Thanks! Jesse |
#2
|
|||
|
|||
access 2007 criteria in query
I wonder about the Like clauses. Maybe something like this instead:
AND InStr(NZ([stations].[shortname] ,0), "-") = 0 AND InStr(NZ([stations].[shortname] ,0), "_") = 0 Actually the above might need to be an OR statement: AND (InStr(NZ([stations].[shortname] ,0), "-") = 0 OR InStr(NZ([stations].[shortname] ,0), "_") = 0) I put in the NZs to trap null values. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "cutthroatjess" wrote: Greetings, This has been an odd issue for this query with the given criteria in the WHERE statement: SELECT Parameters.ShortName, Parameters.LongName, Analyses.Value, Stations.ShortName, Samples.SampleDate_D, Samples.LabSampleID FROM Stations INNER JOIN (Samples INNER JOIN ([Parameters] INNER JOIN Analyses ON Parameters.ParameterNumber = Analyses.ParameterNumber) ON Samples.SampleNumber = Analyses.SampleNumber) ON Stations.StationNumber = Samples.StationNumber WHERE parameters.shortname = "tds" and stations.shortname like "sw*" and stations.shortname not like "*-*" and stations.shortname not like "*_*" and samples.sampledate_d #1/1/2004# and samples.labsampleid "tds"; If I try to add the criteria in design view, Access gives a syntax error. But when I add the criteria in SQL view it works fine. This query was originally designed in Access 2003. I converted the database, and it still has the problem. The date criteria ends up working fine, but as soon as I add text criteria it fails, and it doesn't matter which text criteria or what order I add it. In fact, if I open this SQL statement in design view and remove one of the text criteria it will fail. Other queries seem to work fine. The only difference appears to be that this query seems to indicate in design view that the joins are all one-many (by a 1 to infinity sign). The error states: Syntax error in query expression '(the WHERE expression above)'. Any ideas? Thanks! Jesse |
#3
|
|||
|
|||
access 2007 criteria in query
At first I wondered that too, but I took out all the like statements and just
put in the plain text for parameters.shortname = "tds" just by itself and the same thing happened. It's been pretty bizarre! Thanks! "Jerry Whittle" wrote: I wonder about the Like clauses. Maybe something like this instead: AND InStr(NZ([stations].[shortname] ,0), "-") = 0 AND InStr(NZ([stations].[shortname] ,0), "_") = 0 Actually the above might need to be an OR statement: AND (InStr(NZ([stations].[shortname] ,0), "-") = 0 OR InStr(NZ([stations].[shortname] ,0), "_") = 0) I put in the NZs to trap null values. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "cutthroatjess" wrote: Greetings, This has been an odd issue for this query with the given criteria in the WHERE statement: SELECT Parameters.ShortName, Parameters.LongName, Analyses.Value, Stations.ShortName, Samples.SampleDate_D, Samples.LabSampleID FROM Stations INNER JOIN (Samples INNER JOIN ([Parameters] INNER JOIN Analyses ON Parameters.ParameterNumber = Analyses.ParameterNumber) ON Samples.SampleNumber = Analyses.SampleNumber) ON Stations.StationNumber = Samples.StationNumber WHERE parameters.shortname = "tds" and stations.shortname like "sw*" and stations.shortname not like "*-*" and stations.shortname not like "*_*" and samples.sampledate_d #1/1/2004# and samples.labsampleid "tds"; If I try to add the criteria in design view, Access gives a syntax error. But when I add the criteria in SQL view it works fine. This query was originally designed in Access 2003. I converted the database, and it still has the problem. The date criteria ends up working fine, but as soon as I add text criteria it fails, and it doesn't matter which text criteria or what order I add it. In fact, if I open this SQL statement in design view and remove one of the text criteria it will fail. Other queries seem to work fine. The only difference appears to be that this query seems to indicate in design view that the joins are all one-many (by a 1 to infinity sign). The error states: Syntax error in query expression '(the WHERE expression above)'. Any ideas? Thanks! Jesse |
Thread Tools | |
Display Modes | |
|
|