A while back, I asked about the now-removed function of exporting Reports to Excel. I was told that there's no way to do so in Access 2007 and that the best best would be to export the underlying Query.
I did so and I found out that the query was showing the record number of some data pulled in via combo box (which gets data from a separate table) instead of the data itself. Logically, I can understand why that would happen, but is there a way to change that so that the intended data, not the record number, is shown?
I have a database with many Quires I want to create in the main form "welcome Form" many txt boxes cells
To show a total records in Each Quires
For Example I have the below list queries
Expired Employees PassportExpired Employees Work PermitExpired Employees VisasExpired Employees Insurance
In the "Welcome Forms" I will create a txt boxes Cells for Each one of those Queries to be Show only No "total Record in Each one". It is like a dashboard to monitor daily without opening the Report or Query...
I have a form with a sub form. The parent form has a field called 'ReportedBy'. The user enters their name in this field. I want the subform to display all records that have been created by the name in ReportedBy. I have based the subform source object on the following sql query:
SELECT * FROM tblFault WHERE tblfault.reportedby=[forms]![frmfault]![reportedby];
The sub form only shows one record even though there are more records in the table????
hi, i have a form, which when opened, gives me a blank record. which is what i asked it to do in the OnOpen property. however, i also need a msg box to popup - but after the form has been opened. i tried placing both pieces of 'code' into the OnOpen property, however, the popup displays first, then the form opens.
so currently i have:
Private Sub Form_Load() Select Case MsgBox("Please select the Registration Number from the drop down menu or type it in", vbOKOnly, "Select Registration Number")
End Select End Sub -------------------------------------------------------------------------- Private Sub Form_Open(Cancel As Integer) DoCmd.GoToRecord , , acNewRec
End Sub --------------------------------------------------------------------------
but it does the same thing. so, is there a way to make the msg box appear after the form opens with a blank record? thank you in advance :D
In the current db there are some tables and queries, forms designed around them
With one table Test http://members.optusnet.com.au/~lukechang/access/table_d.JPG
That has 3 records in them http://members.optusnet.com.au/~lukechang/access/table.JPG
Just won't show in form anymore, at this stage http://members.optusnet.com.au/~lukechang/access/form.JPG
The Form was working previously. This is the second time this problem has came up, I thought it would go away if I made a new form and pasted all the controls and codes. It indeed went away for a while until this problem hit me again...
This db is in Access 2000 format and designed using Access 2003
I would appreciate if anyone is willing to point me to the right directions, many many thanks in advance
I've created a query that runs from a form and I would like the data to be shown subtotaled on a report.
Basically, I have Year Group, Behaviour Type, Behaviour Location and I would just like subtotals and %'s for each change. So, for example, there may be 1000 records in total 200 in Year 3, 500 in Year 4, 100 in Year 5 and 200 in Year 6. I would like to show this in a table with %'s as well as figures if possible (a pie chart is the eventual aim!)
Then I would like to subtotal the same records for the changes in Behaviour Type e.g. Bullying, Verbal Aggression etc to find out how many issues of each type have occured and the percentages.
I can get this information in a report using the group feature but it's a real mess and it's broken down far more than I expected.
I have form where I will show the details of one table(member details table). In same form I have subform where I can get the member family details (here I am using the relationship table), now my problem is when Member detail form open based on the member id, the below subform data should be fetched...
I have a temporary table with all text fields for importing data to a normalized table. In order to import I need to prep the data by pulling the numeric values of "lookup" type fields (nb. not actually using MS Access lookups)
My prep query is producing 9 more records than there is rows in the import table, even though every join is a LEFT join, which should just pull all import rows and match up lookup values where they are available.
eg. of import table (all fields are text)
Location; Group; Name; Wfm type; Account Number; WFM Status; Date Created; Date Held To; processdate
Beachville Operating Centre; Service Orders; DAVID JONES; ; Complete Engineering Investigation; Completed; 4/20/2007; ; 4/27/2007
here is my prep query (the iif is to replace userid's that can't be found with UNKNOWN)
SELECT lookup_location.val AS F1location, lookup_group.val AS F2group, IIf([lookup_user.val] Is Not Null,[lookup_user.val],"UNKNOWN") AS F3user, wfm.wfmcode AS F4wfmcode, CURPW020_import.[Account Number] AS F5account, lookup_status.val AS F6status, CURPW020_import.[Date Created] AS F7datecreated, CURPW020_import.[Date Held To] AS F8dateheldto, CURPW020_import.processdate AS F9processdate FROM ((((CURPW020_import LEFT JOIN lookup_location ON CURPW020_import.Location = lookup_location.txt) LEFT JOIN lookup_group ON CURPW020_import.Group = lookup_group.txt) LEFT JOIN lookup_user ON CURPW020_import.Name = lookup_user.txt) LEFT JOIN wfm ON CURPW020_import.[Wfm type] = wfm.wfmname) LEFT JOIN lookup_status ON CURPW020_import.[WFM Status] = lookup_status.txt;
any assistance would be much appreciated. any other info needed let me know.
I have a query criteria where the data in the query field are numbers.
The following works as long as I have a value in the textbox otherwise I get an error message stating, this expression is typed incorrectly.
Code: Like [FORMS]![FONMain]![txtTest15] Or [FORMS]![FONMain]![txtTest15] Is Null
Also tried
Code: [FORMS]![FONMain]![txtTest15] Or [FORMS]![FONMain]![txtTest15] Is Null
Yet I use the following script for another column which has identical data and it works fine either way and I can use it in the other column and it works with or without data in the textbox
Code: Like [Forms]![FONMain]![txtFact1] Or [Forms]![FONMain]![txtFact2] Or [Forms]![FONMain]![txtFact3] Or [Forms]![FONMain]![txtFact4] Or [Forms]![FONMain]![txtFact5] Or [Forms]![FONMain]![txtFact6] Or [Forms]![FONMain]![txtFact7] Or [Forms]![FONMain]![txtFact8]
I have a table with a field named BinNo which has a list of items with a bin no in the format 1.234( this number can be any number up to 6000.9999).
Is there a way for me to have a field on a form where I enter a number (i.e 1) and it lists all the items with a 1 before the decimal point but not 10 or an other number with a one in it. If I enter 10 then it will only give me the items with 10 before the decimal point and not 11 etc.
I have a query that pulls all the data into it but I only want the query to show the items relating to the number I enter into the form field.
Essentially I have a table for rooms around my school and what the rooms contain. Most of the details for the rooms are Boolean (e.g. If the room has a projector, Yes/No). So what im trying to do is have a form where i can select a checkbox(s) and if i tick one, a query that holds all the room details will only show rooms with projectors in them, which i can then display those results in another form.
My question is how do I get the check boxes in the form to narrow down the room results to only show the ones with the criteria i have selected in the form?
I am wondering if it is possible to code something that changes the record number of a selected record. I have a form that lists the schedule of the company. I want to be able to move the position of the records around so it can be changed. The form is based on a query and is sorted by date and sequence number of that date. For example, I would want to be able to move record number 3 on the form to record 4 and vice versa. Thanks :)
I have created query that shows some fields from selected tables. The information that shows from all fields, when I "run" the query can't be edited and I can't add new text on the bottom of field. How can I do this?
I have attached a picture of my query. You maybe understand better if you see it. Will appreciate it alot. Need help very quickly.
I would like to hide the record number scrolling feature in my forms. How is this done? I have searched through the properties of the form but am missing something I suppose. Thanks, Joe
I thank everyone in advance if they can help me with this.
What i would like to do is display the record number on my form in between the record navigation buttons that i have allready added.
I can hear poeple now saying, " why dont i display the navigation buttons in the form properties?" and the reason i do not wish to do this is because i do not want poeple to be able to add a new record!
so, if someone can help me remove the "New Record" button from the standard navigation then that would be excelent.... but if not then how can i replicate the record number into my form?
I am trying to display the records (counts) on my Invoice form.
I tried to put one text box and typed as follows:
=Count-[n]
This kind of text box we normaly use to count records on Reports and it counts well. But this did not support me on form.
I need, it should display records from 1,2,3 and so on upto the last record entered for that invoice.. Each new invoice should start from 1 to the last record number.
I 've created a report that uses a query to show some specific records of a table. I'd like to use a text box that has a nuber for each filtered record shown.For example: Number Name Surname Age 1 Jim Powell 27 2 John Doe 30 etc Thank in advance
I want to create a crosstab query that looks like this:
Date1 Date2 Date3 etc etc caseID value value value
There are 500 caseIDs in my table. Each caseID has up to 5 corresponding dates/values. Therefore I only want 5 dates as the column headings....listed as 1, 2, 3, 4, 5, etc NOT the actual date (as I then end up with hundreds of column headers).
Does anyone know how to create a detailed number by each group (similar to the runningsum feature in Reports)? I know I need to somehow assign each date a "date number" but I dont' know how to.
I have a report with 3 fields in it. I have made the control source for each field an average of the #'s in the field of the query. I only want it to show 1 record but can't seem to figure out how. I've already listed the cycle option to current record, but it still shows the 3 fields repeating over and over... for ex:
Field 1 Label 7.4 Field 2 Label 8.4 Field 3 Label 6.4
Field 1 Label 7.4 Field 2 Label 8.4 Field 3 Label 6.4
And it goes on and on like this... the averages are correct, but I only want to show them 1 time, not repetitively..
Dear All, Ok, I would like to rephrase my question :) , how do I retrieve records even when only one of the 3 text fields in my form are filled up? I have to fill up all of the 3 fields for the records to show up.
SELECT * FROM tblApplicants WHERE (COURSE Like "*" & forms!frmFS!txtCourse & "*" OR COURSE Like "*" & forms!frmFS!txtCourse2 & "*" OR COURSE like "*" & forms!frmFS!txtCourse3 & "*") ORDER BY names;
I have various columns of data, a mixture of text and numbers, which all have data in them, and a few columns that have numbers or text. When I copy'n'paste-append into the Access table, the text in the sparsely populated columns doesn't copy across but the numbers do.
It works if I import the spreadsheet from scratch (don't want to do that however, that data comes from somewhere else and would require modification to suit), or if I sort the data so that the text is at the top of the column, it works. Don't really want to do that either as there are 5 columns that this applies to and performing a 5 level sort shouldn't be necessary IMO. It seems that the paste is analysing the data and deeming that it should paste as a number even though the column contains text. There are only 120 rows of data, not a lot! I've tried redefining the field as Long Text (currently Short Text) but it doesn't make any difference.
I'm having multiple problems with my database like things such as -
i'm currently working on the Query 2 - On the Phone database (ignore Query 1) and i want to search for multiple plot numbers preferably in one parameter prompt with a comma to seperate numbers. (this could be a multitude of numbers so i would like to be able to input as many as needed). Also when i do search on this query since the Criteria is a 'Between' Value i would expect everything between the 2 numbers input to show up - but a lot of numbers out of the range show up too - why is this? (The Numbers are like "69 to 136" and they will show up - but 1-69 and 136-170 would too
I would also like to implement the search results from Query 2 into the Form i currently have made but it just opens up a access table when the search is made?
i cannot link my database as it is too big for the server - But here are the Criteria for Query 2:
Plot No - (criteria = Between [Enter First Plot No:] And [Enter Last Plot No:]) Site - (criteria = Like "*" & [Enter Site:] & "*") Product - (criteria = Like "*" & [Enter Product:] & "*"
The Query is the one im most concerned about , i can live without a form.
