access 2007 criteria in query

[cutthroatjess]

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

 

[Jerry Whittle]

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.

 

[cutthroatjess]

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!

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.

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