Strwhere and 1

[lars7]

Hi,
I'm trying to add a couple of criteria to my Sql statement but with no success so far. I need "left" to be null and "Transfer" to = 0 permanently, but I also have a "strwhere" which is where I can enter criteria into 3 combo boxes.
With the following Sql I am getting a syntax error (missing operator).

If Len(strwhere) Then strwhere = "WHERE" & Mid(strwhere, 4)
' Build the SQL string
strSQL = "SELECT [trust staff].CHP, [trust staff].CHPLocation, [trust staff].PAYNO, [trust staff].[ADDIT PAYNO], [trust staff].FORENAME, [trust staff].SURNAME, [trust staff].START_DATE, [trust staff].[GRADE START], [trust staff].[JD CODE NO], [trust staff].LEFT, [trust staff].TRANSFER, tblahp.group " & _
"FROM [trust staff] " & _
"LEFT JOIN tblahp ON [trust staff].SHORT_GRADE = tblahp.ShortGradegroups " & _
strwhere & "[trust staff].LEFT Is Null AND [trust staff].TRANSFER =0

 

[Remou]

You do not need a Where, you already have one:
[tt] If Len(strwhere) [red] > 0 [/red] Then strwhere = " AND " & Mid(strwhere, 4)[/tt]

Try:
[tt] strSQL = "SELECT <...> " & _
"FROM [trust staff] " & _
"LEFT JOIN tblahp ON [trust staff].SHORT_GRADE = tblahp.ShortGradegroups " & _
"WHERE [trust staff].LEFT Is Null AND [trust staff].TRANSFER =0 " & strwhere[/tt]

 

[lars7]

Hi Remou,
Getting error message with this:

If Len(strwhere) > 0 Then strwhere = "WHERE" & Mid(strwhere, 4)
' Build the SQL string
strSQL = "SELECT [trust staff].CHP, [trust staff].CHPLocation, [trust staff].PAYNO, [trust staff].[ADDIT PAYNO], [trust staff].FORENAME, [trust staff].SURNAME, [trust staff].START_DATE, [trust staff].[GRADE START], [trust staff].[JD CODE NO], [trust staff].LEFT, [trust staff].TRANSFER, tblahp.group " & _
"FROM [trust staff] " & _
"LEFT JOIN tblahp ON [trust staff].SHORT_GRADE = tblahp.ShortGradegroups " & _
"WHERE [trust staff].LEFT Is Null AND [trust staff].TRANSFER =0 " & strwhere

I had to group them since I first posted and added your "where" to it and this works great:

If Len(strwhere) > 0 Then strwhere = "WHERE" & Mid(strwhere, 4)
' Build the SQL string
strSQL = "SELECT [trust staff].CHP, [trust staff].CHPLocation, [trust staff].PAYNO, [trust staff].[ADDIT PAYNO], [trust staff].FORENAME, [trust staff].SURNAME, [trust staff].START_DATE, [trust staff].[GRADE START], [trust staff].[JD CODE NO], [trust staff].LEFT, [trust staff].TRANSFER, tblahp.group " & _
"FROM [trust staff] " & _
"LEFT JOIN tblahp ON [trust staff].SHORT_GRADE = tblahp.ShortGradegroups " & _
"WHERE [trust staff].LEFT Is Null AND [trust staff].TRANSFER =0 " & _
"GROUP BY [trust staff].CHP, [trust staff].CHPLocation, [trust staff].PAYNO, [trust staff].[ADDIT PAYNO], [trust staff].FORENAME, [trust staff].SURNAME, [trust staff].START_DATE, [trust staff].[GRADE START], [trust staff].[JD CODE NO], [trust staff].LEFT, [trust staff].TRANSFER, tblahp.group "

But I cannot fit the "strwhere" into it without getting the error. :-(

 

[lespaul]

if nothing else you need a space here:

If Len(strwhere) > 0 Then strwhere = "WHERE[highlight] [/highlight]" & Mid(strwhere, 4)

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[lars7]

Hi Les,
I tried this but still the same.

If Len(strwhere) > 0 Then strwhere = "WHERE " & Mid(strwhere, 4)
' Build the SQL string
strSQL = "SELECT [trust staff].CHP, [trust staff].CHPLocation, [trust staff].PAYNO, [trust staff].[ADDIT PAYNO], [trust staff].FORENAME, [trust staff].SURNAME, [trust staff].START_DATE, [trust staff].[GRADE START], [trust staff].[JD CODE NO], [trust staff].LEFT, [trust staff].TRANSFER, tblahp.group " & _
"FROM [trust staff] " & _
"LEFT JOIN tblahp ON [trust staff].SHORT_GRADE = tblahp.ShortGradegroups " & _
"WHERE [trust staff].LEFT Is Null AND [trust staff].TRANSFER =0 " & strwhere & _
"GROUP BY [trust staff].CHP, [trust staff].CHPLocation, [trust staff].PAYNO, [trust staff].[ADDIT PAYNO], [trust staff].FORENAME, [trust staff].SURNAME, [trust staff].START_DATE, [trust staff].[GRADE START], [trust staff].[JD CODE NO], [trust staff].LEFT, [trust staff].TRANSFER, tblahp.group

 

[Remou]

I think you missed a bit from my post:
"You do not need a Where, you already have one:
If Len(strwhere) > 0 Then strwhere = " AND " & Mid(strwhere, 4)"

Then you tack the strWhere on the end.

 

[lars7]

Hi Remou
I did miss that, this works fine:

If Len(strwhere) > 0 Then strwhere = " AND " & Mid(strwhere, 4)
' Build the SQL string
strSQL = "SELECT [trust staff].CHP, [trust staff].CHPLocation, [trust staff].PAYNO, [trust staff].[ADDIT PAYNO], [trust staff].FORENAME, [trust staff].SURNAME, [trust staff].START_DATE, [trust staff].[GRADE START], [trust staff].[JD CODE NO], [trust staff].LEFT, [trust staff].TRANSFER, tblahp.group " & _
"FROM [trust staff] " & _
"LEFT JOIN tblahp ON [trust staff].SHORT_GRADE = tblahp.ShortGradegroups " & _
"WHERE [trust staff].LEFT Is Null AND [trust staff].TRANSFER =0 " & strwhere & _
"GROUP BY [trust staff].CHP, [trust staff].CHPLocation, [trust staff].PAYNO, [trust staff].[ADDIT PAYNO], [trust staff].FORENAME, [trust staff].SURNAME, [trust staff].START_DATE, [trust staff].[GRADE START], [trust staff].[JD CODE NO], [trust staff].LEFT, [trust staff].TRANSFER, tblahp.group "

As always thanks for the help.