I have a database which has numbers for different statistics and i would like to be able to search, for example, the past 10 weeks and find out how many time a certain number has been recorded.
View RepliesI am trying to create a statistics function on a database. The idea is that the user will enter a start and end date and either search for all records during that date range or select a client from a combo box and only view records for that client during the selected dates.
After doing quite a bit of searching, it seems that I should be using a wildcard in the criteria however I cannot get this to work. The code I have been playing with is:
Code:
=Nz(Forms![Statistics]![ClientCombo] ,"*")
I have changed the "*" to a specific client number and if the combo box is left blank, results are shown for that client only and if a client is selected from the combo box then the selected client is shown. The only thing I cannot get it to do is show all entries if the combo box is left blank.
I have a parent table called patient info. and a child table called fills. The database in the future will become very large, and will be difficult to find a particular patient in the parent table to update fills in the child table. How can I search through the parent table with ease so that I can update fills?
Thanks for those who help!
hi.. i am currently creating an employee database.. and there is a form on
the database that needs to search on records.. i am planning to create a form
with a single textbox (for the keyword entry) and a search command button
that when clicked, the search results will be shown in a seaparate continuous
form.
now here is my query:
SELECT tblEmployee.EmployeeIDPK, tblEmployee.LastName, tblEmployee.FirstName,
tblEmployee.MiddleName, tblCompany.CompanyName, tblDealer.DealerName,
tblOutlet.OutletName, tblPosition.PositionName
FROM (tblOutlet INNER JOIN (tblDealer INNER JOIN (tblCompany INNER JOIN
tblPosition ON tblCompany.CompanyIDPK = tblPosition.CompanyIDFK) ON tblDealer.
DealerIDPK = tblPosition.DealerIDFK) ON tblOutlet.OutletIDPK = tblPosition.
OutletIDFK) INNER JOIN tblEmployee ON tblPosition.PositionIDPK = tblEmployee.
PositionIDFK
WHERE (((tblPosition.PositionName)=[Forms]![frmSearchEmployee]![txtSearchFor])
) OR (((tblOutlet.OutletName)=[Forms]![frmSearchEmployee]![txtSearchFor])) OR
(((tblDealer.DealerName)=[Forms]![frmSearchEmployee]![txtSearchFor])) OR ((
(tblCompany.CompanyName)=[Forms]![frmSearchEmployee]![txtSearchFor])) OR ((
(tblEmployee.MiddleName)=[Forms]![frmSearchEmployee]![txtSearchFor])) OR ((
(tblEmployee.FirstName)=[Forms]![frmSearchEmployee]![txtSearchFor])) OR ((
(tblEmployee.LastName)=[Forms]![frmSearchEmployee]![txtSearchFor])) OR ((
(tblEmployee.EmployeeIDPK)=[Forms]![frmSearchEmployee]![txtSearchFor]));
but when i click search, the results are empty. what could be wrong with the
query? is it the query? what should be the record source for the search form
and the result form?
if you want to see the actual ms access file, here it is:
http://www.gigafiles.co.uk/files/636/human%20resource%20info%20system_2006-10-11.zip
the name of the form is frmSearchEmployee and frmSearchResultEmployee and the
name of the query is qrySearchResult.. thanks a lot and God bless..
I would like to put a search tool on a form that does pretty much the same as the find dialogue box, with out it being a dialogue box...
I need to be able to search specific fields on the form and all the fields. It needs to "Find Next" so to speak, as in not just finding the first match.
Any help is GREATLY appreciated...... Joe
I wanted to see if it is possible to pull a query by just entering say the first three letters of a project name and the query displaying everything from the table that begins with those three letters. Any help with this is greatly appreciated. I am just completely stuck!
View 2 Replies View RelatedLets say i have records that contain the following dates
09/06/07
14/06/07
20/06/07
31/06/07
14/07/07
And i wanted to know what records contain anything between 09/06/07 and 31/06/07 so it would show the records that have this in the date field
09/06/07
14/06/07
20/06/07
31/06/07
What would be the best way to do this
Thanks in advance
I have a form I am using to search for records based on any number of criteria. The one I am having a problem with is a model number search. I would like to search based on a from value and a to value.......for instance........let's say you had the following items:
bird
birth
bill
birdbath
bite
bitter
If I want to search for values from bir to birt, I would get bird, birdbath and birth. How can I code my query value to look for values between two text boxes in a form?
Hi all,
With some help from this forum (esp. Pat Hartman), i've been able to code up a script that performs a check on duplicate values.
My database has a Room Bookings form which consists of the room name, periods and booking date (BDate) fields. I need to prevent a duplicate on the same date, period and room.
The only problem with my code is that it only does a check on one of the fields e.g. BDate although i select a different period or room it still comes up with an error message saying that booking already exists? This means it is only checking the duplicate value of 1 field and not a combination of fields.
Here is my code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strSearch As String
Dim varKey As Variant
strSearch = "BDate = #" & Me.BDate & "# And Period1 = """ & Me.Period1 & """And Period2 = """ & Me.Period2 & """And Period3 = """ & Me.Period3 & """And Period4 = """ & Me.Period4 & """And Period5 = """ & Me.Period5 & """And Period6 = """ & Me.Period6 & """And Lunch = """ & Me.Lunch & """And After_School = """ & Me.After_School & """"
varKey = DLookup("Booking_ID", "Furtherbookings", strSearch)
If Not IsNull(varKey) Then
If MsgBox("Booking already exists Booking ID: " & varKey & ". Do you wish to continue to create a new record?", vbYesNoCancel) = vbYes Then
Else
Cancel = True
Me.Undo
End If
End If
End Sub
Can any1 identify where i am going wrong?
I'd really appreciate any help/guidance to resolve this problem!
:(
I'm trying to write an app that will allow the user to search for records based on database fields, then select a subset of those records to be manipulated by other functions. The VBA book I'm reading led me to believe that a RecordSet would be the best way to store this subset of records, but searching around on the web has pointed me toward using a DAO.QueryDef. I can't seem to get either method to work!
Here's a quick overview of the app: everything is placed on one form. I have two tab controls that make a sort of upper and lower set of pages. One of the lower pages has the search functions. The user enters his search criteria into one or more textboxes (correlating to database fields, i.e. Last Name, SSN, etc), then hits the Search button. This should query the database, then populate a listbox with the search results. The user can then select one or more records from the listbox, hit another button, and the selected records are copied to another listbox on a page on the upper half of the form, where they can be further manipulated.
Can someone advise me on the best objects to use to accomplish this? A short code sample would be awesome.
By the way, does VBA have some sort of online API reference (like Java)? I know that MS Access has the object browser, but it doesn't give descriptions of the objects, nor does it list methods that can be invoked on them. Could someone also point me towards some good programmer's resources?
Thanks...
Hello All
I've been looking and abusing the search function on this forum for this particular obstacle but no luck.
The client im building a database for wants a search form and everyone who used the old system are used by using *'s in their search criteria.
I know how I can use *'s in queries but its fixed (I think).
I want to let the end user use * when they want to pin down a particular product. So they just type in a part of a word and use a * at the end, middle or before.
Or should I just abandon the idea of using them in input form controls?
I know this works because I have seen it before, too bad I had no access to the source of it.
I hope someone comes up with some ideas, pointers or howto's
Thank you guys in advance
Hi all,
I've got one question again.
Let's say. I've created several tables to store data for several categories.Note: there is no relationship between each table.In each table, there is the date field in which the record is created.
Then I want to make another daily record form that is to find records from all categories which is created at the current date.
Is this possible to search records from various tables and combine them to show in one form or report?
Can anyone help me in this case?
thanks in advance.
I will try to explain my problem as best i can and would appreciate any thoughts other people have on it, it is surely similar in some degree to someone elses previous work!
I need to produce management information on a monthly basis, one example of this type of work are an employees one to ones.
table121 contains following fields, ID scheduledDate CompletedDate Completed(yes/no)
My report/query needs to group records by the month (which i do through formatting date fields to display mm/yy), count the number of scheduled one2ones, count the number of completed one2ones, display a %.
I have played around and got this to work using querys with grouping and sums.
My problem is if the schedule date and the completed date are in different months then all of the statistics become out of sync, particualy when there are more appraisals taking place than scheduled.
Any ideas?
I'm trying to find out the statistics of my dabase:
# of total items
# of unique items from 4 different criterias
When I put that into the queries, using the count function, it works well for 1-2, and then if I add in more into the query, it gives ridiculously high numbers for the counts, and freezes. Am I doing something wrong?
Thanks!
I am looking to come up with statistics for my volunteer tracker. I have a table of transactions that records who works each night we are building our haunted house. These transaction records record the Date, Worker and the Time In & Time Out. I would like (probably a cross tab query) that lists all the dates in the left hand column, and count the number of workers in another column, then the number of man-hours put in for that date. Kind of like this:
Date | Worker Count | Man-Hours
---------------------------------
7/30/2007 | 42 | 168
7/31/2007 | 31 | 124
8/2/2007 | 28 | 120
8/3/2007 | 17 | 68
Any help on this would be awesome. I have no experience with cross tab queries whatsoever.
SW
Sorry if this is a question asked a lot but I need help with statistics in acces.. Im doing a booking system in access with customer and booking/bill tables. I have an IF statement to work out price (in a query) for the booking or which displays error if booking cannot be made. The query runs when a button is pressed in the form. The booking price is worked out by time (morning, afternoon, evening) and special (gold wedding anniversary, extended evening etc) which change the price. The date and booking time fields are set together as primary key (composite or whatever key...). The system works monthly. I need a system to find out how much money has been made, how many bookings have been made (and how many could have been made), how many are regular bookings (there will be a regular booking yes/no field). I have probably given too much information but I need to know how to do this more automatically then copying and pasting info into excel and doin equations in it. Also in excel I would just have to presume month = 30 days or manually type in. Is there any code to copy the data into an excel spreadsheet with predone equations automatically? or is there a better way to do this? that isnt too difficult. I have only just started looking at VB so dont know much. PLEASE HELP! PLEASE!!!
View 1 Replies View RelatedHi,
I have a form that runs a parameter query to search for university name and then displays 2 fields, university name and course name.
I am having difficulty with a search button that i have on the form called search_command; it is supposed to run the exact query as when you enter the form, it does this but displays the result in a dataheet, i want it to repopulate my 2 text label fields as mentioned above.
In addition i want then to be able to go to a specific record, select it and then press a button to append it to another table. i ahve not started this part yet
Can anyone please help ?
i am a beginner..
how i can write a code in a afunction that search
a record in a table according to some conditions
example:
i hv table employee that contains Title field which can have one of 3 values:Admin, Rep or Driver
i need to search the employee that has his type = admin
i can have one or more records that have this type...
so how to find first one, last one and all?
I have a database that I am creating for my work. I have a form that I am trying to get it to search the information from a table to pull the record on the form. I would like to search infomation such as employee id and wanting it to pull that information from the table.
View 7 Replies View RelatedThis is either very simple or very complex, I haven't figured out which yet.
I need to know the number of tables in my database and from each table I need to know how many records are in each table. Ordinarily I would just count the number of tables then open each one up to get the number of records, but I'm working with 100+ tables so that's not very practical.
If it makes a difference, the tables in question are linked tables. I don't imagine that is relevant, but thought I'd mention it.
Thanks!
I have a database in which the year is stored in a table called year with as laid out below
"YearID" autonumber field and the
"Year" field which is a date field.
I am trying to search for records via a query for just the current year, i.e. the highest autonumber and I am having no success.
I have a "form1" to entry data to "table1". In this form I placed a command button to open a "Search form". After I do a search by query in "Search form" and found a record and I want to close this "Search form" and go back to the "form1" which already focus to founded record.
Thank you for any code provided
Is there a way that I can search the queries in my Access Database for certain text string? If someone knows please post some tips.
Thanks...
In the Table I have in the trailer database has, among many, 2 fields called 'Maximum Length' and 'Minimum Length'. Say, the data in these fields could be 5 and 4 respectively. I would like to create a search box where I can type in any length such as 4.4 and the database would return the trailer that can fit this length. (Sot the searched value is between the Min. and Max. length) What would I need to type into the query box and the search form to type in the length?
View 6 Replies View RelatedIs there a way of formulating statistics at the bottom of a report?
Heres what i have.
The report pulls Rank, Last Name, First Name, Assigned weapon, Weapon qualification date. After 6 months i use conditional formatting to highlight the soldiers qualification date red. Im in the military that's why im tracking all this, but I need figures to report to higher, and at the bottom i would like it to show, "#Qualified", "#UnQualified","% Qualified", "% Unqualified", "#Expired","%Expired"
I have a table that has 4 fields; id_vl, id_product, date_vl, vl.
Basically each product is valued either on a monthly, weekly or daily basis. I want to create a filter that is based on a text box that filters the dates from which one can look at the dates of valuation and the associated values.
The problem comes with the products valued on a weekly or monthly basis. For example, if I simply say [tblvl].[date_vl]>=Me.textbox, and me.textbox = 13/09/2013, yet the last value date for a weekly product was 09/09/2013, this record won't be included. Therefore I need to set the date of the previous record as the filter. This is my attempt but it doesnt work...
Code:
SELECT tblVL.Id_VL, tblVL.Id_Product, tblVL.Date_VL, tblVL.VL,
FROM tblVL
WHERE iif(tblvl.date_vl<>me.fees_start_date, [tblVL].Date_VL>=(SELECT
MAX(PrevDate.Date_VL)
FROM tblVL AS PrevDate WHERE PrevDate.Date_VL<[tblVL].[Date_VL] AND PrevDate.Id_Product=tblVL.Id_Product;),tblvl.date_vl>=Me.fees_start_date)
;