Multiple Select Search - Alternative Methods?

  • Thread starter Thread starter mikebaldam
  • Start date Start date
M

mikebaldam

Hi,

I've been working on this now for a while its half sorted but - It
should be straight forward but...

Ok, I have a form were the user can lookup Address details by entering
data clicking a button which then pulls the relevant data into a list
box on screen. This all works fine.

Now I have to get Address4 and Address3 to be inter-searchable (ie that
it will pull any address matching search criteria in either field).....
I'm just not sure how to do it....

The options I see are to either try and update my VB (which is a mix
and match of alot of help sites VB) or to make a conactinated field of
Address3 and Address4 to be able to search via that.....

I'll show you the code I've got so far
----------------------------------------------
Dim strSQL As String, strOrder As String, strWhere As String

strSQL = "SELECT tblAddress.ID, tblAddress.Address1,
tblAddress.Address2, tblAddress.Address3, tblAddress.Address4 " & _
"FROM tblAddress"

strWhere = "WHERE"

strOrder = "ORDER BY tblAddress.ID;"

' Theres one of these (below) for each of the strSQL Address1,2,3,4

If Not IsNull(Me.txtAddress4) Then
strWhere = strWhere & " (tblAddress.Address4) Like '*" & Me.txtAddress4
& "*' AND"
End If

----------------------------------------------------------
Is there a way of pulling all data from me.txtAddress4 from
tblAddress.Address4 or Address3 ?????

Or an alternative solution..>?

I would be grateful for any help....(before I pull out all my hair)

Cheers


Mike

File Attached: http://www.MSAccessForum.com.com/forums/attachment.php?postid=96562 (multisearch.zip)
 
strWhere = strWhere & " ((tblAddress.Address4) Like '*" & Me.txtAddress4
& "*' OR (tblAddress.Address3) Like '*" & Me.txtAddress4
& "*')"


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



mikebaldam said:
Hi,

I've been working on this now for a while its half sorted but - It
should be straight forward but...

Ok, I have a form were the user can lookup Address details by entering
data clicking a button which then pulls the relevant data into a list
box on screen. This all works fine.

Now I have to get Address4 and Address3 to be inter-searchable (ie that
it will pull any address matching search criteria in either field).....
I'm just not sure how to do it....

The options I see are to either try and update my VB (which is a mix
and match of alot of help sites VB) or to make a conactinated field of
Address3 and Address4 to be able to search via that.....

I'll show you the code I've got so far
----------------------------------------------
Dim strSQL As String, strOrder As String, strWhere As String

strSQL = "SELECT tblAddress.ID, tblAddress.Address1,
tblAddress.Address2, tblAddress.Address3, tblAddress.Address4 " & _
"FROM tblAddress"

strWhere = "WHERE"

strOrder = "ORDER BY tblAddress.ID;"

' Theres one of these (below) for each of the strSQL Address1,2,3,4

If Not IsNull(Me.txtAddress4) Then
strWhere = strWhere & " (tblAddress.Address4) Like '*" & Me.txtAddress4
& "*' AND"
End If

----------------------------------------------------------
Is there a way of pulling all data from me.txtAddress4 from
tblAddress.Address4 or Address3 ?????

Or an alternative solution..>?

I would be grateful for any help....(before I pull out all my hair)

Cheers


Mike

File Attached: http://www.MSAccessForum.com.com/forums/attachment.php?postid=96562
(multisearch.zip)
Click to expand...
 
Back
Top