thank you for the reply.

If I select multiple entries in the combo boxes it will still select the right record. It doesn't assign an empty string to all of them. That isn't the problem. The problem is pulling up records by RecordNumber which is a number and EnteredDate which is a DateTime.


Thanks,
Tony

If you are trying to do it this way, then is seems to me you are taking data from a query that has more than one table. Is that correct? For example, CustomerID and OrderDates? Where the same customer might have more than one order. If you are taking data from only one table, then the user should only ever need to select the RecordNumber.

If you are trying to get something like CustomerID and OrderDates, then the problem you are having is with the OrderDate field.

First, is the OrderDate field a Date/Time field, or is it a text field? If it is a text field, then:

Dim sDate As String

sDate = Me.scboEnteredDate

where = where & " AND [EnteredDate] = '" & sDate & "'"


If it is a Date/Time field then (assuming that there are two columns for the combo box, and it is binding to a PK, not the date field):

Dim dtDate As Date

dtDate = Me.scboEnteredDate.Column(1)

where = where & " AND [EnteredDate] = #" & sDate & "#"


At least note in your code that you have added an additional space to the closing # sign which will also mess things up. You have:

where = where & " AND [EnteredDate] = #" + Me![scboEnteredDate] + " #"

This should at least be:

where = where & " AND [EnteredDate] = #" + Me![scboEnteredDate] + "#"

Did that help?

mmcdonal

mmcdonal and Richard:

Thank you both for your help! I was able to get it working now. What I had to do is add If statements to the Date/Time and Number Fields. What it was doing was if I selected the RecordNumber and didn't select a date then the statement was

'where [RecordNumber] = 3 and [EnteredDate] = ## and
[CompletedDate] = ##'

That was screwing it up. Anyway thank you for all of your help. I would have figured it out but it would have taken alot longer.

Here is the code for my subroutine:
================================================== ==================
Private Sub scmdSearch_Click()
    Dim db As Database
    Dim QD As QueryDef
    Dim where As Variant

    Set db = CurrentDb()

    ' Delete the existing dynamic query; trap the error if the query
    ' does not exist.

    On Error Resume Next
    db.querydefs.Delete ("Dynamic_Query")
    On Error GoTo 0

    where = Null
    If Not IsNull(Me![scboRecordNumber]) Then
        where = where & " AND [RecordNumber] = " &
                Me![scboRecordNumber]
    End If
    where = where & " AND [EnteredBy] = '" + Me![scboEnteredBy] + "'"
    If Not IsNull(Me![scboEnteredDate]) Then
        where = where & " AND [EnteredDate] = #" &
                Me![scboEnteredDate] & "#"
    End If
    where = where & " AND [RequestingFacility] = '" +
            Me![scboRequestingFacility] + "'"
    where = where & " AND [RequestedBy] = '" + Me![scboRequestedBy] +
            "'"
    where = where & " AND [CurrentContact] = '" +
            Me![scboCurrentContact] + "'"
    where = where & " AND [OrdersetName] = '" + Me![scboOrdersetName]
            + "'"
    where = where & " AND [OrderItemName] = '" +
            Me![scboOrderItemName] + "'"
    where = where & " AND [OrderSection] = '" + Me![scboOrderSection]
            + "'"
    where = where & " AND [OrdersetFormat] = '" +
            Me![scboOrdersetFormat] + "'"
    where = where & " AND [OrdersetType] = '" + Me![scboOrdersetType]
            + "'"
    If Not IsNull(Me![scboRequestedDate]) Then
        where = where & " AND [RequestedDate] = #" &
        Me![scboRequestedDate] & "#"
    End If
    where = where & " AND [ChangeApproved] = '" +
            Me![scboChangeApproved] + "'"
    If Not IsNull(Me![scboChangeApprovalDate]) Then
        where = where & " AND [ChangeApprovalDate] = #" &
                Me![scboChangeApprovalDate] & "#"
    End If
    where = where & " AND [AssignedAnalyst] = '" +
            Me![scboAssignedAnalyst] + "'"
    If Not IsNull(Me![scboCompletedDate]) Then
        where = where & " AND [CompletedDate] = #" &
                Me![scboCompletedDate] & "#"
    End If

    MsgBox "Select * from Change_Request " & (" where " +
            Mid(where, 6) & ";")
    Set QD = db.CreateQueryDef("Dynamic_Query", _
    "Select * from Change_Request " & (" where " + Mid(where, 6)
     & ";"))
    DoCmd.OpenQuery "Dynamic_Query", , acReadOnly

End Sub

================================================== ================