Like Expression in Query

S

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, tblCaseInfo.Owner, 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

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
 
C

Cheryl Fischer

Hello Shelly,

Would the following work?

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

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

S Jackson said:
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
S Jackson said:
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
Click to expand...
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, tblCaseInfo.Owner, 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,
Click to expand...
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
Click to expand...
Click to expand...
 
S

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


Cheryl Fischer said:
Hello Shelly,

Would the following work?

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

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

S Jackson said:
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
S Jackson said:
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
Click to expand...
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
Click to expand...
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
Click to expand...
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
Click to expand...
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, tblCaseInfo.Owner, 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,
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
C

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

S Jackson said:
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???


Cheryl Fischer said:
Hello Shelly,

Would the following work?

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

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

S Jackson said:
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
Click to expand...
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
Click to expand...
not
sure
of the proper syntax). Anway, I thought I would start here and try
Click to expand...
to
get
this working first, but I am getting a syntax error. I am pretty
Click to expand...
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, tblCaseInfo.Owner, 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
Click to expand...
Click to expand...
Click to expand...
 
S

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

Cheryl Fischer said:
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

S Jackson said:
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???


Cheryl Fischer said:
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,
Click to expand...
can
I?
So the alternative was to create a dialog form with VB code, but
Click to expand...
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
Click to expand...
Click to expand...
try
to tblRegion.ID)
LEFT
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Trouble with Query Statement 2
How do I assign an unbound object in a report? 1
Syntax error in query expression 7
Syntax error (missing operator) in query expression '[]='fname o'l 3
Expression help in Query 12
Syntax error in query expression 4
Can't find the field 'Forms' in your expression 4
Error message: Syntax error(missing operator)in query expression 2

Top