Posts by Mattheq

Re: MDX Return min value (excluding empty and 0)

Hello.

You can run a nested query. First query returns all non-zero data. Second query returns minimum value in the first query.

Assuming your table is called "myTable" and the field of interest is "MinOrder", the following example should achieve what you are looking for.

select Min([myValue]) from (select MinOrder as [myValue] from myTable where MinOrder > 0)

This should return the minimum value for all days where your orders are above zero.

Hope this helps.

Re: Access Populate YTD & 3 Month Total Column

Hello.

Populate the required fields with a query.

SELECT Year, Month, UPC, Sum(Dollars) AS SumOfDollars, Sum(Units) AS SumOfUnits
FROM MyTable
GROUP BY Year, Month, UPC
HAVING (((Year)=2014));

If you want to summarise over a three month period, you can add a Month criteria to add this level of summary.

Hope this helps.

Re: Export Excel sheet directly to MSSQL using script

Hello Barry,

If it's a stored procedure (as indicated by the OP), it will be a query (or SQL View) saved on the SQL server itself that returns a recordset. Without knowing more about the database or the stored procedure itself, I can't be clearer that that.

Presumably the stored procedure doesn't have any parameters (variable fields or constraints) associated with it, as the OP has not appended these in the code.

Hope this helps.

Re: Can Access VBA create table with more than 33 fields?

Hello,

I was able to run the query after editing the data type for your 33rd field. It may be as simple as a typo where your field data type is "TXT" instead of "TEXT".

No issues with running this on Access 2010. Which version of Access are you using?

Hope this helps.

Re: Is there VBA code to reference an array of worksheet names to a list?

Hello,

I think there are a number of ways you could achieve your goal:

1. Create a Table of Contents worksheet within your workbook by following the code in this article http://blogs.office.com/2011/0…e/?utm_source=twitterfeed Then follow your original code idea of scrolling through the worksheet for all worksheet names.

2. Loop through the sheets in your workbook and exclude the data source and chart sheets from your code

Sub Worksheet_Format()
Dim intWS As Integer


For intWS = 1 To ActiveWorkbook.Sheets.Count - 1
    
    Select Case Worksheets(intWS).Name
        
        Case "Data Sheet 1", "Data Sheet 2" 'Add your 10 data source or chart sheet names here
            'Do nothing
        Case Else
            With Worksheets(intWS)
                .Range("A10:D20").ClearContents
            End With
    End Select
    
Next


End Sub

Hope this helps.

Re: Adding spaces into existing text Strings of Variable length

Hello Carl,

You can achieve the desired outcome by selecting the appropriate column and use the "Replace" function within Excel.

Simply replace "," with ", "

To access the "Replace" function on a PC, press "CTRL+H".

Hope this helps.

Re: In VBA Excel Using a subroutine argument to pass on to VLookup to make it genera

Hello again,

I understand that your TableX variable is returning the value "Table2".

This formula:

.Range(Cells(intTHRow + 1, iEmptCol), Cells(35, iEmptCol)).Formula = "=VLOOKUP(TableX[@Ticker],Table1,3,FALSE)"

is not incorporating the variable, which is why you need to change the code to the example shown in my previous post.

For instance, if I had code that writes a formula to cells in different rows, I would use something like:

Dim i As Integer


For i = 1 To 10
    Cells(i, 1) = "This is row " & i
Next

Your code is more like the following example:

Dim i As Integer


For i = 1 To 10
    Cells(i, 1) = "This is row i"
Next

Re: In VBA Excel Using a subroutine argument to pass on to VLookup to make it genera

Hello,

it looks like you're not passing the variable to the formula.

Try replacing this line of code:

.Range(Cells(intTHRow + 1, iEmptCol), Cells(35, iEmptCol)).Formula = "=VLOOKUP(TableX[@Ticker],Table1,3,FALSE)"

with this:

TableX = "Table2" 'TableX should be a string variable
.Range(Cells(intTHRow + 1, iEmptCol), Cells(35, iEmptCol)).Formula = "=VLOOKUP(" & TableX & "[@Ticker],Table1,3,FALSE)"

Hope this helps

Re: Export Excel sheet directly to MSSQL using script

Hello ikith,

I presume you checked the presence of the "headcountstest" stored procedure? This looks to be the first place to start. Make sure you haven't spelled it incorrectly, and remove the "dbo." from within your VBA.

Hope this helps.

Re: SQL Not Like and handle blanks

Hello Smithy7876,

I replicated your table as much as I could given the information and came up with the following SQL table:

[TABLE="width: 500"]

Field Name

Data Type

Allow Nulls

ProjID

float

No

FolderName

nvarchar(50)

No

EntityCode

nvarchar(50)

No

ParentName

nvarchar(50)

Yes

IsActive

nchar(10)

No

[/TABLE]

Populating this table with the sample data you provided, I ran the query shown below:

SELECT        ProjID, EntityCode, ParentName
FROM            dbo.Folders
WHERE        (EntityCode = 'TestCases') AND (ParentName IS NULL OR NOT (ParentName LIKE '%/%'))

Results from the query were as follows:

[TABLE="width: 500"]

ProjID

EntityCode

ParentName

11888

TestCases

NULL

11888

TestCases

Level 1

[/TABLE]

Hope this helps

Re: Display message box if cell value changes....

Hello Varun,

Try copying and pasting the example code into the sheet code within the VBA editor environment. Make sure you edit the target row and column to reflect where your time will be displayed.

Re: Display message box if cell value changes....

Hello Varun,

Have you considered a Worksheet Change event?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim msgResponse


'Code assumes that the Target Time is shown in cell A1 - change to meet your requirement


If Target.Row = 1 And Target.Column = 1 Then
    
    If Target.Value > 7 Then
        msgResponse = MsgBox("Target Time Exceeded", vbCritical)
    End If
    
End If
End Sub

Re: Access Database vba method to return table field names

Quote from G John;685788

I think I might have mislead you with my question. Correct me if I am wrong, but the SQL statement would be used to retrieve the actual data from the table, right?

My current goal is to actually return the field names themselves as text in an array, because I won't know the field names, or the order they are in.

What I tried to say earlier, but didn't make my point very clear, was that the only way I had come up with was something like this:

Code

For Each Field In Recordset.Fields
fieldarray(i) = Field.Name
i = i + 1
Next Field

The main issue I foresee with this method is that, in past experience, looping through a collection like this wouldn't necessarily return the fields in any particular order.

I appreciate the guidance you have provided me so far, thanks!

Display More

This methodology works for me (syntax not tested, please check before using)

strSQL = "select * from Table"

Open the recordset.

for i = 0 to rst.field.count -1


Fieldarray(i) = rst.field(i).name


next

close recordset.

Re: Update multiple records in access from Excel also close access when done

Hello.

Do you specifically need Access to be open to do this? If not you can just open a connection and update the table using an update statement. You need to add a reference to Microsoft ActiveX Data Objects 2.8 Library for this solution (if you haven't already added it). This solution does not require the access database to open at all (only a connection to it).

Public stCon As String
Public stDB As String
Public stPass As String
Public strSQL As String
Public cnt As ADODB.Connection
Public rst As ADODB.Recordset




Sub OpenConn()


    Set cnt = New ADODB.Connection
    Set rst = New ADODB.Recordset
 
    stDB = "C:\Users\n576546\Desktop\Tech Review Reporting.accdb"
      stPass = "saloba3415"
        'Create the connectionstring - After http://www.connectionstrings.com/access-2007
    stCon = "Provider=Microsoft.ACE.OLEDB.12.0;" _
    & "Data Source=" & stDB & ";" _
& "Jet OLEDB:Database Password=stPass;"
   
    With cnt
        .Open stCon
        .CursorLocation = adUseClient
    End With


End Sub


Sub CloseConn()
    If CBool(rst.State And adStateOpen) = True Then rst.Close
    Set rst = Nothing
    If CBool(cnt.State And adStateOpen) = True Then cnt.Close
    Set cnt = Nothing
End Sub




Sub UpdateDatabase()
Dim intRows As Integer


OpenConn


Do While Cells(intRows, 1) > 0
    strSQL = "update [Daily Reconciliation] set " _
        & "[Bank Account Name] = '" & Cells(intRows, 2) & "', " _
        & "[Date Final Revision Completed] = " & Cells(intRows, 4).Value _
        & "[Tech Reviewer] = " & Cells(intRows, 5).Value _
        & "[Technical Review Date] = " & Cells(intRows, 6).Value _
        & "[Final Technical Revision Completed] = " & Cells(intRows, 7).Value _
        & "[Returned Date] = " & Cells(intRows, 8).Value _
        & "[Technical Completed Correctly] = " & Cells(intRows, 9).Value _
        & "[Comments] = " & Cells(intRows, 10).Value _
        & "[Tech Review Score] = " & Cells(intRows, 11).Value _
        & "[Header] = " & Cells(intRows, 12).Value _
        & "[Bank Stmt Bal] = " & Cells(intRows, 12).Value _
        & "[Signature] = " & Cells(intRows, 14).Value _
        & "[Outstanding Items match detail] = " & Cells(intRows, 15).Value _
        & "[In Transit Item] = " & Cells(intRows, 16).Value _
        & "[Ending Balance zero] = " & Cells(intRows, 17).Value _
        & "[Calculator Tape] = " & Cells(intRows, 18).Value _
        & "[FontsSizeColumnWidths] = " & Cells(intRows, 19).Value _
        & "[Dates/Aging/% Items] = " & Cells(intRows, 20).Value _
        & "[Department for Referral] = " & Cells(intRows, 21).Value _
        & "[ReadablePrintout] = " & Cells(intRows, 22).Value _
        & "[Referral backup accurate] = " & Cells(intRows, 23).Value _
        & "[Offsets] = " & Cells(intRows, 24).Value _
        & "[Items loaded to matrix] = " & Cells(intRows, 25).Value _
        & "[Items > 6 days referred] = " & Cells(intRows, 26).Value
    strSQL = strSQL & "[Items > 10 days referred] = " & Cells(intRows, 27).Value _
        & "[Items > 15 days referred] = " & Cells(intRows, 28).Value _
        & "[Items > 30 days referred] = " & Cells(intRows, 29).Value _
        & "Where [Account Number] = " & Cells(intRows, 1).Value & " AND [Date of Reconciliation] = '" & Format(Cells(intRows, 3).Value, "MM/dd/yyyy") & "'"
     'Date format must use uppercase MM.  Lowercase mm will use minutes, not month


    rst.Open strSQL, cnt, adOpenStatic, adLockOptimistic


    intRows = intRows + 1
Loop


CloseConn


End Sub

Hope this helps.

Re: Explaination Needed

A bit of quick playing around with recording macros yields the following:

Public Sub SEDMSolver() 
SolverReset 
'Constraint: Cell F5 >= 0.1 
SolverAdd CellRef:="$F$5", Relation:=3, FormulaText:="0.1" 
'Constraint: Cell F4 >= 0 
SolverAdd CellRef:="$F$4", Relation:=3, FormulaText:="0" 
'Constraint: Cell F5 <= 1 
SolverAdd CellRef:="$F$5", Relation:=1, FormulaText:="1" 
'Solver Solution MinMaxVal:=2 Means find Min Value 
SolverOk SetCell:="$G$8", MaxMinVal:=2, ValueOf:="0", ByChange:="$F$4:$F$5" 
SolverSolve userFinish:=True
End Sub

Re: Accessing SQL using VBA Code

Hello do0bee,

Where does the code fail? I suspect it's the formation of your command text. Clarify your string with spaces after the commas in the select part, and format your date correctly. YOu also don't need to specify the database (catalog) in the FROM clause, as that is set in your connection string. Try the following and let me know if it works for you.

SQL_String = "SELECT date, agreement_type_id, cpe_id, shaw_entity_family_id, margin_usd FROM yak_interim_excess_deficit WHERE date = '01-05-2013' AND strategy_id = 1760076"

Hope this helps.

Re: Query using OraOLEDB with WHERE and BETWEEN

Hello Maria,

You are missing an ' in the end date in the code you posted. Could be as simple as that.

'Select EmpassignHist table, enter query string
cmd.CommandText = "SELECT * FROM DB.TableRef WHERE field BETWEEN '01-03-2013' AND '03-03-2013'"

If that doesn't resolve your issue, try encapsulating the WHERE criteria in brackets.

'Select EmpassignHist table, enter query string
cmd.CommandText = "SELECT * FROM DB.TableRef WHERE (field BETWEEN '01-03-2013' AND '03-03-2013')"

You may also need to format your date to the appropriate format. Some queries can go awry due to this whereby day and month parts of the dates can be transposed (3 January or 1 March for your start date?) You can do this using the following example to ensure you know exactly what format the dates will return:

'Select EmpassignHist table, enter query string
cmd.CommandText = "SELECT * FROM DB.TableRef WHERE (field BETWEEN '" & format(dateStart, "MM/dd/yyyy HH:mm") & "' AND '" & format(dateEnd, "MM/dd/yyyy HH:mm") & "')"

Hope this helps.

Re: Retrieve Range Name When Range is Only 1 Cell

Hello.

Answered my own question.

set rng = Selection
MsgBox rng.Name.Name

A single "name" only returned the cell address (e.g. F32). Name.Name extracts the cell named range.

Thanks anyway.

Hello.

Is it possible to retrieve a named ranged for a range which consists of only one cell? I've seen some examples that apply to ranges with multiple cells, but they don't work for single cell ranges.

Any help would be appreciated.

Re: Duplicate Command Buttons on different Worksheets

Steve,

Try something like this:

Application.ScreenUpdating = False
 
'Your existing code goes here
'You will want error trapping to make sure that screen updating is true if your code breaks.
 
Application.ScreenUpdating = True

This should prevent your users from seeing a flickering screen.

Hope this helps.