Beach Access Software
bchacc at san.rr.com
Thu Nov 30 09:55:17 CST 2006
Keith:
Here's a simple, brute force method of creating a CSV file with quotes
around the strings. But if you need to do the formulas, you'll need to make
a spreadsheet. I'll send you a doc with that method in it off line.
Rocky
Private Sub cmdExport_Click()
strSQL = "SELECT tblEmployer.fldEmployerLBFCode, tblEmployee.fldEmployeeSSN,
" _
& " [fldWorkDetailMonth] & [fldWorkDetailYear] AS [Month] , " _
& "[fldEmployeeLastName] & ',' & [fldEmployeeFirstName] AS Name, " _
& "Sum(tblWorkDetail.fldWorkDetailHours) AS SumOffldWorkDetailHours, " _
& "Sum(tblWorkDetail.fldWorkDetailTotal) AS SumOffldWorkDetailTotal FROM
" _
& "tblEmployer RIGHT JOIN (tblPlan INNER JOIN (tblEmployee INNER JOIN "
_
& "tblWorkDetail ON tblEmployee.fldEmployeeID =
tblWorkDetail.fldEmployeeID) ON " _
& "tblPlan.fldPlanID = tblWorkDetail.fldPlanID) ON
tblEmployer.fldEmployerID = " _
& "tblWorkDetail.fldEmployerID WHERE (((tblPlan.fldPlanName) = '401a')
And " _
& "((tblWorkDetail.fldWorkDetailEntryMonth) = " _
& Forms!frm401aMonthlyReport!cboEntryMonth.Column(0) & ") And " _
& "((tblWorkDetail.fldWorkDetailEntryYear) = " _
& Forms!frm401aMonthlyReport!cboEntryYear.Column(0) & ")) GROUP BY " _
& "tblEmployer.fldEmployerLBFCode, tblEmployee.fldEmployeeSSN, " _
& "[fldWorkDetailMonth] & [fldWorkDetailYear], " _
& "[fldEmployeeLastName] & ',' & [fldEmployeeFirstName] " _
& "ORDER BY tblEmployee.fldEmployeeSSN;"
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
If rs.BOF = True And rs.EOF = True Then
MsgBox "No Records to Export.", vbExclamation
Exit Sub
End If
txtExportFile = db.Name
Do While Right(txtExportFile, 1) <> "\"
txtExportFile = Left(txtExportFile, Len(txtExportFile) - 1)
Loop
txtExportFile = txtExportFile & "401 Text File For " &
Me.cboEntryMonth.Column(1) & " " & Me.cboEntryYear.Column(0) & ".txt"
On Error Resume Next
Kill txtExportFile
On Error GoTo 0
Open txtExportFile For Output As #1
Do While Not rs.EOF
Print #1, """" & Right("00000" & Trim(str(Nz(rs!fldEmployerLBFCode))),
5) _
& """" & "," & """" & Right("000000000" &
Trim(str(rs!fldEmployeeSSN)), 9) _
& """" & "," & """" & rs!Name & """" _
& "," & """" & AlphaMonthYear(rs!Month) & """" & "," _
& rs![SumOffldWorkDetailHours] & "," & rs![SumOffldWorkDetailTotal]
rs.MoveNext
Loop
Close #1
MsgBox "Export Done.", vbExclamation
End Sub
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Keith Williamson
Sent: Thursday, November 30, 2006 7:39 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Still on the Reporting
Thanks, Rocky.
Well...it is only out of the running...because I couldn't find any
reference online for doing this....that wasn't A) bought; and B)
problematic. If you've got something otherwise.....I'd LOVE to put it
back into the running.
:)
Thanks,
Keith E. Williamson | Assist. Controller| kwilliamson at rtkl.com
RTKL Associates Inc. | 901 South Bond Street | Baltimore, Maryland
21231-3305
410-537-6098 direct | 410-276-2136 fax | www.rtkl.com
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Beach Access
Software
Sent: Thursday, November 30, 2006 10:21 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Still on the Reporting
Keith:
I've also got a module that exports to a comma delimited, CSV file with
quotes around the text fields. Can send that off-line if it would help.
Why is CSV out of the running now?
Rocky
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Keith
Williamson
Sent: Thursday, November 30, 2006 7:08 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Still on the Reporting
Man...I forgot what a pain in the A@& getting reports out of Access is.
I asked earlier how to export a report into csv format....doesn't seem
that is likely. I need to ultimately generate a csv format file, to
import into our sql-based application. This is for journal entries. I
have written a couple of reports to generate journal
entries....utilizing the different levels of grouping and summing. A
good bit of the report is taking data, at different groupings, and
calculating percentages to apply to user-entered data (for allocating
overhead to different entities and account numbers.) Since csv is out,
I am resigned to bring the data into excel...and then export to csv
format.
The problem is that, oddly, when I am looking at the data on the report
(on screen) and then hit the "Analyze It with Excel" button....the
columns come over in different order than the report. Plus, one of the
fields is a text field = "00". It keeps coming into Excel as = 0
(general format.) I can't think of a way to store the values, and
export values and formulas to Excel...as Rocky suggested.
Any help is greatly appreciated.
Regards,
Keith E. Williamson | Assist. Controller| kwilliamson at rtkl.com
RTKL Associates Inc. | 901 South Bond Street | Baltimore, Maryland
21231-3305
410-537-6098 direct | 410-276-2136 fax | www.rtkl.com
<http://www.rtkl.com/>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.430 / Virus Database: 268.15.0/557 - Release Date:
11/29/2006
4:15 PM
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.430 / Virus Database: 268.15.0/557 - Release Date: 11/29/2006
4:15 PM