"Bad file.." when creating interface file with Macro-derived Fields

[ShaneSQ]

Hello hello,

I went to run a macro to produce a csv file like normal and I got a "Bad File name or number" error, and the file was only created with the headers.

the macro is as follows:

Option Explicit
Sub ProbToCSV()

Dim today as string
today = format$(Date$, "medium date")

Dim Filename as string
Filename = "C:\HRDocuments\Probation-(" + today + ").csv"
'Filename = "C:\Documents\HEU\Probation\Probation-(" + today + ").csv"

Dim FileNumber as Double
FileNumber = FreeFile

Open FileName For Output ACCESS Write Lock Read Write as FileNumber

Print #FileNumber,"Last Name,FirstName,Type,Position,Probation Status,Hours Worked,Hours Remaining,Days Remaining,PayRateCheck,Pay Rate,BenefitCheck,MSP,GroupHealth"

GetRandom 1

Dim i as integer
Dim FileNum as string
For i=1 to TotalRecords

   If FileNum <> Field$("FILE#") Then

      Print #FileNumber,Field$("LASTNAME")+","+Field$("FIRSTNAME")+","+Field$("EMPLOYEETYPE")+","+Field$("Pos")+","+Field$("Probation")+","+SumField$("Totalwkdhrs","Derived Field",1,"Sum")+","+Field$("ProbtnHrsRemain")+","+Field$("DaysServiceRemain")+","+Field$("IncreaseCheck")+","+Field$("RATE1AMT")+","+Field$("Benefits")+","+Field$("MSP")+","+Field$("GroupHealth")

      FileNum = Field$("FILE#")
   
   End If

   GetNext

Next i

End Sub

When I change the "Print #FileNumber..." line that is within the conditional to just:

Print #FileNumber,Field$("LASTNAME")

.. I don't get the error, but the file is just headers followed by a single last name.


The fields "Probation", "ProbtnHrsRemain", "DaysServiceRemain", "IncreaseCheck", and "Benefits" are all macro-derived.

Here is an example ("Probation"):

Option Explicit
Sub Probation()

Dim EEType as string
EEType = Field$("EMPLOYEETYPE")

Dim Today as variant
Today = DateValue(Date$)

Dim HireDt
HireDt = DateValue(Field$("HIREDATEROE"))

Dim DaysService
DaysService = today - HireDt

Dim hours
hours = Val(SumField$("Totalwkdhrs","Derived Field",1,"Sum")) 

If hours > 480 or DaysService > 180 Then

          DerivedField "Past Probation"

Else
     If EEType <> "C" Then

          If DaysService > 90 Then
               DerivedField "Past if working in Line for 3 months"
          ElseIf hours >400 Then
               DerivedField "Approaching Probation End"
          Else
               DerivedField "Probationary"
          End If

     ElseIf EEType = "C" Then

         If  DaysService > 160 or hours > 440 Then
              DerivedField "Approaching Probation End"
         Else
              DerivedField "Probationary"
         End If

     End If

End If

End Sub

Any ideas would be much appreciated! Thank you!

 

[CharlesCook]

Add the fields back one at a time and see if you can narrow it down a bit.

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[ShaneSQ]

Hi Charles,

I went through them all, and the only fields that cause the error are Macro-derived fields, any of them.

I tried Printing a macro-derived field result outside of the loop, right after the "GetRandom 1", and it gave the same error.

Are you supposed to be able to deal with macro-derived field results in macros?


I managed to figure out the looping problem, and changed "FILE#" to "REPORTS.V_EMPLOYEE.FILE#". This did not help the error

Thanks for any and all help!

 

[CharlesCook]

do the macro derived fields look ok in the report itself?

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[ShaneSQ]

They look perfect. Drat.

I removed "#FileNumber" from the print command within the conditional. The output to the screen was perfect!

I removed the "#" and got perfect output to the screen, with a "1" at the beginning of every record.

I tried replacing the "FileNumber" variable with "1", and got the same error.

For whatever reason, it seems like it just doesn't want to give my macro-derived field results to the file. Is there some setting in the application that needs to allow this? I'm grasping at straws now...

Thank you!

 

[CharlesCook]

I have been working with ReportSmith since 1994 version 1.2(ish) and I don't think I have ever tried this. I always use SQL Derived Fields to do this.

This is what I tried...

My Macro Derived Field:

Sub simple()
DerivedField "CharlesCook.com"
End Sub

My Export Macro:

Sub ProbToCSV()
FileNumber = FreeFile
FileName = "U:\test.csv"
Open FileName For Output Access Write Lock Read Write As FileNumber
GetRandom 1
For i=1 to 100

Rem This works
'print #FileNumber,Field$("EMPLID")

Rem This does not
'print #FileNumber,Field$("EMPLID"),Field$("simple")

GetNext
Next i
End Sub

Can you convert your report to use SQL Derived Fields?

Have you talked to ADP Support?

Sorry I can't be more helpfull.

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting

 

[ShaneSQ]

Thanks for looking into it Charles! What a shame!

The Second big code block in my first post is an example of one of the macro derived fields, some are more complex. I probably should learn SQL well-enough to do the translation, sometime...

I called ADP, and, no surprise, they do not support this feature of ReportSmith!

Maybe I'll just spend the time to align the fields properly and do a "SaveReport Filename, 5" I'd really rather not put the code of each macro-derived field into the conditional..


Thanks again!