Like Expression in Query

[S Jackson]

I have the following expression in my Query:

Like Eval("InputBox$(""Enter first part of case name, e.g. New
Hope"",""Search Cases"")") & "*"

However, I am trying to design a database for multiple users and although
the above expression works great, but if the user does not make an entry
into the InputBox or enters information that does not match a record, the
form opens up blank. I am afraid this will confuse some users and would
like to have a message box come up instead advising the user that there are
not matching entries. I don't think I can do this with a query, can I?

So the alternative was to create a dialog form with VB code, but the SQL is
so long and complicated - ugh!

I started by creating a dialog form and inserting a text box called
"txtSearch."

I want the user to enter part of the text he is searching for in txtSearch
and then click OK. Under the onclick event for the OK button I tried
beginning with the following (Note I have not built the If -no records found
statement yet). I cut and pasted my SQL from my original parameter query and
modified the WHERE clause (I haven't added the & "*" because I am not sure
of the proper syntax). Anway, I thought I would start here and try to get
this working first, but I am getting a syntax error. I am pretty sure it is
in the WHERE statement, but I can't figure it out. Any ideas anyone?

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCaselkp"

stLinkCriteria = "SELECT tblCaseInfo.CaseName, tblStatus.ClosedDate,
tblCaseInfo.DHSNo, " _
& " tlbCaseInfo.SOAHNo, tblCaseInfo.Region, tblCaseInfo.NoticeDate,
tblCaseInfo.Fieldofc, " _
& " tblCaseInfo.AppealDate, tblCaseInfo.AssignDate,
tblCaseInfo.DHSAttny, tblCaseInfo.Asstnt, " _
& " tblCaseInfo.Vendor, tblCaseInfo.Facility,
tblCaseInfo.Facility-PetAddy, tblCaseInfo.Facility-PetCity, " _
& " tblCaseInfo.Facility-PetState, tblCaseInfo.Facility-PetZip,
tblCaseInfo.Facility-PetPh, " _
& " tblCaseInfo.Facility-PetFax, tblCaseInfwner, tblCaseInfo.RepID,
tblCaseInfo.Program, " _
& " tblCaseInfo.Action, tblCaseInfo.Amt, tblCaseInfo.Rate1,
tblCaseInfo.Start1, tblCaseInfo.End1, " _
& " tblCaseInfo.Rate2, tblCaseInfo.Start2, tblCaseInfo.End2,
tblCaseInfo.Sanction, " _
& " tblCaseInfo.Surveys, tblCaseInfo.IDR, tblCaseInfo.TAC,
tblCaseInfo.ViolationDescrip, " _
& " tblCaseInfo.Comment, tblRepresentatives, tblStatus.StatusRptNotes,
tblStatus.BankruptyEntity, " _
& " tblStatus.BankruptDate, tblStatus.Bankruptcy, tblStatus.Disposition,
" _
& " tblStatus.CSAAmt, tblStatus.BoxNo, tblRegion.SUBOFC, tblRegion.PM, "
_
& " tblRegion.PMPH, tblRegion.Ext, tblRegion.Region, " _
& " FROM ((tblRepresentatives RIGHT JOIN tblCaseInfo ON
tblRepresentatives.ID = tblCaseInfo.RepID), " _
& " LEFT JOIN tblRegion ON tblCaseInfo.FieldOfc = tblRegion.ID) LEFT
JOIN tblStatus ON tblCaseInfo.DHSNo = tblStatus.DHSNo, " _
& " WHERE tblCaseInfo.CaseName LIKE " & Me.txtSearch & "" _
& " AND tblStatus.ClosedDate Is Null, " _
& " ORDER BY tblCaseInfo.CaseName; "

DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[S Jackson]

I tried a different approach, hoping this one is simplier:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCaseLkp"

stLinkCriteria = "[CaseName]=" & "'" & Me![txtSearch] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Now, how can I make this so that the user does not have to type the exact
CaseName, but only part of the name (typing "New Hope" with give you all
records with "New Hope" text contained in that field) ??

Here is what I want:
Like [txtSearch] & "*"


????
TIA
S Jackson

 

[Cheryl Fischer]

Hello Shelly,

Would the following work?

stLinkCriteria = "[CaseName]=" & Chr(34) & "*" & Me!txtSearch & "*" &
Chr(34)

hth,
--
Cheryl Fischer
Law/Sys Associates
Houston, TX

I tried a different approach, hoping this one is simplier:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCaseLkp"

stLinkCriteria = "[CaseName]=" & "'" & Me![txtSearch] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Now, how can I make this so that the user does not have to type the exact
CaseName, but only part of the name (typing "New Hope" with give you all
records with "New Hope" text contained in that field) ??

Here is what I want:
Like [txtSearch] & "*"


????
TIA
S Jackson

I have the following expression in my Query:

Like Eval("InputBox$(""Enter first part of case name, e.g. New
Hope"",""Search Cases"")") & "*"

However, I am trying to design a database for multiple users and although
the above expression works great, but if the user does not make an entry
into the InputBox or enters information that does not match a record, the
form opens up blank. I am afraid this will confuse some users and would
like to have a message box come up instead advising the user that there are
not matching entries. I don't think I can do this with a query, can I?

So the alternative was to create a dialog form with VB code, but the SQL is
so long and complicated - ugh!

I started by creating a dialog form and inserting a text box called
"txtSearch."

I want the user to enter part of the text he is searching for in txtSearch
and then click OK. Under the onclick event for the OK button I tried
beginning with the following (Note I have not built the If -no records found
statement yet). I cut and pasted my SQL from my original parameter query and
modified the WHERE clause (I haven't added the & "*" because I am not sure
of the proper syntax). Anway, I thought I would start here and try to get
this working first, but I am getting a syntax error. I am pretty sure

it

is
in the WHERE statement, but I can't figure it out. Any ideas anyone?

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCaselkp"

stLinkCriteria = "SELECT tblCaseInfo.CaseName, tblStatus.ClosedDate,
tblCaseInfo.DHSNo, " _
& " tlbCaseInfo.SOAHNo, tblCaseInfo.Region, tblCaseInfo.NoticeDate,
tblCaseInfo.Fieldofc, " _
& " tblCaseInfo.AppealDate, tblCaseInfo.AssignDate,
tblCaseInfo.DHSAttny, tblCaseInfo.Asstnt, " _
& " tblCaseInfo.Vendor, tblCaseInfo.Facility,
tblCaseInfo.Facility-PetAddy, tblCaseInfo.Facility-PetCity, " _
& " tblCaseInfo.Facility-PetState, tblCaseInfo.Facility-PetZip,
tblCaseInfo.Facility-PetPh, " _
& " tblCaseInfo.Facility-PetFax, tblCaseInfwner, tblCaseInfo.RepID,
tblCaseInfo.Program, " _
& " tblCaseInfo.Action, tblCaseInfo.Amt, tblCaseInfo.Rate1,
tblCaseInfo.Start1, tblCaseInfo.End1, " _
& " tblCaseInfo.Rate2, tblCaseInfo.Start2, tblCaseInfo.End2,
tblCaseInfo.Sanction, " _
& " tblCaseInfo.Surveys, tblCaseInfo.IDR, tblCaseInfo.TAC,
tblCaseInfo.ViolationDescrip, " _
& " tblCaseInfo.Comment, tblRepresentatives, tblStatus.StatusRptNotes,
tblStatus.BankruptyEntity, " _
& " tblStatus.BankruptDate, tblStatus.Bankruptcy, tblStatus.Disposition,
" _
& " tblStatus.CSAAmt, tblStatus.BoxNo, tblRegion.SUBOFC,

tblRegion.PM,

"
_
& " tblRegion.PMPH, tblRegion.Ext, tblRegion.Region, " _
& " FROM ((tblRepresentatives RIGHT JOIN tblCaseInfo ON
tblRepresentatives.ID = tblCaseInfo.RepID), " _
& " LEFT JOIN tblRegion ON tblCaseInfo.FieldOfc = tblRegion.ID) LEFT
JOIN tblStatus ON tblCaseInfo.DHSNo = tblStatus.DHSNo, " _
& " WHERE tblCaseInfo.CaseName LIKE " & Me.txtSearch & "" _
& " AND tblStatus.ClosedDate Is Null, " _
& " ORDER BY tblCaseInfo.CaseName; "

DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[S Jackson]

Hi Cheryl:

I could not get it to work, but here is what DID work:

stLinkCriteria = "[CaseName]Like" & "'" & Me![txtSearch] & "*" & "'"

Now, next question:

How do I build an if statement that says:

If stDocName is Blank Then MsgBox "No records found. Please try again."

StDocName is the name of my form that opens after user enters criteria.

TIA
S Jackson

P.S. Something to do with RecordSet???

Hello Shelly,

Would the following work?

stLinkCriteria = "[CaseName]=" & Chr(34) & "*" & Me!txtSearch & "*" &
Chr(34)

hth,
--
Cheryl Fischer
Law/Sys Associates
Houston, TX

I tried a different approach, hoping this one is simplier:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCaseLkp"

stLinkCriteria = "[CaseName]=" & "'" & Me![txtSearch] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Now, how can I make this so that the user does not have to type the exact
CaseName, but only part of the name (typing "New Hope" with give you all
records with "New Hope" text contained in that field) ??

Here is what I want:
Like [txtSearch] & "*"


????
TIA
S Jackson

I have the following expression in my Query:

Like Eval("InputBox$(""Enter first part of case name, e.g. New
Hope"",""Search Cases"")") & "*"

However, I am trying to design a database for multiple users and although
the above expression works great, but if the user does not make an entry
into the InputBox or enters information that does not match a record, the
form opens up blank. I am afraid this will confuse some users and would
like to have a message box come up instead advising the user that

there
are

not matching entries. I don't think I can do this with a query, can I?

So the alternative was to create a dialog form with VB code, but the

SQL
is

so long and complicated - ugh!

I started by creating a dialog form and inserting a text box called
"txtSearch."

I want the user to enter part of the text he is searching for in txtSearch
and then click OK. Under the onclick event for the OK button I tried
beginning with the following (Note I have not built the If -no records found
statement yet). I cut and pasted my SQL from my original parameter

query
and

modified the WHERE clause (I haven't added the & "*" because I am not sure
of the proper syntax). Anway, I thought I would start here and try to get
this working first, but I am getting a syntax error. I am pretty sure

it

is
in the WHERE statement, but I can't figure it out. Any ideas anyone?

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCaselkp"

stLinkCriteria = "SELECT tblCaseInfo.CaseName, tblStatus.ClosedDate,
tblCaseInfo.DHSNo, " _
& " tlbCaseInfo.SOAHNo, tblCaseInfo.Region, tblCaseInfo.NoticeDate,
tblCaseInfo.Fieldofc, " _
& " tblCaseInfo.AppealDate, tblCaseInfo.AssignDate,
tblCaseInfo.DHSAttny, tblCaseInfo.Asstnt, " _
& " tblCaseInfo.Vendor, tblCaseInfo.Facility,
tblCaseInfo.Facility-PetAddy, tblCaseInfo.Facility-PetCity, " _
& " tblCaseInfo.Facility-PetState, tblCaseInfo.Facility-PetZip,
tblCaseInfo.Facility-PetPh, " _
& " tblCaseInfo.Facility-PetFax, tblCaseInfwner, tblCaseInfo.RepID,
tblCaseInfo.Program, " _
& " tblCaseInfo.Action, tblCaseInfo.Amt, tblCaseInfo.Rate1,
tblCaseInfo.Start1, tblCaseInfo.End1, " _
& " tblCaseInfo.Rate2, tblCaseInfo.Start2, tblCaseInfo.End2,
tblCaseInfo.Sanction, " _
& " tblCaseInfo.Surveys, tblCaseInfo.IDR, tblCaseInfo.TAC,
tblCaseInfo.ViolationDescrip, " _
& " tblCaseInfo.Comment, tblRepresentatives, tblStatus.StatusRptNotes,
tblStatus.BankruptyEntity, " _
& " tblStatus.BankruptDate, tblStatus.Bankruptcy, tblStatus.Disposition,
" _
& " tblStatus.CSAAmt, tblStatus.BoxNo, tblRegion.SUBOFC,

tblRegion.PM,

"
_
& " tblRegion.PMPH, tblRegion.Ext, tblRegion.Region, " _
& " FROM ((tblRepresentatives RIGHT JOIN tblCaseInfo ON
tblRepresentatives.ID = tblCaseInfo.RepID), " _
& " LEFT JOIN tblRegion ON tblCaseInfo.FieldOfc = tblRegion.ID) LEFT
JOIN tblStatus ON tblCaseInfo.DHSNo = tblStatus.DHSNo, " _
& " WHERE tblCaseInfo.CaseName LIKE " & Me.txtSearch & "" _
& " AND tblStatus.ClosedDate Is Null, " _
& " ORDER BY tblCaseInfo.CaseName; "

DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[Cheryl Fischer]

You could use something like the following (untested) in the Open event of
your form, frmCaseLkp

Me.RecordsetClone.MoveLast
if Me.RecordsetClone.RecordCount=0 then
MsgBox "No records found - try again."
else
MsgBox "The database found " _
& Me.RecordsetClone.RecordCount _
& " cases.", vbInformation, "Record Count"
End if

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

Hi Cheryl:

I could not get it to work, but here is what DID work:

stLinkCriteria = "[CaseName]Like" & "'" & Me![txtSearch] & "*" & "'"

Now, next question:

How do I build an if statement that says:

If stDocName is Blank Then MsgBox "No records found. Please try again."

StDocName is the name of my form that opens after user enters criteria.

TIA
S Jackson

P.S. Something to do with RecordSet???

Hello Shelly,

Would the following work?

stLinkCriteria = "[CaseName]=" & Chr(34) & "*" & Me!txtSearch & "*" &
Chr(34)

hth,
--
Cheryl Fischer
Law/Sys Associates
Houston, TX

I tried a different approach, hoping this one is simplier:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCaseLkp"

stLinkCriteria = "[CaseName]=" & "'" & Me![txtSearch] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Now, how can I make this so that the user does not have to type the exact
CaseName, but only part of the name (typing "New Hope" with give you all
records with "New Hope" text contained in that field) ??

Here is what I want:
Like [txtSearch] & "*"


????
TIA
S Jackson
I have the following expression in my Query:

Like Eval("InputBox$(""Enter first part of case name, e.g. New
Hope"",""Search Cases"")") & "*"

However, I am trying to design a database for multiple users and although
the above expression works great, but if the user does not make an entry
into the InputBox or enters information that does not match a

record,
the

form opens up blank. I am afraid this will confuse some users and would
like to have a message box come up instead advising the user that there
are
not matching entries. I don't think I can do this with a query, can I?

So the alternative was to create a dialog form with VB code, but the SQL
is
so long and complicated - ugh!

I started by creating a dialog form and inserting a text box called
"txtSearch."

I want the user to enter part of the text he is searching for in txtSearch
and then click OK. Under the onclick event for the OK button I tried
beginning with the following (Note I have not built the If -no records
found
statement yet). I cut and pasted my SQL from my original parameter query
and
modified the WHERE clause (I haven't added the & "*" because I am

not
sure

of the proper syntax). Anway, I thought I would start here and try

to
get

this working first, but I am getting a syntax error. I am pretty

sure
it

is
in the WHERE statement, but I can't figure it out. Any ideas anyone?

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCaselkp"

stLinkCriteria = "SELECT tblCaseInfo.CaseName, tblStatus.ClosedDate,
tblCaseInfo.DHSNo, " _
& " tlbCaseInfo.SOAHNo, tblCaseInfo.Region, tblCaseInfo.NoticeDate,
tblCaseInfo.Fieldofc, " _
& " tblCaseInfo.AppealDate, tblCaseInfo.AssignDate,
tblCaseInfo.DHSAttny, tblCaseInfo.Asstnt, " _
& " tblCaseInfo.Vendor, tblCaseInfo.Facility,
tblCaseInfo.Facility-PetAddy, tblCaseInfo.Facility-PetCity, " _
& " tblCaseInfo.Facility-PetState, tblCaseInfo.Facility-PetZip,
tblCaseInfo.Facility-PetPh, " _
& " tblCaseInfo.Facility-PetFax, tblCaseInfwner, tblCaseInfo.RepID,
tblCaseInfo.Program, " _
& " tblCaseInfo.Action, tblCaseInfo.Amt, tblCaseInfo.Rate1,
tblCaseInfo.Start1, tblCaseInfo.End1, " _
& " tblCaseInfo.Rate2, tblCaseInfo.Start2, tblCaseInfo.End2,
tblCaseInfo.Sanction, " _
& " tblCaseInfo.Surveys, tblCaseInfo.IDR, tblCaseInfo.TAC,
tblCaseInfo.ViolationDescrip, " _
& " tblCaseInfo.Comment, tblRepresentatives, tblStatus.StatusRptNotes,
tblStatus.BankruptyEntity, " _
& " tblStatus.BankruptDate, tblStatus.Bankruptcy,
tblStatus.Disposition,
" _
& " tblStatus.CSAAmt, tblStatus.BoxNo, tblRegion.SUBOFC, tblRegion.PM,
"
_
& " tblRegion.PMPH, tblRegion.Ext, tblRegion.Region, " _
& " FROM ((tblRepresentatives RIGHT JOIN tblCaseInfo ON
tblRepresentatives.ID = tblCaseInfo.RepID), " _
& " LEFT JOIN tblRegion ON tblCaseInfo.FieldOfc = tblRegion.ID) LEFT
JOIN tblStatus ON tblCaseInfo.DHSNo = tblStatus.DHSNo, " _
& " WHERE tblCaseInfo.CaseName LIKE " & Me.txtSearch & "" _
& " AND tblStatus.ClosedDate Is Null, " _
& " ORDER BY tblCaseInfo.CaseName; "

DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[S Jackson]

If I entered text that does not find a record, I get an error executing
"Me.RecordsetClone.MoveLast"

So I moved that line to the first statement under Else.

And, if user will see a message box telling him they found 0 records, but
the Form, frmCaselkp, opens up blank. I want to avoid this. So I changed
the code and it looks like this now:

If Me.RecordsetClone.RecordCount = 0 Then
DoCmd.Close
MsgBox "No records found - Please try again."
Else
Me.RecordsetClone.MoveLast
MsgBox "The database found " _
& Me.RecordsetClone.RecordCount _
& " cases.", vbInformation, "Record Count"
End If

Thanks for you help!!!! (AGAIN!)

S. Jackson

You could use something like the following (untested) in the Open event of
your form, frmCaseLkp

Me.RecordsetClone.MoveLast
if Me.RecordsetClone.RecordCount=0 then
MsgBox "No records found - try again."
else
MsgBox "The database found " _
& Me.RecordsetClone.RecordCount _
& " cases.", vbInformation, "Record Count"
End if

--
Cheryl Fischer
Law/Sys Associates
Houston, TX

Hi Cheryl:

I could not get it to work, but here is what DID work:

stLinkCriteria = "[CaseName]Like" & "'" & Me![txtSearch] & "*" & "'"

Now, next question:

How do I build an if statement that says:

If stDocName is Blank Then MsgBox "No records found. Please try again."

StDocName is the name of my form that opens after user enters criteria.

TIA
S Jackson

P.S. Something to do with RecordSet???

Hello Shelly,

Would the following work?

stLinkCriteria = "[CaseName]=" & Chr(34) & "*" & Me!txtSearch & "*" &
Chr(34)

hth,
--
Cheryl Fischer
Law/Sys Associates
Houston, TX

I tried a different approach, hoping this one is simplier:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCaseLkp"

stLinkCriteria = "[CaseName]=" & "'" & Me![txtSearch] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Now, how can I make this so that the user does not have to type the exact
CaseName, but only part of the name (typing "New Hope" with give you all
records with "New Hope" text contained in that field) ??

Here is what I want:
Like [txtSearch] & "*"


????
TIA
S Jackson
I have the following expression in my Query:

Like Eval("InputBox$(""Enter first part of case name, e.g. New
Hope"",""Search Cases"")") & "*"

However, I am trying to design a database for multiple users and
although
the above expression works great, but if the user does not make an entry
into the InputBox or enters information that does not match a record,
the
form opens up blank. I am afraid this will confuse some users and would
like to have a message box come up instead advising the user that there
are
not matching entries. I don't think I can do this with a query,

can
I?

So the alternative was to create a dialog form with VB code, but

the
SQL

is
so long and complicated - ugh!

I started by creating a dialog form and inserting a text box called
"txtSearch."

I want the user to enter part of the text he is searching for in
txtSearch
and then click OK. Under the onclick event for the OK button I tried
beginning with the following (Note I have not built the If -no records
found
statement yet). I cut and pasted my SQL from my original parameter query
and
modified the WHERE clause (I haven't added the & "*" because I am not
sure
of the proper syntax). Anway, I thought I would start here and

try

to tblRegion.ID)
LEFT