N00b Question. Query Criteria.
Sep 1, 2006
Okay. So, this is my set up.
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?
thanks!
View Replies
ADVERTISEMENT
Oct 1, 2006
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?
View 2 Replies
View Related
Sep 13, 2006
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.
View 5 Replies
View Related
Dec 8, 2005
Okay, can anyone help me with the following:-
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.
Fergal
View 2 Replies
View Related
Aug 31, 2006
Hello.
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!
Thanks!
View 2 Replies
View Related
Aug 31, 2006
Hello,
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!!
Thanks!
View 2 Replies
View Related
Oct 21, 2006
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.
Any help would gratefully be appreciated. Thanks
View 5 Replies
View Related
Aug 20, 2007
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?
StoreSubjectSalesSales %
1516Fiction56431.5-24.15%
1516Audio Unabridged1650.8-231.04%
1516History / Military History10081.1-29.99%
1516Role Playing / Graphic Novels14773.9-20.27%
1516Mystery13152.6-19.84%
1516Audio Abridged1785.9-141.84%
1516SciFi / Fantasy27535.3-7.93%
1516Juv Audio/Video1580.6-100.13%
1516Biography8103.6-15.89%
1516Sports7910.8-15.64%
1516Current Affairs / Law8141.9-14.34%
1516Reference7183-16.22%
1516Juv Non-Bk4585.9-25.02%
1516Science / Tech2961.4-33.98%
1516Movies / TV / Music / Dance3395.3-29.46%
1872Fiction307344.3-7.49%
1872Business134307.5-13.48%
1872Psych / Self Improvement100650.4-10.05%
1872Audio Unabridged29165.9-27.32%
1872Cookbooks57463.3-13.56%
1872Computers59235.7-12.37%
1872Regional59883.4-11.22%
1872Health & Fitness64713.8-10.29%
1872Maps19358.4-27.66%
1872Current Affairs / Law47927.1-11.08%
1872Travel Foreign42583.7-12.27%
1872Religion / Bibles80255.6-6.07%
1872SciFi / Fantasy67641.4-6.49%
1872Study Aids / Notes38299-11.24%
1872Games41745.1-9.79%
View 2 Replies
View Related
Apr 2, 2008
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.
Please help
Thanks for reading
View 1 Replies
View Related
Aug 10, 2005
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
View 1 Replies
View Related
Sep 27, 2005
Hey Guys,
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!!!
Thanks in advance guys!!
View 3 Replies
View Related
Nov 21, 2006
Is there a way to have a Criteria set to promt a user to select a item from a dropdown list.
So when the prompt is made to ask for the Item Name it will have a dropdown list that the user can choose from
View 4 Replies
View Related
May 10, 2007
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
View 2 Replies
View Related
Aug 24, 2005
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.
"Is Null"
<> "True"
View 1 Replies
View Related
Aug 25, 2005
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?
Thanks
Mos
View 2 Replies
View Related
Sep 7, 2005
Hi all,
In my query i have 5 different fields showing different type of fuel types, these are checkboxes on a form.
I have another field which shows the 'dry time' (this is calculated on two other fields, the time of runout and time of delivery)
What i want to to is have a selection criteria whereby if 'Diesel' (one of the fuel tyeps) dry time is over 06:00 hours, it is displayed.
Im not sure whether this should be in the Diesel field, or the dry time field.
I have tried both using:
'Diesel' > "06:00" and also 'Dry Time' > "06:00"
but niether work...any help would be appreciated. Thanx
View 2 Replies
View Related
Sep 19, 2005
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?
Thanks
View 3 Replies
View Related
Oct 3, 2005
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...
View 6 Replies
View Related
Jan 31, 2006
hi,
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???
Any help would be much appreciated.
Regards,
Jonathan
View 7 Replies
View Related
Jul 24, 2006
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.
http://i54.photobucket.com/albums/g115/TomBP/Print.jpg
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
http://i54.photobucket.com/albums/g115/TomBP/printing.jpg
But my experience of such criteria is'nt all that so I don't know how to typ this so that it matches my database.
My subform = frmSubform
My mainform where the subform is located = frmSearch
I also uploaded my database to yourfilelink.com . If you want to have a look. It's only 200kb.
http://www.yourfilelink.com/get.php?fid=141709
View 3 Replies
View Related
Sep 28, 2006
Hi guys,
I have a newbie question here.
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)
How do I make the correct criteria ??
Hopefully this makes sense. Thanks
View 1 Replies
View Related
Apr 12, 2007
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.
View 1 Replies
View Related
Jun 18, 2007
Hi,
Instead of mention criteria inside query, can I pass criteria through form with some codes? Because I want to use same query for different reports.
thanks for your help
mithani
View 14 Replies
View Related
Jun 22, 2007
I am trying to run a query from two tables.
Table 1 has a Sub Table with a 1 to many relationship.
The PK is the record ID in Table 1.
I want to do a query of all records that have 3 or less records in the sub table?
Show me all records in Table 1 that have 3 or less records in Table Sub.
Thanks!
View 4 Replies
View Related
Sep 9, 2007
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 would just want to extract account 123456
Any help would be appreciated
View 14 Replies
View Related
Nov 6, 2007
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:
View 7 Replies
View Related
