Error checking and multi field searching

[thsoundman]

Morning all,

Quick question... I figured out how to make all the searching items work... individually. The problem is is that I want to be able to search with two fields at a time. For example, say i have mo, date, job, pay and i want to seach for the MO and JOB and query the results accordingly. I can't get it to do that. Would I have to make a large If statement? I'm not entirely sure. Also now my problem is that it won't execute my first if statement or I should say it ignores it all togeather. I know it executes the code in the order it is written. If someone could shed some light I would be much apprecative. As always here is the code and attached is the program. What I'm working on is in the query table.

I also would like to be able for it to say if the fields MO and JOB do not comeup witha result for a messege to display there is no result. Or if the field is null not to do anything. I tried doing that in some of my code to no success perhaps I did somthing wrong.

Have a great day!


Dim strSQL As String
Dim sqlStringWHERE As String
Dim strhead As String
Dim strJOIN As String

' strSQL = "SELECT * from Panel WHERE MO like " & Chr$(39) & "*" & Me.qMO & "*" & Chr$(39)
strJOIN = " AND "
strhead = "SELECT * from Panel "

If Len(Me.qMO & vbNullString) Then
sqlStringWHERE = "WHERE [MO] like " & Chr$(39) & "*" & Me.qMO & "*" & Chr$(39)
Else
sqlStringWHERE = "WHERE [MO] = " & Chr$(39) & Me.qMO & Chr$(39)

sqlStringWHERE = sqlStringWHERE & strJOIN

End If

If Len(Me.qCode & vbNullString) Then
If Len(sqlStringWHERE) = 0 Then
sqlStringWHERE = "WHERE "
End If

sqlStringWHERE = "WHERE

 like " & Chr$(39) & "*" & Me.qCode & "*" & Chr$(39)
    Else
    sqlStringWHERE = "WHERE [CODE] = " & Chr$(39) & Me.qCode & Chr$(39)
    
    sqlStringWHERE = sqlStringWHERE & strJOIN
End If
    

strSQL = strhead & sqlStringWHERE

 

[thsoundman]

Update: I fixed the issue of being able to search one field or the other field. But I still can't figure out why it won't search both fields. Also if i put nothing in it just gives me an error. How Can I check for that error?

 

[EMP]

To dynamically build an SQL string in VBA, see this link:-
http://support.microsoft.com/kb/210242/en-us

Alternatively, you can use a query. See Jon K's sample and his Note (2) in this thread:-
http://www.access-programmers.co.uk/forums/showthread.php?t=103312

^

 

[CraigDolphin]

For searching the two fields, your SQL needs to look something like:

SELECT Panel.MO, Panel.tDate, Panel.Contractor, Panel.LName, Panel.FName, Panel.Code, Panel.Qty, Panel.Start, Panel.Finish
FROM Panel
WHERE (((Panel.MO) Like "*" & [Forms]![Query]![qMO] & "*") AND ((Panel.Code) Like "*" & [Forms]![Query]![qCode] & "*"));

I'm not sure why you feel you need to build this in VBA rather than just creating a regular query and referencing the controls on your forms as parameters.

 

[thsoundman]

grrr this is starting to get frustrating. I'm sure i have one thing misspelled or in wrong palce in my code. Can't seem to find it though. Perhaps someone can see somthing. I read over that example. I understand for the most part howit works. There are a few things in these examples that I don't understand.

what does
if Len(strSQLWhere) Then
sstrSQLWhere = Left$(strSQLWhere, Len(strSQLWhere) - (len(strJoin)-1)

do?


Feel free to point out dumb mistakes as programming is not my speciality.

 

[Newbie100]

Since Last Name is a text box on your search form, I presume you will extend your query criteria to include this field.

However, as the LName field in your table contains some Null values, you cannot use Like in the criteria for this field if you want to be able to leave the last name text box on the form blank because 'Null Like *' is False, not True.

 

[thsoundman]

K i can fix that...my question is now how to I make it search using both fields not just one or theother... am i missing a line of code?

 

[thsoundman]

Sweet I made it work... turns out I had to add and statements to the beginning of my code for some reason. I thought i had added that in.