VBA Help !

[Emma35]

Hi Guys......need a little help with some code i'm trying to use on a form.



This is part of the code i'm using in the OnClick event of a command button to perform a search of records in a table. If you look at the piece of code which reads 'If Len'...the textbox i'm referencing in the brackets will contain a numeral and i'm sure i need to substitute 'If Len' for something else ?. Anyone have any ideas ?


Any help would be greatly appreciated
Thanks

Private Sub cmdViewResults_Click()
strSQL = "1=1"
If Len(TxtAssessIDSearch) > 0 Then
    strSQL = strSQL & " AND Tbl_Master.[AssessmentID] = '" & TxtAssessIDSearch.Value & "'"
End If

[orange]

??? What are you trying to do in plain English?

What do you think strSQL is doing for you?
Do you get an error message?

When you show a snippet of code with little or no context, the best we can do is GUESS at what you are trying to do?
Do you know what the Len() function does?
http://www.techonthenet.com/access/f...string/len.php

[Emma35]

Thanks for the reply. I'm trying to search a table for certain records using this form which allows the users to enter various criteria (Assessment No, Date, Location) etc. The entire code is below. The table is called Tbl_Master. I'm also attaching a screenshot of the error message which occurs when i try to search the field [AssessmentID], which is numeric.

Private Sub cmdViewResults_Click()
strSQL = "1=1"
If Len(TxtAssessIDSearch) > 0 Then
    strSQL = strSQL & " AND Tbl_Master.[AssessmentID] = '" & TxtAssessIDSearch.Value & "'"
End If
If Len(cboBlockSearch) > 0 Then
    strSQL = strSQL & " AND Tbl_Master.[LocationBlock] = '" & cboBlockSearch.Value & "'"
End If
If Len(cboPlantSearch) > 0 Then
    strSQL = strSQL & " AND Tbl_Master.[DepartmentPlant] = '" & cboPlantSearch.Value & "'"
End If
If IsDate(TxtExactDate) Then
    strSQL = strSQL & " AND Tbl_Master.[AssessmentDate] = #" & _
                Format(TxtExactDate.Value, "mm/dd/yyyy") & "#"
End If
If IsDate(TxtDate1) Then 'check the control contains a date
  If IsDate(TxtDate2) Then 'check the control contains a date
    If CDate(TxtDate2) > CDate(TxtDate1) Then 'check the second control has a gdate newer than control 1
      strSQL = strSQL & " AND Tbl_Master.[AssessmentDate] BETWEEN #" & Format(TxtDate1, "mm/dd/yyyy") & "# AND #" & Format(TxtDate2, "mm/dd/yyyy") & "#"
    ElseIf CDate(TxtDate1) = CDate(TxtDate2) Then ' the dates are the same
      strSQL = strSQL & " AND Tbl_Master.[AssessmentDate] >= #" & Format(TxtDate1, "mm/dd/yyyy") & "#"
    Else 'control2 is earlier date thann control 1, so swap 'em as BETWEEN clause works from lowest ot highest value
      strSQL = strSQL & " AND Tbl_Master.[AssessmentDate] BETWEEN #" & Format(TxtDate2, "mm/dd/yyyy") & "# AND #" & Format(TxtDate1, "mm/dd/yyyy") & "#"
    End If
  Else 'we have just the one date
    strSQL = strSQL & " AND Tbl_Master.[AssessmentDate] >= #" & Format(TxtDate1, "mm/dd/yyyy") & "#"
  End If
End If
DoCmd.OpenReport "Rpt_SearchResults", acViewPreview, , strSQL

[Bob Fitz]

I don't think you need the single quotes if AssessmentID is numeric. Try:

If Len(TxtAssessIDSearch) > 0 Then
    strSQL = strSQL & " AND Tbl_Master.[AssessmentID] = " & TxtAssessIDSearch.Value
End If

[Emma35]

Thanks Bob that worked perfectly....took out the single quotes and everything's fine.


Cheers,
Emma

[Bob Fitz]

Your welcome. Glad to help.