Help me exec this SQL statement

[shinichi_nguyen]

Hi all. I'm creating a simple database for managing parts of machines. I have one form, search form with 4 blank textbox for user to enter. I wrote this code to generate the SQL Select statement. How do I display the result? The Access file only have one table: Parts. Structure as below:
Parts (SerialNum, Description, UseOnMSS, DateOf Failure, DescriptionOfFailure)
key is SerialNum

PS: My programming and VBA skill is about 2/10 so I might wrote this code in silly way, if possible help me improve it. Much appreciate.

Dim SQLstring, myCriteria As String
Dim tSerialNum, tUsedOnMSS, tDateOfFailure, tDescriptionOfFailure As Variant

tSerialNum = txtSerialNum.Value
tUsedOnMSS = txtUsedOnMSS.Value
tDateOfFailure = txtDateOfFailure.Value
tDescriptionOfFailure = txtDescriptionOfFailure.Value
SQLstring = "Select * FROM Parts WHERE "

myCriteria = ""
            
            If tSerialNum <> "" Then
                myCriteria = "SerialNum ='" & tSerialNum & "' OR "
           Else
                myCriteria = myCriteria & ""
            End If
      
            If tUsedOnMSS <> "" Then
               myCriteria = myCriteria & " UsedOnMSS ='" & tUsedOnMSS & "' OR "
            Else
                myCriteria = myCriteria & ""
            End If
  
            If tDateOfFailure <> "" Then
               myCriteria = myCriteria & " DateOfFailure='" & tDateOfFailure & "' OR "
           Else
                myCriteria = myCriteria & ""
            End If
 
            If tDescriptionOfFailure <> "" Then
                myCriteria = myCriteria & " DescriptionOfFailure='" & tDescriptionOfFailure & "'"
            Else
                myCriteria = myCriteria & ""
            End If

If myCriteria <> "" Then
    
        If Right(myCriteria, 2) = "OR" Then
            myCriteria = Left(myCriteria, Len(myCriteria) - 3)
            SQLstring = SQLstring & myCriteria
        End If
Else
            SQLstring = "Select Parts.SerialNum, Parts.UsedOnMSS Parts.DateOfFailure FROM Parts"
End If

' Now how to put this query string into display???

 

[pbaldy]

What is it you're trying to accomplish? That will determine what to do with the SQL string. One common use would be to set the record source of a form to it. A couple of thoughts. What do you think this is doing?

myCriteria = myCriteria & ""

Hint...nothing. None of those Else clauses are necessary. You test for "OR" at the end of the string, but in actuality it will be "OR " (note the space). This should help you debug the string as you finish:

http://www.baldyweb.com/ImmediateWindow.htm

 

[shinichi_nguyen]

It's a mess, at least for me, I'm sorry I don't have much experience in programming. What I was trying to do is use the 4 blank text box as criteria in the WHERE clause in the SQL statement. That's why I tried to build up the myCriteria string.
But right now I'm confused again because I might have to add in the AND or OR depending on what the user want to see (Eg: they want to see the part on the machine number 123 that failure on the day 1/4/2012).
How do I put the SQL statement, say for example eventually I have my SQL statement built up like this:
Select * from Parts where serial number="1235"

If I copy it and go to Query in Access and create a new query and paste this in, then i can see the result, but how to I let the user see that result when they use my Search form and put in 1235 in the Serial field?

 

[pbaldy]

Like I said, one way is to set the record source of a form:

Forms!FormName.RecordSource = SQLstring

That form would already be set up to display the fields. The sample db here demonstrates one method of what you're trying to do:

http://www.baldyweb.com/BuildSQL.htm