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
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.
I have a repairs db that records [serial number] and [date in for repair], and other info.
I've been asked to make a report to summarize the number of units [serial number] that come back for repair by time interval between [ship date] and the first [date in for repair].
the [serial number] and [ship date] are in a linked excel file table which contains unique [serial number] entries.
The [serial number] and [date in for repair] are in the access table, there may be multiple [serial number]s entered with different [date in for repair] as in repaired multiple times.
1st query: I want to list all the [serial numbers] in the access table with only the earliest [date in for repair] returned.
2nd query: then I need to compare the 1st query results list of [serial numbers] and [date in for repair]to the excel table list of [serial numbers] and [ship dates],
I've found the =DateDiff('d'[ship date],[date in for repair])
I haven't done a query with two data sources before and not sure how to go about getting the DateDiff for each serial number
Once I get results from the DateDiff, for each serial number, I am comfortable handling the Sum(IIf statements to summarize the results and do the serial number range select criteria in the form.
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...
I am using a basic Order form and Order Details subform to capture my information. Customer is selected, then Products and Quantities are filled in... the subform calculates the total price. This all works great. Then I have a problem.
I have some national customers that require shipping to multi regions. I would need to select the regions and it has to calculate a new case cost for each product for each area. Could be up to 100 (10 products x 10 areas) new calculated prices.
Then for other customers, I will use just the case cost for the whole order (multiple products) and add one shipping fee to cover the drop. 3 products and only 1 fee added to the total.
Currently, my Order Form uses a Tab page and the Order Details are on the first tab. I could put the Shipping Info on the second tab, I just don't know how to add up to 10 different shipping regions to generate 10 new prices or the one drop fee to the total price.
So I have the following query which works perfectly (and will be use to create a report) but when the query runs to create the report I also want it to mail merge some of the items into word to be used on address labels.
Code:
SELECT Dunmow.Date, Dunmow.Undeliverable, Dunmow.Duplicate, Dunmow.[OTM with XXXXXX], Dunmow.[Landlords Salutation], Dunmow.[LL Address 1], Dunmow.[LL Address 2], Dunmow.[LL Address 3], Dunmow.[LL Address 4], Dunmow.[LL Address 5], Dunmow.[LL Postcode], Dunmow.[Letter 1], Dunmow.[Letter 2], Dunmow.[Letter 3], Dunmow.[Letter 4], Dunmow.[Letter 5], Dunmow.[Letter 6], Dunmow.[Letter 7], Dunmow.[Letter 8]
FROM Dunmow
WHERE (((Dunmow.Date)=DateAdd("d",-7,Date())) AND ((Dunmow.Undeliverable)=False) AND ((Dunmow.Duplicate)=False) AND ((Dunmow.[OTM with XXXXXX])=False) AND ((Dunmow.[Letter 1])=False));
So thats my query, What I want it to do is to take the following
[Landlords Salutation]
[LL Address 1]
[LL Address 2]
[LL Address 3]
[LL Address 4]
[LL Address 5]
[LL Postcode]
and use it for a merge item to create the labels required.
I have a standard word document/label document I wish to paste this into.
I am using a cartesian query to create multiple copies of a record to use for printing labels.
Here's my query that produces the cartesian result:
SELECT tblCount.CountID, tblDeliveryOrders.DeliveryOrderNum, CurrentCY.Deliveryorderlineitemnum, CurrentCY.Quant, CurrentCY.UOM1, tblContainerSizeCodes.SizeCode, tblContainerTypeCodes.TypeCode, CurrentCY.WasteDescription, tblEtidDodaac.EtidDodaac, CurrentCY.ETIDDocNum, CurrentCY.Pounds, tblEPAWasteCodes.[EPAWasteCodes(1B)]
[Code] ....
This works just fine in creating the desired result - EXCEPT I don't get all the records.
When I remove the 'cartesian table', and right join everything, then I get the correct results. If I keep everything as-is and reintroduce the cartesian table, then I get an error about there being an ambiguous outer join.
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
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
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 RelatedI 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 Relatedhow 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.
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 RelatedDoes 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 Relatedhow 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 RelatedIs 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?
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 RelatedHi
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
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 Relatedbefore 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 RelatedI 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 RelatedI'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!
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 RelatedI 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.
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
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".