Searching 2 Fields For One Criteria

Oct 31, 2005

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.

Thanks :)

View Replies


ADVERTISEMENT

Criteria: Searching From Beginning

Jul 1, 2005

Hello,

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.



Mateo

View 2 Replies View Related

Searching Using Multiple Criteria

Feb 23, 2005

I have a button with this code attached

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:

View 1 Replies View Related

Searching On Multiple Criteria Problem

Sep 19, 2007

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 .

View 6 Replies View Related

Select Statement Searching On Date Criteria

Feb 23, 2006

Hello everyone! :)

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?

Thanks for reading this... hope it makes sense!

:)

View 4 Replies View Related

Modules & VBA :: Searching For Record Using Multiple Criteria

Aug 2, 2013

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

[code]....

View 14 Replies View Related

Modules & VBA :: Searching For A Variable With Date Criteria In Table

Jun 14, 2013

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.

View 13 Replies View Related

Searching Fields

Jul 24, 2006

Hi,

I have a basic form which currently displays different information such as name, date, country etc.

At the moment if I type anything into the fields it edits the data (allow edits is on).

Is it possible that if can type in a name and it will find the pieces of work by that named person rather than edit the field? At the same time I might want to search by name and date.

Currently I have a listbox that lists all the names and when selected it requeries that data and lists all records just by that name. I am wanting to also search by the fields.

Can anyone help?

View 2 Replies View Related

Searching All Fields In A Table

Mar 20, 2006

Subject: Searching all fields in a table

Hi, All,

I have to send an Access table consisting entirely of Text fields to another company for automatic list cleansing. My table MUST NOT CONTAIN even a single comma, else the whole procedure will go Iraq-shaped.

Therefore before sending the table, I must apply FIND to each field (column) to search for that one comma that may have slipped in.

So far I know only how to search one column at a time. Is there a way of searching the whole table for commas within Access, rather than first exporting the table to Excel?

Thanks for your help.

Adrian

View 4 Replies View Related

Searching For Name Among Table Fields

Jul 24, 2007

I am trying to do a query that is set up like this:

I have a table with several fields which represents company vehicles. In each field, a persons name is inputed, unless no one is using the vehicle, in which then the field is blank. Each record represents one day, so the date is the primary key.
I am trying to make a query look at each field, and pull out one persons name, and show which vehicles that person will use over a date range. I have a form setup where you put in the date and the name you want ([Forms]![staff printing]![staff name]), and I am trying to tell the query to look at the form field and bring back only the results with that persons name.
Here is an SQL portion:
((Vehicles.[901 Morning])=[Forms]![staff printing]![staff name]) AND ((Vehicles.[901 Afternoon])=[Forms]![staff printing]![staff name]) AND ((Vehicles.[961 Morning])=[Forms]![staff printing]![staff name]) AND ((Vehicles.[961 Afternoon])=[Forms]![staff printing]![staff name]) AND ((Vehicles.[979 Morning])=[Forms]![staff printing]![staff name]) AND ((Vehicles.[979 Afternoon])=[Forms]![staff printing]![staff name])...

The date portion is working fine, which is why you do not see it here... it is only the portion where I try to pull the name for each vehicle that I have a problem.
I tried putting an "OR" where all the "AND"s are, but no luck.

Any thoughts?
(And let me know if I was not clear)

Clint

View 4 Replies View Related

Searching Fields From Web Page

Dec 17, 2005

Ok, I used the wizard for the 1st time today to create a web page access to my database. Sure enough the fields I added all appear and can browse up and down the records.

My question is....How do you make it searchable ???
Is there already something there i can add to the page from design ?? or would anyone happen to have a bit of code I can manually add into the HTML to achieve this ??

Thanks for everything and anything.

I originally had this thread in the "queries" forum but had no answers. Maybe nobody knows, or maybe I was in wrong forum. I still might be in wrong forum, in which case apologies. It just sounds like an area I'd like to branch my database out as (I think). If this bit finds an answer my next problem is going to be to make sure that over the web page version I'd need it to be read only.

View 1 Replies View Related

Searching Fields From Two Tables

Mar 4, 2007

Hi, Im new to Access and VB so please be patient with me. Do know a little about sql queries though.

I have two tables. The first (clones) has another table (BLAST Nucleotide) which is dependent on it (ie: one clone can have many BLAST records)

I want to create a search where I can search by either clone name or BLAST name, and return the results in a list box, and then select an entry from the list & view

Any help appreciated

My zip file of my database can be found HERE

View 1 Replies View Related

Searching For Record By Multiple Fields

Sep 9, 2006

Hi,

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....

Thanks, Ben

View 1 Replies View Related

Show Multiple Fields As One, For Searching Purposes.

Dec 8, 2006

Hello everyone,

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.

-Ben Bolduc

View 3 Replies View Related

General :: Searching History Of Fields In Database

Jun 13, 2013

I have a database and would like to search history of some fields but they are autonumber and it won't let me search them.

The fields in question are on the form Workorder:

WorkorderID
CustomerID

View 10 Replies View Related

Queries :: Searching Multi Fields In Table

Jun 23, 2014

i have a form with a control for "repair type" and a subform that has a query that searches a table holding vehicle repair records. on the form the user can enter up to 5 repair types, [repair1], [repair2], etc.. Then the control on the form holds each type of repair to search by. Since the user could use one or all five repair fields and put repair types likes brakes in field one on one record and field 5 on another record, how do i search to just pull out those records that have the specified search criteria?

If i put in [form]![control] in each of the 5 criteria in the query it comes back with no records, because it it looking for that type in every repair field rather than once between the five fields?

View 2 Replies View Related

General :: Searching Database For Unused IP Addresses From Populated Table / Fields

Feb 18, 2015

I am looking to transfer a number of spreadsheets that I use to track IP Addresses to an Access Database, I have set up the tables and fields, what I would like to do is search for unused IP Addresses from populated table/fields.

View 2 Replies View Related

Auto Rearrange Fields Or Hide Fields In DataSheet Based On Criteria

May 17, 2007

I have main form and that has one sub-form , this Main form governs/determines the data in the sub form.

This Sub form ( DataSheet Mode) has approximately 130 columns and based on the Main form criteria only ~ 20 columns has to be filled.

What I want to do is based on the main forms criteria I want to show only the columns that are applicable to main form criteria.

If I use Columnar or Tabular single form for the Sub Form I am able to hide the fields that are not required BUT IT LEAVES A SPACE/GAP on form ( for the hidden fields that are not required)

Private Sub Form_Load()
If Forms!frmShowPIforActiveAndCanAddNewPI!FrmSubFrmFi lterProductInformationPerFMT!CASETIF = True Then

Me.CASETIF.Visible = True
Else
Me.CASETIF.Visible = False
End If
End Sub


And If I use DataSheet and hide ( visible = no) a particular filed it still shows up in Sub Form

Is there a way to Auto-Re Arrange all the fields in the sub form so that the hidden ( visible = no) fields no not leave gap


Or is there a way by VBA program to select fields ( Columns) from a table to be displayed on a sub form based on a criteria

Thanks
Rahul

View 1 Replies View Related

Question About Searching For One Date Accross Multiple Date Fields. Thanks!

Jan 12, 2007

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:

View 14 Replies View Related

Criteria In 2 Fields

Apr 25, 2007

hello everyone! hope someone can help me out.

i have a query that does a search for a string in 1 field right now in the table. it works great, but then i found out that the string may also appear in another field.

for example, my query looks for "abc" in Field 1. then i found out i also want to search for "abc" in Field 2. In Field 1 and Field 2 there's no way to tell where the string will appear. Field 1 and Field 2 are text fields too. So I was thinking it should be a Like "*" & abc & "*" type match.

so basically i want a query that will return results if "abc" is in Field 1 or Field 2.

Is there a way to build this?

View 1 Replies View Related

DLookup All Fields With Same Criteria

Aug 5, 2007

Hello,

I have an inquiry about using dlookup to retrive all fields from a table that are using the same criteria. Here's the table structure

Table1:
ID (Autonumber)
Number
Code
Grade

I have the following data:
Number Code Grade
4 15 68
4 52 78
4 17 69
5 85 83
5 28 17

I am using this to retrive values for number 4 but I only get the first row
DLookup("Code", "Table1", "[Number] = 4")
DLookup("Grade", "Table1", "[Number] = 4")

I get only as a result 15 and 68, how can I retrive all the rows that have Number 4?

Thanks in advance for your help

View 14 Replies View Related

Search For Record Using 2 Fields As Criteria

Sep 27, 2005

Hi all,

May I know what is the easiest way to search for records using 2 fields wich are not primary keys? and then return a boolean value whether it is found or not...

These 2 fields are of integer type.

Recordset.Find can only find record with one field and not two.

Is there any codes available for this?

Thaks a million in advance

View 3 Replies View Related

Comparing Two Fields In One Criteria Field

Aug 9, 2005

Hi,
I just started doing something in access and need your help.
I have created a database and need to create a query which will sort all important fields within a certain time period. I have a date field and in the criteria field I wrote an expression:">Forms![Insert_date_form]![starting_date_field] and < Forms![insert_date_form]![ending_date_field]"

Its purpose is to show all fields which date is between this to dates. User will have to enter this dates in a separate form(Insert_date_form) which will store it in a small database containing only these two fields, e.g. generate report of all employees that are started working within a period between starting_date and a ending date. When I try to create and open a report based on this query I've been prompted with a small dialog which says: Enter parameter value.... for starting_date_field and same for ending_date_field. I don't need that. These dates are allready entered by user in a insert_date form and stored in its databese.

Is there a beter way to do this?

Thanks!
P.S. Sorry for my poor english. It's not my native language.

View 8 Replies View Related

Query Two Fields Based On One Criteria

Aug 21, 2005

Hi all,

I am endeavouring to filter a form based on the position of an individual.
I would like the users to be able to select their position and then any record where there is a match in the "Primary Responsibility" field or the "Secondary Responsibility " field will be displayed.

I have an underlying query that is populated by a combo box on an unbound form.

This has worked when generating reports for individuals but I cannot get the required result when using the form.

Thanking anyone in advance who can assist me with this.

Regards,

Rod

View 2 Replies View Related

Concatenating Fields Based On Criteria?

Sep 20, 2006

I have a query that is set up to join two name fields...for example:

[ELIGVENDORS.LSTNAM] & [ELIGVENDORS.FSTNAM]

The issue is that this is perfect for names that are entered in the table like this:

Lstnam: ABC HOSP Fstnam: ITAL

They are entered like that for groups, but when individual doctors are entered, the data entry folks are entering them like this:

Lstnam: SMITH Fstnam: BOB Titlcd: MD

So when I join them the first displays correctly as ABC HOSPITAL, the second displays as SMITHBOBMD and I have to manually go in and add a comma.

Is there a way to do an if/then statement or something to tell the query to display results If the titlecd is NOT NULL, display as [ELIGVENDORS.FSTNAM] & [ELIGVENDORS.LSTNAM]&", "&[ELIGVENDORS.TITLCD], else display as [ELIGVENDORS.LSTNAM] & [ELIGVENDORS.FSTNAM]

I'm sure there is, I am new to IF/THEN statments though and was hoping to get some help setting it up.

Thanks in advance and I hope my description is clear enough.

Han

View 2 Replies View Related

Query Based On Criteria In Two Fields?

Sep 7, 2007

Hi All

I want to create a query which is based on two fields.

One field is a date query i.e >=Date()-7 OR >=Date+7

The Other is a Combo box where the criteria is No

My problem is that access is currently running this as two individual queries.

Is it possible to make this query return data only when both fields are matching?

Or do I have to run this as a parameter query?

Thanks!!

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved