Query Criteria Probs
Feb 21, 2008
Hi to all,
I'm using a Access 2003 form to access a query that'll be used to generate a report. The example I'm following is found in http://office.microsoft.com/en-us/ac...CL100570041033.
On my form, I have a combo box (Course ID), textbox (StartDate) and textbox (EndDate).
In my query, I'm using the values from the above 3 controls as criteria.
E.g.: In the Course_Date field in my query, I used Between [Forms]![Form_Name]![StartDate] And [Forms]![Form_Name]![EndDate] as the criteria.
If I leave the form controls blank,and click on the OK button, it'll generate a report with errors. What should I do if I want to generate a report that display all records in the report? E.g.: If I select a course in my combo box and leaves the start date & end date blank, it'll generate a report that displays all records for that particular course.
Thx!
View Replies
ADVERTISEMENT
Feb 7, 2006
hi, im a a level it student and my current coursework is making a new program/system for access im making a new order form for the shop where i work and i want to link sundries on a diff order form to sundries on the main order form so it prints it all off on one sheet, basically i want to know how i can get the realtionship between ID number of the order, and the sundrie to link together, and if its possible at all, if its not ill have to change my project slightly which i dont mind, just wondered if any one could help me out a little thanks!:p
View 1 Replies
View Related
Feb 2, 2005
Hi,
I'm developing a database to handle various aspects of quality control reporting. I've got a working structure however, i think, i have gone off the track slightly... First I'll show you part of the structure:
(please see attached .jpg)
(hope that makes sence)... so effictively I have a many to many relationship.
I have forms for each of these entities (frm_parts, frm_Suppliers). The problem i have noticed is you can assign the part a supplier in frm_Parts but it does not appear in the transaction table as a record. Is this because i have based the field "Part Supplier" (a combo box) in frm_Parts on a query? If this is not the right way to go about it. how do i go about it?!
I have an idea... on this.. If i create a new combo box on the form I can use the wizard to select the supplier ID from the Supplier table (i believe) and "store" it in the transaction table... i think! But I want the user to select the Supplier by drop down list showing Supplier Number and Supplier Name.. not the ID.
Any help is much appriciated!
Next job...
I have a form where I would like the user to be able to enter, for example, a Supplier number and see if a record of the Supplier exists yet. Next I want the user to be able, assuming the Supplier record exists, to search for a record of a Part number from that supplier. Next I want them to be able to "select" that part and be able to open a form with a new record using that part number (this form holds details of a report rasied against that part).
See what I'm getting at?
I've had a go at this but it's been a couple of years since i did any detailed work on an Access database so somethings are a bit hazy!
I'm not asking you to do this for me, but I would much appriciate some pointers on how to create this sort of thing!
Many Thanks
Rob
View 6 Replies
View Related
Aug 8, 2005
Hi,
We've got a database that was on an old computer (Windows 98), and we wanted to transfer this database to our new computers (Windows XP). The database transfers over fine, but there seems to be a problem.
When we try to run a report, it doesn't semem to work properly. We are a training agency and the report in question is designed to show the courses booked in for each tutor between two set dates. The dates are typed into a form (start date and end date), and the tutor can also be selected from this form, and then a report is generated.
Without dates being inputted, all the courses are shown (so the report is pages and pages long). This is working ok. However, when dates are inputted the report stops working and error messages appear on the report. Hence it seems to be a problem with the dates, but I can't understand what since all the boxes are exactly the same as the version on the other computer, and when transferred back to the other computer it also works fine.
Any help with what could be going wrong here would be GREATLY appreciated.
Thanks in advance, Chris (FANE).
View 4 Replies
View Related
Apr 2, 2008
Hi guys!
I have a front-end (FE) linked to a back-end (BE) db.
I have added a password to the BE, but the knock on effect is that the FE won't work now. Just says 'Invalid Password'.
I'm using MS Access 2002.
Any help would be appreciatted.
(The password is to prevent unwanted users from deleting data on the BE on the server)
View 1 Replies
View Related
Jun 23, 2005
Private Sub quicksearch_AfterUpdate()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set rs = Me.Recordset.Clone
rs.FindFirst "[ID number] = " & Str(Me![quicksearch])
Me.Bookmark = rs.Bookmark
End Sub
this is the code that im using to help me with a listbox and a search text box that is embedded on my main form. it works ok. The problem is, i was trying to link an already filled out Table with data to this interface.
When i key in information from the form it works, but when i just enter stuff through the table (which was already completed and handed to me) When I click in the listbox on the main form the code window just pops up showing this section of my code. Help./
Help.
View 3 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
