I have two numeric fields which make up what looks like a full decimalised number ie 20.22 - only I am unable to put the decimal point in between the two numbers.
I thought about putting in an unbound object such as a picture of a full-stop but cant get it anchored at the correct angle to make it look convincing in the report.As this is such a huge database I have tried to amend the tables, but it loses vital data ?
I have run across a problem where I have an unbound control on a report that "used to work just fine" until today. It is a concatenated string:
Code:
=[ProgramCity] & "," & (" "+[Programstate]) & (" " & Left([ProgramZip4],5)) & (" "+[ProgramCpsAbbreviations])
I have this on two slightly different reports, where only one line of the report is different. One report works fine, no problems. All the data is there. But on the second report I get the dreaded #Name? error.
So I amended my query and used
Code:
CSZ:[ProgramCity] & "," & (" "+[Programstate]) & (" " & Left([ProgramZip4],5)) & (" "+[ProgramCpsAbbreviations])
And now set the field source as CSZ and it now works fine.
So my question is: Why does the concatenated code not work sometimes in a report but the same code in a query works fine?
Do reports not like concatenated fields?
I have an unbound combo box on a report that looks up to a table of address data.I have a specific requirement where if one of the bound fields on the report is a specific value, I want the combo box to show data. If the bound control on the report is a different value, I want the data in the combo box to be different.
I have tried using the OnOpen method, but it says I cannot assign a value to it.I have tried using the OnLoad procedure, but it doesn't show anything. the code is as follows:
Code:
Private Sub Report_Load(Cancel As Integer)
If Me.[Dispatch Type] = "Sent to A" Then
Me.cboDispatchTo = 15
ElseIf Me.[Dispatch Type] = "Sent to B" Then
Me.cboDispatchTo = 8
End If
End Sub
I have 10 unbound textboxes with the Tag Name "LoopID" in my report. I have to display the "PatientNumbers" field from the table tblPatient in those text boxes. Below is my code.
When I run the report, I get the error message: Run-time error '2448.' You can't assign value to this object and the code "Ctl.Value = rst!LCANumber" is highlighted.
Private Sub Report_Current()
Dim strSql As String
Dim dbs As Database
Dim rst As Recordset
Dim x As Integer
Dim Ctl As Control
strSql = "Select PatientNumber from tblPatient"
[Code] ....
I have a report [Report1] that gets its data from a query [Query1]. On [Report1] there is an unbound text box [EnterEndDate] that I want to use as a filter for the report criteria, and have it filter the report to show every report row with the value in the [EndDate] field greater than what the user entered in [EnterEndDate].
There is no need to save the value used in [EnterEndDate]. It will be entered after the report is run and changed on demand while the report is open for the user to see different date ranges.
I have an unbound form with an associated report. When the user hits the 'print' button on the form/screen, the report is launched in the background. In the On Load event of the report I populate the report fields from the forms field as so:
Code:
Me.txtAddrMainLine2 = "NAME " & UCase([Forms]![frm_OrderRx].[txtPatientName])
This works like a charm as long as I call the report in Print Preview mode (i.e. with acViewPreview). But if I send the report directly to the printer, none of the fields print.
I've read about using other report events to populate the fields (e.g., On Format and On Print) and also something about using TempVars to pass the data. But I haven't read anything that's clear and definitive about the full answer.
I have an unbound report - which is actually a form for the user to print out if they need one. There is no data on the form.
Here is the code that is run when the Button is Clicked:
'First, I reenable the Access menus so that they can use those to print
DoCmd.OpenReport "TransitionSurveyPaperFormLEA", acViewPreview
When I open the report on XP/Access 2007, it works just fine.
When I open the exact same accdb with a Win7/A2007 machine, the report does not open. If I debug and display the error, a msgbox appears
"The OpenReport action was canceled."
I have other bound reports and those open with XP/A2007 or Win7/A2007 just fine.
It makes me VERY nervous that the same exact ACCDB in A2007 runs differently on different OSs (XP vs Win7).
I am having trouble creating a chart within a report. Let me start off by explaing my report.
I have many unbound textboxes on my report that all have the code very similar to this:
" =Count(IIf([Complaints Table]![Month]=1,IIf([Complaints Table]![Decision - Our Favour? (Y/N)]="Y",0))) "
This basically gives a count of for a specific month. There are twelve rows of text boxes and two columns. There is a query applied to the report to input the year, as this is a yearly report.
What I want to do is link a chart to each and every text box to show the data in an easy to view format. But I can't seem to figure it out, and I've had no luck on the web .
SELECT qryHoursReq.VolunteerId, qryHoursReq.Name, qryHoursReq.Program, Sum(qryHoursReq.Total_Time) AS SumOfTotal_Time, qryHoursReq.S_date
FROM qryHoursReq
GROUP BY qryHoursReq.VolunteerId, qryHoursReq.Name, qryHoursReq.Program, qryHoursReq.S_date
HAVING (((qryHoursReq.S_date) Between [startdate] And [enddate]));
This is my query
I'm trying to get a count of distinct volunteerID in an unbound textbox on report.
If I use =Count(*) I get 2 when I put in date parameters and it groups by program
This is kind of correct, but, these 2 are the same person, she had different days she participated.
What code can I use for only get a count of 1
FOrgot to mention, when I use the DCount with numerous examples, I get #Error
Working on a report that displays multiple pie charts. Each chart is based on a different query. I cannot pull a single query for all charts due to the criteria for each conflicting with each other. Each query is filtered by Fiscal Year based on what I input into my Fiscal Year Filter form. The command button on the FY Filter Form opens the report, set TempVar to the FY field (the criteria for each query), and closed the FY Filter Form. This works as I want it to.
The problem: I have additional fields I want to show up on the form such as number of completed students (WINGED). This number is based on yet another query where all completed students are counted [WingedCount]. I have tried to write an expression to an unbound field that points at this [WingedCount] field but it does not work. Then, I changed the report's source as the query with the [WingedCount] field. This does work, however this is where I run into an issue
I open my FY Filter Form and type in my criteria and select the open command button. However, now I am asked for the criteria again for each chart on the report.
I need a way to input the criteria only once and have all charts populate as well as my count field.
I have attached a jpeg of my current report and will upload jpegs of the needed output following this post.
Hey guys im really stuck on this one and hope some one can help me out.
I have the following structure:
Table: Products
ID
Product Name
Table: Suppliers
ID
Supplier Name
Table: Prices
ID
Product Name
Supplier Name
Supplier Offer
Query: QrySuppliers
Prices.ID
Prices.Product Name
Prices.Supplier Name
Prices.Supplier Offer
Percent Off Trade: IIf(IsNull([Products]![Trade Price Euros]),0,([Products]![Trade Price Euros]-Nz([Prices]![Supplier Price],0))/[Products]![Trade Price Euros])
Because the "Percent Off Trade" field is unbound to a table I cant get the data into a report, instead every time I open the report it asks me for Products!Trade Price Euros :(
I've never tried doing complicated calculations in Access and would reallly appreciate any suggestions you guys could give me.
I tried to attatch the database but its 508kb so if anyone has a spare second and wants to check it out heres the link http://www.jeron.co.uk/Products.zip
once again thanks!
To make it simple, I have a list of contact names and their email. I want to have a form with no control source and have 2 simple drop down boxes for their Name and their Email. What I would like to happen is the user choose a name from the drop down in field 1 and then the field below auto-populate their email in field 2. I understand I can use conditional IF/THEN and list out each email, but the contact list is ever changing, so I want to first drop down to link to the TblContacts, have the user pick the name from the list and then have the 2nd field autopopulate from that same TblContacts with their email respective to the Name entered in field 1. My form has two field [Traveler] and [Email]...The tblContacts two fields are [Name] and [Email]
Code:
DLookup("[Email]", "tblContact", "[Name] = [frmEmail]![Traveler]")
I made a report that have 'txtRemarks' field, I just want to get remarks automatically in 'txtRemarks' field based on the other field in report. that is why I used a function like below:
Code:
Private Function Estd_Remarks(Estd_Point As Long) As String
If Me.Estd_Point < 20 Or Me.Estd_Point = 0 Then
Estd_Point = "Earlier Established"
Esle
Estd_Point = "OK"
End If
Estd_Remarks = Estd_Point
End Function
And I wrote in properties 'On Format' event this code below:
Code:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Estd_Remarks = "Ok" Then
Me.txtRemarks = "Ranked & Sortlisted"
Else
Me.txtRemarks = "Estd_Remarks"
End If
End Sub
When preview the report then it shows
Compile error
Argument optional
I have a report that is looking up min and max quality specifications for a product. The table with the specifications has a Yes/No check box field for when the specs are defined as "Internal". So the table looks like Product/MinValue/MaxValue/Internal
I want the report to display "TEST AND RECORD" when the Internal box is checked where the MinValue and MaxValue (that are stored in the table) would be displayed otherwise.
I have created a report but discovered that I need to add a couple more fields.
I created a query for the one field and the other field I added to the existing queries.
Neither field is listed in the "add existing fields" drop down list. The fields have been added to the corresponding forms and tables.
How can I add these two fields without creating a whole new report?
I have a database with a bunch of related tables. One holds information on (let's say) types of food. A field in that table tells whether the food is HOT or COLD. Foods are grouped by MEALS in a related table - the PK of the foods table is stored as an field in the MEALS table. What people here are looking for is a report that lists all the foods in each MEAL, the temperature of the food, and whether the meal is HOT, COLD, or MIXED - foods are both HOT and COLD. I've got a report that lists all the FOODs, grouped by MEALs - it's just the overall temperature of the MEAL that I am missing right now.
View 9 Replies View RelatedI have a report where I added text then a date field based on a Table. In the table I have specified the "Co_ContractEndDate" for input mask as dd/mm/yyyy, however the format to display as dd mmmm yyyy. This works in the Form and in the report as it's own control text box, however when I merge the field with some text the formating changes.
My report text box is as follows:
="The Program Period for this Program will finish on " & Co_ContractEndDate] &"."
The output in Print Preview is:
The Program Period for this Program will finish on 30/06/2013.
I would like to show it as:
The Program Period for this Program will finish on 30 June 2013.
As a work around I have aligned the date field with the label field to get the outcome but as it is a sentence I would like to add a full stop at the end. how to show this text box in design view?
I have a database which gathers and stores the odometer readings of our company vehicles every month. I have built a simple report with columns for Vehicle Number, Employee Number, Employee Name, Month, and Odometer Reading. My boss wants a field for each employee which compares the records for the last two months and displays the difference (i.e. the number of kilometers travelled in that month) /
View 2 Replies View RelatedI'm currently creating an invoicing report for a company, and for record purposes they need a dynamic field on the report for the specific Invoice Number of each client they invoice. I have the field set up currently as a text box =[Invoice#] so they have to enter an invoice number when they generate the report. However, when I generate the report and put in a number, I'm getting some whacky outputs.
For example, I've tested it with a test client, and when I give the client an invoice number of 1, the report somehow changes it to 49.
I have a report that has four fields: Item, Qty, Price and TotalPrice for each line in the detail section. Total Price is calculated by multiplying Qty x Price. The text box name that holds the Total Price for each line is txt_TotalPrice. I want to have a Grand Total in the report footer. I placed a text box in the footer with the following expression: =sum([txt_TotalPrice]). When I run the report Access prompts me for the parameter value of txt_TotalPrice. I've been trying to solve this for quite a while now - but I'm totally baffled.
View 3 Replies View RelatedI have a report and I am trying to Round Up the calculated field SumOfAccrual Amount to 2 decimal places. I am attaching a screenshot of my report and output.
View 2 Replies View RelatedI am creating a report that contains name andd birthdate on one line. I need to have up to seven lines in the report for some groups, but do not want to leave blank lines where no names exist. I am working in 2010 and have read about canshrink in 2007, is this approach availabel in 2010 and for date as well as text?
View 1 Replies View RelatedI have a form with attached subform from which I can choose which records to send to a report. All records in the base table have a comment text field for the user to enter additional data (comments). Typically, there are none, but when there is, if it is only a single record sent to the report, the comment is printed in the page footer. However, if there are multiple records being sent to the report and only one record has text in the comment field, unless it's the last record it the subform, it will not print in the report. There isn't enough room in the detail section to include the comment field (like I said, it's typically not used and when it is, they can be lengthy). Is there a way to get the comment field to print in the report page footer when it is not either the only record selected, or the last record?
View 4 Replies View RelatedI'm using Access 2007.
I have a Customer table that has a field "PostalCode" that is numerical an is connected to a field called PostalID in a table called PostalCode and has the actual postal code stored in a field call Pcode.
I have everything working ok as far as displaying the correct Pcode on the various forms. eg: is the PostalID is 10, I want the form to display the Pcode of 2000.
I can't figure out how to display the Pcode in a report.....
I've got a report that is displaying addresses which are all identified as 'public' or 'private'. I want to be able to display the 'public addresses' and leave the 'private' addresses blank (but still show other fields.. phone no.s etc). The field that the data appears in is Address 1 (for both public or private), depending on what the data entry clerk has selected as the preferred address (ie public or private).I've been playing with the Iif function, but have not had any success...
Address Type: Private or Public
Address1: Address details
In my table, I have a field for "notes" and have it set to long text. It works just fine and has all the text visible. When I create a report or a form from this table, it truncates the text in that field. It limits to the 254 characters of old. I am not sure how to fix this. I have tried setting the WillGrow to yes and it still does not work.
View 4 Replies View Related