Trying to change Export Grouping

A

access2010

Registered User.
Local time
Yesterday, 22:19
Joined
Dec 26, 2009
Messages
1,098
I / we have been trying to add a new grouping “Watch” for some time without success.
The export group “Holdings”, works, but we cannot get the group “Watch” to work.
Your suggestion will be appreciated.
Crystal
 

Attachments

access2010 said:
I / we have been trying to add a new grouping “Watch” for some time without success.
The export group “Holdings”, works, but we cannot get the group “Watch” to work.
Your suggestion will be appreciated.
Crystal
Click to expand...
Are you trying to Export just the records that contain the text "Watch" ?
Please explain.
 
The export group “Holdings”, works, but we cannot get the group “Watch” to work.
Click to expand...

Define 'work'. Not only do I not know what you have tried, I'm not really certain what you hope to achieve.

"Grouping" implies a totals query. What exactly are you trying to total? How is it not working with the "Watch" group?
 
Crystal, you've worked with us enough to know that we need specifics when you say "doesn't work." Tell us what you get that you shouldn't get, or what you don't get when you should get something. We need to determine the failure mode in order to diagnose problems. It's like going to your family physician and saying "I don't feel well." Do you expect the doctor to immediately prescribe something, or do you expect he might ask you a few more questions before deciding what is wrong with you?
 
The_Doc_Man said:
Crystal, you've worked with us enough to know that we need specifics when you say "doesn't work." Tell us what you get that you shouldn't get, or what you don't get when you should get something. We need to determine the failure mode in order to diagnose problems. It's like going to your family physician and saying "I don't feel well." Do you expect the doctor to immediately prescribe something, or do you expect he might ask you a few more questions before deciding what is wrong with you?
Click to expand...
    • Diagnosis by Osmosis, or
      Patient: "Doctor, my head hurts. I need a brain transplant."
      Doctor: "Wait, I won't know whether to prescribe aspirin or recommend brain surgery until I know what's really going on."
 
There is no "grouping" - no aggregation - just filter criteria for two exports.

Simple debugging here.
Run-time error "Object variable or With block variable not set" on Do While Not RstQ.EOF was a big clue on what to look for.
Error because of line continuation character in:
Code: 
     ' => =>     & "WHERE Investments01_tbl.Sergey = 'Holding' " _
Set RstQ = MyQ.OpenRecordset
Line to Set recordset does not execute. Remove the line continuation character after 'Holding'" or delete the line.

You also repeat ".csv" in both procedures when building file name. This results in a filename like "name.Csv.csv"
Code: 
NM = "Yahoo_Holdings_" & Format(Date, "yyyy_mm_dd") & ".Csv"
Open "c:\Portfolio\" & NM & ".csv" For Output As #1
Avoid repetitive coding. These two procedures could easily be combined into one procedure with argument(s).
Code: 
Private Sub Yahoo_Holdings_CSV_Click()
YahooCSV "Holdings"
End Sub

Private Sub Yahoo_Watch_CSV_Click()
YahooCSV "Watch"
End Sub

Private Sub YahooCSV(strGrp As String)
Dim db As Database
Dim MyQ As QueryDef
Dim RstQ As Recordset
Dim NM As String
Dim SName As String
Dim SubIndName As String

Set db = CurrentDb
Set MyQ = db.CreateQueryDef("", "SELECT Investments01_tbl.Symbol_Stock_Y, " _
     & "Investments01_tbl.Stock_Name, Investments01_tbl.Net_Share_Cost, " _
     & "[Net_Share_Cost]*0.9 AS Stop_Price, Investments01_tbl.Sector, " _
     & "Investments01_tbl.[Sub-Industry],Investments01_tbl.AnalystPrice_Target AS TS FROM Investments01_tbl " _
     & "WHERE Investments01_tbl.Sergey = '" & strGrp & "' " _
     & "ORDER BY Investments01_tbl.Stock_Name")

Set RstQ = MyQ.OpenRecordset

NM = "Yahoo_" & strGrp & "_" & Format(Date, "yyyy_mm_dd")

Open "c:\Portfolio\" & NM & ".csv" For Output As #1
' = => eXPORT TO => c:\Portfolio\
' = = = Sergey Yahoo Requires Column Headers = They should be = Symbol = Name = Cost = Caution = Industry = Sector
'                                                               ======   ====   ====   =======   ========   ======
' = = = Old Print #1, "Symbol_stock,Stock Name,Cost Price,90% Price,Sector,Sub-Industry"
    Print #1, "Symbol,Name,Cost,Caution,Industry,Sector,AnalystPrice_Target"
'              ======,====,====,=======,========,======,======
    Do While Not RstQ.EOF
        For I = 1 To Len(RstQ!Stock_Name)
            If Mid(RstQ!Stock_Name, I, 1) <> "," Then SName = SName & Mid(RstQ!Stock_Name, I, 1)
        Next I
        If RstQ![Sub-Industry] <> "" Then
            For I = 1 To Len(RstQ![Sub-Industry])
                If Mid(RstQ![Sub-Industry], I, 1) <> "," Then SubIndName = SubIndName & Mid(RstQ![Sub-Industry], I, 1)
            Next I
        End If
'        If Len(RstQ!Stock_Name) <> Len(SName) Then MsgBox "Áûëî - " & RstQ!Stock_Name & " ñòàëî -" & SName
        Print #1, RstQ!Symbol_Stock_Y & "," & Trim(SName) & "," & _
            Format(RstQ!Net_Share_Cost, "###0.00") & "," & Format(RstQ!Stop_Price, "###0.00") & "," & RstQ!Sector & "," & Trim(SubIndName) & "," & RstQ!TS
        SName = ""
        SubIndName = ""
        RstQ.MoveNext
    Loop
    Close #1

MsgBox strGrp & " Done!"
End Sub
However, instead of all this looping recordset and writing one line at a time, why not DoCmd.TransferText acExportDelim ? Do calcs in query and export query. Code could modify query object SQL to set filter criteria. Or maybe export a report.
 
Last edited:
June7 said:
There is no "grouping" - no aggregation - just filter criteria for two exports.

Simple debugging here.
Run-time error "Object variable or With block variable not set" on Do While Not RstQ.EOF was a big clue on what to look for.
Error because of line continuation character in:
Code: 
     ' => =>     & "WHERE Investments01_tbl.Sergey = 'Holding' " _
Set RstQ = MyQ.OpenRecordset
Line to Set recordset does not execute. Remove the line continuation character after 'Holding'" or delete the line.

You also repeat ".csv" in both procedures when building file name. This results in a filename like "name.Csv.csv"
Code: 
NM = "Yahoo_Holdings_" & Format(Date, "yyyy_mm_dd") & ".Csv"
Open "c:\Portfolio\" & NM & ".csv" For Output As #1
Avoid repetitive coding. These two procedures could easily be combined into one procedure with argument(s).
Code: 
Private Sub Yahoo_Holdings_CSV_Click()
YahooCSV "Holdings"
End Sub

Private Sub Yahoo_Watch_CSV_Click()
YahooCSV "Watch"
End Sub

Private Sub YahooCSV(strGrp As String)
Dim db As Database
Dim MyQ As QueryDef
Dim RstQ As Recordset
Dim NM As String
Dim SName As String
Dim SubIndName As String

Set db = CurrentDb
Set MyQ = db.CreateQueryDef("", "SELECT Investments01_tbl.Symbol_Stock_Y, " _
     & "Investments01_tbl.Stock_Name, Investments01_tbl.Net_Share_Cost, " _
     & "[Net_Share_Cost]*0.9 AS Stop_Price, Investments01_tbl.Sector, " _
     & "Investments01_tbl.[Sub-Industry],Investments01_tbl.AnalystPrice_Target AS TS FROM Investments01_tbl " _
     & "WHERE Investments01_tbl.Sergey = '" & strGrp & "' " _
     & "ORDER BY Investments01_tbl.Stock_Name")

Set RstQ = MyQ.OpenRecordset

NM = "Yahoo_" & strGrp & "_" & Format(Date, "yyyy_mm_dd")

Open "c:\Portfolio\" & NM & ".csv" For Output As #1
' = => eXPORT TO => c:\Portfolio\
' = = = Sergey Yahoo Requires Column Headers = They should be = Symbol = Name = Cost = Caution = Industry = Sector
'                                                               ======   ====   ====   =======   ========   ======
' = = = Old Print #1, "Symbol_stock,Stock Name,Cost Price,90% Price,Sector,Sub-Industry"
    Print #1, "Symbol,Name,Cost,Caution,Industry,Sector,AnalystPrice_Target"
'              ======,====,====,=======,========,======,======
    Do While Not RstQ.EOF
        For I = 1 To Len(RstQ!Stock_Name)
            If Mid(RstQ!Stock_Name, I, 1) <> "," Then SName = SName & Mid(RstQ!Stock_Name, I, 1)
        Next I
        If RstQ![Sub-Industry] <> "" Then
            For I = 1 To Len(RstQ![Sub-Industry])
                If Mid(RstQ![Sub-Industry], I, 1) <> "," Then SubIndName = SubIndName & Mid(RstQ![Sub-Industry], I, 1)
            Next I
        End If
'        If Len(RstQ!Stock_Name) <> Len(SName) Then MsgBox "Áûëî - " & RstQ!Stock_Name & " ñòàëî -" & SName
        Print #1, RstQ!Symbol_Stock_Y & "," & Trim(SName) & "," & _
            Format(RstQ!Net_Share_Cost, "###0.00") & "," & Format(RstQ!Stop_Price, "###0.00") & "," & RstQ!Sector & "," & Trim(SubIndName) & "," & RstQ!TS
        SName = ""
        SubIndName = ""
        RstQ.MoveNext
    Loop
    Close #1

MsgBox strGrp & " Done!"
End Sub
However, instead of all this looping recordset and writing one line at a time, why not DoCmd.TransferText acExportDelim ? Do calcs in query and export query. Code could modify query object SQL to set filter criteria. Or maybe export a report.
Click to expand...

June7 Thank You / T.h.a.n.k. U.
Your suggestion works​

 
plog said:
Define 'work'. Not only do I not know what you have tried, I'm not really certain what you hope to achieve.

"Grouping" implies a totals query. What exactly are you trying to total? How is it not working with the "Watch" group?
Click to expand...
Yes, June7 suggestion works and thank you for your question
 
The_Doc_Man said:
Crystal, you've worked with us enough to know that we need specifics when you say "doesn't work." Tell us what you get that you shouldn't get, or what you don't get when you should get something. We need to determine the failure mode in order to diagnose problems. It's like going to your family physician and saying "I don't feel well." Do you expect the doctor to immediately prescribe something, or do you expect he might ask you a few more questions before deciding what is wrong with you?
Click to expand...
Yes, June7 suggestion works and thank you for your question
 
You must log in or register to reply here.

Similar threads

A
Grouping Not Working
Replies
10
Views
627
June7
June7
A
Report with two groupings
Replies
7
Views
276
access2010
A
S
Need to create an alias for the totals of a field in query
Replies
5
Views
126
sheckay
S
X
When was my Worker's Last Day Off?
Replies
3
Views
213
MarkK
MarkK
R
Query is fast to run, but very slow to export results
Replies
14
Views
405
Rudolph583
R

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom