Getting all records with same name passed from a textbox

[ecwhite]

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.

 

[Maurice]

Try:

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

That might just do what you are looking for...

 

[KARL DEWEY]

Try:

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

--
Build a little, test a little.

Try:

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

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

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.

 

[ecwhite]

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.

Try:

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

--
Build a little, test a little.

Try:

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

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

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.

 

[ecwhite]

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]) &
"*"));

Try:

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

--
Build a little, test a little.

Try:

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

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

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.

 

[KARL DEWEY]

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.

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]) &
"*"));

Try:

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

--
Build a little, test a little.

Try:

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

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


:

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.

 

[ecwhite]

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

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.

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]) &
"*"));

Try:

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

--
Build a little, test a little.


:

Try:

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

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


:

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.