-
Oct 21st, 2008, 12:03 PM
#1
Thread Starter
Hyperactive Member
[RESOLVED] VB6 - RunTime error 3075: Syntax error - Missing operator in query expression
Gentlemen,
they say more eyes are better than two . Can anyone see if they can see where my syntax error is in the code below:
Code:
Set rsin = Dbs.OpenRecordset("SELECT DISTINCT tblUnclaimed.PassNumber, tblUnclaimed.EmployeeName, EmployeeInfo.Address, EmployeeInfo.City, EmployeeInfo.State, EmployeeInfo.ZIP, EmployeeInfo.L3, EmployeeInfo.L5, tblUnclaimed.Original_Check_Date, tblUnclaimed.Amount_of_Check, EmployeeInfo.Status " _
& "FROM EmployeeInfo RIGHT JOIN tblUnclaimed ON (EmployeeInfo.L1 = tblUnclaimed.L1) AND (EmployeeInfo.Pass_Number = tblUnclaimed.PassNumber) " _
& "WHERE (((EmployeeInfo.Status) Like 'N*') AND ((EmployeeInfo.Union_Code) In (Unioncode) AND ((tblUnclaimed.Status) In ('R','P','N'))) OR (((EmployeeInfo.Union_Code) In (Unioncode) AND ((tblUnclaimed.Status) In ('R','P','N')) AND ((Len([EmployeeInfo].[Status]))=1)) " _
& "AND tblUnclaimed.ReIssued_Check_Date Between #Begindate# And #Enddate# " _
& "ORDER BY EmployeeInfo.L3, EmployeeInfo.L5, tblUnclaimed.PassNumber, tblUnclaimed.Original_Check_Date;")
Any quick response will be appreciated.
Giftx.
-
Oct 21st, 2008, 01:05 PM
#2
Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression
Try doing something like this:
Code:
Dim strSQL As String
strSQL = "SELECT DISTINCT" & vbNewLine
strSQL = strSQL & " tblUnclaimed.PassNumber," & vbNewLine
strSQL = strSQL & " tblUnclaimed.EmployeeName," & vbNewLine
strSQL = strSQL & " EmployeeInfo.Address," & vbNewLine
strSQL = strSQL & " EmployeeInfo.City," & vbNewLine
strSQL = strSQL & " EmployeeInfo.State," & vbNewLine
strSQL = strSQL & " EmployeeInfo.ZIP," & vbNewLine
strSQL = strSQL & " EmployeeInfo.L3," & vbNewLine
strSQL = strSQL & " EmployeeInfo.L5," & vbNewLine
strSQL = strSQL & " tblUnclaimed.Original_Check_Date," & vbNewLine
strSQL = strSQL & " tblUnclaimed.Amount_of_Check," & vbNewLine
strSQL = strSQL & " EmployeeInfo.Status" & vbNewLine
strSQL = strSQL & "From" & vbNewLine
strSQL = strSQL & " EmployeeInfo RIGHT JOIN tblUnclaimed ON (EmployeeInfo.L1 = tblUnclaimed.L1 AND" & vbNewLine
strSQL = strSQL & " EmployeeInfo.Pass_Number = tblUnclaimed.PassNumber)" & vbNewLine
strSQL = strSQL & "Where" & vbNewLine
strSQL = strSQL & " EmployeeInfo.Status) Like 'N*'" & vbNewLine
strSQL = strSQL & "AND EmployeeInfo.Union_Code In (" & Unioncode & ")" & vbNewLine
strSQL = strSQL & "AND (tblUnclaimed.Status In ('R','P','N') OR EmployeeInfo.Union_Code In (" & Unioncode & "))" & vbNewLine
strSQL = strSQL & "AND tblUnclaimed.Status) In ('R','P','N')" & vbNewLine
strSQL = strSQL & "AND Len(EmployeeInfo.Status)=1" & vbNewLine
strSQL = strSQL & "AND tblUnclaimed.ReIssued_Check_Date Between #" & Begindate & "# And #" & Enddate & "#" & vbNewLine
strSQL = strSQL & "ORDER BY" & vbNewLine
strSQL = strSQL & " EmployeeInfo.L3," & vbNewLine
strSQL = strSQL & " EmployeeInfo.L5," & vbNewLine
strSQL = strSQL & " tblUnclaimed.PassNumber," & vbNewLine
strSQL = strSQL & " tblUnclaimed.Original_Check_Date;"
Debug.Print strSQL
Set rsin = Dbs.OpenRecordset(strSQL)
Place break pointer directly on the last line, check if sql looks good before trying to populate recordset - you may even execute it directly in your database.
This way is easy to spot what part of sql has the error.
But to answer your question - I think your problem was 3 variables (Unioncode, Begindate, Enddate) that are embeded in the sql instead of dynamically concatenated.
There are also way too many parenthesis [I think].
Last edited by RhinoBull; Oct 21st, 2008 at 01:10 PM.
-
Oct 21st, 2008, 03:43 PM
#3
Thread Starter
Hyperactive Member
Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression
Hello Rhino,
thanks for your input. Your query executed okay, but when I added a new variable (CheckStatus) to the query, I got the same error. I must confess, I am not really good with placing single/double quotes ("" or '').
Please see if the quotes around the new variables are incorrect.
Code:
strSQL = "SELECT DISTINCT" & vbNewLine
strSQL = strSQL & "tblUnclaimed.PassNumber," & vbNewLine
strSQL = strSQL & "tblUnclaimed.EmployeeName," & vbNewLine
strSQL = strSQL & "EmployeeInfo.Address," & vbNewLine
strSQL = strSQL & "EmployeeInfo.City," & vbNewLine
strSQL = strSQL & "EmployeeInfo.State," & vbNewLine
strSQL = strSQL & "EmployeeInfo.ZIP," & vbNewLine
strSQL = strSQL & "EmployeeInfo.L3," & vbNewLine
strSQL = strSQL & "EmployeeInfo.L5," & vbNewLine
strSQL = strSQL & "tblUnclaimed.ReIssued_Check_Date," & vbNewLine
strSQL = strSQL & "tblUnclaimed.ReIssued_Check_Amount," & vbNewLine
strSQL = strSQL & "EmployeeInfo.Status" & vbNewLine
strSQL = strSQL & "From" & vbNewLine
strSQL = strSQL & "EmployeeInfo RIGHT JOIN tblUnclaimed ON (EmployeeInfo.L1 = tblUnclaimed.L1 AND" & vbNewLine
strSQL = strSQL & "EmployeeInfo.Pass_Number = tblUnclaimed.PassNumber)" & vbNewLine
strSQL = strSQL & "Where" & vbNewLine
strSQL = strSQL & "EmployeeInfo.Status Like 'N*'" & vbNewLine
strSQL = strSQL & "AND EmployeeInfo.Union_Code In (" & Unioncode & ")" & vbNewLine
strSQL = strSQL & "AND (tblUnclaimed.Status In (" & CheckStatus & ") OR EmployeeInfo.Union_Code In (" & Unioncode & "))" & vbNewLine
strSQL = strSQL & "AND tblUnclaimed.Status In (" & CheckStatus & ")" & vbNewLine
strSQL = strSQL & "AND Len(EmployeeInfo.Status)=1" & vbNewLine
strSQL = strSQL & "AND tblUnclaimed.ReIssued_Check_Date Between #" & Begindate & "# And #" & Enddate & "#" & vbNewLine
strSQL = strSQL & "ORDER BY" & vbNewLine
strSQL = strSQL & "tblUnclaimed.PassNumber;"
'Debug.Print strSQL
Set rsin = Dbs.OpenRecordset(strSQL)
Thanks.
Giftx.
-
Oct 21st, 2008, 03:56 PM
#4
Thread Starter
Hyperactive Member
Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression
Mr. RBull,
To be more specific, it is not picking up the new variable I added to the query It is saying Missing operator in query expression:
Code:
tblUnclaimed.status In ()
-
Oct 21st, 2008, 04:12 PM
#5
Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression
Perhaps a missing space?
Code:
strSQL = strSQL & "ORDER BY " & vbNewLine
strSQL = strSQL & "tblUnclaimed.PassNumber;"
-
Oct 21st, 2008, 04:29 PM
#6
Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression
That's not a problem - the NewLine is whitespace, so is a perfectly valid separator (and IMO is better - as it makes the SQL easier to read if you print to debug, etc).
To be more specific, it is not picking up the new variable I added to the query
Have you checked what that variable contains when this part of the code runs?
For it to be empty in the SQL statement, the variable itself must evaluate to an empty string.
-
Oct 21st, 2008, 04:47 PM
#7
Member
Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression
Always remove parens from SQL code when using it in VB. It makes life so much easier.
Those parens around your variables are uneeded. That is most likely the error.
Something like this, I did this while on the phone so forgive...you may need the parens around the Len statements through, try it both ways...you may need em around the R,P,N piece as well...you get the point, remove all of the uneeded ones that T-SQL adds.
Code:
Set rsin = Dbs.OpenRecordset("SELECT DISTINCT tblUnclaimed.PassNumber, tblUnclaimed.EmployeeName, EmployeeInfo.Address, EmployeeInfo.City, EmployeeInfo.State, EmployeeInfo.ZIP, EmployeeInfo.L3, EmployeeInfo.L5, tblUnclaimed.Original_Check_Date, tblUnclaimed.Amount_of_Check, EmployeeInfo.Status " _
& "FROM EmployeeInfo RIGHT JOIN tblUnclaimed ON EmployeeInfo.L1 = tblUnclaimed.L1 AND EmployeeInfo.Pass_Number = tblUnclaimed.PassNumber " _
& "WHERE EmployeeInfo.Status Like 'N*' AND EmployeeInfo.Union_Code In Unioncode AND tblUnclaimed.Status In 'R','P','N' OR EmployeeInfo.Union_Code In Unioncode AND tblUnclaimed.Status In 'R','P','N' AND Len([EmployeeInfo].[Status])=1 " _
& "AND tblUnclaimed.ReIssued_Check_Date Between #Begindate# And #Enddate# " _
& "ORDER BY EmployeeInfo.L3, EmployeeInfo.L5, tblUnclaimed.PassNumber, tblUnclaimed.Original_Check_Date")
Last edited by Beall49; Oct 21st, 2008 at 04:55 PM.
-
Oct 21st, 2008, 05:00 PM
#8
Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression
Providing us the value of CheckStatus should be helpful. Likewise, posting the result of Debug.Print should be of help...
Originally Posted by si_the_geek
That's not a problem - the NewLine is whitespace, so is a perfectly valid separator (and IMO is better - as it makes the SQL easier to read if you print to debug, etc).
Oooppsss... Sorry, didn't read the code carefully and never used vbNewLine when formatting my sqls...
-
Oct 21st, 2008, 05:41 PM
#9
Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression
Originally Posted by Beall49
Those parens around your variables are uneeded. That is most likely the error.
They are needed, they are part of the syntax for IN. I'm not sure if they are needed in the Join clauses, but I always use them as it makes them easier to read.
-
Oct 21st, 2008, 09:03 PM
#10
Lively Member
Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression
What datatypes are:
EmployeeInfo.Union_Code
tblUnclaimed.Status
EmployeeInfo.Status
???
If they are string, then the comparatives MUST be enclosed within single quotes.
If SQL is expecting quoted strings, and you don't have quotes around them, you WILL get that error message you got.
-
Oct 22nd, 2008, 12:00 PM
#11
Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression
That would cause a Type Mismatch error, or something with a similar meaning, so is not the issue at hand (but it may be an issue later!).
The last we heard from the OP was back in post #4 - and the error there was due to the value of CheckStatus not being in the SQL statement.
I suspect that is because of a scope issue, which is not being noticed due to a lack of Option Explicit, but until we hear more we can only make educated guesses.
-
Oct 22nd, 2008, 07:01 PM
#12
Lively Member
Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression
Si,
I just ran a query against a MSAccess database using DAO3.6. The SQL statement has missing single quotes surrounding a comparative.
The error message is "Run time error -2147217904 (80040e10)"
"No value given for one or more parameters"
Which is obviously not the error message he quoted.
I stand corrected.
But notice, the error message is not really meaningful either.
I notice this more with MSAccess rather than SQLServer.
-
Oct 23rd, 2008, 09:29 AM
#13
Thread Starter
Hyperactive Member
Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression
Hi Si,
Below is my complete module. If you look at my variable definitions, Unioncode, Begindate, enddate, the sql is picking up all other variables except "Checkstatus".
Code:
bPath = "\\Livctrls03-08\Data1\APP\UNCLAIMW\UCCTest\Databases\"
DbName = "Unclaimed Checks.mdb"
'Open Database/Recordset:
'------------------------
Set Dbs = OpenDatabase(DbPath & DbName)
'Prompt User to Enter required Values: Union codes and Dates:
'------------------------------------------------------------
Unioncode = vbEmptyString
Begindate = vbEmptyString
Enddate = vbEmptyString
CheckStatus = vbEmptyString
Unioncode = InputBox("Please Enter Union Codes for this report. Enter each value enclosed in single quotes. if you have more than one union code, separate them with commas (,):Example: '01','00'")
If IsNull(Unioncode) Or Unioncode = "" Then
MsgBox "Please Enter valid union code(s), enclosed with Single quotes: ' ' " _
& "If entering more than one union code, separated by commas (,)"
Screen.MousePointer = vbNormal
Exit Sub
End If
Begindate = InputBox("Please Enter the Unclaimed Check ReIssue Begining date range with format mm/dd/yyyy:")
If Not IsDate(Begindate) Or IsNull(Begindate) Then
MsgBox "Please Enter valid date with format mm/dd/yyyy", vbInformation, "Unclaimed Checks System"
Screen.MousePointer = vbNormal
Exit Sub
End If
Enddate = InputBox("Please Enter the Unclaimed Check ReIssue Ending date range with format mm/dd/yyyy:")
If Not IsDate(Enddate) Or IsNull(Enddate) Then
MsgBox "Please Enter valid date with format mm/dd/yyyy", vbInformation, "Unclaimed Checks System"
Screen.MousePointer = vbNormal
Exit Sub
End If
CheckStat = InputBox("Please Enter the Unclaimed Check Status enclosed in single quotes ('')- R=ResIssued,P=Paid thru PRAP, N=Not Entitled,U=Unclaimed:") _
& " If entering multiple check status,enclose each in single quotes, separated by commas"
If IsNull(CheckStat) Or CheckStat = "" Then
MsgBox "Please Enter Check Status, R,N,P,U,", vbInformation, "Unclaimed Checks System"
Screen.MousePointer = vbNormal
Exit Sub
End If
strSQL = "SELECT DISTINCT tblUnclaimed.PassNumber, tblUnclaimed.EmployeeName, "
strSQL = strSQL & "EmployeeInfo.Address, EmployeeInfo.City, EmployeeInfo.State, "
strSQL = strSQL & "EmployeeInfo.ZIP, EmployeeInfo.L3, EmployeeInfo.L5, "
strSQL = strSQL & "tblUnclaimed.Original_Check_Date, "
strSQL = strSQL & "tblUnclaimed.Amount_of_Check, EmployeeInfo.Status "
strSQL = strSQL & "FROM EmployeeInfo RIGHT JOIN tblUnclaimed ON (EmployeeInfo.L1 = tblUnclaimed.L1) "
strSQL = strSQL & "AND (EmployeeInfo.Pass_Number = tblUnclaimed.PassNumber) "
strSQL = strSQL & "WHERE (((EmployeeInfo.Status) Like 'N*') "
strSQL = strSQL & "AND ((EmployeeInfo.Union_Code) In (Unioncode) "
strSQL = strSQL & "AND ((tblUnclaimed.Status) In (CheckStatus))) "
strSQL = strSQL & "AND ((Len([EmployeeInfo].[Status]))=1)) "
strSQL = strSQL & "AND tblUnclaimed.ReIssued_Check_Date Between #" & Begindate & "# And #" & Enddate & "# "
strSQL = strSQL & "ORDER BY tblUnclaimed.PassNumber;"
Set rsin = Dbs.OpenRecordset(strSQL)
Thanks.
-
Oct 23rd, 2008, 09:29 AM
#14
Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression
Originally Posted by Antithesus
But notice, the error message is not really meaningful either.
I notice this more with MSAccess rather than SQLServer.
That message is actually meaningful, just not worded particularly clearly... As the text is not in quotes it is assumed to be the name of a field or other object, but as there is no field etc with that name, it is apparently assumed to be a parameter - and as no kind of value has been given for it (not even Null), the error is shown.
I think it would be better if there was an alternative word instead of "parameter", and the item it doesn't recognise was shown in the message... but it is how it is!
Last edited by si_the_geek; Oct 23rd, 2008 at 09:54 AM.
Reason: bad timing of post - added quote for clarity
-
Oct 23rd, 2008, 09:46 AM
#15
Thread Starter
Hyperactive Member
Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression
Si,
so how do I properly enclose "CheckStatus" is quotes within the sql?
Thanks.
Giftx.
-
Oct 23rd, 2008, 09:52 AM
#16
Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression
Originally Posted by Giftx
Hi Si,
Below is my complete module. If you look at my variable definitions, Unioncode, Begindate, enddate, the sql is picking up all other variables except "Checkstatus".
That is not your complete module, or even the complete sub/function... among other things it does not contain the variable declarations.
One problem there is that you have not changed this part back to how it was (as I said to do in the other thread):
Code:
strSQL = strSQL & "AND ((tblUnclaimed.Status) In (CheckStatus))) "
It needs to be like this:
Code:
strSQL = strSQL & "AND ((tblUnclaimed.Status) In (" & CheckStatus & "))) "
Presumably you were getting the error that Antithesus mentioned, and making this change will get rid of that error.
As to why the value is not being put into the SQL statement, it actually is... the problem is that the value will always be an empty string, because that is the only value you ever put in CheckStatus.
If you don't have it already, add Option Explicit (and always use it), as it should point out the mistake to you.
You would also be able to spot the problem by debugging, which is a very useful skill to have - most programmers consider it to be mandatory.
Last edited by si_the_geek; Oct 23rd, 2008 at 09:55 AM.
-
Oct 23rd, 2008, 10:01 AM
#17
Thread Starter
Hyperactive Member
Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression
Si,
I noticed something when I did The Debug.Print output picked up user-supplied input: for Begindate and enddate but did not pick up user-supplied input for Unioncode and CheckStatus.
Below is the output of the "Debug.Print strSQL"
Code:
SELECT DISTINCT tblUnclaimed.PassNumber, tblUnclaimed.EmployeeName, EmployeeInfo.Address, EmployeeInfo.City, EmployeeInfo.State, EmployeeInfo.ZIP, EmployeeInfo.L3, EmployeeInfo.L5, tblUnclaimed.Original_Check_Date, tblUnclaimed.Amount_of_Check, EmployeeInfo.Status FROM EmployeeInfo RIGHT JOIN tblUnclaimed ON (EmployeeInfo.L1 = tblUnclaimed.L1) AND (EmployeeInfo.Pass_Number = tblUnclaimed.PassNumber) WHERE (((EmployeeInfo.Status) Like 'N*') AND ((EmployeeInfo.Union_Code) In (Unioncode) AND ((tblUnclaimed.Status) In (CheckStatus))) AND ((Len([EmployeeInfo].[Status]))=1)) AND tblUnclaimed.ReIssued_Check_Date Between #03/1/2008# And #03/31/2008# ORDER BY tblUnclaimed.PassNumber;
Giftx.
-
Oct 23rd, 2008, 10:09 AM
#18
Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression
I hadn't checked Unioncode before, but as the SQL statement contains the name of the variable rather than a value (ie: "... In (Unioncode) .."), the problem is the same thing I showed how to correct in the code snippets of my previous post.
Unioncode should be OK then, it does not have the other problem that CheckStatus has.
-
Oct 23rd, 2008, 10:26 AM
#19
Thread Starter
Hyperactive Member
Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression
Si,
So, how do I fix it? I changed this to add quotes to Checkstatus as you specified and I had the same error. This time it is not even picking up the variable name, rather Checkstatus has empty parenthesis ().
See Debug.Print output below:
Code:
strSQL = strSQL & "AND ((tblUnclaimed.Status) In (" & CheckStatus & "))) "
Debug.Print Output:
Code:
SELECT DISTINCT tblUnclaimed.PassNumber, tblUnclaimed.EmployeeName, EmployeeInfo.Address, EmployeeInfo.City, EmployeeInfo.State, EmployeeInfo.ZIP, EmployeeInfo.L3, EmployeeInfo.L5, tblUnclaimed.Original_Check_Date, tblUnclaimed.Amount_of_Check, EmployeeInfo.Status FROM EmployeeInfo RIGHT JOIN tblUnclaimed ON (EmployeeInfo.L1 = tblUnclaimed.L1) AND (EmployeeInfo.Pass_Number = tblUnclaimed.PassNumber) WHERE (((EmployeeInfo.Status) Like 'N*') AND ((EmployeeInfo.Union_Code) In (Unioncode) AND ((tblUnclaimed.Status) In ())) AND ((Len([EmployeeInfo].[Status]))=1)) AND tblUnclaimed.ReIssued_Check_Date Between #03/1/2008# And #03/31/2008# ORDER BY tblUnclaimed.PassNumber;
Giftx.
-
Oct 23rd, 2008, 11:08 AM
#20
Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression
That is half of what I said back in post #16, and as mentioned in my previous post you need to do the same correction for Unioncode - when you have done that, the error "No value given for one or more parameters" will be gone (as you will have fixed both things that were causing it).
Re-read the second half of post #16 (from "As to why" onwards), as it explains why it seems like the value of CheckStatus is not being used.
-
Oct 23rd, 2008, 11:27 AM
#21
Thread Starter
Hyperactive Member
Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression
Si,
I made the changes to both variables as you said how I have: Syntax error in String in query expression.
Note that I am supplying the values thru an inputbox, but the Debug.Print is not showing any of the values I entered for Unioncode and CheckStatus
Below are the changes I made:
Code:
strSQL = strSQL & "AND ((EmployeeInfo.Union_Code) In (" & Unioncode & ") "
strSQL = strSQL & "AND ((tblUnclaimed.Status) In (" & CheckStatus & "))) "
This is the Debug.Print output:
Code:
SELECT DISTINCT tblUnclaimed.PassNumber, tblUnclaimed.EmployeeName, EmployeeInfo.Address, EmployeeInfo.City, EmployeeInfo.State, EmployeeInfo.ZIP, EmployeeInfo.L3, EmployeeInfo.L5, tblUnclaimed.Original_Check_Date, tblUnclaimed.Amount_of_Check, EmployeeInfo.Status FROM EmployeeInfo RIGHT JOIN tblUnclaimed ON (EmployeeInfo.L1 = tblUnclaimed.L1) AND (EmployeeInfo.Pass_Number = tblUnclaimed.PassNumber) WHERE (((EmployeeInfo.Status) Like 'N*') AND ((EmployeeInfo.Union_Code) In (Unioncode) AND ((tblUnclaimed.Status) In ())) AND ((Len([EmployeeInfo].[Status]))=1)) AND tblUnclaimed.ReIssued_Check_Date Between #03/1/2008# And #03/31/2008# ORDER BY tblUnclaimed.PassNumber;
-
Oct 23rd, 2008, 11:34 AM
#22
Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression
CheckStatus is blank because you have not put any value into the variable (other than an empty string), see post #16.
You seem to have copied the wrong Debug.Print output, unless what the Unioncode variable contains is actually "Unioncode".
The code you showed in that post is correct, so the values of Unioncode and CheckStatus should be in strSQL - the problem is that the value of CheckStatus is an empty string.
-
Oct 23rd, 2008, 11:50 AM
#23
Thread Starter
Hyperactive Member
Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression
Si,
please take a look at my inputBox syntax. Maybe my problem is here. Something looks funky within the codes.
Code:
Unioncode = InputBox("Please Enter Union Codes for this report. Enter each value enclosed in single quotes. if you have more than one union code, separate them with commas (,):Example: '01','00'")
If IsNull(Unioncode) Or Unioncode = "" Then
MsgBox "Please Enter valid union code(s), enclosed with Single quotes: ' ' " _
& "If entering more than one union code, separate them with commas (,)"
Screen.MousePointer = vbNormal
Exit Sub
End If
CheckStatus = InputBox("Please Enter the Unclaimed Check Status enclosed in single quotes ('') - R=ResIssued,P=Paid thru PRAP, N=Not Entitled,U=Unclaimed:") _
& " If entering multiple check status,enclose each value in single quotes, separated by commas"
If IsNull(CheckStatus) Or CheckStatus = "" Then
MsgBox "Please Enter Check Status, R,N,P,U,", vbInformation, "Unclaimed Checks System"
Screen.MousePointer = vbNormal
Exit Sub
End If
-
Oct 23rd, 2008, 12:09 PM
#24
Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression
You've sorted out the name of the CheckStatus variable, nice one.
You have a minor issue on the second InputBox, as you have appended text to the result, rather than appending it to the message - you need to move the ) to the very end (after commas" ).
Note that there is no need to use the IsNull function in your If statements, as a String can never be Null (so that part will always return False). This line:
Code:
If IsNull(Unioncode) Or Unioncode = "" Then
should simply be this:
Code:
If Unioncode = "" Then
-
Oct 23rd, 2008, 05:26 PM
#25
Re: VB6 - RunTime error 3075: Syntax error - Missing operator in query expression
Originally Posted by si_the_geek
Code:
If Unioncode = "" Then
To optimize it...
Code:
If Len(Trim$(Unioncode)) = 0 Then
Trim$ would remove the possibility of white space and using LEN is slightly faster than using equal comparison...
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|