Free Microsoft Excel 2013 Quick Reference

Sql syntax Results

Hello,

Sorry if the title is a little convoluted; best I could do without being much longer.

I am trying to write an SQL query in VB that will calculate the "RecurringMonthlyCost" of each record, then use that value in the ORDER BY clause when returning the records.

The relevant data is structured into 3 columns:
StdMonthlyCost, PromoMonthlyCost, PromoPeriod.

The formula as it would be written in VB looks like:

	VB:
	
TotalRecurringCost = PromoMonthlyCost * MIN(PromoPeriod, BPCount) + StdMonthlyCost * (BPCount - MIN(PromoPeriod,
BPCount)) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
TotalRecurringCost will be a new field.
BPCount is a value I will specify when writing the SQL statement.

The problem is I need to be able to use the smaller value of PromoPeriod or BPCount in the above formula.

I may have an outline to a solution, but I can't make it work.

1.)Use a subquery to select records where promoperiod is smaller than bpcount:

	VB:
	
Select PromoPeriod from table where promoperiod

hi,
I'm getting the error for the following piece of code...plz help


	VB:
	
 
Sub itconfandscratch() 
     
    Dim Cn As ADODB.Connection 
    Dim Server_Name As String 
    Dim Database_Name As String 
    Dim User_ID As String 
    Dim Password As String 
    Dim SQLStr As String 
    Dim rs As ADODB.Recordset 
    Set rs = New ADODB.Recordset 
     
     
     
    Server_Name = "sturecord" 
    Database_Name = "Scratch" ' Enter your database name here
    SQLStr = "SELECT stuname FROM dbo.sturec" ' Enter your SQL here
     
    Set Cn = New ADODB.Connection 
    Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & "" 
     
     
    rs.Open SQLStr, Cn, adOpenStatic 
     ' Dump to spreadsheet
     
    Dim str2 As String 
     
     
    str2 = "{" 
    Dim i As Integer 
    i = 0 
    While (i < rs.RecordCount And i < 10) 
         
        str2 = str2 + "'" 
        str2 = str2 + rs(0) 
        str2 = str2 + "'" 
        str2 = str2 + "," 
        rs.Move (1) 
         
        i = i + 1 
         
    Wend 
     
    str2 = str2 + "}" 
     
    SQLStr = "SELECT [stud name],class,subject FROM dbo.stuconfig where [stud name] in " + str2 
     
    Dim Cn1 As ADODB.Connection 
     
    Dim rs1 As ADODB.Recordset 
    Set rs1 = New ADODB.Recordset 
    Server_Name = "cbvdhg-v" 
    Database_Name = "exceptions" 
     
     
    Set Cn1 = New ADODB.Connection 
    Cn1.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & "" 
     
    rs1.Open SQLStr, Cn1, adOpenStatic 
     
    With Worksheets("sheet4").Range("a2:z1000") ' Enter your sheet name and range here
        .ClearContents 
        .CopyFromRecordset rs 
         
    End With 
     '            Tidy up
    rs1.Close 
    Set rs = Nothing 
    Cn1.Close 
    Set Cn = Nothing 
    rs.Close 
    Cn.Close 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The above is the entire code for getting a list of students from one database, storing it in a recordset and using this list to get the details of all these students from another database.
Please suggest the solution for the error.

Thanks,
Oucsester

not sure if you can use a case statement when querying a spreadsheet using ado... but here is what i have and i am getting a syntax error...

any ideas where i am wrong?


	VB:
	
SELECT [LOB], [REQ_STATUS_DATE], 
(Case [CANDIDATE_POOL_DESC] WHEN [CANDIDATE_POOL_DESC] = 'External and Internal' THEN 'External' Else 'Internal' END),
[JOB_TYPE],[REQ_STATUS_CODE] 
FROM [All Req Statuses$] 
WHERE ([REQ_STATUS_DATE] between #2/1/2005# And #1/31/2006#) 
And [JOB_TYPE]  'UNKNOWN'
And [LOB] In ( 'IT','Legal','Manufacturing', 'CS')

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
thanks,

John

I keep getting a runtime error stating a syntax error converting the varchar value ' & CellValue1 & ' to a column of datatypr int. The value is 1 and my SQL table in dataType Integer. I have also tried with various removal of ' and & from the insert statement. Any ideas of the correct syntax?

	VB:
	
 btnUpdate_Click() 
     
    Dim c As ADODB.Connection 
    Dim r As ADODB.Recordset 
    Set c = New ADODB.Connection 
    Dim rw As Integer 
    Dim cellValue1 As Integer 
    Dim cellValue2 As String 
    Dim ws As Worksheet 
     
    strCn = "Server=ServerName;Database=dbName;User Id=mm;password=ma" 
     
    c.Provider = "sqloledb" 
    c.Open strCn 
     
    rw = 1 
    Do While Len(Cells(rw, 1)) > 0 
         
        Set ws = Sheets("Pivot") 
         
        cellValue1 = ws.Range("A" & rw).Value 
        cellValue2 = ws.Range("B" & rw).Value 
         
        MsgBox (cellValue1) 
        MsgBox (cellValue2) 
         
        sq = "Insert into marc_temp_excel (Policy_id, Policy_desc) values ('& cellValue1 &', ' & cellValue2 & ')" 
         
        Set r = c.Execute(sq) 
        rw = rw + 1 
    Loop 
     
    c.Close 
    Set c = Nothing 
    MsgBox ("marc_temp_excel Table Successfully Updated.") 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi

I want to know how to create a table in Access using an ADODB connection and an SQL 'CREATE TABLE' statement, but I'm not sure of the correct syntax. I don't know whether I should use a ADODB recordset or command (I've tried both out of desperation), and I keep getting an error message saying that there is a syntax error in my SQL statement.

Here is the code from one of my attemps..


	VB:
	
 ADODB.Recordset 
Dim cnn As ADODB.Connection 
 
Set cnn = CurrentProject.Connection 
Set rstTry = New ADODB.Recordset 
rstTry.Open "CREATE TABLE tblUserId (SigmaId TEXT(3), Password TEXT(15), FirstName TEXT(50), Surname TEXT(50), LogonId
TEXT(6), SecStatus LONG, Attempts LONG); ", cnn 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any help appreciated

Cheers
Eljays

SELECT * INTO [Reports1] FROM [Raw_Data$] In "C:Documents and SettingsDesktopReports" [EXCEL 8.0; HDR=YES; IMEX=2;DATABASE=ExcelFile];

the error msg reads: Incorrect syntax near the keyword 'In'.

thanks
Renita

Hi all.
I'm trying to write an SQL statement that will search a query and bring back recordsets that both match a unique number (personid) and contain a date field that is later than today's date. In Access, the date field I am concerned with is called "enddate" and I have called a cell in Excel CurrentDate using the formula: =TODAY().
Here is the SQL statement that I'm having trouble with.

	VB:
	
ConfirmedPatientID = Worksheets("Sheet1").Range("ConfirmedPatientID") 
CurrentDate = Worksheets("Sheet2").Range("E6") 
sSQL_Meds = "SELECT medname, activedate, enddate, dose, Freq FROM med4personquery WHERE person.personid = " &
ConfirmedPatientID & " AND WHERE enddate > " & CurrentDate 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
When I run the SQL statement, it returns a syntax error (missing operator). If I take out the AND WHERE statement, it runs fine. And if I move the "enddate > CurrentDate" to be the only WHERE statement, it returns records but does not seem to follow the ">" instruction.

This may be a simple problem to fix, but I'm fairly new and don't pick out the obvious mistakes very quickly yet. Thanks in advance for any help you can give.

Problem
SQL. REQUEST Function in Excel for Oracle restricts all but the most basic SQL statements. For example, you cannot use the left/right/mid & sum functions. The same SQL statement works in MS Access but not in Excel for Oracle (the problem does not exist with MS SQL environment).

e.g. "SELECT left(project,4) from FYTD" will be rejected by the function

Any idea if this is a syntax issue or of the SQL.Request function for Oracle is just that limited?

System:
Windows XP
Excel 2002
Oracle client ODBC driver
Oracle 8.1

Morning all, hope everyone had a good weekend...

having a little trouble with the following statement

	VB:
	
stCn = "Driver={SQL Server};Server=sjhsql01;Database=test_dev;Uid=HPS_ORDER;Pwd=PASS" 
stSql = "INSERT INTO HPO_HPS_ORDER SELECT * FROM [data$] IN '" & ThisWorkbook.FullName & "' 'Excel 8.0;'" 
 
 
Set cn = New ADODB.Connection 
 
With cn 
    .Open stCn 
    .Execute stSql 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Sure it must a syntxa error in stSql but for the life of me I can't see it.

The error fires up on the .Execute line and states
Run-Time Error '-2147217900 (80040e14)':

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect Syntax near the keyword IN The code I have put together from posts in the forum (maily WillR I think) and is attempting to insert a named range 'data' from excel in to a SQL Server table 'HPO_HPS_ORDER'

If anyone could point me in the right direction I would ge grateful.

I am new at this and everyday is a learning experience. I searched the web and can not find anyone who talks about referring to a datetime user input in an excel file and then using that date time in their SQL code.

I am trying to get a date and time from a cell in excel and use that date and time in a sqloledb connection to my SQL server. the macro works fine when i input the dates into the code as follows:


	VB:
	
And ProductionData.DateTime= TO_DATE ['" & CStr(begdate) & "','MM/DD/YY HH:MM:SS'] " & _ 
"And ProductionData.DateTime >= TO_DATE ['" & CStr(enddate) & "','MM/DD/YY HH:MM:SS']) " & _[/B]"And
ProductionData.SchemeName like 'Harrisburg Letters' " & _ 
"ORDER BY ProductionData.SchemeName, ProductionData.FamilyName, ProductionData.Zip5", conn 
 
 '*****************************************************
 '  Start of Algorith for populating data
 '*****************************************************
 
Set DatRng = Sheet1.Range("$5:$65536") 
Sheet1.Range("DatRng").ClearContents 
 
row = "5" 
If Not (rsAD.EOF And rsAD.BOF) Then 
     
    Do Until rsAD.EOF 
        DoEvents 
         
        Sheet1.Cells(row, 1).Value = rsAD.Fields.Item(14).Value 
        Sheet1.Cells(row, 2).Value = rsAD.Fields.Item(4).Value 
        Sheet1.Cells(row, 3).Value = rsAD.Fields.Item(6).Value 
        Sheet1.Cells(row, 4).Value = rsAD.Fields.Item(0).Value 
        Sheet1.Cells(row, 5).Value = rsAD.Fields.Item(1).Value 
        Sheet1.Cells(row, 6).Value = rsAD.Fields.Item(2).Value 
        Sheet1.Cells(row, 7).Value = rsAD.Fields.Item(5).Value 
        Sheet1.Cells(row, 8).Value = rsAD.Fields.Item(7).Value 
        Sheet1.Cells(row, 9).Value = rsAD.Fields.Item(8).Value 
        Sheet1.Cells(row, 10).Value = rsAD.Fields.Item(9).Value 
        Sheet1.Cells(row, 11).Value = rsAD.Fields.Item(10).Value 
        Sheet1.Cells(row, 12).Value = rsAD.Fields.Item(11).Value 
        Sheet1.Cells(row, 13).Value = rsAD.Fields.Item(12).Value 
        Sheet1.Cells(row, 14).Value = rsAD.Fields.Item(13).Value 
         
         
        row = row + 1 
        rsAD.MoveNext 
         
    Loop 
     
Else 
    errdetect = True 
    Write #1, (strSource & " - No LCTS Data Retrieved - " & Time) 
End If 
 
 
 '*****************************************************
 '  Close SQL Connection
 '*****************************************************
 
If rsAD.State  adStateClosed Then 
    rsAD.Close 
End If 
 
If conn.State  adStateClosed Then 
    conn.Close 
End If 
 
 
 ' Save it and turn screen updating on
Calculate 
Application.Calculation = xlAutomatic 
Application.MaxChange = 0.001 
Application.ScreenUpdating = True 
ActiveWorkbook.PrecisionAsDisplayed = False 
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst 
 
MsgBox "Update is Done" 
 
 
 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


MS Excel VBA and SQL – problem with INSERT and UPDATE command

Could somebody help me with a little (?) problem?
I need to construct a procedure that should connect MS Access database and INSERT or UPDATE a data record into a table.

I know, SELECT statements are going very well there by using functions supported by XLODBC.xla. In Help there is written that INSERT, UPDATE or DELETE statements are also supported.
But I don’t know, which functions and syntax are needed.

In the case of SELECT statements I use following procedure (a simple example):

strQuery = “SELECT * FROM users WHERE name = ‘Tom’” ‘specifying of the SQL expression
strDatabaseName = "Nabidky" ‘specifying of the name of the database connection
intChan = SQLOpen("DSN=" & strDatabaseName) ‘specifying of the connection ID
SQLExecQuery intChan, strQuery
Set output = Worksheets("Sheet1").Range("A10")
SQLRetrieve intChan, output, , , True
SQLClose intChan

As far as INSERT, UPDATE or DELETE I think that first four lines should be the same (with an appropriate SQL expression in strQuery). But what else? How to continue? Using them “only” I haven’t succeed.
I haven’t found anything useful in MS Help.
Thank you very much for YOUr Help.

Excuse my pure English, pleas.

Tomas

Hi

I get run-time error '2147217900 (80040E14)' Incorrect syntax near keyword IF - I can't figure out what is causing - many thanx

Code:
 
sSQL = "SELECT "
    sSQL = sSQL & " a.FN AS 'FN',"
    sSQL = sSQL & " a.FTYPE AS 'FTYPE',"
    sSQL = sSQL & " m12 AS '" & Format(DateAdd("M", -11, sDate), "mmm-yyyy") & "',"
    sSQL = sSQL & " m11 AS '" & Format(DateAdd("M", -10, sDate), "mmm-yyyy") & "',"
    sSQL = sSQL & " m10 AS '" & Format(DateAdd("M", -9, sDate), "mmm-yyyy") & "',"
    sSQL = sSQL & " m9 AS '" & Format(DateAdd("M", -8, sDate), "mmm-yyyy") & "',"
    sSQL = sSQL & " m8 AS '" & Format(DateAdd("M", -7, sDate), "mmm-yyyy") & "',"
    sSQL = sSQL & " m7 AS '" & Format(DateAdd("M", -6, sDate), "mmm-yyyy") & "',"
    sSQL = sSQL & " m6 AS '" & Format(DateAdd("M", -5, sDate), "mmm-yyyy") & "',"
    sSQL = sSQL & " m5 AS '" & Format(DateAdd("M", -4, sDate), "mmm-yyyy") & "',"
    sSQL = sSQL & " m4 AS '" & Format(DateAdd("M", -3, sDate), "mmm-yyyy") & "',"
    sSQL = sSQL & " m3 AS '" & Format(DateAdd("M", -2, sDate), "mmm-yyyy") & "',"
    sSQL = sSQL & " m2 AS '" & Format(DateAdd("M", -1, sDate), "mmm-yyyy") & "',"
    sSQL = sSQL & " m1 AS '" & Format(sDate, "mmm-yyyy") & "',"
    sSQL = sSQL & " m0 AS 'YTD Total'"
    sSQL = sSQL & " FROM"
    sSQL = sSQL & " ("
    sSQL = sSQL & " ("
    sSQL = sSQL & " SELECT FN,"
    sSQL = sSQL & " SUM(IF(FILE_TIME >= DATE_ADD('" & sDate & "', INTERVAL -11 Month) AND FILE_TIME < DATE_ADD('" & sDate &
"', INTERVAL -10 Month),COUNT(ID),'')) AS 'm12',"
    sSQL = sSQL & " SUM(IF(FILE_TIME >= DATE_ADD('" & sDate & "', INTERVAL -10 Month) AND FILE_TIME < DATE_ADD('" & sDate &
"', INTERVAL -9 Month),COUNT(ID),'')) AS 'm11',"
    sSQL = sSQL & " SUM(IF(FILE_TIME >= DATE_ADD('" & sDate & "', INTERVAL -9 Month) AND FILE_TIME < DATE_ADD('" & sDate &
"', INTERVAL -8 Month),COUNT(ID),'')) AS 'm10',"
    sSQL = sSQL & " SUM(IF(FILE_TIME >= DATE_ADD('" & sDate & "', INTERVAL -8 Month) AND FILE_TIME < DATE_ADD('" & sDate &
"', INTERVAL -7 Month),COUNT(ID),'')) AS 'm9',"
    sSQL = sSQL & " SUM(IF(FILE_TIME >= DATE_ADD('" & sDate & "', INTERVAL -7 Month) AND FILE_TIME < DATE_ADD('" & sDate &
"', INTERVAL -6 Month),COUNT(ID),'')) AS 'm8',"
    sSQL = sSQL & " SUM(IF(FILE_TIME >= DATE_ADD('" & sDate & "', INTERVAL -6 Month) AND FILE_TIME < DATE_ADD('" & sDate &
"', INTERVAL -5 Month),COUNT(ID),'')) AS 'm7',"
    sSQL = sSQL & " SUM(IF(FILE_TIME >= DATE_ADD('" & sDate & "', INTERVAL -5 Month) AND FILE_TIME < DATE_ADD('" & sDate &
"', INTERVAL -4 Month),COUNT(ID),'')) AS 'm6',"
    sSQL = sSQL & " SUM(IF(FILE_TIME >= DATE_ADD('" & sDate & "', INTERVAL -4 Month) AND FILE_TIME < DATE_ADD('" & sDate &
"', INTERVAL -3 Month),COUNT(ID),'')) AS 'm5',"
    sSQL = sSQL & " SUM(IF(FILE_TIME >= DATE_ADD('" & sDate & "', INTERVAL -3 Month) AND FILE_TIME < DATE_ADD('" & sDate &
"', INTERVAL -2 Month),COUNT(ID),'')) AS 'm4',"
    sSQL = sSQL & " SUM(IF(FILE_TIME >= DATE_ADD('" & sDate & "', INTERVAL -2 Month) AND FILE_TIME < DATE_ADD('" & sDate &
"', INTERVAL -1 Month),COUNT(ID),'')) AS 'm3',"
    sSQL = sSQL & " SUM(IF(FILE_TIME >= DATE_ADD('" & sDate & "', INTERVAL -1 Month) AND FILE_TIME < '" & sDate &
"',COUNT(ID),'')) AS 'm2',"
    sSQL = sSQL & " SUM(IF(FILE_TIME >= '" & sDate & "' AND FILE_TIME < DATE_ADD('" & sDate & "', INTERVAL 1
Month),COUNT(ID),'')) AS 'm1',"
    sSQL = sSQL & " SUM(IF(FILE_TIME >= DATE_ADD('" & sDate & "', INTERVAL -11 Month) AND FILE_TIME < DATE_ADD('" & sDate &
"', INTERVAL 1 Month),COUNT(ID),'')) AS 'm0'"
    sSQL = sSQL & " FROM DailyArchive"
    sSQL = sSQL & " GROUP BY FN"
    sSQL = sSQL & " )"
    sSQL = sSQL & " ) tmp1"
    sSQL = sSQL & " RIGHT OUTER JOIN DailyArchive a ON tmp1.FN = a.FN"
    sSQL = sSQL & " GROUP BY a.FN"
    sSQL = sSQL & " ORDER BY a.FN"


Hello all,

I attempted to record a get data macro to grab data from my SQL table. Right now this is a macro i recorded that doesnt work, eventually i would like to allow the user to select criteria from a validation list in a cell on a menu tab, press a buton and it will grab and place the corresponding data into a new tab in the workbook. I have the code snippet below, the items that dont seem to work are in the set that call specific criteria.

If any of you code junkies could assist i would be greatful, Thanks!

Code:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 10/5/2008 by jl5327
'

'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DRIVER=SQL Server;SERVER=wd08sqldv001;UID=JL1234;APP=Microsoft Office
2003;WSID=M7050799V9YC1;Trusted_Connection=Yes" _
        , Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT ""Wave Data - C-C-C-COMBO 2"".""Survey Type"", ""Wave Data - C-C-C-COMBO 2"".QTR, ""Wave Data - C-C-C-COMBO
2"".Year, ""Wave Data - C-C-C-COMBO 2"".Month, ""Wave Data - C-C-C-COMBO 2"".Week, ""Wave Data - C" _
        , _
        "-C-C-COMBO 2"".""Call Center"", ""Wave Data - C-C-C-COMBO 2"".""Rep ID"", ""Wave Data - C-C-C-COMBO 2"".""Contact
Date"", ""Wave Data - C-C-C-COMBO 2"".Mobile, ""Wave Data - C-C-C-COMBO 2"".Type, ""Wave Data - C-C-C-" _
        , _
        "COMBO 2"".Tech, ""Wave Data - C-C-C-COMBO 2"".Region, ""Wave Data - C-C-C-COMBO 2"".Cluster, ""Wave Data -
C-C-C-COMBO 2"".""Interview Date"", ""Wave Data - C-C-C-COMBO 2"".""Call Category"", ""Wave Data - C-C-C-CO" _
        , _
        "MBO 2"".""Reason Code"", ""Wave Data - C-C-C-COMBO 2"".""Sub-Category 1-1"", ""Wave Data - C-C-C-COMBO
2"".""Sub-Category 1-2"", ""Wave Data - C-C-C-COMBO 2"".""Q1 Reason for Call 2"", ""Wave Data - C-C-C-COMBO 2"".""S" _
        ,,,,,,,,,,,,,,,,,,,,,,,,,,,,)
        .Name = "NISPS"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .SourceConnectionFile = _
        "C:Program FilesCommon FilesODBCData SourcesNISPS.dsn"
        .Refresh BackgroundQuery:=False
    End With
End Sub

something to add: I get a compile error/syntax error when i run this recorded macro the highlighted error in the syntax is below....
.CommandText = Array( _
"SELECT ""Wave Data - C-C-C-COMBO 2"".""Survey Type"", ""Wave Data - C-C-C-COMBO 2"".QTR, ""Wave Data - C-C-C-COMBO 2"".Year, ""Wave Data - C-C-C-COMBO 2"".Month, ""Wave Data - C-C-C-COMBO 2"".Week, ""Wave Data - C" _
, _
"-C-C-COMBO 2"".""Call Center"", ""Wave Data - C-C-C-COMBO 2"".""Rep ID"", ""Wave Data - C-C-C-COMBO 2"".""Contact Date"", ""Wave Data - C-C-C-COMBO 2"".Mobile, ""Wave Data - C-C-C-COMBO 2"".Type, ""Wave Data - C-C-C-" _
, _
"COMBO 2"".Tech, ""Wave Data - C-C-C-COMBO 2"".Region, ""Wave Data - C-C-C-COMBO 2"".Cluster, ""Wave Data - C-C-C-COMBO 2"".""Interview Date"", ""Wave Data - C-C-C-COMBO 2"".""Call Category"", ""Wave Data - C-C-C-CO" _
, _
"MBO 2"".""Reason Code"", ""Wave Data - C-C-C-COMBO 2"".""Sub-Category 1-1"", ""Wave Data - C-C-C-COMBO 2"".""Sub-Category 1-2"", ""Wave Data - C-C-C-COMBO 2"".""Q1 Reason for Call 2"", ""Wave Data - C-C-C-COMBO 2"".""S" _
,,,,,,,,,,,,,,,,,,,,,,,,,,,,)

I have used MSQuery with the following criteria

WHERE
(opheadm.order_no Like 'T%') AND (opheadm.status>'5' And opheadm.status'5' And opheadm.status'5' And opheadm.status the right way round?)

Hello,

I'm trying to query a query in Access 2003, from Excel 2003.

The query in Access looks like:
AccessQuery: [SELECT VBAFunction(field1) FROM Table]

The query in Excel looks like:
ExcelQuery: [SELECT * FROM AccessQuery]

I use the following VBA code in Excel to excecute the query:
Code:
    With ThisWorkbook.Worksheets(cDataSheetName).QueryTables.Add(Connection:=strConnection, _
        Destination:=ThisWorkbook.Worksheets(cDataSheetName).Range("A1"), Sql:=strQuery)
        .RowNumbers = True
        .Refresh BackgroundQuery:=False
        iResultRowCount = .ResultRange.Rows.Count
    End With
When I execute this code I get the error message 'SQL Syntax Error' (Error 1004). When I remove the VBA function from the query in Access, it all works fine.

So my question is; why can't I query a query with a VBA function in Access from Excel?

when the answer is like "because it can't be done", my next question will be:
Who can give me a suggestion for a work-around to achieve the same results?

thanx!

Hi,

i am very new to excel and want to do the next:

i have several sheets with data and in the last sheet I want to get the latest 3 sales of a product, calculating the avarege discount of it and the sum of the units sols in thos latest 3 sales (product may have been sold les than 3 times)

Ejample:

Sheet 1 have the data of the articles
Code Art descripcion price
12 TV 29" 500
13 TV 21" 300
14 TV 20" 250

Sheet 2 has the sales during a period of 2008 orderd by date

Cod Art Date discount units
12 01/05/2008 5 10
12 25/04/2008 5 15
12 22/04/2008 10 50
12 20/04/2008 10 50
14 02/05/2008 40 100
14 01/05/2008 5 10
13 01/05/2008 5 10
13 24/05/2008 35 500

Now what I want to get is for every product we have in sheet 2 the AVG of the discount and the total of the amount sold for the last 3 sales

I have done it in ASP using SQL syntaxes and loops. But never used Excel. Iam sure it is possible, but how?

Hi

I am trying to construct a query output using SQL on named ranges within the same file. This is the first time I'm trying to build this into a macro and I'm encountering a Type Mismatch compile error in the section highlighted in red.

Code:
Sub CCA_S_Jnl()

Dim ws As Worksheet, MePth As String

MePth = ActiveWorkbook.FullName

Application.DisplayAlerts = False
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name = "SQL_Output" Then
            ws.Delete
        End If
    Next ws
Application.DisplayAlerts = True

Sheets.Add.Name = "SQL_Output"

Sheets("SQL_Output").Activate

With ActiveSheet.QueryTables.Add(Connection:="OLEDB;" & MePth, Destination:=Range("A1"))
    
    .CommandType = xlCmdSql
    .CommandText = Array( _
        "SELECT" & _
            "PC_Selection.Profit_Centre," & _
            "Account_Map.Account_Code," & _
            "Sum(Temp_Budget_Tbl.Period_1) AS SumOfPeriod_1," & _
            "Sum(Temp_Budget_Tbl.Period_2) AS SumOfPeriod_2," & _
            "Sum(Temp_Budget_Tbl.Period_3) AS SumOfPeriod_3," & _
            "Sum(Temp_Budget_Tbl.Period_4) AS SumOfPeriod_4," & _
            "Sum(Temp_Budget_Tbl.Period_5) AS SumOfPeriod_5," & _
            "Sum(Temp_Budget_Tbl.Period_6) AS SumOfPeriod_6," & _
            "Sum(Temp_Budget_Tbl.Period_7) AS SumOfPeriod_7," & _
            "Sum(Temp_Budget_Tbl.Period_8) AS SumOfPeriod_8," & _
            "Sum(Temp_Budget_Tbl.Period_9) AS SumOfPeriod_9," & _
            "Sum(Temp_Budget_Tbl.Period_10) AS SumOfPeriod_10," & _
            "Sum(Temp_Budget_Tbl.Period_11) AS SumOfPeriod_11," & _
            "Sum(Temp_Budget_Tbl.Period_12) AS SumOfPeriod_12," & _
            "Sum(Temp_Budget_Tbl.Total) AS SumOfTotal," & _
            "FROM PC_Selection, Account_Map, Temp_Budget_Tbl" & _
        "WHERE" & _
            "PC_Selection.Profit_Centre = Temp_Budget_Tbl.Profit_Centre AND Account_Map.Account_Code =
Temp_Budget_Tbl.Account_Code" & _
        "GROUP BY" & _
            "PC_Selection.Profit_Centre, Account_Map.Account_Code;")
            
    .Refresh

End With

End Sub
Is this a mere syntax error, or something more fundamental? I have gone thru this and made sure all named ranges (table names) and filed names are correct, so still unsure what the error is.

Many thanks
Jon

Excel 2003, XP.

I have a ms query that is retriving data from an exteranl source. I have added filters in the query and then back in my spreadsheet I have created cells and mapped the parameters to get their values from these cells. E.G., get customer # from cell a10. It does my filtering for me.

Now, I have a need for a filter (parameters) where I want to be able to exclude rows based on values the user will enter. This 'exclusion' list will vary each time the user runs the query. I have created criteria in my query, entered values and reviewed the sql syntax but that does not seem to work back in excel.

Can this be done?

Thanks.

Hello all,

Using the code from this link.
http://www.erlandsendata.no/english/...acimportadotxt

I have some code to extract records from a DBF file.

This is the entry from my code which is working fine.

Code:
I wish to have some more flexibility with the macro.

I can not get the following syntax correct. I would like to have something like.
Code:
dbase = "Hammer"
criteria = "RUN_NO is not null"

GetTextFileData "SELECT * FROM & dbase & .dbf WHERE & criteria & , "servertrackingCarboTrack", Range("A1")
Also sometimes I would like to add another Item for the criteria.

ex.
criteria = "RUN_NO is not null and PART_NO is WIDGET"

Hope I explained it well enough.

Thanks to anyone who can offer any assistance.

Ross

This is my VBA Statement:
sSql = "INSERT INTO ExcelLib." & WUser _
& " SELECT Mid(CRSG01,1,2) AS CO, Mid(CRSG02,3) AS Dept," _
& " Mid(CRSG03,1,4) AS Acct, GLH.LHYEAR AS GLYear," _
& " GLH.LHPERD AS Period, GLH.LHJNEN AS JeNo," _
& " DateSerial(Mid(lhdate,1,4),Mid(lhdate,5,2),Mid(lhdate,7,2))" _
& " AS PDate, GLH.LHJRF1 AS JeRef1, GLH.LHJRF2 AS JERef2, " _
& " GLH.LHDRAM AS DrAmt, GLH.LHCRAM AS CrAmt, GLH.LHLDES AS JEDesc," _
& " GLH.LHDREF AS JDocRef, GLH.LHDATE" _
& " FROM BPCSFV60.GLHL01 AS GLH INNER JOIN BPCSFV60.GCRL01" _
& " AS GCR ON GLH.LHIAN = GCR.CRIAN" _
& " WHERE (((Mid(CRSG03,1,4))= '" & Str(Trim(WAcct)) & "')" _
& " AND ((GLH.LHYEAR)= " & WYear & ") AND " _
& "((GLH.LHPERD) between " & WFPeriod & " And " & WTPeriod & " )) " _
& " ORDER BY GLH.LHYEAR,Mid(CRSG03,1,4), GLH.LHPERD,Mid(CRSG02,1,3) "

The run-time error says
SQL0204 MID in *libl not found.

Is there another MID syntax, that I can use?

Thanks


No luck finding an answer? You could always try Google.