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 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 RelatedHow can I get a PK in another table, based on searching for it based on 2 values. By searching with 2 values you will always get only 1 record.
READ BELOW FOR SITUATION
My Problem on my form:
The user enters 2 values, after entering the 2nd value a Unbound combo box runs a query, which searches for these 2 values (unique), that appear in a separate table.
These 2 values are unique toegether, but records in this separate table have another field, its Primary Key (AutoNumber), used to identfy the pair.
So back to this Unbound Combo Box that searched for the primary key of the 2 values entered. Now Only the Primary key can be selected from the combo, the value that I'm after.
But since there will only be 1 option (being unique), how can I have the PK value aready stored in an Unbound text box, as opposed to having options in a Combo box?
Hi,
I have a form that is based on a query with many fields. As an example there is a field called "Medium" which contains values such as "Oil on canvas", "Oil on wood" etc.
At the moment I have a filter which works fine but this doesn't allow me to search all records which have the word "Oil" as part of the Medium.
I would like to be able to search all fields on the form. I have looked at a few exmaples on the forums but could not find one that applies to my scenario.
Can anyone help?
Thanks in advance.
I have a requirement of searching the database based on 3 columns. I have attached the search form and the code.
Private Sub cmdSearch_Click()
Dim strSQLHead As String
Dim strSQLWhere As String
Dim strSQLOrderBy As String
Dim strSQL As String
Dim strJoin As String
[Code] .....
I have a report that I am trying to complete based on several queries. I am trying to count the number of records based on certain criteria and using the following DCount.
=DCount("[Calculated time]","IPG1","[Calculated Time] <= 0.04" And [Ship-to party] In ("SN00207PJZ","SN09162XXX","SN09324XXX"))
I want to count the number of IPG1 records that are under .04 and have the Ship-to party of the ones listed. I have tried everything that I can think of to get it to work but can's seem to get it to. I figure it's something easy but I don't see it.
I have a table of data regarding companies (contact info, etc). The company I work for provides these companies with up to 10 different products. On my input form, I have created 10 check boxes (and thus, 10 columns of Yes/No data in the corresponding table).
Each company has at least 1 product checked off, and up to all 10.
I would like to create a combo box that lists all 10 products, and upon selecting one, a list box then populates with the names of the companies (primary key) that use that specific product.
So, for example,
Company A buys CATS and DOGS from us
Company B buys DOGS
Company C buys CATS and ELEPHANTS
Combo box options: CATS, DOGS, ELEPHANTS
If I choose DOGS, then a list box gets populated with:
Company A
Company B
and when I click either of these, the record should be brought up for the respective company.
All of this should take place in the header of the form, while the form itself can be updated based on search selections.
I have a table with Zip CODE field. I uploaded my information to this table form an excel file. There were over 120K rows of data so no way i could do this in excel.
So I found that some of the ZIP codes are either blank or have less than 5 digits.
Can I build a a query with the ZIP code field that will return all zip code records with less than 5 characters or blank. I'd like to repair these zip codes and a update query will be my next step.
I repeat excel is not an option because when I try to sort by the Zip Code column my computer explodes (okay not literally) ....
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 RelatedI want to know how to create a test box one or more than one and can search with a button. I did that but I have to fill all the text box to have a result and i want to know how to search even if i did not fill all the text box ...
View 2 Replies View Relatedmy database lag whenever i start searching, not really bad but i could notice that it blinks whenever i press a key on my keyboard. computer has a big ram (4gb), i5, with 500gb HDD... lots of free space. the database file is only 6.9MB..maybe there is a code that disable or makes the blinking unnoticed?
View 3 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...
Custom Autonumber based on lookup. I am creating an access database where autonumbers to be work.
I have a table with Segment Name my main table will lookup the values of segment table.
Based on the Segment I choose Autonumber has to be created
My main table to house each record (tbl_import) has the following fields:
ID
Segment
Port
MOT
TOTAL CIF
Child table - Segment contains
Development
Deployment
Testing
So based on segment field which I choose
Development autonumber has to set DEV-1001 and Deployment has to set DEP-2001, TEST-3001
My thought is if there a way to code the Segment field to lookup the segment what I choose based on the Autonumber series starts (similar to a vlookup in excel), then concatenate the DEV in the Record ID field.
I would like to filter a table with 3 fields: Name, Surname, and Chart number.I would like to have the results in a subform designed in the same form.In fact every patient can have several admisions (1, 2 or more).I have arranged the main form named men with 3 text unbound box (name, surname, CCN) and 2 button clear and run the query.I have created a query .The problem is that when i push the button the SQL starts and a different window is opened. I would like that when you type the name for example in real time the subform should select the record with that criteria. Moreover I would like that when in the subform appears the records with the criteria the users could click on the selected record to open a different form.
View 3 Replies View RelatedHello 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 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 1 Replies View RelatedI have two tables that are related: BUCKETS & LOCATION
BUCKETS has these fields:ID (autonum)
Type (type of bucket)
Creation Date (when it was created)
Operational (if it is operational or not (Yes/No).
Location (Location of the bucket)
Notes (notes)
LOCATIONS has these fields:LocationAutoID (Autonumber)
LocationCity
LocationID
BucketsOnSite (can have multiple entries)
What I'd like to have done is for LOCATIONS table to be aware of how many different cities there are. So for instance, if there is multiple location entries for the cities of Vancouver and Toronto in there, I want the LocationID to first know how many Torontos there are and in LocationID to combine the LocationCity and a count (starting from 1) to that field.
So for example, if there are three toronto locations and two vancouver locations, LocationID would have TO-01, TO-02, T0-03 for the Toronto locations and VAN-01, VAN-02 for Vancouver locations.
At first I had the expression of [LocationCity] & "-" & [LocationAutoID] in the Expression field in the Field Properties but I quickly realized that it wouldn't count the amount of cities but just add the autonumber and the city (which kind of works, but not ideally).
1.
i create a form and i want do do the searching in it so i but a button and combo box and the list box to show the results and the searching is from query
my form name is form2 and the button name is search and the combobox name is combo212
2.
how can i show the total in the charts in access like i create a chart for students and years so every year i know how many student in and i want to know how to sum all the student in all the years ans show it in the chart?
3.
i create a tow search combobox and button the question is how can i siprait the searching from the tow combobox like i search for the interest in the first combobox and the seacond one is for the years but i want to search for the interest in all years i tray it but it sis not work with me so how i can do it
4.
how can i add data to the combobox without going to the table ?
Function that searches modules for instances of early binding? Im sure i could write one, but why reinvent the wheel?
View 2 Replies View Related