Printing Mailing Labels starting at specified label 1

[LRBFAN]

thread149-1421170

CR2008 Foxpro DB using ODBC

I've tried using lbass's suggestions from the above Thread on how to print mailing labels starting at a certain label, BUT so far I've had no success. It ALWAYS prints on the first label instead of the label I specify in the parameter.

I created a command with the following SQL:

Select Students.`lname`,
Students.`fname`,
Students.`address`,
Students.`city`,
Students.`state`,
Students.`zip`
From Students
Union All
Select Null,Null,Null,Null,Null,Null
From Students
Where {?Label_No} >= 0
Order by 1

Parameter is {?Label_No}, it's a number and the prompt text is: "Start printing on what label number?"

I have unchecked Suppress Bank Section in all Detail sections and entered the following in each of the associated X+2 boxes:

recordnumber >= {?Label_No}

I am sorting by Students.lname ascending.

As a test I added "isnull(Students.lname)" to the Record Selection Formula and I get ONE null label on label #1 even though ?Label_No = 6, but NO null labels when I don't have a Record Selection Formula.

Does that mean there is something wrong with the Select Null,Null,Null,Null,Null,Null in the SQL??

Any suggestions lbass? Much appreciated.

 

[lbass]

Make sure you don't have "convert nulls to default values" checked in file->report options.

-LB

 

[LRBFAN]

Convert Database NULL Values to Default" and "Convert Other NULL Values to Default" are both unchecked.

 

[lbass]

Do you have "select distinct" records checked (database->select distinct records?) Are you showing the actual command here?

-LB

 

[LRBFAN]

I am showing the actual command which I copied/pasted from the command. Database->select distinct records AND Report Options->select distinct records is NOT a highlighted option for me to check/uncheck.

 

[crystalkiwibruce]

Are you sure it's not a sorting issue? Sort by a field so the NULL values appear first.

Bruce Ferguson

http://www.crystalkiwi.com/nlog.htm

 

[LRBFAN]

As I mentioned above, I am sorting by Students.lname ascending.

 

[LRBFAN]

Lbass......I used your example/code using the Xtreme Sample Database and of course it works perfectly Both our reports use ODBC but I'm accessing a FOXPRO DB. I'm guessing my version isn't creating NULL fields/records from the SQL Select?? Hmmmmmm.

 

[lbass]

Try this--replace each "null" in the command with: ' '. This seemed to work as well as using null.

Thanks for testing with the Xtreme database--that was what I used to create the solution.

-LB

 

[LRBFAN]

Thank You Lbass.....replacing "null" with '' (without a space in between) worked. Now I'm having 2 additional issues that I've tried to resolve but can't figure them out quite yet.

Issue #1 - I have the following @Client_Name formula that I would like to use which is....Lname + ", " + Fname. If I put the formula on the first Detail line the report prints out hundreds of BLANK labels before the ones I want. Using just Lname and Fname prints fine. Is there a way to SELECT Lname + ", " + Fname in the SQL instead of Lname and Fname like I'm doing?

Issue #2 - Here is a slightly different Command SQL where I want to select records using {?Label_No} and additional parameters :

Select Students.`lname`,
Students.`fname`,
Students.`client_no`,
Students.`citation`,
Students.`group`
From Students
Where Students.`stat_date` = {?Status_Date} and
Students.`status` = {?Status} and
Students.`program` = {?Program}
Union All
Select '','','','',''
From Students
Where {?Label_No} >= 0
Order by 1

There are no problems with {?Status_Date} or {?Label_No}.

I get errors when using either {?Status} or {?Program} which are strings. If I test the SQL using just Students.`status` = {?Status} I get "Column 'A' is not found" error message if I enter "A" in the parameter. If I test the SQL using just Students.`program` = {?Program} I get "Column 'FO' is not found" error message if I enter "FO" in the parameter.

Something's wrong with the SQL obviously but if I create a different label report NOT using the COMMAND or @Label_No and just selecting records using the Selection formula, I get desired results and here is the SQL it generates:

SELECT
students.`client_no`, students.`lname`, students.`fname`, students.`citation`, students.`status`, students.`stat_date`, students.`group`
FROM
`students` students
WHERE
students.`stat_date` = {d '2012-04-09'} AND
students.`status` = 'A' AND
students.`program` = 'FO'

Of course I would want to implement Issue#1's solution to this as well.

Any ideas?? Thanks for all your time and effort

 

[LRBFAN]

Finally able to figure out issues 1 and 2 and learned some SQL in the process. Now I have issue #3.

When I create a new label program using the Add Command, the labels print at the correct starting label. When I REFRESH, the labels print at the correct starting label BUT it prints 2 labels for each student selected instead of one. Here is the SQL:

Select RTRIM(Students.`lname`) + ', ' + RTRIM(Students.`fname`) AS Client_Name,
Students.`client_no`,
Students.`citation`,
Students.`group`
From Students
Where Students.`stat_date` = {?Status_Date} and
Students.`status` = '{?Status}' and
Students.`program` = '{?Program}'
Union All
Select '','','',''
From Students
Where {?Label_No} >= 0
Order by 1

I have unchecked Suppress Bank Section in all Detail sections and entered the following in each of the associated X+2 boxes:

recordnumber >= {?Label_No}

I am sorting by Client_Name ascending and have selected Avery 5266 File Folder labels.

Thanks in advance for any help.

 

[lbass]

Well, what did you do to fix those two issues? I tested a query like this without causing any duplicate records, so I'm wondering whether there is something in your table causing multiple records per student.

-LB

 

[LRBFAN]

Issue #1 fix - Select RTRIM(Students.`lname`) + ', ' + RTRIM(Students.`fname`) AS Client_Name,

Issue #2 fix - Students.`status` = '{?Status}' and
Students.`program` = '{?Program}' .......I never saw ANY examples of putting single quotes around a STRING Parameter. I just tried it and it worked.

There are no dups in Students table. Like I said, it prints perfectly the first time right after creating the report. Each subsequent time it prints 2 labels for each student.

Don't know if this helps, but when I browse data for each of the 4 fields, there is one blank/null record followed by the records I'm expecting based on my parameters.

I haven't tried testing with Extreme Sample Database but that is my next step. I believe there is a table that has first and last name that I can concatenate to simulate what I'm trying to do. And I want to use a String Parameter as well.

Did your test use first and last name or String Parameter?

Thanks again for your willingness to help.

 

[lbass]

If you take the same query and add it to a new (non-label) report, and then place the fields in the detail section, do you again see duplicates? I can't recreate this issue.

-LB

 

[LRBFAN]

I created a non label report using the same Command and entered the same code in the Suppress Blank Section etc, and had the same result.....the first time prints perfectly and all other times after that prints dups. I've used this table 200+ times before but first time using the Add Command/SQL.

Also, I too could NOT recreate this problem using Employee Table in Extreme Sample Database.

 

[lbass]

You could try attaching your report and I will take a look at it, since I can't recreate the issue.

-LB

 

[LRBFAN]

Thanks.....I have attached (hopefully) the report with Saved Data. It is correctly printing 4 labels starting at label #3. If I rerun it, it will print 8 labels starting at label #3

 

[LRBFAN]

Oops....looks like it didn't attach. I tried to attach by entering C:\Custom Reports\ADI\Test File Folder Labels.rpt in the Step 3 box. What's the correct way? Thanks.

 

[lbass]

You should upload it to MediaFire. Click on the link below.

-LB

 

[LRBFAN]

Finally figured out how to attach....I think