Putting Unbound Query Field Into Report
Jun 11, 2007
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!
View Replies
ADVERTISEMENT
Nov 7, 2005
Hi,
I have a two column table and I want to display all records in the second column but I want it to be displayed in another single field.
Anyone has a trick on this?
Example:
Column1 Column2
001 Basket
002 Jar
003 Pillow
004 Hat
Result should be something like this:
Basket, Jar, Pillow, Hat
View 1 Replies
View Related
Jul 3, 2013
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 ?
View 7 Replies
View Related
Jan 30, 2015
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]")
View 5 Replies
View Related
Feb 21, 2005
How does one cause certain data to appear in another field based upon the data entered in another field.
For example: I enter in the field the word "Carrot" in a field called food and the word "Orange" appears in the field color. And then if I enter the word "Beans" in the field food then the word "Green" appears in the field color.
Thanks for you help in advance.
Eric
View 1 Replies
View Related
Jan 12, 2006
I have a query which has a field where it shows reservations. if there is no reservation the field is empty.
how can I achieve getting a 0 in this field when it is empty?
View 14 Replies
View Related
Dec 13, 2013
I have a value in an unbound field on a Form, which is 1234 OR 765 OR 356.
In the QBE criteria grid, I used builder to reference this form:
Forms!myform!myunboundfield
The column this is in is for the ID field, which is a number.
However, it is not filtering the data correctly. If I copy the above text and paste it into the QBE grid, then it will work. But when I reference it, it fails. If I change the value to just a number on my unbound field, it works. So the issue seems to be that its bringing across the text as a string and so perhaps effectively puts quotes around it when referencing it.
View 11 Replies
View Related
Jan 26, 2006
Sorry guys, I thought this would be simpler than it was!
Basically I just want to update my "Resolution date" field with the date or time now (which I know how to do) but AFTER I input data in my "Resolution" field ie I want a field to automatically record what time I am entering the text in my "Resolution" field?
How do I do this, and can I do it without writing a macro? I thought it was along the lines of After Update, but now I am confused!
Thanks again - I am learning just slowly!
View 5 Replies
View Related
Jul 11, 2006
Somewhat simplistic question, but I can't seem to get it to work correctly.
SELECT Field1, Field2
FROM Table1
WHERE Field1 = [Forms]![Form1]![Text1];
Form has two quieries, named Text0 and Text1. Text0 contains the Field info for the query to search under.
How do I change it so that this will work.
SELECT Field1, Field2
FROM Table1
WHERE [Forms]![Form1]![Text0] = [Forms]![Form1]![Text1];
Presently I am getting nothing but blank queries. I'm sure its some simplistic thing but I can't figure it out atm.
View 4 Replies
View Related
Apr 30, 2014
I am trying to find a way to allow the user to enter multiple numbers in an unbound box that I will pass to a query as parameters. I already have it set up to pas the parameters from the form but if i try to do more than one number it doesn't work.
I have tried:
1306 or 1307 or 1308
1306, 1307, 1308
"1306 or 1307 or 1308"
I was reading somewhere that when the value is picked up from the form it is not like you are typing it right in the criteria box of the query.
So are there any other options here? Is there a way to enter all the number in a box and then use vba to create an array and then pass that to the query?
View 2 Replies
View Related
Mar 8, 2013
My table has 3 fields, Employee Number (text), Job Description (text) and Current (yes/no). The table keeps track of the Job Descriptions that an Employee has had along with his current Job Description (actually Title is more appropriate a word but client requested Description) So the table has records such as:
0001 - Floor Sweeping - no
0001 - Ceiling Cleaning - yes
0001 - Dumpster Turning - no
The yes signifies that that is the current Job Description for employee #0001 and the others with no are previous Job Descriptions that the employee #0001 has held.
Now - I created a form to add these records to the table and on that form I want a list box to show the records in the table for this employee but I want to sort it such that the current Job Description is listed first and the rest show up in alphabetical order following. I have the query to list all of the Description, but how can I get the current one to always appear on the top of the list?
View 14 Replies
View Related
Apr 4, 2014
I have a query in which some of the field names were assigned a while back and don't make a lot of sense to the person who gets the report so I give them new labels in the query...for example:
I pull in the field AE_resp_req and in front of that I type in RM Response: AE_resp_req and when I run the query, the name for the field is RM Response and it works great. I have another field called Category_Rating that I pull into this query and I did the same thing: Exam Rating: Category_Rating (field name) but when I run the query it still says Category_Rating and I get no errors or messages..
View 2 Replies
View Related
Oct 15, 2013
I want to calculate the average of 16 anodes from text box into the "average drop" box and simultaneously want the data to be saved in the table too as one of the fields. Also, How to load form view while the database loads?
View 10 Replies
View Related
Jan 15, 2015
I have 2 databases with the same structure, but different data.
I want to put all the data in 1 database.
The problem is that the most important table (clients)has an autonumeric id field, that is used to link the records to the fields of many other tables; so, I cannot delete or change that field without losing connections.
View 4 Replies
View Related
Aug 9, 2006
I would like to put a query or some look up device into my form so that a certain part number can be found and its form opened for changes. How should I do this?
View 1 Replies
View Related
Jul 22, 2005
Putting the same text into every cell in a query column
Hi All,
I sometimes have to mark every record in a query with the same text in a field, when the field is either empty or contains whatever text.
At present I do this by copying the text, and then pasting it (ctrl-v cursor-down, a thousand times) into every cell in a column.
How can I do this in a more efficient way?
Thanks for your help.
Adrian
View 8 Replies
View Related
Sep 25, 2013
I have a database of people who have a "Joined" date. i.e. 09/1/2012. I would like to set up a query that tells me how long (in years) that they joined.
View 5 Replies
View Related
May 25, 2013
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.
View 3 Replies
View Related
Oct 20, 2014
I want to build a form that allows a user to book an appointment.
I want them to be able to see a choice of their own or other people's appointments for the day so they can choose a time.
I'm told i can build a query to show the appointments and put that in a subform on the form.
My question is this: to select which person's appointment to display and which day, i need to give the query parameters. I don't really want to do it with the parameter query popups. What i'd like is a couple of pull down lists at the top of the timetable to select and display the person and date.
Is this possible? Should i put the pull down boxes on the form or in the query?
(I'm using a2007)
View 6 Replies
View Related
Dec 19, 2012
NOTE: Im working in MS Access 2003, only results in this SQL/database (not mysyl, MSSQL etc)
I want to be able to ORDER an SQL query and put the NULL values last.
I have a basic databse:
Table name: PeopleTable
Field names: TableID, PersonField, PersonID
The aim is to order by PersonID and put the NULL values last
See pic1.jpg;
The picture (pic1.jpg) shows the database as it is without a query.
See pic2.jpg;
Picture 2 (pic2.jpg) shows the query results when I use the orderby statement (SELECT * FROM PeopleTable ORDER BY PersonID As you can see it shows the results ordered by the PersonID however the NULL values are first.
See pic3.jpg
Desired results, it is ordered by the PeronID and the NULL values are last (NOTE this is an editied screenshot).How can i achieve this is MS Access 2003?
View 1 Replies
View Related
Nov 30, 2006
I have a report that displays 5 different status. The problem is I need an unbound box to display a certain word when one of the status's is displayed. But the thing is there should be an over-ride checkbox that will display "Void" if it is checked regardless of the status and in the Unbound Box Void will appear regardless of status. Here is an example:
Status Unbound Box
Happy Good
Sad Problem
Happy Void
Sad Void
Note the last 2 on status's list had the checkbox for "Void" Checked.
I know this sounds crazy! I don't even know why I was asked to do somethng like this. People just make up things to do in ACCESS for no apparent reason becuase it comes to their mind!
View 1 Replies
View Related
Jul 7, 2015
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?
View 11 Replies
View Related
Feb 19, 2014
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
View 1 Replies
View Related
Oct 25, 2005
I have set up a query to give me details of employees names, the business they work for and where they work.
If I run it without criteria I get all of my records, as you would expect.
However, I want to be able to select parameters from combo boxes and then run a specific report of employees by location. I did have this working by accident a while ago but have changed quite a lot since then.
I have set up an unbound form with three combo boxes - Business, Business Unit and Location. Then a button for preview and print report linking to my report template.
I have refernced the controls in the relevant columns of my query as: [Forms]![FormName]![Control].
When I run the query I get nothing back and I don't understand why.
View 9 Replies
View Related
Oct 25, 2005
I have a very simple report - just one field.
I have a Form - "Cases". The button to open the report is on that form as are the values I want in the report.
I want to pull the value of Case Name and Case Number from the current form view and put them together in the single field on my report.
I can "almost" do it. Then VBA gives me a warning - it says that you can't assign a value to the control on my report.
Can anyone give me any ideas?
by the way:
I chose to make this an unbound report because I'm using SQL server as my back-end and my Access is an .adp file. SQL (so I was told by the programmers at work) can't pull in variable criteria from a form like Access Queries can. So this all has to be put in VBA.
View 5 Replies
View Related
Feb 5, 2015
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] ....
View 14 Replies
View Related
