Continuous form search problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All,

About a year ago, I found a FROM - WHERE code here to build a search in a
continuous form. It worked great with many "Where" criteria but now that I
added a field that affects the full database, it acts strange.

Below is the code
Private Sub Kommandoknapp30_Click()
Dim strSql As String
Dim lngLen As Long
Const strcStub = "SELECT Doctorsearchquery.ID, Doctorsearchquery.Note,
Doctorsearchquery.Quicknote, Doctorsearchquery.Country.Country AS Uttryck1,
[Doctorsearchquery].[Country.Country] AS Uttryck2, Doctorsearchquery.Region,
Doctorsearchquery.[First name], Doctorsearchquery.[Last name],
Doctorsearchquery.[Name Practice/Hospital], Doctorsearchquery.[Male/Female],
Doctorsearchquery.[Language ID], Doctorsearchquery.Phonenumber,
Doctorsearchquery.Faxnumber, Doctorsearchquery.[E-mail address],
Doctorsearchquery.Street, Doctorsearchquery.[Postal Code + City],
Doctorsearchquery.Country, Doctorsearchquery.specialisme2,
Doctorsearchquery.Speciality.Speciality,
Doctorsearchquery.Speciality_1.Speciality FROM Doctorsearchquery"

'Build the WHERE clause from the non-Null boxes.

If Not IsNull(Me.Kombinationsruta58) Then
strSql = strSql & "([Doctorsearchquery].[Country].[Country] Like
""" & Me.Kombinationsruta58 & """) AND "
End If

If Not IsNull(Me.Text50) Then
strSql = strSql & "(Doctorsearchquery.Speciality.Speciality Like
""" & Me.Text50 & "*"") OR "
End If

If Not IsNull(Me.Text50) Then
strSql = strSql & "(Doctorsearchquery.Speciality_1.Speciality Like
""" & Me.Text50 & "*"") AND "
End If


'etc for other text boxes.

lngLen = Len(strSql) - 5 'Chop off the trailiing " AND ".
If lngLen > 0 Then
strSql = strcStub & " WHERE " & Left$(strSql, lngLen) & ";"
Else
strSql = strcStub & ";"
MsgBox "No criteria"
End If

'Finally, show the search results in this form.
Me.RecordSource = strSql
End Sub

I used to only have Doctorsearchquery.Speciality.Speciality and added
Doctorsearchquery.Speciality_1.Speciality.
When I do a search and select a country in Me.Kombinationsruta58 and a
speciality in Me.Text50, it gives me results with more than one country. I
don't understand.
Anyone that has an idea?

Thanks

PS: I know it's a mess.
 
Ok, I tested a bit and the problem is that it doesn't want to work with
OR "

only with AND "

What can I do?
Thanks
 
Ben,

From you code, I'm not quite sure what
Doctorsearchquery.specialty_1.Speciality is, since this syntax doesn't make
any sense to me.

What happens when you replace:
If Not IsNull(Me.Text50) Then
strSql = strSql & "(Doctorsearchquery.Speciality.Speciality Like
""" & Me.Text50 & "*"") OR "
End If

If Not IsNull(Me.Text50) Then
strSql = strSql & "(Doctorsearchquery.Speciality_1.Speciality Like
""" & Me.Text50 & "*"") AND "
End If
Click to expand...

With:

If Not IsNull(Me.Text50) Then
strSql = strSql & "(" _
& "(Speciality.Speciality Like '" &
Me.Text50 & "*'") OR " _
& "(Speciality_1.Speciality Like '" &
Me.Text50 & "*'")" _
& ")" AND "
End If

I think your problem is that you need to process both of the tests agains
me.text50 as a single element and wrap that portion of the WHERE clause in
parenthesis.

BTW, I recommend creating your Where clause (I use varCriteria) separate
from the strSQL and then appending it to strSQL, something like:

strSQL = strSQL & (" WHERE " + varCriteria)

This way, if you don't define any criteria, varCriteria will be NULL, and
the sum (+) of " WHERE " + varCriteria will be NULL, thereby leaving the
WHERE clause out of the sql statement altogether.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.


Ben said:
Hi All,

About a year ago, I found a FROM - WHERE code here to build a search in a
continuous form. It worked great with many "Where" criteria but now that I
added a field that affects the full database, it acts strange.

Below is the code
Private Sub Kommandoknapp30_Click()
Dim strSql As String
Dim lngLen As Long
Const strcStub = "SELECT Doctorsearchquery.ID, Doctorsearchquery.Note,
Doctorsearchquery.Quicknote, Doctorsearchquery.Country.Country AS Uttryck1,
[Doctorsearchquery].[Country.Country] AS Uttryck2, Doctorsearchquery.Region,
Doctorsearchquery.[First name], Doctorsearchquery.[Last name],
Doctorsearchquery.[Name Practice/Hospital], Doctorsearchquery.[Male/Female],
Doctorsearchquery.[Language ID], Doctorsearchquery.Phonenumber,
Doctorsearchquery.Faxnumber, Doctorsearchquery.[E-mail address],
Doctorsearchquery.Street, Doctorsearchquery.[Postal Code + City],
Doctorsearchquery.Country, Doctorsearchquery.specialisme2,
Doctorsearchquery.Speciality.Speciality,
Doctorsearchquery.Speciality_1.Speciality FROM Doctorsearchquery"

'Build the WHERE clause from the non-Null boxes.

If Not IsNull(Me.Kombinationsruta58) Then
strSql = strSql & "([Doctorsearchquery].[Country].[Country] Like
""" & Me.Kombinationsruta58 & """) AND "
End If

If Not IsNull(Me.Text50) Then
strSql = strSql & "(Doctorsearchquery.Speciality.Speciality Like
""" & Me.Text50 & "*"") OR "
End If

If Not IsNull(Me.Text50) Then
strSql = strSql & "(Doctorsearchquery.Speciality_1.Speciality Like
""" & Me.Text50 & "*"") AND "
End If


'etc for other text boxes.

lngLen = Len(strSql) - 5 'Chop off the trailiing " AND ".
If lngLen > 0 Then
strSql = strcStub & " WHERE " & Left$(strSql, lngLen) & ";"
Else
strSql = strcStub & ";"
MsgBox "No criteria"
End If

'Finally, show the search results in this form.
Me.RecordSource = strSql
End Sub

I used to only have Doctorsearchquery.Speciality.Speciality and added
Doctorsearchquery.Speciality_1.Speciality.
When I do a search and select a country in Me.Kombinationsruta58 and a
speciality in Me.Text50, it gives me results with more than one country. I
don't understand.
Anyone that has an idea?

Thanks

PS: I know it's a mess.
Click to expand...
 
Dale,

Thanks for your help. You made me realise that Doctorsearchquery was not
needed. I have it straight from the original table now.

If Not IsNull(Me.Text50) Then
strSql = strSql & "(Specialisme Like """ & Me.Text50 & "*"") OR "
End If

If Not IsNull(Me.Text50) Then
strSql = strSql & "(Specialisme2 Like """ & Me.Text50 & "*"") AND "
End If


If Not IsNull(Me.Kombinationsruta58) Then
strSql = strSql & "([Country ID] Like ""*" & Me.Kombinationsruta58
& "*"") AND "
End If



Both tests work great separately but once I put them together and search on
a specialisme in a country, it includes records from other countries too.

(PS: Contact database with Drs. A doctor can have a requested specialisme
(me.text50) in either specialisme or specialisme2)

Will working with varCriteria be easier if I have 16 criteria?
If yes, how to combine them?

Thanks


Dale Fye said:
Ben,

From you code, I'm not quite sure what
Doctorsearchquery.specialty_1.Speciality is, since this syntax doesn't make
any sense to me.

What happens when you replace:
If Not IsNull(Me.Text50) Then
strSql = strSql & "(Doctorsearchquery.Speciality.Speciality Like
""" & Me.Text50 & "*"") OR "
End If

If Not IsNull(Me.Text50) Then
strSql = strSql & "(Doctorsearchquery.Speciality_1.Speciality Like
""" & Me.Text50 & "*"") AND "
End If
Click to expand...

With:

If Not IsNull(Me.Text50) Then
strSql = strSql & "(" _
& "(Speciality.Speciality Like '" &
Me.Text50 & "*'") OR " _
& "(Speciality_1.Speciality Like '" &
Me.Text50 & "*'")" _
& ")" AND "
End If

I think your problem is that you need to process both of the tests agains
me.text50 as a single element and wrap that portion of the WHERE clause in
parenthesis.

BTW, I recommend creating your Where clause (I use varCriteria) separate
from the strSQL and then appending it to strSQL, something like:

strSQL = strSQL & (" WHERE " + varCriteria)

This way, if you don't define any criteria, varCriteria will be NULL, and
the sum (+) of " WHERE " + varCriteria will be NULL, thereby leaving the
WHERE clause out of the sql statement altogether.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.


Ben said:
Hi All,

About a year ago, I found a FROM - WHERE code here to build a search in a
continuous form. It worked great with many "Where" criteria but now that I
added a field that affects the full database, it acts strange.

Below is the code
Private Sub Kommandoknapp30_Click()
Dim strSql As String
Dim lngLen As Long
Const strcStub = "SELECT Doctorsearchquery.ID, Doctorsearchquery.Note,
Doctorsearchquery.Quicknote, Doctorsearchquery.Country.Country AS Uttryck1,
[Doctorsearchquery].[Country.Country] AS Uttryck2, Doctorsearchquery.Region,
Doctorsearchquery.[First name], Doctorsearchquery.[Last name],
Doctorsearchquery.[Name Practice/Hospital], Doctorsearchquery.[Male/Female],
Doctorsearchquery.[Language ID], Doctorsearchquery.Phonenumber,
Doctorsearchquery.Faxnumber, Doctorsearchquery.[E-mail address],
Doctorsearchquery.Street, Doctorsearchquery.[Postal Code + City],
Doctorsearchquery.Country, Doctorsearchquery.specialisme2,
Doctorsearchquery.Speciality.Speciality,
Doctorsearchquery.Speciality_1.Speciality FROM Doctorsearchquery"

'Build the WHERE clause from the non-Null boxes.

If Not IsNull(Me.Kombinationsruta58) Then
strSql = strSql & "([Doctorsearchquery].[Country].[Country] Like
""" & Me.Kombinationsruta58 & """) AND "
End If

If Not IsNull(Me.Text50) Then
strSql = strSql & "(Doctorsearchquery.Speciality.Speciality Like
""" & Me.Text50 & "*"") OR "
End If

If Not IsNull(Me.Text50) Then
strSql = strSql & "(Doctorsearchquery.Speciality_1.Speciality Like
""" & Me.Text50 & "*"") AND "
End If


'etc for other text boxes.

lngLen = Len(strSql) - 5 'Chop off the trailiing " AND ".
If lngLen > 0 Then
strSql = strcStub & " WHERE " & Left$(strSql, lngLen) & ";"
Else
strSql = strcStub & ";"
MsgBox "No criteria"
End If

'Finally, show the search results in this form.
Me.RecordSource = strSql
End Sub

I used to only have Doctorsearchquery.Speciality.Speciality and added
Doctorsearchquery.Speciality_1.Speciality.
When I do a search and select a country in Me.Kombinationsruta58 and a
speciality in Me.Text50, it gives me results with more than one country. I
don't understand.
Anyone that has an idea?

Thanks

PS: I know it's a mess.
Click to expand...
Click to expand...
 
Dale,

Thanks for your help. You made me realise that Doctorsearchquery was not
needed. I have it straight from the original table now.

If Not IsNull(Me.Text50) Then
strSql = strSql & "(Specialisme Like """ & Me.Text50 & "*"") OR "
End If

If Not IsNull(Me.Text50) Then
strSql = strSql & "(Specialisme2 Like """ & Me.Text50 & "*"") AND "
End If


If Not IsNull(Me.Kombinationsruta58) Then
strSql = strSql & "([Country ID] Like ""*" & Me.Kombinationsruta58
& "*"") AND "
End If



Both tests work great separately but once I put them together and search on
a specialisme in a country, it includes records from other countries too.

(PS: Contact database with Drs. A doctor can have a requested specialisme
(me.text50) in either specialisme or specialisme2)

Will working with varCriteria be easier if I have 16 criteria?
If yes, how to combine them?

Thanks


Dale Fye said:
Ben,

From you code, I'm not quite sure what
Doctorsearchquery.specialty_1.Speciality is, since this syntax doesn't make
any sense to me.

What happens when you replace:
If Not IsNull(Me.Text50) Then
strSql = strSql & "(Doctorsearchquery.Speciality.Speciality Like
""" & Me.Text50 & "*"") OR "
End If

If Not IsNull(Me.Text50) Then
strSql = strSql & "(Doctorsearchquery.Speciality_1.Speciality Like
""" & Me.Text50 & "*"") AND "
End If
Click to expand...

With:

If Not IsNull(Me.Text50) Then
strSql = strSql & "(" _
& "(Speciality.Speciality Like '" &
Me.Text50 & "*'") OR " _
& "(Speciality_1.Speciality Like '" &
Me.Text50 & "*'")" _
& ")" AND "
End If

I think your problem is that you need to process both of the tests agains
me.text50 as a single element and wrap that portion of the WHERE clause in
parenthesis.

BTW, I recommend creating your Where clause (I use varCriteria) separate
from the strSQL and then appending it to strSQL, something like:

strSQL = strSQL & (" WHERE " + varCriteria)

This way, if you don't define any criteria, varCriteria will be NULL, and
the sum (+) of " WHERE " + varCriteria will be NULL, thereby leaving the
WHERE clause out of the sql statement altogether.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.


Ben said:
Hi All,

About a year ago, I found a FROM - WHERE code here to build a search in a
continuous form. It worked great with many "Where" criteria but now that I
added a field that affects the full database, it acts strange.

Below is the code
Private Sub Kommandoknapp30_Click()
Dim strSql As String
Dim lngLen As Long
Const strcStub = "SELECT Doctorsearchquery.ID, Doctorsearchquery.Note,
Doctorsearchquery.Quicknote, Doctorsearchquery.Country.Country AS Uttryck1,
[Doctorsearchquery].[Country.Country] AS Uttryck2, Doctorsearchquery.Region,
Doctorsearchquery.[First name], Doctorsearchquery.[Last name],
Doctorsearchquery.[Name Practice/Hospital], Doctorsearchquery.[Male/Female],
Doctorsearchquery.[Language ID], Doctorsearchquery.Phonenumber,
Doctorsearchquery.Faxnumber, Doctorsearchquery.[E-mail address],
Doctorsearchquery.Street, Doctorsearchquery.[Postal Code + City],
Doctorsearchquery.Country, Doctorsearchquery.specialisme2,
Doctorsearchquery.Speciality.Speciality,
Doctorsearchquery.Speciality_1.Speciality FROM Doctorsearchquery"

'Build the WHERE clause from the non-Null boxes.

If Not IsNull(Me.Kombinationsruta58) Then
strSql = strSql & "([Doctorsearchquery].[Country].[Country] Like
""" & Me.Kombinationsruta58 & """) AND "
End If

If Not IsNull(Me.Text50) Then
strSql = strSql & "(Doctorsearchquery.Speciality.Speciality Like
""" & Me.Text50 & "*"") OR "
End If

If Not IsNull(Me.Text50) Then
strSql = strSql & "(Doctorsearchquery.Speciality_1.Speciality Like
""" & Me.Text50 & "*"") AND "
End If


'etc for other text boxes.

lngLen = Len(strSql) - 5 'Chop off the trailiing " AND ".
If lngLen > 0 Then
strSql = strcStub & " WHERE " & Left$(strSql, lngLen) & ";"
Else
strSql = strcStub & ";"
MsgBox "No criteria"
End If

'Finally, show the search results in this form.
Me.RecordSource = strSql
End Sub

I used to only have Doctorsearchquery.Speciality.Speciality and added
Doctorsearchquery.Speciality_1.Speciality.
When I do a search and select a country in Me.Kombinationsruta58 and a
speciality in Me.Text50, it gives me results with more than one country. I
don't understand.
Anyone that has an idea?

Thanks

PS: I know it's a mess.
Click to expand...
Click to expand...
 
HI Dale,

Made it work!
Thanks for your help. Below is what I made of it.

If Not IsNull(Me.Text50) Then
strSql = strSql & "((([Läkare List].[Country ID]) Like """ &
Me.Kombinationsruta58 & """) AND (([Läkare List].specialisme) Like """ &
Me.Text50 & """)) OR ((([Läkare List].[Country ID]) Like """ &
Me.Kombinationsruta58 & """) AND (([Läkare List].specialisme2) Like """ &
Me.Text50 & """)) AND "
End If

Take care

Ben said:
Dale,

Thanks for your help. You made me realise that Doctorsearchquery was not
needed. I have it straight from the original table now.

If Not IsNull(Me.Text50) Then
strSql = strSql & "(Specialisme Like """ & Me.Text50 & "*"") OR "
End If

If Not IsNull(Me.Text50) Then
strSql = strSql & "(Specialisme2 Like """ & Me.Text50 & "*"") AND "
End If


If Not IsNull(Me.Kombinationsruta58) Then
strSql = strSql & "([Country ID] Like ""*" & Me.Kombinationsruta58
& "*"") AND "
End If



Both tests work great separately but once I put them together and search on
a specialisme in a country, it includes records from other countries too.

(PS: Contact database with Drs. A doctor can have a requested specialisme
(me.text50) in either specialisme or specialisme2)

Will working with varCriteria be easier if I have 16 criteria?
If yes, how to combine them?

Thanks


Dale Fye said:
Ben,

From you code, I'm not quite sure what
Doctorsearchquery.specialty_1.Speciality is, since this syntax doesn't make
any sense to me.

What happens when you replace:
If Not IsNull(Me.Text50) Then
strSql = strSql & "(Doctorsearchquery.Speciality.Speciality Like
""" & Me.Text50 & "*"") OR "
End If

If Not IsNull(Me.Text50) Then
strSql = strSql & "(Doctorsearchquery.Speciality_1.Speciality Like
""" & Me.Text50 & "*"") AND "
End If
Click to expand...

With:

If Not IsNull(Me.Text50) Then
strSql = strSql & "(" _
& "(Speciality.Speciality Like '" &
Me.Text50 & "*'") OR " _
& "(Speciality_1.Speciality Like '" &
Me.Text50 & "*'")" _
& ")" AND "
End If

I think your problem is that you need to process both of the tests agains
me.text50 as a single element and wrap that portion of the WHERE clause in
parenthesis.

BTW, I recommend creating your Where clause (I use varCriteria) separate
from the strSQL and then appending it to strSQL, something like:

strSQL = strSQL & (" WHERE " + varCriteria)

This way, if you don't define any criteria, varCriteria will be NULL, and
the sum (+) of " WHERE " + varCriteria will be NULL, thereby leaving the
WHERE clause out of the sql statement altogether.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.


Ben said:
Hi All,

About a year ago, I found a FROM - WHERE code here to build a search in a
continuous form. It worked great with many "Where" criteria but now that I
added a field that affects the full database, it acts strange.

Below is the code
Private Sub Kommandoknapp30_Click()
Dim strSql As String
Dim lngLen As Long
Const strcStub = "SELECT Doctorsearchquery.ID, Doctorsearchquery.Note,
Doctorsearchquery.Quicknote, Doctorsearchquery.Country.Country AS Uttryck1,
[Doctorsearchquery].[Country.Country] AS Uttryck2, Doctorsearchquery.Region,
Doctorsearchquery.[First name], Doctorsearchquery.[Last name],
Doctorsearchquery.[Name Practice/Hospital], Doctorsearchquery.[Male/Female],
Doctorsearchquery.[Language ID], Doctorsearchquery.Phonenumber,
Doctorsearchquery.Faxnumber, Doctorsearchquery.[E-mail address],
Doctorsearchquery.Street, Doctorsearchquery.[Postal Code + City],
Doctorsearchquery.Country, Doctorsearchquery.specialisme2,
Doctorsearchquery.Speciality.Speciality,
Doctorsearchquery.Speciality_1.Speciality FROM Doctorsearchquery"

'Build the WHERE clause from the non-Null boxes.

If Not IsNull(Me.Kombinationsruta58) Then
strSql = strSql & "([Doctorsearchquery].[Country].[Country] Like
""" & Me.Kombinationsruta58 & """) AND "
End If

If Not IsNull(Me.Text50) Then
strSql = strSql & "(Doctorsearchquery.Speciality.Speciality Like
""" & Me.Text50 & "*"") OR "
End If

If Not IsNull(Me.Text50) Then
strSql = strSql & "(Doctorsearchquery.Speciality_1.Speciality Like
""" & Me.Text50 & "*"") AND "
End If


'etc for other text boxes.

lngLen = Len(strSql) - 5 'Chop off the trailiing " AND ".
If lngLen > 0 Then
strSql = strcStub & " WHERE " & Left$(strSql, lngLen) & ";"
Else
strSql = strcStub & ";"
MsgBox "No criteria"
End If

'Finally, show the search results in this form.
Me.RecordSource = strSql
End Sub

I used to only have Doctorsearchquery.Speciality.Speciality and added
Doctorsearchquery.Speciality_1.Speciality.
When I do a search and select a country in Me.Kombinationsruta58 and a
speciality in Me.Text50, it gives me results with more than one country. I
don't understand.
Anyone that has an idea?

Thanks

PS: I know it's a mess.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top