Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Query in Access vs VB 6 3

Status
Not open for further replies.
JPimp

JPimp

Technical User
Mar 27, 2007
79
US

I have this query, which works great when I run it in Access, then I move this over to my VB code, and it fails:

Code: 
Dim cmdTabwareBase As New ADODB.Command
Dim rsTabwareBase As New ADODB.Recordset
Dim oracleTabwareBaseData As String


oracleTabwareBaseData = " SELECT [CMS_COMPLAINT].[COMPLAINT_ID], [CMS_COMPLAINT].[STATUS_CD], [CMS_COMPLAINT].[CREATE_DT], [CMS_COMPLAINT].[CLOSE_DT], [CMS_COMPLAINT].[SAMPLE_IN], [CMS_COMPLAINT].[SAMPLE_RECD_IN], [CMS_COMPLAINT].[SAMPLE_RECD_TEXT], [CMS_COMPLAINT].[SAMPLE_RECD_DT], [CMS_CONTACT].[FIRST_NAME], [CMS_CONTACT].[LAST_NAME], [CMS_CONTACT].[EMAIL_ADDRESS], [CMS_COMPLAINT_OWNER].[OWNER_NAME], [CMS_COMPLAINT_LINE_ITEM].[LOT_NO], [CMS_COMPLAINT_LINE_ITEM].[PRODUCT_ID], [CMS_COMPLAINT_LINE_ITEM].[LINE_ID]" & _



"FROM [CMS_COMPLAINT], [CMS_CONTACT], [CMS_COMPLAINT_OWNER], [CMS_COMPLAINT_LINE_ITEM]" & _


"WHERE [CMS_COMPLAINT].[COMPLAINT_ID] In (SELECT [COMPLAINT_ID] FROM [CMS_COMPLAINT_LINE_ITEM] As TmpID)

AND [CMS_COMPLAINT].[STATUS_CD]<>'Closed' AND 

[CMS_COMPLAINT].[CREATE_DT] <= SYSDATE-15 AND 

[CMS_COMPLAINT].[CLOSE_DT] IS NULL AND 

[CMS_COMPLAINT].[SAMPLE_IN]='N' AND 

[CMS_COMPLAINT].[SAMPLE_RECD_IN]='N' AND 

[CMS_COMPLAINT_OWNER].[OWNER_NAME]='Kennebunk Plant' 

GROUP BY [COMPLAINT_ID] HAVING Count(*)=1 "

The error is ORA-00936 missing expression, which could be almost anything...any ideas why VB does not like the format, what am I missing?

Thanks!!

Kai-What?
 
Sort by date Sort by votes
When you continue a line, you need to add a space so that the 'words' do not run together.

Code: 
Dim cmdTabwareBase As New ADODB.Command
Dim rsTabwareBase As New ADODB.Recordset
Dim oracleTabwareBaseData As String


oracleTabwareBaseData = " SELECT [CMS_COMPLAINT].[COMPLAINT_ID], [CMS_COMPLAINT].[STATUS_CD], [CMS_COMPLAINT].[CREATE_DT], [CMS_COMPLAINT].[CLOSE_DT], [CMS_COMPLAINT].[SAMPLE_IN], [CMS_COMPLAINT].[SAMPLE_RECD_IN], [CMS_COMPLAINT].[SAMPLE_RECD_TEXT], [CMS_COMPLAINT].[SAMPLE_RECD_DT], [CMS_CONTACT].[FIRST_NAME], [CMS_CONTACT].[LAST_NAME], [CMS_CONTACT].[EMAIL_ADDRESS], [CMS_COMPLAINT_OWNER].[OWNER_NAME], [CMS_COMPLAINT_LINE_ITEM].[LOT_NO], [CMS_COMPLAINT_LINE_ITEM].[PRODUCT_ID], [CMS_COMPLAINT_LINE_ITEM].[LINE_ID][!]Put a space here[/!]" & _



"FROM [CMS_COMPLAINT], [CMS_CONTACT], [CMS_COMPLAINT_OWNER], [CMS_COMPLAINT_LINE_ITEM][!]Put a space here[/!]" & _


"WHERE [CMS_COMPLAINT].[COMPLAINT_ID] In (SELECT [COMPLAINT_ID] FROM [CMS_COMPLAINT_LINE_ITEM] As TmpID)

AND [CMS_COMPLAINT].[STATUS_CD]<>'Closed' AND

[CMS_COMPLAINT].[CREATE_DT] <= SYSDATE-15 AND

[CMS_COMPLAINT].[CLOSE_DT] IS NULL AND

[CMS_COMPLAINT].[SAMPLE_IN]='N' AND

[CMS_COMPLAINT].[SAMPLE_RECD_IN]='N' AND

[CMS_COMPLAINT_OWNER].[OWNER_NAME]='Kennebunk Plant'

GROUP BY [COMPLAINT_ID] HAVING Count(*)=1 "

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote



You might also consider building & concatenating the SQL string like this, as it aids in readability...
Code: 
    sSQL = "SELECT DISTINCT"
    sSQL = sSQL & "  BOM.PARTNO_101"
    sSQL = sSQL & ", PMP.NOMEN_101"
    sSQL = sSQL & ", BOM.COMPPART_192"
    sSQL = sSQL & ", PMC.NOMEN_101"
    sSQL = sSQL & ", BOM.SOURCECD_192"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "FROM FRH_MAPL.PSH01333 BOM"
    sSQL = sSQL & ",    FRH_MAPL.PSH01101 PMP"
    sSQL = sSQL & ",    FRH_MAPL.PSH01101 PMC"
    sSQL = sSQL & vbCrLf
    sSQL = sSQL & "WHERE BOM.PARTNO_101   ='" & sParent & "'"
    sSQL = sSQL & "  AND PMP.PARTCODE_101 =BOM.PARTCODE_101"
    sSQL = sSQL & "  AND PMP.PARTNO_101   =BOM.PARTNO_101"
    sSQL = sSQL & "  AND PMC.PARTCODE_101 =BOM.COMPCODE_192"
    sSQL = sSQL & "  AND PMC.PARTNO_101   =BOM.COMPPART_192"
    sSQL = sSQL & "  AND BOM.THRUEFF_133  =999999"

Skip,

[glasses] When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[tongue]
 
Upvote 0 Downvote
Also keep in mind that Access SQL has some variations from what is acceptable in ADO (although in this case I don't see incorrect syntax other than the spaces as pointed out).

Something that catches many people is the wildcards used in a LIKE clause. Access uses * and ?, while ADO uses the more standard % and _ characters.

 
Upvote 0 Downvote

Also, after building your SQL, try:
Code: 
Debug.Print oracleTabwareBaseData
look at Immediate Window (Ctrl-G), Cut it, Paste it into Notepad and most of the time you will be able to see problem(s) rigth away - missing quotes, spaces, wrong date formats, etc.


Have fun.

---- Andy
 
Upvote 0 Downvote
Andy said:
Paste it into Notepad
Click to expand...

Andy, you could always paste it into the SQL window in Access and try it... [wink]

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.
 
Upvote 0 Downvote
Another thing to consider is the length of the query string.
I can't find it right now, but I am quite certain that the string may not exceed a certain - and doable - length.

You might want to use aliases to shorten your string - and make it easier readable:

Code: 
oracleTabwareBaseData = " SELECT [b]a.[/b][COMPLAINT_ID], a.[STATUS_CD], a.[CREATE_DT], a.[CLOSE_DT], " & _
"a.[SAMPLE_IN], a.[SAMPLE_RECD_IN], a.[SAMPLE_RECD_TEXT], a.[SAMPLE_RECD_DT], " & _
"[b]b.[/b][FIRST_NAME], b.[LAST_NAME], b.[EMAIL_ADDRESS], " & _ 
"[b]c.[/b][OWNER_NAME], [b]d.[/b][LOT_NO], d.[PRODUCT_ID], d.[LINE_ID] " & _
"FROM [CMS_COMPLAINT] [b]As a[/b], [CMS_CONTACT] [b]As b[/b], [CMS_COMPLAINT_OWNER] [b]As c[/b], [CMS_COMPLAINT_LINE_ITEM] [b]As d[/b]" & _
...
...

;-)

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell
 
Upvote 0 Downvote
Would this be of date
[CMS_COMPLAINT].[CREATE_DT] <= SYSDATE-15

then you need # around the value and a unambigius ANSI sepperated format for always accurate valid dates.

[CMS_COMPLAINT].[CREATE_DT] <=#" & Format(SYSDATE-15,"yyyy-mm-dd") & "# AND " & _
 
Upvote 0 Downvote
I made those changes and still the same, not sure what is going on...

Kai-What?
 
Upvote 0 Downvote
What happens when you use the suggestions of using
Code: 
Debug.Print oracleTabwareBaseData
and then pasting the result into the SQL query window in Access and trying to run it in there?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.
 
Upvote 0 Downvote
Code: 
oracleTabwareBaseData = " SELECT [CMS_COMPLAINT].[COMPLAINT_ID], [CMS_COMPLAINT].[STATUS_CD], [CMS_COMPLAINT].[CREATE_DT], [CMS_COMPLAINT].[CLOSE_DT], [CMS_COMPLAINT].[SAMPLE_IN], [CMS_COMPLAINT].[SAMPLE_RECD_IN], [CMS_COMPLAINT].[SAMPLE_RECD_TEXT], [CMS_COMPLAINT].[SAMPLE_RECD_DT], [CMS_CONTACT].[FIRST_NAME], [CMS_CONTACT].[LAST_NAME], [CMS_CONTACT].[EMAIL_ADDRESS], [CMS_COMPLAINT_OWNER].[OWNER_NAME], [CMS_COMPLAINT_LINE_ITEM].[LOT_NO], [CMS_COMPLAINT_LINE_ITEM].[PRODUCT_ID], [CMS_COMPLAINT_LINE_ITEM].[LINE_ID]" & _
"FROM [CMS_COMPLAINT], [CMS_CONTACT], [CMS_COMPLAINT_OWNER], [CMS_COMPLAINT_LINE_ITEM]" & _
"WHERE [CMS_COMPLAINT].[COMPLAINT_ID] In (SELECT [COMPLAINT_ID] FROM [CMS_COMPLAINT_LINE_ITEM] As TmpID) " & _
"AND [CMS_COMPLAINT].[STATUS_CD]<>'Closed' AND " & _
"[CMS_COMPLAINT].[CREATE_DT] <= SYSDATE-15 AND " & _
"[CMS_COMPLAINT].[CLOSE_DT] IS NULL AND " & _
"[CMS_COMPLAINT].[SAMPLE_IN]='N' AND " & _
"[CMS_COMPLAINT].[SAMPLE_RECD_IN]='N' AND " & _
"[CMS_COMPLAINT_OWNER].[OWNER_NAME]='Kennebunk Plant' " & _
"GROUP BY [COMPLAINT_ID] HAVING Count(*)=1 "

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
What is it with lines that look like these:
Code: 
"WHERE [CMS_COMPLAINT].[COMPLAINT_ID] In (SELECT [COMPLAINT_ID] FROM [CMS_COMPLAINT_LINE_ITEM] As TmpID)

AND [CMS_COMPLAINT].[STATUS_CD]<>'Closed' AND
Are there linebreaks in the midst of the string?
That won't go. You need to add a line continuation and NO blank lines in between; just like this:
Code: 
"WHERE [CMS_COMPLAINT].[COMPLAINT_ID] In (SELECT [COMPLAINT_ID] FROM [CMS_COMPLAINT_LINE_ITEM] As TmpID) [b]" & _[/b] [green]'rem: no blank line following[/green]
"AND [CMS_COMPLAINT].[STATUS_CD]<>'Closed' AND ....

[navy]"We had to turn off that service to comply with the CDA Bill."[/navy]
- The Bastard Operator From Hell
 
Upvote 0 Downvote
gmmastros, I copied your lines and inserted, still same thing...I just don't see any errors in the statement, especially now after you changed it.

Kai-What?
 
Upvote 0 Downvote
JPimp - Did you try what was mentioned in my last post?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.
 
Upvote 0 Downvote
Yes, and when I paste it in to Excel, it complains about the other fields not being part of the aggregate function...could that be the issue, i.e. that I need to group by the other fields?

Kai-What?
 
Upvote 0 Downvote

What does it have to do with Excel?

Why don't you Debug.Print your SQL, take it to Access to SQL Window and run it from there. What happens?

Have fun.

---- Andy
 
Upvote 0 Downvote
Code: 
SELECT [CMS_COMPLAINT].[COMPLAINT_ID], 
[CMS_COMPLAINT].[STATUS_CD], 
[CMS_COMPLAINT].[CREATE_DT], 
[CMS_COMPLAINT].[CLOSE_DT], 
[CMS_COMPLAINT].[SAMPLE_IN], 
[CMS_COMPLAINT].[SAMPLE_RECD_IN], 
[CMS_COMPLAINT].[SAMPLE_RECD_TEXT], 
[CMS_COMPLAINT].[SAMPLE_RECD_DT], 
[CMS_CONTACT].[FIRST_NAME], 
[CMS_CONTACT].[LAST_NAME], 
[CMS_CONTACT].[EMAIL_ADDRESS], 
[CMS_COMPLAINT_OWNER].[OWNER_NAME], 
[CMS_COMPLAINT_LINE_ITEM].[LOT_NO], 
[CMS_COMPLAINT_LINE_ITEM].[PRODUCT_ID], 
[CMS_COMPLAINT_LINE_ITEM].[LINE_ID] 

FROM [CMS_COMPLAINT], [CMS_CONTACT], [CMS_COMPLAINT_OWNER], [CMS_COMPLAINT_LINE_ITEM] 

WHERE [CMS_COMPLAINT].[COMPLAINT_ID] In (SELECT [CMS_COMPLAINT_LINE_ITEM].[COMPLAINT_ID] As TmpID)
AND [CMS_COMPLAINT].[STATUS_CD]<>'Closed'
AND [CMS_COMPLAINT].[CREATE_DT] <= SYSDATE-15
AND [CMS_COMPLAINT].[CLOSE_DT] IS NULL 
AND [CMS_COMPLAINT].[SAMPLE_IN]='N'  
AND [CMS_COMPLAINT].[SAMPLE_RECD_IN]='N'
AND [CMS_COMPLAINT_OWNER].[OWNER_NAME]='Kennebunk Plant' 

GROUP BY [CMS_COMPLAINT_LINE_ITEM].[COMPLAINT_ID], [CMS_COMPLAINT].[COMPLAINT_ID], 
[CMS_COMPLAINT].[STATUS_CD], 
[CMS_COMPLAINT].[CREATE_DT], 
[CMS_COMPLAINT].[CLOSE_DT], 
[CMS_COMPLAINT].[SAMPLE_IN],
[CMS_COMPLAINT].[SAMPLE_RECD_IN],
[CMS_COMPLAINT].[SAMPLE_RECD_TEXT],
[CMS_COMPLAINT].[SAMPLE_RECD_DT],
[CMS_CONTACT].[FIRST_NAME],
[CMS_CONTACT].[LAST_NAME],
[CMS_CONTACT].[EMAIL_ADDRESS],
[CMS_COMPLAINT_OWNER].[OWNER_NAME], [CMS_COMPLAINT_LINE_ITEM].[LOT_NO], [CMS_COMPLAINT_LINE_ITEM].[PRODUCT_ID], [CMS_COMPLAINT_LINE_ITEM].[LINE_ID]
 HAVING Count(*)=1

This is what Debug.Print gave me, which I then pasted in to Access, and now Access is telling me that there is something wrong with the select statement, it either includes a reserved word or an argument name is misspelled, or the punctuation is incorrect, so there is something in the select statement is does not like at all, could it be where I assign a variable as tmp?


Kai-What?
 
Upvote 0 Downvote
hehehe, my bad, I meant Access, not Excel, could it be the way Oracle handles the sysdate request?

Kai-What?
 
Upvote 0 Downvote
Wait a minute, Oracle?? I think most of us assumed the backend was Access and the front end was VB.

You might get a better answer in an Oracle forum.

 
Upvote 0 Downvote
Right, Oracle it is then. There are a few things that strike me here about this query now I know what language it's written for:
Code: 
WHERE [CMS_COMPLAINT].[COMPLAINT_ID] In (SELECT [CMS_COMPLAINT_LINE_ITEM].[COMPLAINT_ID] As TmpID)
That can't work, as you need to reference a full SQL statement for the IN clause, perhaps:
Code: 
WHERE [CMS_COMPLAINT].[COMPLAINT_ID] In (SELECT [COMPLAINT_ID] FROM [CMS_COMPLAINT_LINE_ITEM] As TmpID)
Better?

The other thing that throws me is that there are NO joins in the query at all, do your results (when run against Oracle) not come out a bit strange as there's no way for the query engine to determine how the tables relate to each other?

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

sal21
  • Locked
  • Question
via vb 6.0, please split access table
Replies
1
Views
241
strongm
strongm
2009luca
  • Locked
difficult to crteate a query very complicated 1
Replies
9
Views
1K
Andrzejek
Andrzejek
2009luca
  • Locked
  • Question
graph with access database - im on vb6 2
Replies
10
Views
895
strongm
strongm
wires
  • Locked
  • Question
Install VB6 IDE and Access97 on W11 ARM version 1
Replies
3
Views
491
Gabor526
G
sacsac
  • Locked
  • Question
Commas in CSV fle
Replies
8
Views
421
gmmastros
gmmastros

Part and Inventory Search

Sponsor

Back
Top