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

  • Thread starter Thread starter ecwhite
  • Start date Start date
E

ecwhite

Please help everything works well till i put in the Like "*" &
([Forms]![Main]![txt_exis_pt_name]) & "*"

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

What do you want the txt_exis_pt_name_search to do for the query? What you
have done with the OR is to include in the query any records that match this
criteria, as well as any records that the query would have returned without
this criteria.

If you wanted to reduce the records the query was returning by limiting
those records to ones with the txt_exis_pt_name_search in the patient_name
field, then you need to AND this criteria. Be careful of the parentheses -
like this:

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]))) AND
(((t.patient_name) Like "*" & ([Forms]![Main]![txt_exis_pt_name_search]) &
"*"));

If this is not what you are asking for, then please post some results of the
query before and after adding the criteria, and why the records don't meet
your needs.
 
txt_exis_pt_name_search - with name search, if i type betty, there could be
more than one betty in the table so i want it to bring back all records with
betty in it.

I want them to search with one criteria at a time that is why i am using the
OR instead of an AND.

The rest of the search criteria is one to one meaning there could only be
one record with the same case number but many people with the same name.

I also tried the suggestion Like "*" & [Forms]![Main]![txt_exis_pt_name] & "*"
but it gives error. It makes the name one try to run first.

Daryl S said:
Ecwhite -

What do you want the txt_exis_pt_name_search to do for the query? What you
have done with the OR is to include in the query any records that match this
criteria, as well as any records that the query would have returned without
this criteria.

If you wanted to reduce the records the query was returning by limiting
those records to ones with the txt_exis_pt_name_search in the patient_name
field, then you need to AND this criteria. Be careful of the parentheses -
like this:

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]))) AND
(((t.patient_name) Like "*" & ([Forms]![Main]![txt_exis_pt_name_search]) &
"*"));

If this is not what you are asking for, then please post some results of the
query before and after adding the criteria, and why the records don't meet
your needs.

--
Daryl S


ecwhite said:
Please help everything works well till i put in the Like "*" &
([Forms]![Main]![txt_exis_pt_name]) & "*"

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]) &
"*"));
Click to expand...
Click to expand...
 
Ecwhite -

OK, so the OR is good. Your Like statement will return all records if the
txt_exis_pt_name_search is blank. To prevent this, you need to test for null
and replace that with something that would never be in the name field. I
have done that with the Z1Z2Z23Z4 in the code below. I also added the text
delimeter of a single-quote to the asterisks. Try this:

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 "'*" &
(nz([Forms]![Main]![txt_exis_pt_name_search],"Z1Z2Z3Z4")) &
"*'"));


--
Daryl S


ecwhite said:
txt_exis_pt_name_search - with name search, if i type betty, there could be
more than one betty in the table so i want it to bring back all records with
betty in it.

I want them to search with one criteria at a time that is why i am using the
OR instead of an AND.

The rest of the search criteria is one to one meaning there could only be
one record with the same case number but many people with the same name.

I also tried the suggestion Like "*" & [Forms]![Main]![txt_exis_pt_name] & "*"
but it gives error. It makes the name one try to run first.

Daryl S said:
Ecwhite -

What do you want the txt_exis_pt_name_search to do for the query? What you
have done with the OR is to include in the query any records that match this
criteria, as well as any records that the query would have returned without
this criteria.

If you wanted to reduce the records the query was returning by limiting
those records to ones with the txt_exis_pt_name_search in the patient_name
field, then you need to AND this criteria. Be careful of the parentheses -
like this:

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]))) AND
(((t.patient_name) Like "*" & ([Forms]![Main]![txt_exis_pt_name_search]) &
"*"));

If this is not what you are asking for, then please post some results of the
query before and after adding the criteria, and why the records don't meet
your needs.

--
Daryl S


ecwhite said:
Please help everything works well till i put in the Like "*" &
([Forms]![Main]![txt_exis_pt_name]) & "*"

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]) &
"*"));
Click to expand...
Click to expand...
Click to expand...
 
I tried it gives me an error '2001' you can canceled the previous operation
when i enter the patient number as the criteria on this line which works when
i don't have the name code If DCount("[t.fin_nbr]",
"the_name_of_the_query") = 0 Then

Here is what the code behind the click event of the serch button look like.
Please note i have one If Statement block for each search criteria and it
works perfectly till i put the name criteria in the query design

If Not IsNull(Me.txt_exis_pt_fin_nbr) Then

If DCount("[t.fin_nbr]", "the_name_of_the_query") = 0 Then

Dim stmacro_No_Existing_Case_RecordFound As String

stmacro_No_Existing_Case_RecordFound =
"Macro_No_RecordFound"
DoCmd.RunMacro stmacro_No_Existing_Case_RecordFound


Else
stDocexistingName =
"open_form_and_populate_fields_from_query_result"
DoCmd.OpenForm stDocexistingName

End If

End If

I reduced the where clause to two fields for now to resolve this first

WHERE (((t.fin_nbr)=Forms!Main.txt_exis_pt_fin_nbr_search)) Or
(((t.patient_name) Like "'*" &
(nz(Forms!Main!txt_exis_pt_name_search,"########")) & "*'"));
) Like "'*" & (nz(Forms!Main!txt_exis_pt_name_search,"########")) & "*'"));



Daryl S said:
Ecwhite -

OK, so the OR is good. Your Like statement will return all records if the
txt_exis_pt_name_search is blank. To prevent this, you need to test for null
and replace that with something that would never be in the name field. I
have done that with the Z1Z2Z23Z4 in the code below. I also added the text
delimeter of a single-quote to the asterisks. Try this:

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 "'*" &
(nz([Forms]![Main]![txt_exis_pt_name_search],"Z1Z2Z3Z4")) &
"*'"));


--
Daryl S


ecwhite said:
txt_exis_pt_name_search - with name search, if i type betty, there could be
more than one betty in the table so i want it to bring back all records with
betty in it.

I want them to search with one criteria at a time that is why i am using the
OR instead of an AND.

The rest of the search criteria is one to one meaning there could only be
one record with the same case number but many people with the same name.

I also tried the suggestion Like "*" & [Forms]![Main]![txt_exis_pt_name] & "*"
but it gives error. It makes the name one try to run first.

Daryl S said:
Ecwhite -

What do you want the txt_exis_pt_name_search to do for the query? What you
have done with the OR is to include in the query any records that match this
criteria, as well as any records that the query would have returned without
this criteria.

If you wanted to reduce the records the query was returning by limiting
those records to ones with the txt_exis_pt_name_search in the patient_name
field, then you need to AND this criteria. Be careful of the parentheses -
like this:

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]))) AND
(((t.patient_name) Like "*" & ([Forms]![Main]![txt_exis_pt_name_search]) &
"*"));

If this is not what you are asking for, then please post some results of the
query before and after adding the criteria, and why the records don't meet
your needs.

--
Daryl S


:

Please help everything works well till i put in the Like "*" &
([Forms]![Main]![txt_exis_pt_name]) & "*"

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]) &
"*"));
Click to expand...
Click to expand...
Click to expand...
 
Ecwhite -

You are talking about two things - the error in the code and the query in
design mode. Let's get the query working first.

If you are working in query design mode with the form open and some criteria
in the cells, does the query work? Can you change the criteria on the form
and re-run the query from design mode and get what you want? Does it work
with each criteria separately (which is how I understand it will be run)? If
it works with all criteria except the name, then let's try to figure out why.
Do you get an error message? Do you get data, but not what you expect?
Could there be extra spaces or something in the txt_exis_pt_name_search
control? Is the query running, but you get no data back when you expect it?
If so, what are you entering into the text field, and what t.patient_name
value are there that you would expect to see?

Let us know...

--
Daryl S


ecwhite said:
I tried it gives me an error '2001' you can canceled the previous operation
when i enter the patient number as the criteria on this line which works when
i don't have the name code If DCount("[t.fin_nbr]",
"the_name_of_the_query") = 0 Then

Here is what the code behind the click event of the serch button look like.
Please note i have one If Statement block for each search criteria and it
works perfectly till i put the name criteria in the query design

If Not IsNull(Me.txt_exis_pt_fin_nbr) Then

If DCount("[t.fin_nbr]", "the_name_of_the_query") = 0 Then

Dim stmacro_No_Existing_Case_RecordFound As String

stmacro_No_Existing_Case_RecordFound =
"Macro_No_RecordFound"
DoCmd.RunMacro stmacro_No_Existing_Case_RecordFound


Else
stDocexistingName =
"open_form_and_populate_fields_from_query_result"
DoCmd.OpenForm stDocexistingName

End If

End If

I reduced the where clause to two fields for now to resolve this first

WHERE (((t.fin_nbr)=Forms!Main.txt_exis_pt_fin_nbr_search)) Or
(((t.patient_name) Like "'*" &
(nz(Forms!Main!txt_exis_pt_name_search,"########")) & "*'"));
) Like "'*" & (nz(Forms!Main!txt_exis_pt_name_search,"########")) & "*'"));



Daryl S said:
Ecwhite -

OK, so the OR is good. Your Like statement will return all records if the
txt_exis_pt_name_search is blank. To prevent this, you need to test for null
and replace that with something that would never be in the name field. I
have done that with the Z1Z2Z23Z4 in the code below. I also added the text
delimeter of a single-quote to the asterisks. Try this:

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 "'*" &
(nz([Forms]![Main]![txt_exis_pt_name_search],"Z1Z2Z3Z4")) &
"*'"));


--
Daryl S


ecwhite said:
txt_exis_pt_name_search - with name search, if i type betty, there could be
more than one betty in the table so i want it to bring back all records with
betty in it.

I want them to search with one criteria at a time that is why i am using the
OR instead of an AND.

The rest of the search criteria is one to one meaning there could only be
one record with the same case number but many people with the same name.

I also tried the suggestion Like "*" & [Forms]![Main]![txt_exis_pt_name] & "*"
but it gives error. It makes the name one try to run first.

:

Ecwhite -

What do you want the txt_exis_pt_name_search to do for the query? What you
have done with the OR is to include in the query any records that match this
criteria, as well as any records that the query would have returned without
this criteria.

If you wanted to reduce the records the query was returning by limiting
those records to ones with the txt_exis_pt_name_search in the patient_name
field, then you need to AND this criteria. Be careful of the parentheses -
like this:

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]))) AND
(((t.patient_name) Like "*" & ([Forms]![Main]![txt_exis_pt_name_search]) &
"*"));

If this is not what you are asking for, then please post some results of the
query before and after adding the criteria, and why the records don't meet
your needs.

--
Daryl S


:

Please help everything works well till i put in the Like "*" &
([Forms]![Main]![txt_exis_pt_name]) & "*"

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]) &
"*"));
Click to expand...
Click to expand...
Click to expand...
 
Ecwhite -

I just saw in another posting of yours that had % for a wildcard. Do you
have a SQL Server back-end? If so, change the asterisk (wildcard in Access)
to the % (wildcard for SQL Server)...

--
Daryl S


ecwhite said:
I tried it gives me an error '2001' you can canceled the previous operation
when i enter the patient number as the criteria on this line which works when
i don't have the name code If DCount("[t.fin_nbr]",
"the_name_of_the_query") = 0 Then

Here is what the code behind the click event of the serch button look like.
Please note i have one If Statement block for each search criteria and it
works perfectly till i put the name criteria in the query design

If Not IsNull(Me.txt_exis_pt_fin_nbr) Then

If DCount("[t.fin_nbr]", "the_name_of_the_query") = 0 Then

Dim stmacro_No_Existing_Case_RecordFound As String

stmacro_No_Existing_Case_RecordFound =
"Macro_No_RecordFound"
DoCmd.RunMacro stmacro_No_Existing_Case_RecordFound


Else
stDocexistingName =
"open_form_and_populate_fields_from_query_result"
DoCmd.OpenForm stDocexistingName

End If

End If

I reduced the where clause to two fields for now to resolve this first

WHERE (((t.fin_nbr)=Forms!Main.txt_exis_pt_fin_nbr_search)) Or
(((t.patient_name) Like "'*" &
(nz(Forms!Main!txt_exis_pt_name_search,"########")) & "*'"));
) Like "'*" & (nz(Forms!Main!txt_exis_pt_name_search,"########")) & "*'"));



Daryl S said:
Ecwhite -

OK, so the OR is good. Your Like statement will return all records if the
txt_exis_pt_name_search is blank. To prevent this, you need to test for null
and replace that with something that would never be in the name field. I
have done that with the Z1Z2Z23Z4 in the code below. I also added the text
delimeter of a single-quote to the asterisks. Try this:

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 "'*" &
(nz([Forms]![Main]![txt_exis_pt_name_search],"Z1Z2Z3Z4")) &
"*'"));


--
Daryl S


ecwhite said:
txt_exis_pt_name_search - with name search, if i type betty, there could be
more than one betty in the table so i want it to bring back all records with
betty in it.

I want them to search with one criteria at a time that is why i am using the
OR instead of an AND.

The rest of the search criteria is one to one meaning there could only be
one record with the same case number but many people with the same name.

I also tried the suggestion Like "*" & [Forms]![Main]![txt_exis_pt_name] & "*"
but it gives error. It makes the name one try to run first.

:

Ecwhite -

What do you want the txt_exis_pt_name_search to do for the query? What you
have done with the OR is to include in the query any records that match this
criteria, as well as any records that the query would have returned without
this criteria.

If you wanted to reduce the records the query was returning by limiting
those records to ones with the txt_exis_pt_name_search in the patient_name
field, then you need to AND this criteria. Be careful of the parentheses -
like this:

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]))) AND
(((t.patient_name) Like "*" & ([Forms]![Main]![txt_exis_pt_name_search]) &
"*"));

If this is not what you are asking for, then please post some results of the
query before and after adding the criteria, and why the records don't meet
your needs.

--
Daryl S


:

Please help everything works well till i put in the Like "*" &
([Forms]![Main]![txt_exis_pt_name]) & "*"

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]) &
"*"));
Click to expand...
Click to expand...
Click to expand...
 
Yes I have a MYSQL backend - sorry i should have said this. Thanks for your
deligence in helping me with this. I will be glad if i can get it to work.

Yes all other criteria searches work correctly till i add the name search
with a wild character and it starts pulling all the record in the table even
when i search with let say the case number

There is no error with the code behind except when i use Using - Like "'*" &
(nz([Forms]![Main]![txt_exis_pt_name_search],"Z1Z2Z3Z4"))

This code if fixed is the result that I want .Using this Like "*" &
([Forms]![Main_Menu]![txt_exis_pt_name_search]) & "*" - Pulls the exact name
correctly like if i search for angel curt. If i search for betty it also
brings back all the records with betty in it like betty white, betty jack and
so on which is exactly what i want for the name search only. The problems is
when I search for a case with case no 12, it brings back all the records in
the database which is different from before i put in the "*" "*"

Using this Like "%" & [Forms]![Main]![txt_exis_pt_name] & "%" - Same result
with Like "*" & ([Forms]![Main_Menu]![txt_exis_pt_name_search]) & "*"

Using - Like "'*" &
(nz([Forms]![Main]![txt_exis_pt_name_search],"Z1Z2Z3Z4")) & "*'" - This gives
me an error '2001' you can canceled the previous operation
when i enter the patient number as the criteria on this line which works
when i don't have the name code If DCount("[t.fin_nbr]",
"the_name_of_the_query") = 0 Then





Daryl S said:
Ecwhite -

I just saw in another posting of yours that had % for a wildcard. Do you
have a SQL Server back-end? If so, change the asterisk (wildcard in Access)
to the % (wildcard for SQL Server)...

--
Daryl S


ecwhite said:
I tried it gives me an error '2001' you can canceled the previous operation
when i enter the patient number as the criteria on this line which works when
i don't have the name code If DCount("[t.fin_nbr]",
"the_name_of_the_query") = 0 Then

Here is what the code behind the click event of the serch button look like.
Please note i have one If Statement block for each search criteria and it
works perfectly till i put the name criteria in the query design

If Not IsNull(Me.txt_exis_pt_fin_nbr) Then

If DCount("[t.fin_nbr]", "the_name_of_the_query") = 0 Then

Dim stmacro_No_Existing_Case_RecordFound As String

stmacro_No_Existing_Case_RecordFound =
"Macro_No_RecordFound"
DoCmd.RunMacro stmacro_No_Existing_Case_RecordFound


Else
stDocexistingName =
"open_form_and_populate_fields_from_query_result"
DoCmd.OpenForm stDocexistingName

End If

End If

I reduced the where clause to two fields for now to resolve this first

WHERE (((t.fin_nbr)=Forms!Main.txt_exis_pt_fin_nbr_search)) Or
(((t.patient_name) Like "'*" &
(nz(Forms!Main!txt_exis_pt_name_search,"########")) & "*'"));
) Like "'*" & (nz(Forms!Main!txt_exis_pt_name_search,"########")) & "*'"));



Daryl S said:
Ecwhite -

OK, so the OR is good. Your Like statement will return all records if the
txt_exis_pt_name_search is blank. To prevent this, you need to test for null
and replace that with something that would never be in the name field. I
have done that with the Z1Z2Z23Z4 in the code below. I also added the text
delimeter of a single-quote to the asterisks. Try this:

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 "'*" &
(nz([Forms]![Main]![txt_exis_pt_name_search],"Z1Z2Z3Z4")) &
"*'"));


--
Daryl S


:

txt_exis_pt_name_search - with name search, if i type betty, there could be
more than one betty in the table so i want it to bring back all records with
betty in it.

I want them to search with one criteria at a time that is why i am using the
OR instead of an AND.

The rest of the search criteria is one to one meaning there could only be
one record with the same case number but many people with the same name.

I also tried the suggestion Like "*" & [Forms]![Main]![txt_exis_pt_name] & "*"
but it gives error. It makes the name one try to run first.

:

Ecwhite -

What do you want the txt_exis_pt_name_search to do for the query? What you
have done with the OR is to include in the query any records that match this
criteria, as well as any records that the query would have returned without
this criteria.

If you wanted to reduce the records the query was returning by limiting
those records to ones with the txt_exis_pt_name_search in the patient_name
field, then you need to AND this criteria. Be careful of the parentheses -
like this:

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]))) AND
(((t.patient_name) Like "*" & ([Forms]![Main]![txt_exis_pt_name_search]) &
"*"));

If this is not what you are asking for, then please post some results of the
query before and after adding the criteria, and why the records don't meet
your needs.

--
Daryl S


:

Please help everything works well till i put in the Like "*" &
([Forms]![Main]![txt_exis_pt_name]) & "*"

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]) &
"*"));
Click to expand...
Click to expand...
Click to expand...
 
Ecwhite -

I wish I had more experience with SQL Server...

Let's see if we can make sure the nz is evaluating correctly. Take it out
of the criteria and instead put it in as a new field, like this (slight
alteration on the location of the first double-quote on purpose).

Expr1: = "Like '*" &
(nz([Forms]![Main]![txt_exis_pt_name_search],"Z1Z2Z3Z4")) & "*'"

Run the query two times - once with something in the txt_exis_pt_name_search
control, and once with it blank. Post the results of this field for each
query run.

--
Daryl S


ecwhite said:
Yes I have a MYSQL backend - sorry i should have said this. Thanks for your
deligence in helping me with this. I will be glad if i can get it to work.

Yes all other criteria searches work correctly till i add the name search
with a wild character and it starts pulling all the record in the table even
when i search with let say the case number

There is no error with the code behind except when i use Using - Like "'*" &
(nz([Forms]![Main]![txt_exis_pt_name_search],"Z1Z2Z3Z4"))

This code if fixed is the result that I want .Using this Like "*" &
([Forms]![Main_Menu]![txt_exis_pt_name_search]) & "*" - Pulls the exact name
correctly like if i search for angel curt. If i search for betty it also
brings back all the records with betty in it like betty white, betty jack and
so on which is exactly what i want for the name search only. The problems is
when I search for a case with case no 12, it brings back all the records in
the database which is different from before i put in the "*" "*"

Using this Like "%" & [Forms]![Main]![txt_exis_pt_name] & "%" - Same result
with Like "*" & ([Forms]![Main_Menu]![txt_exis_pt_name_search]) & "*"

Using - Like "'*" &
(nz([Forms]![Main]![txt_exis_pt_name_search],"Z1Z2Z3Z4")) & "*'" - This gives
me an error '2001' you can canceled the previous operation
when i enter the patient number as the criteria on this line which works
when i don't have the name code If DCount("[t.fin_nbr]",
"the_name_of_the_query") = 0 Then





Daryl S said:
Ecwhite -

I just saw in another posting of yours that had % for a wildcard. Do you
have a SQL Server back-end? If so, change the asterisk (wildcard in Access)
to the % (wildcard for SQL Server)...

--
Daryl S


ecwhite said:
I tried it gives me an error '2001' you can canceled the previous operation
when i enter the patient number as the criteria on this line which works when
i don't have the name code If DCount("[t.fin_nbr]",
"the_name_of_the_query") = 0 Then

Here is what the code behind the click event of the serch button look like.
Please note i have one If Statement block for each search criteria and it
works perfectly till i put the name criteria in the query design

If Not IsNull(Me.txt_exis_pt_fin_nbr) Then

If DCount("[t.fin_nbr]", "the_name_of_the_query") = 0 Then

Dim stmacro_No_Existing_Case_RecordFound As String

stmacro_No_Existing_Case_RecordFound =
"Macro_No_RecordFound"
DoCmd.RunMacro stmacro_No_Existing_Case_RecordFound


Else
stDocexistingName =
"open_form_and_populate_fields_from_query_result"
DoCmd.OpenForm stDocexistingName

End If

End If

I reduced the where clause to two fields for now to resolve this first

WHERE (((t.fin_nbr)=Forms!Main.txt_exis_pt_fin_nbr_search)) Or
(((t.patient_name) Like "'*" &
(nz(Forms!Main!txt_exis_pt_name_search,"########")) & "*'"));
) Like "'*" & (nz(Forms!Main!txt_exis_pt_name_search,"########")) & "*'"));



:

Ecwhite -

OK, so the OR is good. Your Like statement will return all records if the
txt_exis_pt_name_search is blank. To prevent this, you need to test for null
and replace that with something that would never be in the name field. I
have done that with the Z1Z2Z23Z4 in the code below. I also added the text
delimeter of a single-quote to the asterisks. Try this:

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 "'*" &
(nz([Forms]![Main]![txt_exis_pt_name_search],"Z1Z2Z3Z4")) &
"*'"));


--
Daryl S


:

txt_exis_pt_name_search - with name search, if i type betty, there could be
more than one betty in the table so i want it to bring back all records with
betty in it.

I want them to search with one criteria at a time that is why i am using the
OR instead of an AND.

The rest of the search criteria is one to one meaning there could only be
one record with the same case number but many people with the same name.

I also tried the suggestion Like "*" & [Forms]![Main]![txt_exis_pt_name] & "*"
but it gives error. It makes the name one try to run first.

:

Ecwhite -

What do you want the txt_exis_pt_name_search to do for the query? What you
have done with the OR is to include in the query any records that match this
criteria, as well as any records that the query would have returned without
this criteria.

If you wanted to reduce the records the query was returning by limiting
those records to ones with the txt_exis_pt_name_search in the patient_name
field, then you need to AND this criteria. Be careful of the parentheses -
like this:

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]))) AND
(((t.patient_name) Like "*" & ([Forms]![Main]![txt_exis_pt_name_search]) &
"*"));

If this is not what you are asking for, then please post some results of the
query before and after adding the criteria, and why the records don't meet
your needs.

--
Daryl S


:

Please help everything works well till i put in the Like "*" &
([Forms]![Main]![txt_exis_pt_name]) & "*"

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]) &
"*"));
Click to expand...
Click to expand...
Click to expand...
 
Thanks Daryl for working with me on this. I has been resolved. I had to wrap
the rest of the criteria in the NZ function too eg

Nz([Forms]![Main![txt_exis_pt_caseno_search],"00000")

I did it for each one and the whole thing work correctly. Thanks again for
working with me on this.

Daryl S said:
Ecwhite -

I wish I had more experience with SQL Server...

Let's see if we can make sure the nz is evaluating correctly. Take it out
of the criteria and instead put it in as a new field, like this (slight
alteration on the location of the first double-quote on purpose).

Expr1: = "Like '*" &
(nz([Forms]![Main]![txt_exis_pt_name_search],"Z1Z2Z3Z4")) & "*'"

Run the query two times - once with something in the txt_exis_pt_name_search
control, and once with it blank. Post the results of this field for each
query run.

--
Daryl S


ecwhite said:
Yes I have a MYSQL backend - sorry i should have said this. Thanks for your
deligence in helping me with this. I will be glad if i can get it to work.

Yes all other criteria searches work correctly till i add the name search
with a wild character and it starts pulling all the record in the table even
when i search with let say the case number

There is no error with the code behind except when i use Using - Like "'*" &
(nz([Forms]![Main]![txt_exis_pt_name_search],"Z1Z2Z3Z4"))

This code if fixed is the result that I want .Using this Like "*" &
([Forms]![Main_Menu]![txt_exis_pt_name_search]) & "*" - Pulls the exact name
correctly like if i search for angel curt. If i search for betty it also
brings back all the records with betty in it like betty white, betty jack and
so on which is exactly what i want for the name search only. The problems is
when I search for a case with case no 12, it brings back all the records in
the database which is different from before i put in the "*" "*"

Using this Like "%" & [Forms]![Main]![txt_exis_pt_name] & "%" - Same result
with Like "*" & ([Forms]![Main_Menu]![txt_exis_pt_name_search]) & "*"

Using - Like "'*" &
(nz([Forms]![Main]![txt_exis_pt_name_search],"Z1Z2Z3Z4")) & "*'" - This gives
me an error '2001' you can canceled the previous operation
when i enter the patient number as the criteria on this line which works
when i don't have the name code If DCount("[t.fin_nbr]",
"the_name_of_the_query") = 0 Then





Daryl S said:
Ecwhite -

I just saw in another posting of yours that had % for a wildcard. Do you
have a SQL Server back-end? If so, change the asterisk (wildcard in Access)
to the % (wildcard for SQL Server)...

--
Daryl S


:

I tried it gives me an error '2001' you can canceled the previous operation
when i enter the patient number as the criteria on this line which works when
i don't have the name code If DCount("[t.fin_nbr]",
"the_name_of_the_query") = 0 Then

Here is what the code behind the click event of the serch button look like.
Please note i have one If Statement block for each search criteria and it
works perfectly till i put the name criteria in the query design

If Not IsNull(Me.txt_exis_pt_fin_nbr) Then

If DCount("[t.fin_nbr]", "the_name_of_the_query") = 0 Then

Dim stmacro_No_Existing_Case_RecordFound As String

stmacro_No_Existing_Case_RecordFound =
"Macro_No_RecordFound"
DoCmd.RunMacro stmacro_No_Existing_Case_RecordFound


Else
stDocexistingName =
"open_form_and_populate_fields_from_query_result"
DoCmd.OpenForm stDocexistingName

End If

End If

I reduced the where clause to two fields for now to resolve this first

WHERE (((t.fin_nbr)=Forms!Main.txt_exis_pt_fin_nbr_search)) Or
(((t.patient_name) Like "'*" &
(nz(Forms!Main!txt_exis_pt_name_search,"########")) & "*'"));
) Like "'*" & (nz(Forms!Main!txt_exis_pt_name_search,"########")) & "*'"));



:

Ecwhite -

OK, so the OR is good. Your Like statement will return all records if the
txt_exis_pt_name_search is blank. To prevent this, you need to test for null
and replace that with something that would never be in the name field. I
have done that with the Z1Z2Z23Z4 in the code below. I also added the text
delimeter of a single-quote to the asterisks. Try this:

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 "'*" &
(nz([Forms]![Main]![txt_exis_pt_name_search],"Z1Z2Z3Z4")) &
"*'"));


--
Daryl S


:

txt_exis_pt_name_search - with name search, if i type betty, there could be
more than one betty in the table so i want it to bring back all records with
betty in it.

I want them to search with one criteria at a time that is why i am using the
OR instead of an AND.

The rest of the search criteria is one to one meaning there could only be
one record with the same case number but many people with the same name.

I also tried the suggestion Like "*" & [Forms]![Main]![txt_exis_pt_name] & "*"
but it gives error. It makes the name one try to run first.

:

Ecwhite -

What do you want the txt_exis_pt_name_search to do for the query? What you
have done with the OR is to include in the query any records that match this
criteria, as well as any records that the query would have returned without
this criteria.

If you wanted to reduce the records the query was returning by limiting
those records to ones with the txt_exis_pt_name_search in the patient_name
field, then you need to AND this criteria. Be careful of the parentheses -
like this:

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]))) AND
(((t.patient_name) Like "*" & ([Forms]![Main]![txt_exis_pt_name_search]) &
"*"));

If this is not what you are asking for, then please post some results of the
query before and after adding the criteria, and why the records don't meet
your needs.

--
Daryl S


:

Please help everything works well till i put in the Like "*" &
([Forms]![Main]![txt_exis_pt_name]) & "*"

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]) &
"*"));
Click to expand...
Click to expand...
Click to expand...
 
Yay! Glad it works.

--
Daryl S


ecwhite said:
Thanks Daryl for working with me on this. I has been resolved. I had to wrap
the rest of the criteria in the NZ function too eg

Nz([Forms]![Main![txt_exis_pt_caseno_search],"00000")

I did it for each one and the whole thing work correctly. Thanks again for
working with me on this.

Daryl S said:
Ecwhite -

I wish I had more experience with SQL Server...

Let's see if we can make sure the nz is evaluating correctly. Take it out
of the criteria and instead put it in as a new field, like this (slight
alteration on the location of the first double-quote on purpose).

Expr1: = "Like '*" &
(nz([Forms]![Main]![txt_exis_pt_name_search],"Z1Z2Z3Z4")) & "*'"

Run the query two times - once with something in the txt_exis_pt_name_search
control, and once with it blank. Post the results of this field for each
query run.

--
Daryl S


ecwhite said:
Yes I have a MYSQL backend - sorry i should have said this. Thanks for your
deligence in helping me with this. I will be glad if i can get it to work.

Yes all other criteria searches work correctly till i add the name search
with a wild character and it starts pulling all the record in the table even
when i search with let say the case number

There is no error with the code behind except when i use Using - Like "'*" &
(nz([Forms]![Main]![txt_exis_pt_name_search],"Z1Z2Z3Z4"))

This code if fixed is the result that I want .Using this Like "*" &
([Forms]![Main_Menu]![txt_exis_pt_name_search]) & "*" - Pulls the exact name
correctly like if i search for angel curt. If i search for betty it also
brings back all the records with betty in it like betty white, betty jack and
so on which is exactly what i want for the name search only. The problems is
when I search for a case with case no 12, it brings back all the records in
the database which is different from before i put in the "*" "*"

Using this Like "%" & [Forms]![Main]![txt_exis_pt_name] & "%" - Same result
with Like "*" & ([Forms]![Main_Menu]![txt_exis_pt_name_search]) & "*"

Using - Like "'*" &
(nz([Forms]![Main]![txt_exis_pt_name_search],"Z1Z2Z3Z4")) & "*'" - This gives
me an error '2001' you can canceled the previous operation
when i enter the patient number as the criteria on this line which works
when i don't have the name code If DCount("[t.fin_nbr]",
"the_name_of_the_query") = 0 Then





:

Ecwhite -

I just saw in another posting of yours that had % for a wildcard. Do you
have a SQL Server back-end? If so, change the asterisk (wildcard in Access)
to the % (wildcard for SQL Server)...

--
Daryl S


:

I tried it gives me an error '2001' you can canceled the previous operation
when i enter the patient number as the criteria on this line which works when
i don't have the name code If DCount("[t.fin_nbr]",
"the_name_of_the_query") = 0 Then

Here is what the code behind the click event of the serch button look like.
Please note i have one If Statement block for each search criteria and it
works perfectly till i put the name criteria in the query design

If Not IsNull(Me.txt_exis_pt_fin_nbr) Then

If DCount("[t.fin_nbr]", "the_name_of_the_query") = 0 Then

Dim stmacro_No_Existing_Case_RecordFound As String

stmacro_No_Existing_Case_RecordFound =
"Macro_No_RecordFound"
DoCmd.RunMacro stmacro_No_Existing_Case_RecordFound


Else
stDocexistingName =
"open_form_and_populate_fields_from_query_result"
DoCmd.OpenForm stDocexistingName

End If

End If

I reduced the where clause to two fields for now to resolve this first

WHERE (((t.fin_nbr)=Forms!Main.txt_exis_pt_fin_nbr_search)) Or
(((t.patient_name) Like "'*" &
(nz(Forms!Main!txt_exis_pt_name_search,"########")) & "*'"));
) Like "'*" & (nz(Forms!Main!txt_exis_pt_name_search,"########")) & "*'"));



:

Ecwhite -

OK, so the OR is good. Your Like statement will return all records if the
txt_exis_pt_name_search is blank. To prevent this, you need to test for null
and replace that with something that would never be in the name field. I
have done that with the Z1Z2Z23Z4 in the code below. I also added the text
delimeter of a single-quote to the asterisks. Try this:

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 "'*" &
(nz([Forms]![Main]![txt_exis_pt_name_search],"Z1Z2Z3Z4")) &
"*'"));


--
Daryl S


:

txt_exis_pt_name_search - with name search, if i type betty, there could be
more than one betty in the table so i want it to bring back all records with
betty in it.

I want them to search with one criteria at a time that is why i am using the
OR instead of an AND.

The rest of the search criteria is one to one meaning there could only be
one record with the same case number but many people with the same name.

I also tried the suggestion Like "*" & [Forms]![Main]![txt_exis_pt_name] & "*"
but it gives error. It makes the name one try to run first.

:

Ecwhite -

What do you want the txt_exis_pt_name_search to do for the query? What you
have done with the OR is to include in the query any records that match this
criteria, as well as any records that the query would have returned without
this criteria.

If you wanted to reduce the records the query was returning by limiting
those records to ones with the txt_exis_pt_name_search in the patient_name
field, then you need to AND this criteria. Be careful of the parentheses -
like this:

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]))) AND
(((t.patient_name) Like "*" & ([Forms]![Main]![txt_exis_pt_name_search]) &
"*"));

If this is not what you are asking for, then please post some results of the
query before and after adding the criteria, and why the records don't meet
your needs.

--
Daryl S


:

Please help everything works well till i put in the Like "*" &
([Forms]![Main]![txt_exis_pt_name]) & "*"

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]) &
"*"));
Click to expand...
Click to expand...
Click to expand...
 
Sorry to bug you on this again.

Do you know the easiest way i can do this on the same query that i have been
working on. How do i
pass / change the current query criteria from its current value to the value
coming from a second form dynamically. My code in the query design looks
like

From Form main (To the same field in the query design)

Nz([Forms]![Main]![txt_fin_nbr],"00000")

When the criteria is not coming from form Main, i want it to be

Nz([Forms]![frm_display_multiple_patient_names]![fin_nbr], "00000")

The reason I want to use the same query is because a big form with 9
Tabcontrols has this query as its record source and i want to keep everything
the same no matter which form supplied the value to the query when the form
opens.

Thanks.



Daryl S said:
Ecwhite -

I wish I had more experience with SQL Server...

Let's see if we can make sure the nz is evaluating correctly. Take it out
of the criteria and instead put it in as a new field, like this (slight
alteration on the location of the first double-quote on purpose).

Expr1: = "Like '*" &
(nz([Forms]![Main]![txt_exis_pt_name_search],"Z1Z2Z3Z4")) & "*'"

Run the query two times - once with something in the txt_exis_pt_name_search
control, and once with it blank. Post the results of this field for each
query run.

--
Daryl S


ecwhite said:
Yes I have a MYSQL backend - sorry i should have said this. Thanks for your
deligence in helping me with this. I will be glad if i can get it to work.

Yes all other criteria searches work correctly till i add the name search
with a wild character and it starts pulling all the record in the table even
when i search with let say the case number

There is no error with the code behind except when i use Using - Like "'*" &
(nz([Forms]![Main]![txt_exis_pt_name_search],"Z1Z2Z3Z4"))

This code if fixed is the result that I want .Using this Like "*" &
([Forms]![Main_Menu]![txt_exis_pt_name_search]) & "*" - Pulls the exact name
correctly like if i search for angel curt. If i search for betty it also
brings back all the records with betty in it like betty white, betty jack and
so on which is exactly what i want for the name search only. The problems is
when I search for a case with case no 12, it brings back all the records in
the database which is different from before i put in the "*" "*"

Using this Like "%" & [Forms]![Main]![txt_exis_pt_name] & "%" - Same result
with Like "*" & ([Forms]![Main_Menu]![txt_exis_pt_name_search]) & "*"

Using - Like "'*" &
(nz([Forms]![Main]![txt_exis_pt_name_search],"Z1Z2Z3Z4")) & "*'" - This gives
me an error '2001' you can canceled the previous operation
when i enter the patient number as the criteria on this line which works
when i don't have the name code If DCount("[t.fin_nbr]",
"the_name_of_the_query") = 0 Then





Daryl S said:
Ecwhite -

I just saw in another posting of yours that had % for a wildcard. Do you
have a SQL Server back-end? If so, change the asterisk (wildcard in Access)
to the % (wildcard for SQL Server)...

--
Daryl S


:

I tried it gives me an error '2001' you can canceled the previous operation
when i enter the patient number as the criteria on this line which works when
i don't have the name code If DCount("[t.fin_nbr]",
"the_name_of_the_query") = 0 Then

Here is what the code behind the click event of the serch button look like.
Please note i have one If Statement block for each search criteria and it
works perfectly till i put the name criteria in the query design

If Not IsNull(Me.txt_exis_pt_fin_nbr) Then

If DCount("[t.fin_nbr]", "the_name_of_the_query") = 0 Then

Dim stmacro_No_Existing_Case_RecordFound As String

stmacro_No_Existing_Case_RecordFound =
"Macro_No_RecordFound"
DoCmd.RunMacro stmacro_No_Existing_Case_RecordFound


Else
stDocexistingName =
"open_form_and_populate_fields_from_query_result"
DoCmd.OpenForm stDocexistingName

End If

End If

I reduced the where clause to two fields for now to resolve this first

WHERE (((t.fin_nbr)=Forms!Main.txt_exis_pt_fin_nbr_search)) Or
(((t.patient_name) Like "'*" &
(nz(Forms!Main!txt_exis_pt_name_search,"########")) & "*'"));
) Like "'*" & (nz(Forms!Main!txt_exis_pt_name_search,"########")) & "*'"));



:

Ecwhite -

OK, so the OR is good. Your Like statement will return all records if the
txt_exis_pt_name_search is blank. To prevent this, you need to test for null
and replace that with something that would never be in the name field. I
have done that with the Z1Z2Z23Z4 in the code below. I also added the text
delimeter of a single-quote to the asterisks. Try this:

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 "'*" &
(nz([Forms]![Main]![txt_exis_pt_name_search],"Z1Z2Z3Z4")) &
"*'"));


--
Daryl S


:

txt_exis_pt_name_search - with name search, if i type betty, there could be
more than one betty in the table so i want it to bring back all records with
betty in it.

I want them to search with one criteria at a time that is why i am using the
OR instead of an AND.

The rest of the search criteria is one to one meaning there could only be
one record with the same case number but many people with the same name.

I also tried the suggestion Like "*" & [Forms]![Main]![txt_exis_pt_name] & "*"
but it gives error. It makes the name one try to run first.

:

Ecwhite -

What do you want the txt_exis_pt_name_search to do for the query? What you
have done with the OR is to include in the query any records that match this
criteria, as well as any records that the query would have returned without
this criteria.

If you wanted to reduce the records the query was returning by limiting
those records to ones with the txt_exis_pt_name_search in the patient_name
field, then you need to AND this criteria. Be careful of the parentheses -
like this:

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]))) AND
(((t.patient_name) Like "*" & ([Forms]![Main]![txt_exis_pt_name_search]) &
"*"));

If this is not what you are asking for, then please post some results of the
query before and after adding the criteria, and why the records don't meet
your needs.

--
Daryl S


:

Please help everything works well till i put in the Like "*" &
([Forms]![Main]![txt_exis_pt_name]) & "*"

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]) &
"*"));
Click to expand...
Click to expand...
Click to expand...
 
Ecwhite -

Do you have a main form that is always open (it doesn't have to be visible)?
If so, you can add a hidden textbox on it and have the query always look
here for the value. From each form that you want to run this query, just put
the correct data on this textbox in the main form and then run the query.

If this doesn't work for you, there are other options. If you need more
help, then post a new question with your SQL and explaining what you need.
(I will be leaving shortly and will be out of touch until at least the 15th,
and I don't want to leave you hanging...)
 
Back
Top