Creating query from string - NOT clause

[Guest]

Hi Guys,

I am programatically creating a query and the exclusion part ("NOT") of my
WHERE clause is not being added to the query. Is this a glitch? Can it not
be done? Any Ideas?

string: (look for "NOT")
strSQL = strSQL & "WHERE (Fund=[forms]![frmFund].[txtFundName] AND "
strSQL = strSQL & "Report_Year=[Forms]![frmFund]![cboYear] And "
strSQL = strSQL & "Report_Month=[forms]![frmFund]![cboMonth]) "
strSQL = strSQL & "AND NOT (Share_Class is null AND"
strSQL = strSQL & "(Fund_Name = " & """PUTNAM STABLE VALUE FUND""" & " ) "
strSQL = strSQL & "GROUP BY.......

Query created:
SELECT DISTINCTROW tblFund.Acct_Num.....
FROM tblFund

WHERE (((tblFund.Fund)=[forms]![frmFund].[txtFundName])
AND ((tblFund.Report_Year)=[Forms]![frmFund]![cboYear])
AND ((tblFund.Report_Month)=[forms]![frmFund]![cboMonth])
AND (([Share_Class] Is Null And ([Fund_Name]="PUTNAM STABLE VALUE FUND" Or
[Fund_Name]="PUTNAM INDEX"))=False))

GROUP BY ..........

 

[Guest]

Hi Matt,

One thing that I see right away is that you don't have a space at the end of
the line containing "NOT", and you also don't have a space at the start of
the next line. Try adding a space there and post back if it still doesn't
work.

HTH, Ted Allen

 

[Guest]

Hey Ted. the spacing was me just trying to fit onto the post page. But I
checked my typing and reran it and it still drops the "NOT".

Theoritically, it should pick up any word inside the qoutes.. right?

Hi Matt,

One thing that I see right away is that you don't have a space at the end of
the line containing "NOT", and you also don't have a space at the start of
the next line. Try adding a space there and post back if it still doesn't
work.

HTH, Ted Allen

Hi Guys,

I am programatically creating a query and the exclusion part ("NOT") of my
WHERE clause is not being added to the query. Is this a glitch? Can it not
be done? Any Ideas?

string: (look for "NOT")
strSQL = strSQL & "WHERE (Fund=[forms]![frmFund].[txtFundName] AND "
strSQL = strSQL & "Report_Year=[Forms]![frmFund]![cboYear] And "
strSQL = strSQL & "Report_Month=[forms]![frmFund]![cboMonth]) "
strSQL = strSQL & "AND NOT (Share_Class is null AND"
strSQL = strSQL & "(Fund_Name = " & """PUTNAM STABLE VALUE FUND""" & " ) "
strSQL = strSQL & "GROUP BY.......

Query created:
SELECT DISTINCTROW tblFund.Acct_Num.....
FROM tblFund

WHERE (((tblFund.Fund)=[forms]![frmFund].[txtFundName])
AND ((tblFund.Report_Year)=[Forms]![frmFund]![cboYear])
AND ((tblFund.Report_Month)=[forms]![frmFund]![cboMonth])
AND (([Share_Class] Is Null And ([Fund_Name]="PUTNAM STABLE VALUE FUND" Or
[Fund_Name]="PUTNAM INDEX"))=False))

GROUP BY ..........

 

[Guest]

Hi Matt,

I think that Access interprets the query text and will only assign it to a
qdf if it is valid. My guess is that there is some type of syntax error. I
also noticed that it appears that the parenthesis are unbalanced after the
NOT, you may want to check that.

If it still doesn't work, I would suggest stepping through the code and
copying the value of strSQL to a text editor to look at the string value
before it is being passed to the query. That usually helps me spot syntax
problems. If you don't spot anything, post that sql (from strSQL) and maybe
myself or someone else will be able to spot something.

HTH, Ted Allen

Hey Ted. the spacing was me just trying to fit onto the post page. But I
checked my typing and reran it and it still drops the "NOT".

Theoritically, it should pick up any word inside the qoutes.. right?

Hi Matt,

One thing that I see right away is that you don't have a space at the end of
the line containing "NOT", and you also don't have a space at the start of
the next line. Try adding a space there and post back if it still doesn't
work.

HTH, Ted Allen

Hi Guys,

I am programatically creating a query and the exclusion part ("NOT") of my
WHERE clause is not being added to the query. Is this a glitch? Can it not
be done? Any Ideas?

string: (look for "NOT")
strSQL = strSQL & "WHERE (Fund=[forms]![frmFund].[txtFundName] AND "
strSQL = strSQL & "Report_Year=[Forms]![frmFund]![cboYear] And "
strSQL = strSQL & "Report_Month=[forms]![frmFund]![cboMonth]) "
strSQL = strSQL & "AND NOT (Share_Class is null AND"
strSQL = strSQL & "(Fund_Name = " & """PUTNAM STABLE VALUE FUND""" & " ) "
strSQL = strSQL & "GROUP BY.......

Query created:
SELECT DISTINCTROW tblFund.Acct_Num.....
FROM tblFund

WHERE (((tblFund.Fund)=[forms]![frmFund].[txtFundName])
AND ((tblFund.Report_Year)=[Forms]![frmFund]![cboYear])
AND ((tblFund.Report_Month)=[forms]![frmFund]![cboMonth])
AND (([Share_Class] Is Null And ([Fund_Name]="PUTNAM STABLE VALUE FUND" Or
[Fund_Name]="PUTNAM INDEX"))=False))

GROUP BY ..........

 

[Ken Snell [MVP]]

May I suggest that you copy and paste the code *exactly as it is in your
module* so that we are looking at what you're actually using....

The code that you've posted is missing a closing parenthesis for the SQL
statement, it seems, and perhaps there are some other differences between
what you posted and what you're actually trying to use.

--

Ken Snell
<MS ACCESS MVP>

Hey Ted. the spacing was me just trying to fit onto the post page. But I
checked my typing and reran it and it still drops the "NOT".

Theoritically, it should pick up any word inside the qoutes.. right?

Hi Matt,

One thing that I see right away is that you don't have a space at the end of
the line containing "NOT", and you also don't have a space at the start of
the next line. Try adding a space there and post back if it still doesn't
work.

HTH, Ted Allen

Hi Guys,

I am programatically creating a query and the exclusion part ("NOT") of my
WHERE clause is not being added to the query. Is this a glitch? Can it not
be done? Any Ideas?

string: (look for "NOT")
strSQL = strSQL & "WHERE (Fund=[forms]![frmFund].[txtFundName] AND "
strSQL = strSQL & "Report_Year=[Forms]![frmFund]![cboYear] And "
strSQL = strSQL & "Report_Month=[forms]![frmFund]![cboMonth]) "
strSQL = strSQL & "AND NOT (Share_Class is null AND"
strSQL = strSQL & "(Fund_Name = " & """PUTNAM STABLE VALUE FUND""" & " ) "
strSQL = strSQL & "GROUP BY.......

Query created:
SELECT DISTINCTROW tblFund.Acct_Num.....
FROM tblFund

WHERE (((tblFund.Fund)=[forms]![frmFund].[txtFundName])
AND ((tblFund.Report_Year)=[Forms]![frmFund]![cboYear])
AND ((tblFund.Report_Month)=[forms]![frmFund]![cboMonth])
AND (([Share_Class] Is Null And ([Fund_Name]="PUTNAM STABLE VALUE FUND" Or
[Fund_Name]="PUTNAM INDEX"))=False))

GROUP BY ..........

 

[John Viescas]

First, it looks like you're missing a closing paren in your example - but
the query wouldn't save if that were the case.

strSQL = strSQL & "WHERE (Fund=[forms]![frmFund].[txtFundName] AND "
strSQL = strSQL & "Report_Year=[Forms]![frmFund]![cboYear] And "
strSQL = strSQL & "Report_Month=[forms]![frmFund]![cboMonth]) "
strSQL = strSQL & "AND NOT (Share_Class is null AND"
strSQL = strSQL & "(Fund_Name = " & """PUTNAM STABLE VALUE FUND""" & " ) "

... should result in:
WHERE (Fund=[forms]![frmFund].[txtFundName]
AND Report_Year=[Forms]![frmFund]![cboYear]
And Report_Month=[forms]![frmFund]![cboMonth])
AND NOT (Share_Class is null AND (Fund_Name = "PUTNAM STABLE VALUE FUND" )

There's a closing paren missing somewhere.

Do you mean to say:

AND NOT ((Share_Class IS NULL) AND (Fund_Name = "PUTNAM STABLE VALUE FUND"))

??

The equivalent would be:

AND ((Share_Class IS NOT NULL) OR (Fund_Name <> "PUTNAME STABLE VALUE
FUND"))


--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

 

[Guest]

Hi Guys, it's in there and we all overlooked it. Access interpreted the
"NOT" by placing "=False" at the end of the statement. Check it out! I learn
something new everyday.

:


string: (look for "NOT")

strSQL = strSQL & "WHERE (Fund=[forms]![frmFund].[txtFundName] AND "
strSQL = strSQL & "Report_Year=[Forms]![frmFund]![cboYear] And "
strSQL = strSQL & "Report_Month=[forms]![frmFund]![cboMonth]) "
strSQL = strSQL & "AND NOT (Share_Class is null AND"
strSQL = strSQL & "(Fund_Name = " & """PUTNAM STABLE VALUE FUND""" & " ) "
strSQL = strSQL & "GROUP BY.......

Query created:

SELECT DISTINCTROW tblFund.Acct_Num.....
FROM tblFund

WHERE (((tblFund.Fund)=[forms]![frmFund].[txtFundName])
AND ((tblFund.Report_Year)=[Forms]![frmFund]![cboYear])
AND ((tblFund.Report_Month)=[forms]![frmFund]![cboMonth])
AND (([Share_Class] Is Null
And ([Fund_Name]="PUTNAM STABLE VALUE
FUND" Or [Fund_Name]="PUTNAM INDEX"))=False))

GROUP BY ..........

 

[Marshall Barton]

I am programatically creating a query and the exclusion part ("NOT") of my
WHERE clause is not being added to the query. Is this a glitch? Can it not
be done? Any Ideas?

string: (look for "NOT")
strSQL = strSQL & "WHERE (Fund=[forms]![frmFund].[txtFundName] AND "
strSQL = strSQL & "Report_Year=[Forms]![frmFund]![cboYear] And "
strSQL = strSQL & "Report_Month=[forms]![frmFund]![cboMonth]) "
strSQL = strSQL & "AND NOT (Share_Class is null AND"
strSQL = strSQL & "(Fund_Name = " & """PUTNAM STABLE VALUE FUND""" & " ) "
strSQL = strSQL & "GROUP BY.......

Query created:
SELECT DISTINCTROW tblFund.Acct_Num.....
FROM tblFund

WHERE (((tblFund.Fund)=[forms]![frmFund].[txtFundName])
AND ((tblFund.Report_Year)=[Forms]![frmFund]![cboYear])
AND ((tblFund.Report_Month)=[forms]![frmFund]![cboMonth])
AND (([Share_Class] Is Null And ([Fund_Name]="PUTNAM STABLE VALUE FUND" Or
[Fund_Name]="PUTNAM INDEX"))=False))

GROUP BY ..........

It looks to me as if the "created" query is a different
(perhaps old?) than the one you say you're setting it to.
Is it possible that it' is not being set?

OR, maybe you are not posting an exact Copy/Paste of your
code and the resulting SQL statement, so we can't really see
all the details??