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.

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.

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.

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.

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.

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

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.