Logical error

[tiku]

i have this logical error in my program. i will like the program to work like this--> if user only keys in CUS (customer name), the program will search all the entries under customer name which matches with what user have key in... if user keys in CUS and PROJ(projectname), program will find all those entries which matches both CUS and PROJ..etc.. below is the only comparsion statement to determine what to output... i tried using OR instead of AND, result wasnt correct..

Do Until rs.EOF


If IsNull(rs("Bidlog")) Then
BL = "na"
Else
BL = rs("Bidlog")
End If

If IsNull(rs("Customer")) Then
CUS = "na"
Else
CUS = rs("Customer")
End If


If IsNull(rs("MFGPN")) Then
MFG = "na"
Else
MFG = rs("MFGPN")
End If

If IsNull(rs("ProjectName")) Then
PROJ = "na"
Else
PROJ = rs("ProjectName")
End If


strinsert = "INSERT into PCEfinal (DbDate,Bidlog,FileDate,Customer,ProjectName,MFGPN,CUSPN,Cmdty,Description,Mfg,VndPN,Vnd,PriceUSD,SelPrice,CStatus,Remarks)" & _
"SELECT DbDate, Bidlog,FileDate, Customer, ProjectName, MFGPN, CUSPN,Cmdty, Description, Mfg, VndPN, Vnd, PriceUSD, SelPrice, CStatus, Remarks" & _
"FROM PCEquoteDB " & _
"WHERE Bidlog LIKE '" & BL & "' OR Customer = '" & CUS & "' ProjectName = '" & PROJ & "' OR MFGPN= '" & MFG & "';"
'Bidlog LIKE '" & BL & "' and Customer = '" & CUS & "' and ProjectName = '" & PROJ & "'and MFGPN = '" & MFG & "';"

DoCmd.RunSQL strinsert


rs.MoveNext
Loop
rs.Close

i have some doubts under strinsert INSERT . i will like to ask will the rest of the comparsion statement (example : PCEquoteDB.[ProjectName] = '" & PROJ & " )works like "LIKE" statement as i only key in once INSERT ??

 

[RainLover]

Have a look at the attached.

Hope this helps.

 

[tiku]

thank u Rain for the attachment.. but the program wasnt what im looking for

my program works in a way that the sub form is a table that allows user to key in multiple entries at the same time.. example user wanna find info on customername AMD and 3M... the program will output all information of AMD and 3M in a table.. i have tried searching sample program.. but none allows multiple rows of search entries...

 

[RainLover]

I believe that what you are needing is a List Box.

I don't have any samples for you and it is not something I have had a lot of experience in.

Search "List Box" for help or wait for someone else to help you.

 

[vbaInet]

First of all, what are you doing with the INSERT statement in your code? I thought you said it's a search form, i.e. a SELECT statement? Return records from a query that is based on the search criteria.

Secondly, have you actually gotten one search string working before thinking about two?

Finally, what is the delimeter for the search string?