I have a form that has various input fields that are connected to my main table. What I want to do is have my form allow the user to enter information, leaving some of it blank if they don't want it, and then press a button and it will open the query with the arguements given. The problem i'm having is... when i leave a field blank, it doesn't pull up anything, but if i fill everything out then it's fine. Right now my criteria on my query sections just link directly to the combo boxes that i have created on the input forms. I believe i just have to have additional criteria, telling it to ignore null or something like that... but i'm not sure how! Please aide?
Hey guys, I have a simple design question I hope someone can help me with. I have little to no experience with Access, but I know enough to get around. Anyways, I'm working as an extern for a rock band. One of my assignments is to create a publicity contact database. I have all my forms and tables set up but I'm having some trouble creating my query. There are about 10 fields in the contact table. I want the user to be able to search any one of those 10 fields OR any combination of them. Is there a way to do this with only one query, where it willl prompt the user for only the fields they have selected to search?
In Access 2000 I need to create a primary key out of elements from two different tables across several different fields. So the first question is how do I do this? The second question is where do I do this, i.e. is there a wizard of sorts that can walk me through this? I thought I'd checked everywhere and I just can't seem to find anything. If someone could let me know I'd appreciate it so much.
I have to create a database on theatre booking system. So far, ive identified the following entities:
Customer(Customer ID, Title, Initials, Surname) Customer ID is PK Bookings(-------) Play(Playname, Playwright, Number Of Characters, Length(min)) Playname is PK Actors(-------)
i do not know what to create as attributes for the bookings table, especially seeing as the attributes have to relate to customer and play. for the actors table i was thinking of putting in the following attributes:-
Actor ID, Name, Age, Playname, Role (Actor ID is PK)
but i was unsure if they worked, although i think they would, because playname from the play table would be a foreign key in the actors table. this is where i come into conflict with the bookings table, ie what could add to teh custoemr table as a primary key that would make a suitable foreign key in the bookings table, and what could be a suitable primary key in teh bookings table, and also a suitable foreign key in the play table? please help, its an urgent issue. thanks very much for your time.
So, basically I have an input form. I disabled pretty much all of the controls and instead of using the left and right arrow buttons I wanted to create my own ones to go to the next record. What happens is that the button pushes down, but doesn't unpush. And then if i push it again it pops back up.
This is what is in it, as far as code goes for my button: DoCmd.GoToRecord , , acNewRec
... so, yah. It seems kind of idiotic, but i hope someone can tell me what's up because it's really annoying!
When I open an already created form just linked to a table to input new information. At the bottom is says, for example, 1 of 30. How can I make it so that it just adds a new entry at the end of that 30, being 31? And then, perhaps sorting the table... after all the data has been entered. Your help would be much appreciated!!
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?
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 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:
