Getting all records with same name passed from a textbox

Hello,

How do i get all records based on the name a customer enters in a textbox 
from a form

Let say in the query i do %betty% - this will return all records with betty 
partly in it. I am trying to replicate the same based on a value passed from 
the form

How do i get the same result using 

Like ([Forms]![Main]![txt_exis_pt_name]) - this gives me only one record how 
do i replicate the same result which gives me all records like when i did 
%betty% from query design?

Thanks for your help.
0
Utf
3/1/2010 9:43:01 PM
access.formscoding 7493 articles. 0 followers. Follow

6 Replies
1071 Views

Similar Articles

[PageSpeed] 23

Try:

Like * & ([Forms]![Main]![txt_exis_pt_name]) & *

That might just do what you are looking for...
-- 
Maurice Ausum


"ecwhite" wrote:

> Hello,
> 
> How do i get all records based on the name a customer enters in a textbox 
> from a form
> 
> Let say in the query i do %betty% - this will return all records with betty 
> partly in it. I am trying to replicate the same based on a value passed from 
> the form
> 
> How do i get the same result using 
> 
> Like ([Forms]![Main]![txt_exis_pt_name]) - this gives me only one record how 
> do i replicate the same result which gives me all records like when i did 
> %betty% from query design?
> 
> Thanks for your help.
0
Utf
3/1/2010 10:08:04 PM
Try:

Like "*" & ([Forms]![Main]![txt_exis_pt_name]) & "*"

-- 
Build a little, test a little.


"Maurice" wrote:

> Try:
> 
> Like * & ([Forms]![Main]![txt_exis_pt_name]) & *
> 
> That might just do what you are looking for...
> -- 
> Maurice Ausum
> 
> 
> "ecwhite" wrote:
> 
> > Hello,
> > 
> > How do i get all records based on the name a customer enters in a textbox 
> > from a form
> > 
> > Let say in the query i do %betty% - this will return all records with betty 
> > partly in it. I am trying to replicate the same based on a value passed from 
> > the form
> > 
> > How do i get the same result using 
> > 
> > Like ([Forms]![Main]![txt_exis_pt_name]) - this gives me only one record how 
> > do i replicate the same result which gives me all records like when i did 
> > %betty% from query design?
> > 
> > Thanks for your help.
0
Utf
3/1/2010 10:40:01 PM
Thank a lot to both of you. Karl with your code access did not have to add 
the quotes because it is there already but with Maurice's Acess added the 
quotes automatically so they both worked.

Thanks again.

"KARL DEWEY" wrote:

> Try:
> 
> Like "*" & ([Forms]![Main]![txt_exis_pt_name]) & "*"
> 
> -- 
> Build a little, test a little.
> 
> 
> "Maurice" wrote:
> 
> > Try:
> > 
> > Like * & ([Forms]![Main]![txt_exis_pt_name]) & *
> > 
> > That might just do what you are looking for...
> > -- 
> > Maurice Ausum
> > 
> > 
> > "ecwhite" wrote:
> > 
> > > Hello,
> > > 
> > > How do i get all records based on the name a customer enters in a textbox 
> > > from a form
> > > 
> > > Let say in the query i do %betty% - this will return all records with betty 
> > > partly in it. I am trying to replicate the same based on a value passed from 
> > > the form
> > > 
> > > How do i get the same result using 
> > > 
> > > Like ([Forms]![Main]![txt_exis_pt_name]) - this gives me only one record how 
> > > do i replicate the same result which gives me all records like when i did 
> > > %betty% from query design?
> > > 
> > > Thanks for your help.
0
Utf
3/2/2010 2:37:01 PM
Using Like "*" & ([Forms]![Main]![txt_exis_pt_name]) & "*"  broke the code 
for all the other single relationship record criteria that bring back 1 
record how can I fix it? When I say it broke it what I meant it brings back 
the same record when I test for 

1. case_nbr
2. med_rec and the other single row record 

but the name works correctly. When I take out Using Like "*" & 
([Forms]![Main]![txt_exis_pt_name]) & "*"   everything starts working 
correctly again but i need to be able to pull multiple records when available 
with the name.

Here is what my code look like

SELECT t.case_nbr, t.medrec_nbr, t.fin_nbr, t.patient_name, t.old_case_nbr, 
t.admit_date, t.gender, FROM t
WHERE (((t.case_nbr)=[Forms]![Main]![txt_exis_pt_caseno_search])) OR 
(((t.fin_nbr)=[Forms]![Main].[txt_exis_pt_fin_nbr_search])) OR 
(((t.medrec_nbr)=[Forms]![Main]![txt_exis_pt_medrec_nbr_search])) OR 
(((t.old_case_nbr)=[Forms]![Main]![txt_exis_pt_oldcaseno_search])) OR 
(((t.patient_name) Like "*" & ([Forms]![Main]![txt_exis_pt_name_search]) & 
"*"));


"KARL DEWEY" wrote:

> Try:
> 
> Like "*" & ([Forms]![Main]![txt_exis_pt_name]) & "*"
> 
> -- 
> Build a little, test a little.
> 
> 
> "Maurice" wrote:
> 
> > Try:
> > 
> > Like * & ([Forms]![Main]![txt_exis_pt_name]) & *
> > 
> > That might just do what you are looking for...
> > -- 
> > Maurice Ausum
> > 
> > 
> > "ecwhite" wrote:
> > 
> > > Hello,
> > > 
> > > How do i get all records based on the name a customer enters in a textbox 
> > > from a form
> > > 
> > > Let say in the query i do %betty% - this will return all records with betty 
> > > partly in it. I am trying to replicate the same based on a value passed from 
> > > the form
> > > 
> > > How do i get the same result using 
> > > 
> > > Like ([Forms]![Main]![txt_exis_pt_name]) - this gives me only one record how 
> > > do i replicate the same result which gives me all records like when i did 
> > > %betty% from query design?
> > > 
> > > Thanks for your help.
0
Utf
3/2/2010 6:04:01 PM
>>Using Like "*" & ([Forms]![Main]![txt_exis_pt_name]) & "*"  broke the code 
If you do not enter anything in the form as criteria then it assumes all 
records to be pulled as it is OR'd with any other criteria.   Try putting a 
default of 5 spaces in the text box.

What do you mean by 'single row record'?
 
-- 
Build a little, test a little.


"ecwhite" wrote:

> 
> Using Like "*" & ([Forms]![Main]![txt_exis_pt_name]) & "*"  broke the code 
> for all the other single relationship record criteria that bring back 1 
> record how can I fix it? When I say it broke it what I meant it brings back 
> the same record when I test for 
> 
> 1. case_nbr
> 2. med_rec and the other single row record 
> 
> but the name works correctly. When I take out Using Like "*" & 
> ([Forms]![Main]![txt_exis_pt_name]) & "*"   everything starts working 
> correctly again but i need to be able to pull multiple records when available 
> with the name.
> 
> Here is what my code look like
> 
> SELECT t.case_nbr, t.medrec_nbr, t.fin_nbr, t.patient_name, t.old_case_nbr, 
> t.admit_date, t.gender, FROM t
> WHERE (((t.case_nbr)=[Forms]![Main]![txt_exis_pt_caseno_search])) OR 
> (((t.fin_nbr)=[Forms]![Main].[txt_exis_pt_fin_nbr_search])) OR 
> (((t.medrec_nbr)=[Forms]![Main]![txt_exis_pt_medrec_nbr_search])) OR 
> (((t.old_case_nbr)=[Forms]![Main]![txt_exis_pt_oldcaseno_search])) OR 
> (((t.patient_name) Like "*" & ([Forms]![Main]![txt_exis_pt_name_search]) & 
> "*"));
> 
> 
> "KARL DEWEY" wrote:
> 
> > Try:
> > 
> > Like "*" & ([Forms]![Main]![txt_exis_pt_name]) & "*"
> > 
> > -- 
> > Build a little, test a little.
> > 
> > 
> > "Maurice" wrote:
> > 
> > > Try:
> > > 
> > > Like * & ([Forms]![Main]![txt_exis_pt_name]) & *
> > > 
> > > That might just do what you are looking for...
> > > -- 
> > > Maurice Ausum
> > > 
> > > 
> > > "ecwhite" wrote:
> > > 
> > > > Hello,
> > > > 
> > > > How do i get all records based on the name a customer enters in a textbox 
> > > > from a form
> > > > 
> > > > Let say in the query i do %betty% - this will return all records with betty 
> > > > partly in it. I am trying to replicate the same based on a value passed from 
> > > > the form
> > > > 
> > > > How do i get the same result using 
> > > > 
> > > > Like ([Forms]![Main]![txt_exis_pt_name]) - this gives me only one record how 
> > > > do i replicate the same result which gives me all records like when i did 
> > > > %betty% from query design?
> > > > 
> > > > Thanks for your help.
0
Utf
3/2/2010 7:47:01 PM
What i meant by single row is there is only one case number to a record 
meaning no two records will have the same case number. The same with medrec 
number 1 per patient but multiple record can have the same name like betty 
white, betty jack and so on.

I check for null on each textbox before using it to search so the code to 
search with a txt_exis_pt_name only runs when it is not null. I will try the 
default and let you know if it solves it but once i put the Like "*" & 
([Forms]![Main]![txt_exis_pt_name]) & "*"  code the rest of the searches like 
case number, medrec number except the name pulls incorrect data back 

like when i search for case with case number 7, it pulls back the first 
record in the table and same with the all the other ones except the name. 
When I take it out everything works correctly and i can pull only one betty 
with Like ([Forms]![Main]![txt_exis_pt_name]).

I think it has something to do with the * *

"KARL DEWEY" wrote:

> >>Using Like "*" & ([Forms]![Main]![txt_exis_pt_name]) & "*"  broke the code 
> If you do not enter anything in the form as criteria then it assumes all 
> records to be pulled as it is OR'd with any other criteria.   Try putting a 
> default of 5 spaces in the text box.
> 
> What do you mean by 'single row record'?
>  
> -- 
> Build a little, test a little.
> 
> 
> "ecwhite" wrote:
> 
> > 
> > Using Like "*" & ([Forms]![Main]![txt_exis_pt_name]) & "*"  broke the code 
> > for all the other single relationship record criteria that bring back 1 
> > record how can I fix it? When I say it broke it what I meant it brings back 
> > the same record when I test for 
> > 
> > 1. case_nbr
> > 2. med_rec and the other single row record 
> > 
> > but the name works correctly. When I take out Using Like "*" & 
> > ([Forms]![Main]![txt_exis_pt_name]) & "*"   everything starts working 
> > correctly again but i need to be able to pull multiple records when available 
> > with the name.
> > 
> > Here is what my code look like
> > 
> > SELECT t.case_nbr, t.medrec_nbr, t.fin_nbr, t.patient_name, t.old_case_nbr, 
> > t.admit_date, t.gender, FROM t
> > WHERE (((t.case_nbr)=[Forms]![Main]![txt_exis_pt_caseno_search])) OR 
> > (((t.fin_nbr)=[Forms]![Main].[txt_exis_pt_fin_nbr_search])) OR 
> > (((t.medrec_nbr)=[Forms]![Main]![txt_exis_pt_medrec_nbr_search])) OR 
> > (((t.old_case_nbr)=[Forms]![Main]![txt_exis_pt_oldcaseno_search])) OR 
> > (((t.patient_name) Like "*" & ([Forms]![Main]![txt_exis_pt_name_search]) & 
> > "*"));
> > 
> > 
> > "KARL DEWEY" wrote:
> > 
> > > Try:
> > > 
> > > Like "*" & ([Forms]![Main]![txt_exis_pt_name]) & "*"
> > > 
> > > -- 
> > > Build a little, test a little.
> > > 
> > > 
> > > "Maurice" wrote:
> > > 
> > > > Try:
> > > > 
> > > > Like * & ([Forms]![Main]![txt_exis_pt_name]) & *
> > > > 
> > > > That might just do what you are looking for...
> > > > -- 
> > > > Maurice Ausum
> > > > 
> > > > 
> > > > "ecwhite" wrote:
> > > > 
> > > > > Hello,
> > > > > 
> > > > > How do i get all records based on the name a customer enters in a textbox 
> > > > > from a form
> > > > > 
> > > > > Let say in the query i do %betty% - this will return all records with betty 
> > > > > partly in it. I am trying to replicate the same based on a value passed from 
> > > > > the form
> > > > > 
> > > > > How do i get the same result using 
> > > > > 
> > > > > Like ([Forms]![Main]![txt_exis_pt_name]) - this gives me only one record how 
> > > > > do i replicate the same result which gives me all records like when i did 
> > > > > %betty% from query design?
> > > > > 
> > > > > Thanks for your help.
0
Utf
3/3/2010 2:34:01 AM
Reply:

Similar Artilces:

Passing dates to Sql Server 2005 stored procedure
Hi, I am trying to execute a SQL Stored procedure from Excel using VBA. Apart from 2 string inputs, this proc also accepts two datetime imputs. When these dates are passed via VBA, they are in the format "dd/mm/yyyy. However, the SQL Server does not seem to accept dates passed in this format. Applying different date formats in VBA does not help because eventually when the dates shoot from Excel to SQL Server, they are in the format "dd/mm/yyyy". I don't want to change the regional settings of the machine, since it will only be a temporary solution. This is...

turn OFO off for one user so external users do not get message
Hi all I have a slight problem hope you can help. We use Exchange 2003 and I have a Distribution list which public people (customers outside the network) email to and if one of the people in the distribution list has there out of office turned on the customer get the OFO message. Now I have been doing my research and it seems that you can only turn OFO messages for everyone and not for users or groups. Do you know a way in which I can tell the exchange server not to send out OFO messages for a group. If you need more information then please do ask. Thank you in advance. F Hi, Perha...

APL code in textbox
Hi I was working with the APL programming language. It has a lot of wierd symbols that excel does not recognize. However I needed to share breif snipets of code, so I wanted to put the apl in texboxes. Is there anyway to put the code in excel. Thank a lot. Weird? Hardly! APL was one of the first languages I had to learn 20+ years ago (along with Fortran, Pascal, COBOL, several flavors of Basic, and 3 different assemblers). It's by far the most efficient and elegant language I've ever used. Be that as it may, one suggestion would be to copy the APL code as a picture (the...

Is it possible to print 2 records per page in a mail merge
Hello all, I have a word template with some document properties, how do i instruct access only to print 2 records per page. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-reports/200711/1 ...

Need help with getting MFC to work under VS6 IDE on AMD64, please
I purchased the 2003 DDK. It's all setup and I can compile 64-bits under the Visual Studio 6 IDE as long as it's not MFC, just Win64. Does anyone know of a way to get MFC to compile under the IDE, or am I forced to use the shell for compiling with them? Thanks for any help "goblin" <goblin@spooks.net> wrote in message news:Xns94DBA840747DBgoblinspooksnet@216.196.105.130... > I purchased the 2003 DDK. It's all setup and I can compile 64-bits under > the Visual Studio 6 IDE as long as it's not MFC, just Win64. Does anyone > know of a way to get MFC to c...

How to find the number of rows in a named range?
Dear all, I have a named range called "rng" in a worksheet. How can I write a function to count the number of rows in "rng"? It seems that "rng".rows.count does not work. Best Regards, Andy msgbox worksheets("sheet99").range("rng").rows.count if that range is a single area. Andy Chan wrote: > > Dear all, > > I have a named range called "rng" in a worksheet. How can I write a > function to count the number of rows in "rng"? It seems that > "rng".rows.count does not work. > &...

Currently locked record due to too many users (>4) on Access?
We get a Could not update; currently locked message when we try to add a new record, though no users are accessing the record. Seems to add OK to our main table - problem seems to be adding records to a linked table. Only difference I can tell from previous use is that there are 5 users on the database and I wonder if that has an effect on locking...using Access 2003 on XP SP2 on a Win Server 2003 server network "MnTAPBob" You said all tables are in one database file, so there is only one database. The tables then are not linked, but have relationships between each othe...

Get Save As Filename Dialogue Box using Applescript
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Does anyone here know the syntax for changing the starting location for the browser window in the dialogue box that pops up when you call the &quot;Get Filename Save As&quot; command in Applescript? <br><br>It would appear that it defaults to the folder that you last saved a spreadsheet in. I would like to pass a path into the command as a parameter using applescript but have not found an answer in any of the documentation. <br><br>Thanks! > This message is in MIME format. S...

#NAME? in field
Access 2003. I have a field in a form that should automatically show today's date when I add a new record. It is set up as Date$() in the table the form is drawing from. When I add a new record, I get #NAME? in the field until I enter data into the form. Then it changes the date field to today's date. I compared the setup to another database that is set up exactly the same that is working correctly (it shows today's date when I add a new record). What is even more bizarre is that I do not have this issue on my computer, and the rest of the staff has the issue. We a...

Get Range to Lookup from a Lookup table
Does someone have an example of using one lookup table to provide the range for another lookup table? PS Is this not easier than my providing all the details of my situation, most of which are irrelevant to the question? One way: Given: Tables named table1, table2, etc. Cell named tableswitch. table1 is: J K 1 A table2 2 B table3 3 C table4 4 .... =VLOOKUP(A1, INDIRECT(VLOOKUP(tableswitch, table1, 2)), 2, FALSE) > PS Is this not easier than my providing all the details of my > situation, most of w...

have xp can't get free trial office to download
page comes up saying no pid me tooo!!! can someone pllleeasee help cause i really need this trial "kscableguy" wrote: > page comes up saying no pid Which page? I can get through as far as Shipping Information page. Already have 2003 so went no further than that page. Gord Dibben Excel MVP On Tue, 2 Nov 2004 10:14:11 -0800, "kscableguy" <kscableguy@discussions.microsoft.com> wrote: >page comes up saying no pid When you hit the down load now tab it will act as if downloading the page will come up saying its done put will just say no pid on the page &qu...

Named ranges- how do I "combine" several into a larger named range?
I started with a large range (1x300 cells) that represented 3 sections of data that is graphed- but I've since found out that my (internal) customer sometimes needs to suppress one or two of the sections, so the graph doesn't show large empty sections when there is no data. I'm having trouble with the named range(s); Instead of NamedRange1 = MySheet!A1:A30 I now have NamedRange1a = MySheet!A1:A100 NamedRange1b = MySheet!A101:A200 NamedRange1c = MySheet!A201:A300 Now I need a statement that puts all three of these together into one range...

Odd exchange 2003 Organisation name
To all who can help. After sucessful installation of Exhnage 2003 on windows 2003 server. All went well, however, the Organisation name i gave appears as a string of charachters and letters, nothing remotely like what i would expect! also looking at services none of the MS ex services are there. Like IS etc services. Is this a sign of very failed installtion or i am loosing my mind?! If anyone has seen this quirky status and can shed light on it, will be very greatful indeed. My search on web and ms site reveal zer0 info. Much thanks to all Neberd The Exchange org name should be e...

Filter out records
I have thousands of records in excel where some records are duplicated with one field. How can I retrieve the duplicate field record in another worksheet?? Million thanks. I don't quite understand your question, but Chip Pearson has a bunch of techniques for working with duplicates at: http://www.cpearson.com/excel/duplicat.htm Grey wrote: > > I have thousands of records in excel where some records are duplicated with > one field. How can I retrieve the duplicate field record in another > worksheet?? > > Million thanks. -- Dave Peterson ec35720@msn.com ...

Access 2003 number of records
Hi all, I'm wondering if there's a limit/recommendation in the number of records that it can be inserted in an Access 2003 table before the performance is affected considerably. I know that in Access 200 the limit was around 100,000 records but I'd like to know it for Access 2003. thanks in advance, Marc Soleda Last I new the limit was the file size of the database itself. AFAIK this has not changed from 2GB so if you have tiny records you can have a huge number, with fat records - obviously nowhere near as may. The performance of the database in Access is much the sa...

named range in macro call 04-05-10
Is there a way to pass a named range (or even an unnamed block of cells) into a macro? I have written a macro that I want to use for several different ranges of data. I have created multiple buttons that each call the macro, but I cannot seem to find the right syntax to pass in the range of data that I want the macro to use for each button. All I can do is to pass in the row and column numbers that define the upper left and lower right corners of the data range, as follows: Private Sub CommandButton1_Click() ' Call Macro1(UpperLeftRow, UpperLeftCol, LowerRightRow, Lo...

Retrieve only missing records from two spreadsheet
hi all, first spreadsheet contain all the data backup from a database, secon spreadsheet contain a collection of data that was backup later, howeve due to broken pointer, some records are missing. so is there any add-ins that can compare two spreadsheets and list ou the missing records? i've tried the cpearson.com's compare.xla, but i showed some of the duplicate records, which still is giving a long lis of result... regards, ra -- ray5_8 ----------------------------------------------------------------------- ray5_83's Profile: http://www.excelforum.com/member.php?action=getinf...

How to get CC: properties of messages in AppleScript
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel Email Client: pop I already know how to get a &quot;message&quot; given its ID and to extract various propertie from it, but I still have to find a way to get the CC: and BCC: fields from that message. <br><br>The only way I can think of for now is getting the headers and extracting the values from the text... <br><br>I could find in the Entourage AppleScript Dictionary anything relating to CC: and BCC: properties. <br><br>What am I missing? ...

some inbound mails get stopped at Exchange IMS server
Hello, I hope someone can help me understand this dilema. My environment: . A relay (running Sendmail on Linux) sits on DMZ, called RELAY1 . A MS exchange 5.5 IMS server, called SERVER1 . A MS exchange 5.5 mailbox server called SERVER2 Automatically generated emails from a website are sent to our users after they filled out information at the website. The problem is that my users don't received the mail. I investigated and saw that although the message entered our relay RELAY1, then SERVER1, it didn't get delivered to the mailbox server SERVER2. When I did a "Mess...

Unique Values, not Unique Records
My data looks like the following: ID Type Amt 1234 A $10.00 1234 B $10.00 1234 C $10.00 I would like to find a way to get only one of the items on to a new spreadsheet, because they are duplicate records in my eyes. The Advanced filter does not work because each record is unique. Can anyone help? in the dialog for the advanced filter, make sure only the ID column is referenced "steph44haf" wrote: > My data looks like the following: > > ID Type Amt > 1234 ...

Delete from 1 if there is no record in many
I have 2 tables. Department (one) linked to Phone Book (many). I want to delete record in Department table where there is no record in Phone Book table. DELETE Department.*, [Phone Book].Department FROM Department LEFT JOIN [Phone Book] ON Department.Department = [Phone Book].Department WHERE ((([Phone Book].Department) Is Null)); Error message is:" could delete from specified tables" How to fix it? Thanks. Try something like this: DELETE FROM Department WHERE NOT EXISTS (SELECT [Phone Book].Department FROM [Phone Book] WHERE [Phone Book].Department = Department.Depa...

CRecordSet
I want to add a record to an existing record set. I don't want to requery the recordset because I'm using bookmarks and they would be invalid. How do you get the bookmark for the new record? I've tried: rs.AddNew(); rs.Update(); rs.MoveLast(); rs.GetBookmark(); and rs.MoveLast() throws and exception "Scrolling before beginning or after end...." Any ideas? Thanks, Neil ...

locking a record to edit it.
I have a form where a user selects a ref number and then another form opens with this record details showing. The problem is the record does not lock until the user begins to type an update in one of the boxes. In the meantime another user can open the same record and begin to type an update. Only the second users changes are kept and the first user is told changes not saved. How can I stop this and lock the record immediately on opening? Do you have "Open Databases Using Record Level Locking" checked? And "Default Record Locking/Edited Records" check? You'll find thes...

The parameter '{0}' cannot be an empty string. Parameter name: cookie.Domain
At random intervals, we experience an "The parameter '{0}' cannot be an empty string. Parameter name: cookie.Domain" when attempting to view our reports. We are running SQL Server 2008 Reporting Services in integrated mode (with MOSS 2007). Our reports are deployed to a "Report Library" on the same site as the pages that display our Reports. Reports are displayed using the Report Viewer Web Part (the one that comes with SSRS for MOSS add in). Each page has at most 4 reports being displayed at once. Our default zone is FBA (via Windows LiveID). A...

CRM 4.0 CRMAsyncService and Physical Delete of Records from Entity Base Tables
Hello, Can someone point me to a blog, document, etc. that explains how the physical delete of entity records is accomplished through this service? I assume this .exe has replaced crmdeletionservice.exe that existed in 3.0? I'm looking for information on how often it runs and whether it can be configured, etc. Thanks, Todd Langdon Answer: there is a SQL Agent job named p_HardDeletes that does this work. On Feb 25, 2:06=A0pm, Todd Langdon <todd.lang...@gmail.com> wrote: > Hello, > > Can someone point me to a blog, document, etc. that explains how the > physical delete...