I'm an Access novice. I have a query based report that is based on several tables. All tables are joined by the same field "customer ID," but 1 table is not available under "Add Existing Fields." I cannot figure out why that table isn't available, but I need to add a field.
View RepliesI have a form based on a table and I just added 3 fields to the table. When I go to the form to add these fields, they do not show up in the field list. I've also tried creating a textbox and going to the Data Tab and choosing the data source but those 3 new fields are still not showing up.
How can I go about adding these 3 fields?
Thanks in advance
I am trying to create a report that will show me any record that does NOT have a specific field met. I have a basic query set up to display the information desired, but need it to refine the search to just the records missing a specific part in a different table. The missing information does not have to be shown in the report.
View 7 Replies View RelatedDear all, is it possible to run a query (or ideally a report) based on a selection from a drop down list in a form? (in accsess 97). The reson being is i have a big list of of codes for different absence reasons & at present the user has to type in the correct code to display the data in a report based on the code they enter, however its not easy for users to remember which code to type in to run a report.
Idealy what i would like is a drop down list with the absence codes & the absence desciption next to each code, then when the user selects a code from the list it displays the corresponding data in the report?
Is this possible?, any help would be much appreciated, thanks.
Hi, I'm not sure if this is possible, but I've heard you guys really know your stuff.
I have a client who would like to import a list of e-mail addresses, but would like to verify the domain name used in the provided address against a list of domain names pre-populated in a table. Do I need to use Dlookup? Here are some additional details. Thanks in advance!
MAIN_IMPORT_TBL
ID
FNAME
LNAME
E-MAIL
PHONE
DOMAIN_TBL
DOMAIN_ID
DOMAIN_NAME
AP
I have fairly large table called QBInvoices that consist two fields- qbinv and date . Also, I have a report that showing info off query called TotalQueryQB. The table and query have no relations except dates . I need to show on report filed qbinv off table QBInvocies on the top of the reprot based on date. The user type date on form -frmQB text box txt Date and I would like to have contest of field qbinv off table QBInvocies on the top of the report base on date that was typed.
View 2 Replies View RelatedI have a table x where the field value is selected via a combo box in a form that is populated from another table z.
When I look in x, it appears to have correctly stored the text from z, not the ID number.
I then built a Query, qX, which looks in x and grabs the fields I want. That query shows the text correctly in each selected field.
Now when I build a form, frmQx and use a List control, it displays some of the fields as ID values from the original table z, not the text values.
How can I get round this? I've searched and searched for an answer, sigh. Maybe I'm just not quite certain how to phrase the search.
Hi,
I have a table with a field that shows the number of pieces that a parcel contains. It looks like this:
ParcelID, Pieces, Description
Data example:
1001, 5, Jackets
1002, 10, shoes
etc
I need to print labels for each piece that shows to which parcel it belongs. The report will look like:
1001, Total pieces = 5, 1 of 5 pieces, Jackets
1001, Total pieces = 5, 2 of 5 pieces, Jackets
1001, Total pieces = 5, 3 of 5 pieces, Jackets
1001, Total pieces = 5, 4 of 5 pieces, Jackets
1001, Total pieces = 5, 5 of 5 pieces, Jackets
and
1002, Total pieces = 10, 1 of 10 pieces, shoes
1002, Total pieces = 10, 2 of 10 pieces, shoes
1002, Total pieces = 10, 3 of 10 pieces, shoes
1002, Total pieces = 10, 4 of 10 pieces, shoes
1002, Total pieces = 10, 5 of 10 pieces, shoes
If I could populate a table where the number of records equal the value of the pieces column then it's a pretty simple report but I can't figure this out.
Can anyone help?
I have an access database in which I have a table A and table B. Table A has a list of 200 website URLs. Table B has one column ID and another criteria.
I want to create a query to filter websites list which does not have values or characters from table b.
I have these values in table B that I want to be filtered out or not shown in my URL Select Query
.org
.gov
.du
.pk
.dk
I would keep on adding more criteria into this so criteria table so adding new criteria into table B should not disturb our filtering.
Below is what I have tried but in vain and it says atmost you can atmost one criteria row in sub query
SELECT tableA.WEB_ADDRESS
FROM tableA
WHERE ((([tableA].[wEB_ADDRESS] Not Like '*'+(SELECT * FROM tableB)+'*')=True));
I have an access database in which I have a table A and table B. Table A has a list of 200 website URLs. Table B has one column ID and another criteria.
I want to create a query to filter websites list which does not have values or characters from table b.
I have these values in table B that I want to be filtered out or not shown in my URL Select Query
.org
.gov
.du
.pk
.dk
I would keep on adding more criteria into this so criteria table so adding new criteria into table B should not disturb our filtering.
SELECT tableA.WEB_ADDRESS
FROM tableA
WHERE ((([tableA].[wEB_ADDRESS] Not Like '*'+(SELECT * FROM tableB)+'*')=True));
I was wondering if it is possible to determine weather to show a field based on the values from another field.
Field1, SHOW_Field1, Field2, SHOW_Field2
Field1 and Field are text, SHOW_Field1 and SHOW_Field2 are Boolean values
So if SHOW_Field1 is true, then show Field1 in the query else hide. I was wondering how to do this, I seem to not be able to do this for some reason.
Okay I have a report (rptHorneOstbergQuestionnaire) that is based on a query (qryrptHorneOstbergQuestionnaire). In the report I have a total (HOTotal) which is the result of an expression created in the qry. Based on this result I would like to generate text in a text field (HOType) that is found in the same report.
Basically if the field HOTOtal shows any vaue between:
16 and 30 then I want the unbound text field to show the text Definitely evening typw and so on ...(see case statement below)
I thought I could do a case statement on report open but I am getting an error that my expression contains no value.
Here is the case statement
Private Sub Report_Open(Cancel As Integer)
Select Case Me.HOTotal
Case 16 To 30
Me.HOType.Value = "Definitely evening type"
Case 31 To 41
Me.HOType.Value = "Moderately evening type"
Case 42 To 58
Me.HOType.Value = "Neither type"
Case 59 To 69
Me.HOType.Value = "Moderately morning type"
Case Else
Me.HOType.Value = "Definitely morning type"
End Select
End Sub
What else can I do?
I need to show an image called PAID if my hidden field Paid Date has a date in.
I thought this would be straight forward but so far nothing.
I have my images within the database itself on Access 2010, so I am not referencing any in a C drive or other location.
Code:
Private Sub Report_Current()
On Error GoTo ErrHandler
If paiddate = Date Then
Paid.Visible = True
Else
Paid.Visible = False
End If
ErrHandler:
MsgBox "Error detected, error # " & Err.Number & ", " & Err.Description, vbOKOnly, "Error"
End Sub
But with no joy.
I am trying to show a certain subform based on whether or not a field is blank or null. This database is not owned by me--it's what we use to run queries and reports on the data on a centralized server for the library. It's not even in the same city--so changing the tables is not an option for me.
So, I have a subform (subform1) that is linked to the main form based on 2 fields (BibID and Item_Enum). The trouble is that the second field (Item_Enum) is sometimes blank, so the subform will not display. So I created another subform (subform2), which is a duplicate of the first one, but this time only linked on the first field (BibID). So, if that second field (Item_Enum) is blank, I want subform2 to display and subform1 to be hidden. If the second field (Item_Enum) is not blank, I want subform1 to display, and subform2 to be hidden.
I've tried putting the VBA code on different events for the form, but I can't get it to work consistently.
There are two problems, one of which I think I have fixed--but I would prefer a better solution. The VBA code doesn't run unless the form is newly loaded. I've tried placing the VBA code on different form events, but nothing works. I want the code to activate whenever a new record is displayed. The work around is that the form IS newly loaded every time the user scans a barcode for a new book. That works now, but if I need to change the way the user looks up new records, this might not work anymore.
The second problem is that the VBA code doesn't display subform1 when Item_Enum is blank. It doesn't display subform2 either, but that's the desired result.
Private Sub Form_Load()
If Me.ITEM_ENUM = "*" Then
Me.subform2.Visible = False
Me.subform1.Visible = True
Else
Me.subform2.Visible = True
Me.subform1.Visible = False
End If
End Sub
I've tried changing the condition to Me.Item_Enum = null, and changing the false/true results accordingly, but that didn't work either. In that case, only subform2 is visible, whether it should be or not.
Okay so basically below is the table I'm linking to a list box, the field i'm focusing on is the 'Disallowed' field:
Now this is the form i'm linking it too:
As you can see the list box on the right is currently just showing the whole column under the Disallowed list, but I want it to show the specific country, obviously I'm using a single form template but the information changes depending on what country i'm on (I presume I need some kind of Query that uses whatever is shown in the Country box)
I was trying to use SELECT * from tblCountries where [Country]='"&forms!frmCountryDetail!Country&"'" but this is giving me an error from the [Country] tag so this isn't working:
I am trying to show the Current Repair Estimate total in currency form on my report. The attachment shows my criteria, which works on the query, but does not show in the report. The second attachment shows what pops up when I try to view the report. What can I do to get this current rep estimate to show correctly in currency form on the report?
View 8 Replies View RelatedI have an Access 2010 report that is pulled from a dedicated query. When i run the report, one of the fields pull the information in both the numerical ID number and the Field information. For example, the field in question is a field for "business type"......the report comes back in some lines as Manufacturer, Supplier, distributor, or contract. In other lines it comes back as either 1, 2, 3, 4...which is the ID number form the table. I have gone through all the properties and data source and can not figure out my the information is being returned so differently.
View 1 Replies View RelatedI am trying to setup a button that will open a report on click. The catch is that the report name is coming from a query result that is populated into a list box. The way it works the list box will never have more than one result so by default it will always house the name of the report appropriate to a chosen client. I would like to do it this way so that I don't have to clutter up the from with extra buttons all pointing to specific reports.
I found some references to this in this forum, including the example listed below, but I have been unsucessful in making work so I assume that I am entering wrong, entering it the wrong place or maybe the code is incomplete. Unfortunately I am a complete novice to Access VBA so I am still learning how it all works together.
List box name is BillingFormat
The first of 5 reports is named Standard Billing
So in the form I enter the client number and BillingFormat is auto populated with Standard Billing
I tied the button to this code: DoCmd.OpenReport Me.BillingFormat, acPreview
The results I was looking for was for the report named Standard Billing would open. The first way I set it up nothing happened not even any errors. The other way threw an error stating it did not recognize DoCmd as a valid function.
what I am doing wrong?
I have an Access 2010 database with a memo field formatted for Rich Text.
I created a simple form. It accepts and shows paragraphs, i.e. I press Return and a new paragraph appears (with a blank line in between paragraphs).
I then created a report based on the same fields. In the memo field it shows the text entered in the form, but not the paragraph breaks. It just shows one big block of text without any paragraph breaks. I have looked for a field property in the report design and layout views that might affect how the text is displayed in the report but I cannot find one.
It is not much use if you can input paragraph breaks in a form but not see them in a report.
In Access, when using Look Up function, Table/Query, how do I make more than 1 field from the query appear in the drop down list?I will be doing the same thing for students and some other tables. But for this example I am only wanting to show instructors first and last name and their ID number in the drop down.
I am using
Code:
SELECT instructors.first, instructors.last, instructors.ID FROM instructors;
in my Classes table.
What am I doing wrong?
I would like for it to show their instructor first and last name but actually put the ID in the field as I will be using that key for various relationship based functions. Show the first and last name to the person who will be using the database, but actually put the ID in the field so I can use it as a common field across other tables.
I created a Report from a query. The query shows the correct data that should be on the Report. I created the report to sort by Field A and then sum Fields D, E, & F. None of the query data shows up on the Report. I;m stymied as to why I can see data on a query, but the ONLY data that shows up on the Report is Field A. None of the summations show. All are blank with the box outline.
I've created the report 3x thinking I did something incorrect. Whatever it is, I did it 3x!
I have a table 'table1' which has various field including an ID field and a yes/no field. I then have a form based on a query originally derived from data in the original table. The form provides a list with some ID's with a yes and some with a no.
I'm trying to write a button code to convert all the no's into yes' for those ID's picked out by the query.
I've searched lots of sites and get that I need to set recordsets for both table1 and the forms' query but all my efforts crash or give an error.
I am looking for the right way to say .." for each ID in form set the yes/no field corresponding to the same ID in table1 to yes"
I'd attach my version ....
I am trying to use this query. It gives me correct results as query. However when I make chart with query on a report it doesn't show correct data and eventually stops making chart
SELECT qry.txtRC, Count(tbl.txtRC) AS CountOftxtRC
FROM tblMain AS tbl, qryRC AS qry
WHERE (((tbl.txtDepartment)=[Forms]![frmRC]![cboDepartment] Or [Forms]![frmRC]![cboDepartment] Is Null) AND ((tbl.txtZone)=[Forms]![frmRC]![cboZone] Or [Forms]![frmRC]![cboZone] Is Null) AND ((tbl.txtRC )=[qry].[atnRC ID]) AND ((tbl.date) Between [Forms]![frmRC]![startDate] And [Forms]![frmRC]![endDate]))
GROUP BY qry.txtRC , tbl.txtRC
HAVING (((Count(tbl.txtRC )) Is Not Null)) OR (((Count(tbl.txtRC )) Is Not Null))
ORDER BY Count(tbl.txtRC ) DESC;
Hello all,
I have a temporary table with Yes/No fields (F101,F102,F103,....etc).
In another table i have a field called ProductCode with values (101,102,103,....etc)
Is it possible to append(or update) the records of the temp table with criteria on "F" &ProductCode field? (For example if ProductCode=101 then F101 sets to yes)
Thank you in advance.
My computer has been updated to 2010 whilst I've been off sick (was 2003 before my accident).
I've created a main table, for devises across the company, and a combo box/selection box based on another table which holds a list of all the "Responsible" employee's aswell as another combo box/selection box for the device location.
So the person entering the information, can enter all the information for a device (torq wrench, socket set etc), who is responsible for it and which department they belong to (where to find the device).
Which all works fine
However, I'd like to create 2 queries, one to enable the user to run a report of all device's allocated with an employee or to be able to run a query for all device's stored in a particular department.
But I have been unable to set the correct query criteria, to enable to query user to be able to selection from a drop down list, which responsible person or location to pull back the correct list.
I was getting an error asking me to set the parenthesis, I have now deleted criteria for both queries, as even if I put [Enter] and type a Responsible person's name exactly as its held on the table, the report comes back blank.
I have a report that gets its data from a query. I need the query to run before the report based on criteria based from two combo boxes on a form.
View 3 Replies View Related