i have created a form for my database where i intend to give the user the option of searhing my more than one criteria but not all.
i have used a system of combo boxes for the user to enter thier choises leaving the combobox blank for any criteria they dont know.
the user then clicks th button to open the query
in the query i have set the criteria for the fields to equall the related combo bo in the form.
however if the comboboy is left blank (0 or null) the query looks for a record where that field is also null. as a result it wont return any records.
an example
on the table tblFault there is the record
FaultID; 7
Computer id; 2
Name; c
Room-ID; it1
Date Of Fault; 05/06/07
on the form the user enters the following values into the combobox
FaultID; 7
ComputerID;
Nme; c
Room-ID; it1
Date of Fault 05/06/07
the query returns no records insead of the record above.
this is because it is looking for a record in the table where the Computer-ID field contains a null value.
is ther a way to ignore the chriteria if the combobox contains a null value????
i have tried to use vb to convert all null values to be "Like "*"" but it keeps returning a type missmatch error but still changes to combo box value to ; Like .
Dim SearchStr2 As String SearchStr = "[PROTECHNIC_NUMBER] = " & "'" & Forms![FRMPATIENT]![Frm_ICP_Select].Form![Protechnic_Number] & "'" _ And "[ICP_Code]" = Forms![FRMPATIENT]![Frm_ICP_Select].Form![ICP_Code] DoCmd.OpenForm "FRMASSESMENTHEAD", acNormal Forms!FRMASSESMENTHEAD.Filter = SearchStr Forms!FRMASSESMENTHEAD.FilterOn = True
If the button is clicked it should open the FRMASSESMENTHEAD form where the Protechnic Number (which is a text field) and the ICP Code(which is numeric) is the same as the ones on the current subform.
It works great if just using one of the criteria but when i put the and command in and then add the second criteria i get an erroer message saying
Run-time error '13':
Type mismatch
hope this makes sense and can anyone help :confused:
I have a form where a user enters data. One of the things the user enters is a "lot size". I need this lot size field to be checked against a query in the database to determine if the quantity is acceptable or not.
The order number in this query is in certain cases missing a leading zero, so I need to truncate this from the user entered form field.Finally I want the user to be notified if they are trying to exceed the lot size in the query.My code is as follows:
Code: Public Function RemoveFirstChar(RemFstChar As String) As String Dim TempString As String TempString = RemFstChar If Left(RemFstChar, 1) = "0" Then If Len(RemFstChar) > 1 Then TempString = Right(RemFstChar, Len(RemFstChar) - 1) End If End If
Im having trouble with Criteria for a query. I have my criteria like this: Like "*" & [Forms]![NewSearch]![txtSearch2] & "*"
The only problem is, if im trying to search for a last name such as "Aber" the search results come up with not only Aber but also anything that contains Aber, such as the last name Singaber.
How can I make the criteria search from the beginning of the Name, and not anywhere in between?
Please help, going to drive myself nuts :)
Thanks in advance for any help you may be able to provide.
hi. i'm a novice with Access so any explantions need to be pretty simple. I'm trying to create a query that searches 2 fields when 1 word is entered into a parameter value box..... would any1 hav a sugestion. please post a reply if you don't understant what i'm trying to achieve.
I've been banging my head of a brick wall with this one. Its probably straight forward... but I can't see the solution! A problem shared is a problem halved... or so they say!
Ok I have this table called CustomerComments. In it are the following columns
CustID Comment CommentDate
For each custID I want the most recent commentdate and its matching comment from that row... (there can be multilple comments recorded per custID)
Problem is I can't seem to get this to work.. instead its returning all rows. I have run a query that will sort and group this list. The top line of the grouping gives the most recent comment and commentdate per customer.
Does anyone know how I could just get that top line per custID rather than all rows returned?
I'm trying to search for a variable (varCod) in a table(SerialNumberCustomer) but I want to only store the returned variable in an array if its shipDate is between 9/30/2001 and 10/1/2012
this is what my code looks like so far:
Set rst = CurrentDb.OpenRecordset( _ "Select * from SerialNumberCustomer WHERE SerialCardId = " & varCod & " AND (ShipDate BETWEEN #09/30/2001# AND #10/01/2012#) ")
The line of code works and it returns all of the values in SerialCardId where it's = to varCode but it doesn't go through with the shipDate requirement. It's instead returning all of the dates with the found value.
Hi, I currently have an employee database - all info is currently in one table. I am wanting to create a search box in my form which allows me to search for multiple bits of information. ie. I would like to be able to type in either a first name, surname, full name, or employee id number (for example), press enter, and for it to display the results (either by going to the first that it finds, and me then clicking find next, or by displaying all found in an index so that you double click the one that you want - i am not too fussed... the main issue is getting the search box to look in multiple fields).
I currently have the following code:
Private Sub cmdFind_Click()
DoCmd.ShowAllRecords DoCmd.GoToControl ("Surname") DoCmd.FindRecord Me!txtIDFind 'If FindRecord = True Then 'Exit Sub 'End If DoCmd.GoToControl ("EIN") DoCmd.FindRecord Me!txtIDFind 'If FindRecord = True Then 'Exit Sub 'End If DoCmd.GoToControl ("forename") DoCmd.FindRecord Me!txtIDFind
End Sub
(ignore the notes - they were trial and error for something else).
Currently, when i press find, this code will find the first of anything - ie i time in Frank and the first Frank will appear (unless someones surname begins with Frank). What i can't seem to do is get it to progress to the next record if i press a find next button.
I have also tried the following code, but it only works if i put in an EIN number:
Is there a way to search multiple tables for a record. I have 4 tables, each containing a different product. In each table are lists of serial numbers (primary key) and other information (ship_to, sales_order_number,etc.) for the product. I want to be able to enter a serial number and display the information related to that serial number. Not all fields are the same for each table. Is this possible? I would greatly appreciate any help!!
Ok folks I have what is proving to be a doozy for me.
I have a multiple table database related fields in each one are: Procedure Description Payable Amount
I am trying to find a way to search particular tables for particular Procedures. My output must include fields: Procedure, Description, Payable Amount. I have tried a form but I would have to included way too many text boxes to accompish what I am looking for. If possible I would like to have three text boxes if a form is possible. I would start the search and my output would end up in the text boxes below: Procedure, Description, Payable Amount
With of course some sort of Identifier of the table that was selected to search in.
I have three tables that are named different, but have the same column names, but different data in them. The data in each table is linked by a master table that has a primary key.
What I need is a way to make a form with a box where I can search on a value that could be in any of the three tables and then the associted information from the site will display in the above boxes.
(Site Master Table contains all site information and the primary key) (The three tables are sales order information. Stage Only table, Install table, and srop ship table)
Not sure where to put this, but as I want the information to be shown in form view, I guess this is the bext place to start.
In breif, I have created a database, in which infomation on machinery is held.
The problem I am having (atm), is I have to create a function, which I want to be presented in form form, where by each item can be searched by it's RequestID (unique) or the customer who owns it, so that its current location can be attached (or viewed).
I'm not sure whether i've worded this well, so to sumerise:
I need a form with 3 fields, RequestID, Customer and Location.
Where by I input the RequestID, which brings up the Customer and Location, or input Customer which brings up the RequestID and Location (obviously there will be mulitple results from Customer search).
Again, I hope I haven't confused anyone because it's pretty simple what i'm trying to say, but....
I am in the process of creating a database in access to search for a persons name. However, the results will give many ansers. For example if I search for the srname 'jones' I get many names. I eed then to be able to select one of these jones's with the information relavent information.
It has many years since I have done this although i did have quite a good knowledge at the time. Plesae can you make any suggestions and answers as basic as possible !
Many thanks in advance http://forums.aspfree.com/newthread.php?do=newthread&f=18# Mad
OK - I'm kinda new to all this stuff (as you're about to find out from my question).
I've got a page with a search form, where visitors enter a search term. That page uses GET to pass the variable to the results page. The SQL on the results page is pretty simple, it just takes the variable from the URL and filters for records:
WHERE columnname CONTAINS Request.QueryString("passedvariable")
So that all works fine - except that it does not allow for complex searches. If someone searches for 'fish' then that works fine. If someone searches for 'fish and food' then the search looks for "fish and food" somewhere in the record, rather than looking for both of those terms in a record.
Now, I'm guessing that there's no simple solution for who to get this to work, but I'm hoping that someone can help me out and at least point me in the right direction. I'd be happy to post additional code if you need it - but I don't even know what else to post!
I have around twenty shipment tables for different days and one main store check table.In the shipment tables i have bill numbers that correspond with shipping id's and in the store table i only have bill numbers.
I would like to check if the bill number in the store table appears in all 20 tables.after doing that i want the corresponding shipping ID to appear in the store table. Is this possible?
Im trying to set up a combobox on my Customer Contact Information form, where you can enter a phone/fax/cell/pager or any misc number, and it will bring up the appropriate customer.
Is it possible to make a query that will show the different phone number fields as one field?
Thank you so much for your help, and please let me know if I can clarify anything.
Hi. Please do not lecture me on database normalization, as this truly is not in my control.
I have 2 dozen tables, each with 13 fields. All of the field names exist in at least 12 of the tables. And all tables share a ssn field where values are common.(Confused yet? Sorry, if you are...)
If I design a query showing all 2 dozen tables and their fields... Can I set up a query criteria where I enter the requested ssn and then the required field name and have the result show the values of all the fields with that name, among the ones of the 2 dozen tables where that field name exists? (for that specified ssn)
If I am only as clear as mud, please let me know, and I will try again. Or, by asking if I can search for field values querying by field name, am I any clearer?
I need to count records based on multiple criteria from two different tables. I have two tables (i.e. "tblTasks" and "tblTaskHistory"). The tables have a one-to-many relationship based on the "TaskID" field. "tblTasks" has a field called "AssignedTo" and "tblTaskHistory" has a field called "TaskStatus". I need to know how many tasks have been "reopened", the "reopened" status is located in the "TaskStatus" field in "tblTaskHistory". I need this count against a unique listing of employees which can be found in the "AssignedTo" field in "tblTasks".
I am currently building a booking and invoicing database for the small business where I work. One aspect of this database allows you to enter an employee name and schedule days off via a form I have already built. To access this information I would like a parameter query where you can enter a date once and then have this date looked up across a number of date fields. Here are my table column headings:
ID Employee Name Date 1 Date 2 Date 3 Date 4 Date 5 Date 6 Date 7 ....etc
So, basically, the parameter query would ask "What date?" which the person would enter, and then search all 12 date fields to see if the date was contained in any one of them. Then it would pull any records which did have the date in one of the fields.
It seems really simple but I have searched and searched and can't seem to find the answer. Tried a multivalue field but does not seem to work.
Thanks very much, any help appreciated! Elspeth :confused:
hi. my name is tim. im having some problems with a query that I am writing. it should be rather simple and involves only one table.
the table is called tblCustomer. The query involves 2 fields: cResort and cWeek. Neither of these fields are unique. Together they make up the primary key.
cResort is populated with a textfield: PF, PV, and GS. cWeek is populated with an integer: 1-52 (the number of weeks in a year)
This is what the query is supposed to do: SELECT tblCustomer.* FROM tblCustomer WHERE cResort DOES NOT EQUAL "PF" AND WEEK is LESS THAN 10
here's where the problem lies, this filters out ALL "PF" and ALL cWeek < 10.
I want PF's with a corresponding cWeek from 1-10. I dont want to see PFs with corresponding cWeek 11-52.
Example: Show me cResorts: PF with corresponding cWeek:1-10. Do NOT show me cResorts: PF with corresponding cWeek:11-52.
I am trying to construct a fairly complex query on the fly. I have it working except for the criteria side of the query which I am having trouble with. It doesn't really matter about the vb side of it because I can alter the construction easy enough. it is more a question of how I get the criteria right.
I'll show you the SQL below first:
SELECT DISTINCTROW tblCandidate.CandidateID, tblCandidate.FirstName & ' ' & [Surname] AS Name, tblCandidate.Telephone, tblCandidate.Mobile, tblCandidateAvailability.Status, tblCandidate.CandidateID AS [Count] FROM ((tblCandidate LEFT JOIN tblCandidateArea ON tblCandidate.CandidateID = tblCandidateArea.CandidateID) LEFT JOIN tblCandidateJobCode ON tblCandidate.CandidateID = tblCandidateJobCode.CandidateID) INNER JOIN ((tblBranchDetail RIGHT JOIN (tblCandidateAvailability INNER JOIN tblBooking ON tblCandidateAvailability.AvailableDate = tblBooking.ScheduleDate) ON tblBranchDetail.BranchID = tblBooking.BranchID) INNER JOIN tblBookingDetail ON tblBooking.ScheduleID = tblBookingDetail.ScheduleID) ON tblCandidate.CandidateID = tblCandidateAvailability.CandidateID WHERE (((tblCandidateAvailability.Status)='Available') AND ((tblBookingDetail.ScheduleDetailsID)=103) AND ((tblCandidateJobCode.JobCode)='DO') AND ((tblCandidateArea.PostCodeID)='HP')) OR (((tblCandidateAvailability.Status)='Available') AND ((tblBookingDetail.ScheduleDetailsID)=105) AND ((tblCandidateJobCode.JobCode)='DO') AND ((tblCandidateArea.PostCodeID)='DO')) OR (((tblCandidateAvailability.Status)='Available') AND ((tblBookingDetail.ScheduleDetailsID)=106) AND ((tblCandidateJobCode.JobCode)='DO') AND ((tblCandidateArea.PostCodeID)='HP'));
The table structuring is fine it is the SQL after the WHERE statement that I am having trouble with.
This query works fine to give me all Candidates that comply with these criteria. The only trouble is I want to only results where the Candidate(s) ALL three set of criteria. I have tried replacing the OR with AND and using extra brackets to enclose the whole WHERE statement to imply I need all criteria met but this reports back no results despite me having test data where a candidate meets all criteria.
I am sure this is not as hard as I am making it but I can't figure it out at all.
I also ideally wanted to show only one record (ie one candidate that meets all instead of three records showing the candidates). This is not so important as I can work this out easily enough, by probably hiding fields I don't want to see and showing only unique values, etc...
ONLY NUMBERS ABOVE (2) (3) AND (6) DO NOT WORK, IF ANYONE COULD GIVE SOME LIGHT ON THIS I WOULD BE VERY GRATEFUL, AS I AM IN THE BLUE ON THIS.:rolleyes:
Wonder if you guys can help me with something. I have a table with about 1200 guests, what I want to do is to search the table base on different criteria (or combination of criteria), namely phone #, name, street name, and postal code. Not everyone has all this info, and their names aren't separted into proper lastname or firstnames (old data).
What I want to do is to be able to type in a person's first name, last name, or both (an maybe other info if the first search wasn't successful).
http://www.psynic.com/files/access.jpg
What should I do to implement this? I was thinking of running 4 different queries, and interesect them into the final query. What do you think?
Not sure if this belonged in reports or queries, so I chose general. I have looked at several DCount threads but haven't quite found my answer. I want to use Dcount in an unbound textbox in a report. It counts the number of records in another table - comparison the first part of the statement works fine ( up to 'iss'"). When i added the between date part i'm not getting any # returned in my report. I want to addthe criteria of RecDate between the 2 dates on the open form. Can anyone tell me where my problem lies? ( if this makes sense)
Thanks Kevin
=DCount("[type]","comparison","[type] Like 'iss*'" And ("[comparison].[RecDate]" Between Forms!DateInputforRMAsReturned!Text0 And Forms!DateInputforRMAsReturned!Text2))
