Hi All,
Can someone pls help me ...
Below is the query which i usely run in Access -
select d.DEAL_FOLDER_STATUS, d.VALUE_DATE, d.BUSINESS_DATE,
-
case
-
when d.BUY_SETL_TYPE_IND = 11
-
then 'NET PEND'
-
when d.BUY_SETL_TYPE_IND = 12
-
then 'NETTED '
-
when d.buy_setL_type_ind = 15
-
then 'NET AS GROSS'
-
when d.buy_setl_type_ind = 19
-
then ' NET GO GROSS'
-
when d.BUY_SETL_TYPE_IND = 21
-
then 'GROSS PEND'
-
when d.BUY_SETL_TYPE_IND = 26
-
then 'GROSS AGGR'
-
when d.BUY_SETL_TYPE_IND = 22
-
then 'GROSS AD HOC'
-
when d.BUY_SETL_TYPE_IND = 29
-
then 'GROSS '
-
when d.BUY_SETL_TYPE_IND > 40
-
then 'CLS '
-
ELSE cast(d.buy_setl_type_ind as char(2))
-
end as setl_type
-
,d.LEGAL_ENTITY_ID, d.CPTY_ID, c.formal_name, tm.book_area_id as trade_type
-
,d.BUY_CCY_ID, d.BUY_AMOUNT, d.SELL_CCY_ID, d.SELL_AMOUNT, d.DEAL_RATE, tm.acct_ccy_equiv_amt,tm.TRADE_SOURCE_ID
-
, case
-
when tm.trade_source_id = 'RMS'
-
then (substr(tm.fo_deal_id,5,10))
-
else tm.fo_deal_id
-
end as fo_trade_id
-
,d.DEAL_FOLDER_ID, s.SETL_FOLDER_ID, tm.ndf_ind
-
, d.FULLY_MATCHED_IND,d.INST_OK_IND, tm.portfolio_id
-
from cpty c, cpty_legal_entity cle, deal_folder d, setl s, trade_master tm
-
where
-
cle.company_id = c.company_id
-
and cle.cpty_id = c.cpty_id
-
and d.company_id = cle.company_id
-
and d.legal_entity_id = cle.legal_entity_id
-
and d.cpty_id = cle.cpty_id
-
and c.record_state = 'V'
-
and cle.cpty_id LIKE 'ABSA JB%'
-
and cle.record_state = 'V'
-
and d.record_state = 'V'
-
and d.value_date > (substring(cast(current_timestamp as char(16)) from 1 for 8))
-
and s.deal_folder_id = d.deal_folder_id
-
and s.ccy_id = d.buy_ccy_id
-
and tm.trade_id = d.trade_id
-
and tm.trans_id = d.trade_trans_id
-
and tm.ver_id = d.trade_ver_id
-
order by d.value_date , SETL_TYPE, tm.book_area_id , d.BUY_CCY_ID , d.SELL_CCY_ID , d.CPTY_ID
-
---------------------------------------------------------------------------------------------------------------
I want to run this query thru VB when i am trying to do this as below iam getting syntax error
-------------------------------------------------------------------------------------------- -
MIS_Query = "select d.DEAL_FOLDER_STATUS, d.VALUE_DATE, d.BUSINESS_DATE, case when d.BUY_SETL_TYPE_IND = 11 then 'NET PEND' when d.BUY_SETL_TYPE_IND = 12" _
-
& " then 'NETTED' when d.buy_setL_type_ind = 15 then 'NET AS GROSS' when d.buy_setl_type_ind = 19 then 'NET GO GROSS' when d.BUY_SETL_TYPE_IND = 21" _
-
& " then 'GROSS PEND' when d.BUY_SETL_TYPE_IND = 26 then 'GROSS AGGR' when d.BUY_SETL_TYPE_IND = 22 then 'GROSS AD HOC' when d.BUY_SETL_TYPE_IND = 29" _
-
& " then 'GROSS ' when d.BUY_SETL_TYPE_IND > 40 then 'CLS' ELSE cast(d.buy_setl_type_ind as char(2)) end as setl_type ,d.LEGAL_ENTITY_ID, d.CPTY_ID, c.formal_name, tm.book_area_id as trade_type" _
-
& " ,d.BUY_CCY_ID, d.BUY_AMOUNT, d.SELL_CCY_ID, d.SELL_AMOUNT, d.DEAL_RATE, tm.acct_ccy_equiv_amt,tm.TRADE_SOURCE_ID, case when tm.trade_source_id = 'RMS' " _
-
& " then (substr(tm.fo_deal_id,5,10)) else tm.fo_deal_id end as fo_trade_id,d.DEAL_FOLDER_ID, s.SETL_FOLDER_ID, tm.ndf_ind,d.FULLY_MATCHED_IND,d.INST_OK_IND, tm.portfolio_id" _
-
& " from cpty c, cpty_legal_entity cle, deal_folder d, setl s, trade_master tm where cle.company_id = c.company_id and cle.cpty_id = c.cpty_id and d.company_id = cle.company_id" _
-
& " and d.legal_entity_id = cle.legal_entity_id and d.cpty_id = cle.cpty_id and c.record_state = 'V' and cle.cpty_id LIKE 'ABSA JB%' and cle.record_state = 'V'" _
-
& " and d.record_state = 'V' and d.value_date > (substring(cast(current_timestamp as char(16)) from 1 for 8)) and s.deal_folder_id = d.deal_folder_id" _
-
& " and s.ccy_id = d.buy_ccy_id and tm.trade_id = d.trade_id and tm.trans_id = d.trade_trans_id and tm.ver_id = d.trade_ver_id order by d.value_date " _
-
& ", SETL_TYPE, tm.book_area_id , d.BUY_CCY_ID , d.SELL_CCY_ID , d.CPTY_ID"
-
-
DoCmd.RunSQL (MIS_Query)
-
Is this error due to larger query...
Pls can someone pls help how to run this query it urjent.
11 2071
which line is throwing the error ?
Hi
Could you pls provide your email id i will just drop you a mail with all screen shots
Thanks for your prompt response
@harshakusam
No email please.
why not open a recordset using the query.
- Public Function Metrics()
-
On Error GoTo Err_Mod_MIS
-
-
Dim ftp_Date
-
Dim LocMetrics, StrMetrics As String
-
Dim RstMetrics As Recordset
-
Dim MIS_Query As String
-
MIS_Query = Str(20000)
-
-
DoCmd.SetWarnings False
-
ftp_Date = Format(Date, "yyyymmdd")
-
LocMetrics = DLookup("location", "tbl_location", _
-
"[function]='Metrics'")
-
MsgBox LocMetrics
-
StrMetrics = LocMetrics & "Metrics_" & ftp_Date & ".xls"
-
MsgBox StrMetrics
-
-
-
MIS_Query = "select d.DEAL_FOLDER_STATUS, d.VALUE_DATE, d.BUSINESS_DATE, case when d.BUY_SETL_TYPE_IND = 11 then 'NET PEND' when d.BUY_SETL_TYPE_IND = 12" _
-
& " then 'NETTED' when d.buy_setL_type_ind = 15 then 'NET AS GROSS' when d.buy_setl_type_ind = 19 then 'NET GO GROSS' when d.BUY_SETL_TYPE_IND = 21" _
-
& " then 'GROSS PEND' when d.BUY_SETL_TYPE_IND = 26 then 'GROSS AGGR' when d.BUY_SETL_TYPE_IND = 22 then 'GROSS AD HOC' when d.BUY_SETL_TYPE_IND = 29" _
-
& " then 'GROSS ' when d.BUY_SETL_TYPE_IND > 40 then 'CLS' ELSE cast(d.buy_setl_type_ind as char(2)) end as setl_type ,d.LEGAL_ENTITY_ID, d.CPTY_ID, c.formal_name, tm.book_area_id as trade_type" _
-
& " ,d.BUY_CCY_ID, d.BUY_AMOUNT, d.SELL_CCY_ID, d.SELL_AMOUNT, d.DEAL_RATE, tm.acct_ccy_equiv_amt,tm.TRADE_SOURCE_ID, case when tm.trade_source_id = 'RMS' " _
-
& " then (substr(tm.fo_deal_id,5,10)) else tm.fo_deal_id end as fo_trade_id,d.DEAL_FOLDER_ID, s.SETL_FOLDER_ID, tm.ndf_ind,d.FULLY_MATCHED_IND,d.INST_OK_IND, tm.portfolio_id" _
-
& " from cpty c, cpty_legal_entity cle, deal_folder d, setl s, trade_master tm where cle.company_id = c.company_id and cle.cpty_id = c.cpty_id and d.company_id = cle.company_id" _
-
& " and d.legal_entity_id = cle.legal_entity_id and d.cpty_id = cle.cpty_id and c.record_state = 'V' and cle.cpty_id LIKE 'ABSA JB%' and cle.record_state = 'V'" _
-
& " and d.record_state = 'V' and d.value_date > (substring(cast(current_timestamp as char(16)) from 1 for 8)) and s.deal_folder_id = d.deal_folder_id" _
-
& " and s.ccy_id = d.buy_ccy_id and tm.trade_id = d.trade_id and tm.trans_id = d.trade_trans_id and tm.ver_id = d.trade_ver_id order by d.value_date " _
-
& ", SETL_TYPE, tm.book_area_id , d.BUY_CCY_ID , d.SELL_CCY_ID , d.CPTY_ID"
-
-
MsgBox MIS_Query
-
-
-
DoCmd.RunSQL (MIS_Query)
-
-
DoCmd.TransferSpreadsheet acExport, 8, "MIS_Counts", StrMetrics, True, ""
-
-
Exit_Mod_MIS:
-
DoCmd.SetWarnings True
-
Exit Function
-
-
Err_Mod_MIS:
-
MsgBox Err.Number & " - " & Err.Description
-
Resume Exit_Mod_MIS
-
-
End Function
In access when i run it will run without any errors but if iam using same in VB its throwing sytax error ... Can you pls look into this plsss
Do u know how to use ADO in VB 6 ?
Iam oracle guy .. first time iam using VB to automate some queries ....
try this
from project---->references------>select ADO 2.5 or higher library
they try the following code -
-
Dim CON As New ADODB.Connection
-
Dim RS As New ADODB.Recordset
-
-
Private Sub Command1_Click()
-
CON.Open "your connection string here" find more
-
rs.open con, your query
-
-
End Sub
Iam alreay connecting my database using below code
Function RefreshODBCLinkedTables() As Boolean
On Error GoTo RefreshODBCLinkedTables_Err
Dim strTblName As String, strConn As String
Dim db As Database, rs As Recordset, tbl As TableDef
DoCmd.Hourglass True
' ---------------------------------------------
' Register ODBC database(s)
' ---------------------------------------------
Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_ODBC")
With rs
While Not .EOF
DBEngine.RegisterDatabase rs("DSN"), _
"Oracle ODBC Driver for RDB", _
True, "Attributes=" & rs("Attributes") & Chr(13) _
& "Class=" & rs("class") & Chr(13) _
& "Cursor Option=" & rs("Cursor Option") & Chr(13) _
& "Description=" & rs("Description") & Chr(13) _
& "Options=" & rs("Options") & Chr(13) _
& "Schema=" & rs("Schema") & Chr(13) _
& "Server=" & rs("ServerName") & Chr(13) _
& "Translation Option=" & rs("Translation Option") & Chr(13) _
& "Transport=" & rs("Transport") & Chr(13) _
& "UserID=" & rs("UID")
strTblName = rs("LocalTableName")
strConn = "ODBC;"
strConn = strConn & "DSN=" & rs("DSN") & ";"
strConn = strConn & "APP=Microsoft Access;"
strConn = strConn & "DATABASE=" & rs("schema") & ";"
strConn = strConn & "UID=" & rs("UID") & ";"
strConn = strConn & "PWD=" & rs("PWD") & ";"
strConn = strConn & "TABLE=" & rs("ODBCTableName") & ";"
If (DoesTblExist(strTblName) = False) Then
Set tbl = db.CreateTableDef(strTblName, _
dbAttachSavePWD, rs("ODBCTableName"), _
strConn)
db.TableDefs.Append tbl
Else
Set tbl = db.TableDefs(strTblName)
tbl.Connect = strConn
tbl.RefreshLink
End If
rs.MoveNext
Wend
End With
RefreshODBCLinkedTables = True
RefreshODBCLinkedTables_End:
DoCmd.Hourglass False
Exit Function
RefreshODBCLinkedTables_Err:
MsgBox Err.Description, vbCritical, "Refresh ODBC"
DoCmd.Hourglass False
Resume RefreshODBCLinkedTables_End
End Function
you are connencting to oracle or access database ?
the following link might be helpful. link Sign in to post your reply or Sign up for a free account.
Similar topics
by: joemyre |
last post by:
Hi everyone,
What I'm trying to do is take php variables i got from user input, and
pass them as the MySQL query terms.
$query = "select * from ident where ".$searchtype1."=".$searchterm1."";...
|
by: shank |
last post by:
1) I'm getting this error: Syntax error (missing operator) in query
expression on the below statement. Can I get some advice.
2) I searched ASPFAQ and came up blank. Where can find the "rules"...
|
by: Arnold |
last post by:
Hi,
I'm using visual studio .NET 2003 enterprise and MySQL
5.0. I've created a database project in wich I like to
create scripts for stored procedures and queries.
I've created a connection...
|
by: KevLow |
last post by:
Hi,
Hope some kind soul can help me out here..
I'm trying to programmatically modify the column headings of a crosstab
query such that it can be dynamic based on user specified period (Month...
|
by: Crash |
last post by:
Hi,
..NET v1.x SP1
VS 2003
SQL Server 2000 SP3
Server 2000, XP, Server 2003
I would like to programmatically execute {possibly many} SQL Server
batch scripts. Aka I have many scripts that...
|
by: John Øllgård Jensen |
last post by:
Hi
Using MS Asccess 2000:
In a query I'm trying to create a new field with following expression:
FilmDate: Left(,4)
The field "FilmNo" is another text field in the query.
This is...
|
by: KoliPoki |
last post by:
Hello every body.
I have a small issue.
Problem: I have a table with 4 descriptor columns (type). I need to
formulate a query to retrieve a count for each type so I can group
by...etc. The...
|
by: Daniel |
last post by:
In Microsoft Access I can write a query that includes the criteria:
Between Date()-7 And Date()
to retrieve the records from a table that fall within the last week.
I'm trying to write a...
|
by: Flic |
last post by:
Hi,
I have a basic db that I access with MySQL query browser. Everything
seems fine to me but I am using this db as part of a php shopping
basket and when I try to add an item I get:
Notice:...
|
by: bryant |
last post by:
Hi all.
I am new to ASP and working in Expression Web. The following query
displays the information I need in the gridview for a single record.
SELECT "OE_HDR"."ORD_NO", "OE_HDR"."CUST_NAM",...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
| |