My forms are based on query that provide little speed while opeing the forms. But as we know when form opens, Access loads all the records into memory which might take time if the reocrds are in 10s of 1000s.
As a step to reduce traffic onto server because of these kind of form loadiing, I set Criteria for each query so that a little amount of records will be fetch up by access and it would be fast.
All fields are bound to the query and displays limited records as desired.
Let us assume that INVOICE table contains 54,000 records. But after seting the criteria on filed InvoiceNo > 50,000 in query, it shows latest 4,000 invoice records (which are greater than invoice no. 50,000). Off course, the form became fast and moreover it reduced traffic on server.
My question is if a user need to see invoice number 48650 which is not availble with the currently loaded invoice form..what should we do ?
Should I use all unbound text boxes on the form and make a save button then write code lines to save record and again write another code lines to read the required record?
Would not be a lengthy process..? Bcz I have plenty of code lines with all the forms and it would be big trouble if I play with it...
I have set up a database that stores actions (i.e jobs). In the table; two of the fields are...'required completion date' and 'actual completion date'. I wish to lookup, by using a query, all of the open actions (those which havent yet been complete (i.e the 'actual completion date' is null)) and then later on all those which are overdue (i.e the 'actual completion date' is null And the 'required completion date' <today....this being the criteria for an overdue action).
However, I have used a form which has a combo box which contains the values open and overdue. When a selection has been made I want a form to display with the results depending on the selection that has been made. I am capable of creating a form based on a query, but am unsure of how to construct the query with the correct criteria based on the option that is selected from the form.
I have data for hundreds of stores. The data was pulled for the top 15 items by store, so I cannot obtain only the top 5 items that I need. How can I query this data to extract only the top (or bottom) 5 Subjects, by store, based on the percentage column?
I am trying to use a single record and cell of data generated from a query as criteria in another query but can't figure out how to do it? Is there a way to reference a query field in the criteria in design view of another query?
Details:
I have a table of data for each month with supplier codes in each table, but no date field.
I am trying to build a query that will automatically pick the most recent month of data. I have built a query that uses an iif statement in SQL assigning a number to the most recent month of data and then I am using the max filter in that query to show the single highest number (But this logic misses suppliers some suppliers but does assign a number to the most recent set of data). In this case its 2 for february data. I want to use this digit to filter a seperate criteria that will show me all the supplier codes for the month of february. One problem is that some suppliers are in every month others, come and go throughout the year, so I have to assign this criteria for each month. Therefore I want to use the 2 from the first query and plug that into each criteria section under each month field of the second query.
The other option that I can think of but can't seem to find a fix, is to merge all 12 tables so there are duplicate entries and can have a date or number assigned for each month that the supplier code shows up, then just use the max number. I can't seem to find a way to add all the codes creating duplicates into one field. I used a union query but there are no duplicates with this method thus foiling my plan.
HI all, i have a really annoying proble where the solution is probably staring me straight in the face. Basically i want to produce a report where the user is prompted to enter months to be looked at within a certain period and also the years, in my query i am using:
Between [First month] And [Last month]
and
Between [First year] And [to end year]
when i do this the query just asks for the months and doesnt go on to ask for the years...help?! :D
What i want to be able to do, is that when a user clicks on a report, they are asked to enter a month and only the records with that month will be displayed!! What expression can i enter in the citeria of the query, i did try =like"month" but that means creating a query for every month of the year!!!
Hi, I hope someone will be able to help me with this one, I have a simple access query set-up which references to a table containing information about processed orders (From a ficticious company). I am trying to set up a query that will display all order placed within the last week. I have edited the criteria on the date filed to
Between Now() And Now()-"7"
Now this sort of works in that it filters the data, but it doesn't filter it by the day but by the year. i.e. Any order played within the last 7 years rather than the last 7 days. Any help much appreciated. Sorry for being such a n00b, Thanks for looking! cheers xRes
I'm developing a report whose query is linked to a resources table containing background info about each of the resources in my department. Currently, the query pulls data for all resources regardless of whether or not they're still employed by my organization. In the resources table there is a field called "end date." If a resource is no longer employed with the organization, we input their date of departure. I only want the report to show info for those employees currently employed with the organization. Hence, I must make note of this in the query. I've tried the following in the criteria box for the end field,however I continue to receive a data type mismatch error. Help would be greatly appreciated. Thanks in advance.
hey people, im new to the forum and so far its been v.benficial, iv got a simple database running but can't get a query going; can some one please show me how to do the following on a query:
What do i put in the criteria so that it does not show records with null value?
I am trying to get all records that have been delinquent in any part for 45 days. Each record has these categories: G, SC, CL, O. Not all records have data in these categories.
Here's what I have:
I have an expression: Total: Sum([dtmGrec] - [dtmSent]) dtmGrec is the received date for this refund. I am using this to get the 45 days. I have an expression for each category.
The problem I'm coming across is that G may return a result of 100 days and SC returns a result of 4 days. Is there anyway that I can have the result for the SC show 0 or Null?
I have to put a report together to send to our personnel department in regards to some one to one training i have been doing.
I have a database with the following fields:
Forename (text) Surname (text) basic training (yes/no) basic training cpmplete (yes/no) basic training date (date/time) advanced training (yes/no) advanced training date (date/time) advanced training complete (yes/no)
The report i need to send must contain information on name, the type of training completed and the training date.
I have to send the report each week and it must not include any duplicate information from previous weeks (i.e. if a tutor has completed basic training a few weeks ago and have been included in previous report they don't need to be included in future reports).
Can anyone help me in setting up the query which the report will be based on???
I have a number of filters across the top (6 in total). I want to run any number of these filters and then only print the relevant records that pop-up after I pressed the search button. As you can see now I have searched for records that contain TSA. It shows 3 records from a total of 16.
I had the following in mind but haven't got the knowledge to make it happen:
I need to build a query that picks up the criteria from the form. Then Build a report that is based on this query. So that the query only shows the records that are shown in the subform.
Someone already tried to help me and suggested that I filled in the following in my query:
Forms.[MyForm].[MyField] Or Like Forms.[MyForm].[MyField] Is Null
I have a sum in my query, which sums the # of particular units, and want to compare it to a MinimumLevel field. I can get the query to display the Sum and the MinLevel fields, but I can't get them to compare each other and only display the correct results...!
I want to only show records where the SumofQuantity < MinLevel . (The reason I need the sum is because a unit has different lot numbers and have a record for each lot number)
I'd like to only show records in my query that either have an address or an email. If both are empty, it shouldn't show up. I don't know how to go about doing it so it tells if one of the two is at least full. I tried Is Not Null And [Email] is not null, in the criteria for address, but this did not work.
I am new to access so I hope someone can point me in the right direction.
I have a database that I need to query by account number and invoice type.
Extracting unique single records is ok but I can not work out how extract multiple records.
If the account number has several records(rows) and mixed invoice types(credit and invoice) is is there a way to extract all records for an account number if they are just type credit.
account number type 123456 credit 123456 credit 123456 credit 234561invoice 234561 credit 234561 invoice 345612invoice 345612 credit 345612 credit
I have a what I think should be a simple enough query, but I'm pulling my hair out trying to get this to work. I have a report based off of a query which prompts the user to either enter a record number manually or simply use the record number for the current record being displayed as the default. My criteria is setup as [Enter Record Number] Or [Forms]![Formname]![RecordNumber]. This works fine if I let it default to the current record being displayed. The problem occurs when I manually enter a different record number than the record currently being displayed, it reports on both record numbers. Any suggestions:confused:
The information I have in a table contains details of Vendor Name, Vendor Number, Amount Spent and then the organisation is given a classification according to what area of business they operate in from a standard code. There are about 350-400 codes in use to classify the type of business activity.
I need to produce a query/report that will sum all of the spend for each classification and display it in a report to enable me to focus on individual groups.
The table is organised
Vendor Number Vendor Name Amount Key (5 digit indexer for the classifcation) Code (8 digit code for classification) Classification (Text based description)
All I need within the quqey is for the Amount and Key to be totalled, and Code and Classification to be shown on the report that I'll generate. I attempted to build a function from within Access using SQL Sum with the Amount and Key but it unfortunately didn't work.
I have a db with articles linked to the articles of a supplier. 1 of our articles can come from more then one supplier. So to make up my order, i choose the supplier and our article number and want to look it up in the db holding the supplier articles. eg.: our supplier supl. art. 2001 suplier 1 thx36 2001 supplier 2 895568 2002 suplier 1 TXX895
so the result for supl. 1 with 2001 would be thx36. When i put this in a query i get an error, to few parameters, expected is 1. Here is the code:
strSQL = "SELECT tblArtikelKopLev.Artikel, tblArtikelKopLev.Leveranciernummer, tblArtikelKopLev.ArtikelLev, tblArtikelKopLev.OmschrijvingLev FROM tblArtikelKopLev" & _ " WHERE ((tblArtikelKopLev.Artikel = " & strArtikel & ") AND (tblArtikelKopLev.Leveranciernummer = " & strLevnummer & "));"
I created a form to allow the user to enter in their own criteria with respect to the query I have setup. Here is my situation. I have a form with 3 text boxes "CenterName", "CourseNumber", and "Status" that correspond to the query and the criteria is setup to get information from text boxes on the form. Right now, I can type in either the center name, the course number, or the status. I cannot do center name and course number, or i can't do course number and status, etc... Any advice would be greatly appreciated.
How do i offer the option of entering in values into a query? I thinkk it has to do with the
where clause, but i'm not sure! Can someone help me out? I want to be able to enter in the name of a venue and have only that venue info come up! Thanks in advance!
I have a user form that has a combo box called Company that is used to limit a report to all or a single company. I am trying to make it where the user can leave it blank (Null) for all companies or Pick a single company. I know there are several ways to take the value of the company (CompanyID) and use it for criteria. What I was trying was to use an expression in the reports query to get my results. This is what I tried to put in the criteria grid in my query.
IIF(IsNull(Forms![frmBillingReports]![Company]), Like "*", Forms![frmBillingReports]![Company])
If I leave the field null on my active form, I get 0 rows back. If I pick a company, I get the right number of rows back for the company the user picks. Now If I put a 1 in the place of the Like "*", I get the right number of rows for company one. Also, If I take the whole expression out and put (Like "*") it brings me back all the rows in my table.
