Multiple Lookup - Or comand Problem

[mikebaldam]

Hi,

Ive got a Access Form which I'm using to search a table and display the reuslts on screen.

Users can enter data and it will search an Address Table and display the matching values below in an auto generated list box.

I've been frankinstiening some different VB to get it going and it all works fine .....except I now would like to be able to get address4 field to be able to search address3 field AS WELL as the address4 field.

Maybe it'll become a little clearer if I show you what I have so far......

--------------------------------------------------------------------------------
Private Sub cmdSearch_Click()
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String


'Constant Select Address4ment for the RowSource
strSQL = "SELECT tblAddress.ID, tblAddress.Address1, tblAddress.Address2, tblAddress.Address3, tblAddress.Address4, tblAddress.Postcode " & _
"FROM tblAddress"

strWhere = "WHERE"

strOrder = "ORDER BY tblAddress.ID;"


'Set the WHERE clause for the Listbox RowSource if information has been entered into a field on the form
If Not IsNull(Me.txtAddress1) Then '<--If the textbox txtAddress1 contains no data THEN do nothing
strWhere = strWhere & " (tblAddress.Address1) Like '*" & Me.txtAddress1 & "*' AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If

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

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

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

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

'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

'Pass the SQL to the RowSource of the listbox

Me.lstCustInfo.RowSource = strSQL & " " & strWhere & "" & strOrder

End Sub
--------------------------------------------------------------------------------

I'm not too hot on VB (but slowly starting to get a little sinkning though)

Any help would be most greatfull appreciated..

Mike

________
vaporizer reviews

 

[EMP]

'Remove the last AND from the SQL statment 
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

Since the code needs to remove the last five characters from strWhere, you have to add a space after each of the five AND in the code, i.e.

strWhere = strWhere & " (tblAddress.Address1) Like '*" & Me.txtAddress1 & "*' AND "

strWhere = strWhere & " (tblAddress.Address2) Like '*" & Me.txtAddress2 & "*' AND "

... etc.

 

[mikebaldam]

It already works... but I want to add a search routin

Sorry, I dont think I explained things fully,

The code I have works fine.

What I would like to do is modify it so that on the Address4 lookup it will also pull any matching (or similar) values from Address3.




Thanks

Mike
________
extreme q

 

[mikebaldam]

Anyone?

Ok.... Can anyone think of another way to get this pulling out the info...?

At the moment the way the search works is by pulling any similar fileds into a list box displayed on a form.

Thats greatb but I'd like to take it one step further.

What I want to be able to do is where address4 is null (in tbladdress) grab any thing similar in tbladdress.address3 and display that too in a list box (with the other ones if possible).

I'm been playing with some OR and Null statements but dont seem to be able to get it even close to working.

A helpful pointer in the right direction would be HUGELY appriciated.

Cheers

Mike
________
weed vaporizer