IIF - Help In Query & Labels

Aug 11, 2005

Greetings,

Have an address list with over 600 names, and I need to create mailing labels.

Have First Name, Last Name fields, with city, state zip. BUT...

There is also a field called SPOUSE. This field is NOT always populated.

I need a query that I'll use to create the mailing labels that will;

Have the FIRST NAME SPOUSE NAME LAST NAME

If the SPOUSE field is blank, I need the query to filter that out, so that the address lable will only show First Name and Last Name, without a blank space in between.

Also need the query to put the '&' sign in IF there is a spouse.

Example 1: John & Jane Doe
Example 2: John Doe

Any assistance will be appreciated!

Bob in Indy

View Replies


ADVERTISEMENT

Queries :: Query For Shipping Labels

May 29, 2013

I have three related tables, tblRooms, tblAssy and tblParts (Please see Capture.jpg for Relationships)I want a query that will return a line for every part shipped. For instance, if I have a row in tblParts that has a Qty of 2 and a Description of "Part A" and the assembly that uses that part is called for in Room "101" and "102", I'd like 4 separate rows of labels as below

Room 101 Part A
Room 101 Part A
Room 102 Part A
Room 102 Part A

View 3 Replies View Related

Getting Results From 7 Query's And Populate Text Labels

May 18, 2005

Ok, i have tried and searched but could not find a good start to a solution for my problem.
I have a calendar form, which shows the dates and under it text labels.
Now whenever something is present in my table (tblKalender) on those dates, i want it to show in the text labels under the correct date.
Now, i have the results through a query, but i dont want to link the query to the form. Only thing that i have trouble with now is getting the information i need to show to those labels. I have written some code (early stages) which i want to show me the results for testing, later on i can fill the fields. I keep getting an error saying not enough parameters, expected is 1.

Can anybody take a look at the code or help me with this?
My query for day one is called qerKal1 and the fields i need to get back are the hour and the task. The query gets it's criteria from the label above that day (lbDag1), which shows the date.

I have this code in a module and when the form is opened it runs the fucntion.

Option Compare Database
Option Explicit

Const strSQLWhere1 As String = "" & _
"SELECT qerKal1.Uur, qerkal1.Taak, qerKal1.Datum " & _
"FROM qerKal1 " & _
"WHERE (((qerKal1.Datum) = [Forms]![frmKalender]![lbDag1].[Caption]));"

Public Function fFillDay1()
Dim db As DAO.Database
Dim rs As DAO.Recordset

Dim strSQL As String
Dim strText As String
Dim strText2 As String

strSQL = strSQLWhere1
Set db = CurrentDb


Set rs = db.OpenRecordset(strSQL, dbOpenForwardOnly)
Do Until rs.EOF
If strText = "" Then
strText = rs!Uur
strText2 = rs!Taak
Else
strText = strText & ", " & rs!Uur
strText2 = strText & "," & rs!Taak
End If
rs.MoveNext
Loop

rs.Close
MsgBox strText & strText2
Set rs = Nothing
Set db = Nothing

End Function

View 5 Replies View Related

General :: Query To Build Labels For Nametags

Jan 14, 2013

Currently, I use ACCESS to track reservations for various types of events. When I want to make nametags, I download the "attendee" query that I've already set up to EXCEL and then merge into a WORD document. Is there a way to set up in ACCESS so the labels can be printed straight from the database? I've tried, but I can't get the labels to space out properly (EX: 2 across and 6 down) even though I put the correct label # in there.

View 4 Replies View Related

Queries :: Print Shipping Labels - Hide Query Results

Jul 5, 2015

I have a button on a form which is used to print shipping labels. This button runs a Query and then a report using the code below.

Private Sub SaveBtn_Click()
DoCmd.SetWarnings False
DoCmd.RunSQL "Update BookInTable SET DateBookedOut = '" & Me!DateTxt & "' WHERE BarCode ='" & Me![BarTxt] & "'"
DoCmd.RunSQL "Update BookInTable SET BookedOut = True WHERE BarCode ='" & Me![BarTxt] & "'"
DoCmd.OpenQuery "PrintLabelQuery"
DoCmd.OpenReport "Labels", acViewPreview
DoCmd.PrintOut , , , , 1
DoCmd.SetWarnings True
End Sub

But I need both the query results and the report to not be seen. All I want to happen when clicking the button is that a label is printed.

View 5 Replies View Related

Queries :: Setting Parameter Labels Is Causing Query To Return No Data

Jul 3, 2014

My parameters are linked to a form and say:

[Forms]![FormName]![Field] or [Forms]![FormName]![Field] Is Null

Ordinarily this works fine in returning either the selected value or all values if left null.

I need to pull in data from a Crosstab query, which means setting my parameter labels to [Forms]![FormName]![Field].

The problem is that setting the parameter labels is conflicting with pulling all records if the form dropdown is left null.

If I keep my parameters simple and just say [Forms]![FormName]![Field] then the query works with the crosstab data, but I can't do that. I need to show any records if the dropdown is left null.

The crosstab data isn't specifically the problem but needing to set the parameter names seems to be

I think I may have found a workaround by labeling the column headings in the crosstab, which means I don't have to assign parameter labels

It would still be good to know if there's a way of making it work with the parameter labels but this will do for now...

View 7 Replies View Related

Labels

Oct 11, 2004

I have a field in my database that list the number of personnel folders a person has, I would like to print labels for each folder for that person. For example, if a person has 4 folders I would like to print 4 of the same label at once by clicking the command button I have set up to print the label. The default is set to 1 label but I need the number of labels to change according to the number of folders listed. Can this be done and if so how?I will be using Avery 4031-20 labels

View 2 Replies View Related

Dimming Labels

May 24, 2005

how can i dim label that i wtn it to be disable...but visible.
i know i can do it with command button but i dont know how can i do it with label.

View 6 Replies View Related

Autonumbers In Labels

Feb 4, 2005

Uh, hi. I'm new to using access, and I'm wondering if its possible to display an autonumber at the top of a form in a label box. For example, if I had a field set to autonumber and was ading a new record to the database, could I display the autonumber field on the actual form?

View 8 Replies View Related

Using LABELS As BUTTONS!!! Help!

Oct 11, 2006

Does anyone know of an example (code or DB) where someone used labels as buttons? The labels should have special effects when mouse overed, clicked, etc. Also, it should have code that resets button state and checks button state. Any help would be great!!!

View 3 Replies View Related

Change Labels

Jan 9, 2005

how do I create on on click event to change a label in form view so that a user can customise a form??

View 9 Replies View Related

Mailing Labels

Feb 15, 2007

Is it possible to change the format of the mailing labels once the report has been created? I have one that has 24 labels on it, but I need for it to have 30.
Also I can't remember how I created it in the first place. What steps do I need to go through to make a new report?

View 1 Replies View Related

Too Many Blanks In Labels?

Dec 13, 2012

creating mailing labels starting with an Excel Workbook imported into Access. When I get to the point of previewing the labels, there seem to be maybe 10 blank labels. I don't have a clue how this could happen but I obviously don't want to waste the ten or so labels (out of about 90 total). setting the print area and removing empty Worksheets, but nothing works.

View 4 Replies View Related

Skippping Used Mailing Labels

Dec 13, 2005

Hi

I have created a report for labels using the Label Wizard, and found the code on the MS KB about skipping the labels that have already been used, and printing on the next one along.

Page on MS KB (http://support.microsoft.com/?kbid=299024)

When I try using this code in Access 2003 however, it seems to go into some sort of loop, and produces 100+ pages for the report when I try and skip 1 label for example.
Can anyone help me get this working for 2003?

Here's my module code, same as on the site above:


'************************************************* ********
'Declarations section of the module.
'************************************************* ********
Option Compare Database
Option Explicit
Dim LabelBlanks&
Dim LabelCopies&
Dim BlankCount&
DimCopyCount&
'================================================= ========
' The following function will cause an input box to
' display when the report is run that prompts the user
' for the number of used labels to skip and how many
' copies of each label should be printed.
'================================================= ========
Function LabelSetup()
LabelBlanks& = Val(InputBox$("Enter number of used labels to skip"))
LabelCopies& = Val(InputBox$("Enter number of copies to print"))
If LabelBlanks& < 0 Then
LabelBlanks& = 0
If LabelCopies& < 1 Then
LabelCopies& = 1
End Function
'================================================= ========
' The following function sets the variables to a zero
'================================================= ========
Function LabelInitialize()
BlankCount& = 0
CopyCount& = 0
End Function
'================================================= ========
' The following function is the main part of this code
' that allows the labels to print as the user desires.
'================================================= ========
Function LabelLayout(R As Report)
If BlankCount& < LabelBlanks& Then
R.NextRecord = False
R.PrintSection = False
BlankCount& = BlankCount& + 1
Else
If CopyCount& < (LabelCopies& - 1) Then
R.NextRecord = False
CopyCount& = CopyCount& + 1
Else
CopyCount& = 0
End If
End If
End Function


Thanks

View 7 Replies View Related

Count Only Report Labels

May 18, 2006

I have a report that is broken down by regions. Each Region could have multiple entries for each. I would like to have a Summary at the bottom that counts each Region on the report and gives a total for the report. What I am getting is it counts each time there is an entry for the region. I assume since I have the Region as a Header it still thinks it is listing it multiple times. Any help???

View 1 Replies View Related

Chart Labels Bunching Up

Apr 7, 2008

before i pull out any more of my hair, I am making a pie chart and the "Best Fit" function in access charts for form flat out doesnt work. Does anyone have any suggestions on how to get these labels from bunching up?

View 3 Replies View Related

Removing Text Labels

Jul 30, 2007

I have large sets of data that has labels on them. For example "25.56 lbs". I just want the raw numbers. Can someone help with what to type in the update to field of an update query to just keep the numbers? Thanks

View 2 Replies View Related

Data Labels On A Chart

Dec 16, 2005

I'm in the middle of doing a huge survey at work. The end resut will be a report which reflects the survey answers as graphs.

The graph part I have down. There will be one graph for each question. Within each graph are three groupings: Classified, Management, Certificated. Within the groupings you will see at least three bars where each bar represents a grouping of answers.

Example: the classified grouping may have 2 bars. One bar represents 16 classified people who said "Agree" to question 11 and the other bar represents 5 classified people who said "Disagree" to question 11.

...ok, so now I have all that, but I have to have data labels on the graph. I can do that, I know where to click, but unfortunately it comes up as the actual number of people who responded this particular way (agree, disagree, etc.) and I need the data label to show up as a percentage.

Where do I make the change so that it shows the percentage of 16 classified out of 21 who said "Agree"?

Hopefully it's not too confusing. Thanks!

View 2 Replies View Related

Aligning Labels And Controls.

Mar 16, 2006

How do I get my labels and controls to line up vertically? If I click on align and up or down, they all get moved on top of each other with no spaces inbetween them. I want an equal amount of space inbetween them. How do I do this?

View 6 Replies View Related

Odd Behavior On Form Labels

Aug 18, 2006

I have a form with a few labels that for some reason, very often will have a strange looking line above them after I am done clicking on them. I can't seem to find out why this happens. It happens on every label on this particular form.

Please take a look at the attachment for a screenshot.

I'd appreciate any advice you may have on why this happens and how to fix it.

View 5 Replies View Related

Suppress Labels & Fields

Jul 20, 2005

We use an Access database to review multiple banking regulations on a sample of loans each month. I have reports set up to show the findings of different requirements. Each field is populated with a "Yes" "No" "N/A" or is left blank depending on the loan characteristics.

My queries pull regulatory errors if any of the fields = "No" and the reports show all regulatory requirements for that loan. For example, if the loan has real estate as collateral and no flood determination was ran prior to loan closing, they would select "No" from the combobox and it populates the field. All questions dealing with the flood laws populate on the report.

[U]If I want to show ONLY the fields that say "No" with the corresponding label, how do I get all fields that say "Yes" "N/A" or are blank to disappear, the corresponding lable to disappear, and the data to "shrink up" so there is not a lot of blank space?

Thanks for all of your help!
Dradich

View 1 Replies View Related

Printing Labels For A Specified Record

Jul 24, 2014

I am looking to set up a database where I have a table of records that correspond labels to product numbers. The product numbers may have anywhere from 1 to 30 labels that correspond to them, and I want the user to have a form where they search by a product number and then the labels that correspond to that product number are printed out on an Avery label.

Here is how the main data table will look: (everything is simply arbitrary to illustrate)

For example: If I type in "1111" into the form - the labels that print would be AA, AB, AC, AD, & AE

I know how to set up labels to print using forms, but the only way I can think of this working is by looking up the product number in the table, then copying the row of labels into another table (let's call it "labels to print") and pasting them in a column, then printing to the labels, and clearing the "labels to print" table for the next time.

Is this the correct route to go? If so, how do I go about looking up the product number in the main table and copy them to the "labels to print table".

View 6 Replies View Related

Develop GUI To Print Labels

Feb 22, 2013

I have to develop a software that allows users to print data from a GUI. The GUI is a form containing several fields: the user can choose only the fileds he wants to print (I thought to a checkbox to choose the needed fields)

For each field the user can specify: font, x,y coordinate to locate it into the label to be printed

The user can decide also the format (height,width dimensions) of the label

I imagine that the great difficulties is to find the function to create the string to sent to the printer....

View 1 Replies View Related

Change Colour Of ALL Labels In Database

Feb 6, 2006

Hi

I have a problem where the user has certain contraints in colour.

I am trying to change all the colours of labels in the database to purple.

Is there any way to declare it once in the database and all label statements will pick up the new colour. Saves me going through every form. Also there are other users who want to use the database with black.

???:rolleyes:

View 2 Replies View Related

Seperate Text Boxes From Labels

Jun 8, 2006

I am learning access on my own using MS Press step by step manual and it tells me to hold down the shift key while selecting the text boxes that are next to the labels, but when I do the labels get selected also . how do I disconnect the labels from the text boxes in order to insert space between the labels and the text boxes as it intructs me to in the book. I am able to select the labels separately, but it will not allow the text to be selected separately.

View 1 Replies View Related

Data Labels Exporting To Excel

Sep 15, 2004

when i analyze my report in Excel, none of the data labels in my report header are copied across can i set this up so that they do.

Andy

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved