Hello All:

I am trying to use parameters in combo boxes to get search criteria for a form. I got all of the text fields working fine but I can't get a number and date field to work. Here are my variables statements:

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

I am just selecting the RecordNumber (1) and/or EnteredDate (07/12/2007) in a drop down from a combo box but I get the data mismatch error. The fields in the table are Autonumber and DateTime respectively.

Any ideas??

Thanks,
Tony

Try this replacement code...


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

The + symbol is used for math. The & is for concatenation. Since both fields are of different types you will get an error when you + them together.

Hope this helps.

This isn't working. I tried adding the record number by itself and rem'd out the [EnteredDate] and the record doesn't pull up anything when I select a record number in the dropdown. If I use both [RecordNumber] and use the [EnteredDate] it doesn't pull up either. I am getting runtime 3075.

The query printout is:

Syntax error in Date in query expression [RecordNumber] = 3 AND [EnteredDate] = ##

This looks like it isn't picking up a value:

Me![scboEnteredDate]

Use Me. and see is the combo box auto completes. Then use that name. For example, if you type in Me.scbo it should auto complete and give you the combo box names on your form.

Then instead of passing the value directly to the string, you should be doing some value checking. For example:

Dim dtDate As Date

If IsNull(Me.scboEnteredDate) Or Me.scboEnteredDate = "" Then
   MsgBox "Please select a date.", vbCritical
   Exit Sub
Else
   dtDate = Me.scboEnteredDate
End If

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

Did that help any?



mmcdonal

I have never had a problem with the values for the RecordNumber and EnteredDate displaying in the dropdown of the combo box The problem has been data mismatch errors.

When i tried it your way previously I still got the values in the dropdown but didn't return anything using the [RecordNumber] and using the [EnteredDate] gave the syntax error.

With your new suggestion, nothing is displayed in using either/or both parameters but no errors.

Thanks for all of your help.
Tony

Now I am not sure what you are doing.

Are you trying to pass parameters to a form?

If so, pass the parameters to a query, and then open the form on the query instead. Take the values in the query criteria from the first form.

Did this help? I am not sure why you are talking about combo boxes not displaying data. Also, it seems to me that if you are using a PK as a parameter, then no other parameters are needed.

mmcdonal

I was talking about the data displayed in the combo boxes because I misread your message..Sorry.

Here is the code snippet from the subroutine. Maybe it will show you something. You have to remember that it works great for the text strings just not the numbers or date.

Tony
================================================== =====================

Private Sub scmdSearch_Click()
    Dim db As Database
    Dim QD As QueryDef
    Dim where As Variant

    Set db = CurrentDb()

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

    where = Null
    where = where & " AND [RecordNumber] = " & Me![scboRecordNumber]
    where = where & " AND [EnteredBy] = '" + Me![scboEnteredBy] + "'"
    where = where & " AND [EnteredDate] = #" & Me.scboEnteredDate & "#"
    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] + "'"
    'where = where & " AND [RequestedDate] = + Me![scboRequestedDate] + "
    where = where & " AND [ChangeApproved] = '" + Me![scboChangeApproved] + "'"
    'where = where & " AND [ChangeApprovalDate] = + Me![scboChangeApprovalDate] + "
    where = where & " AND [AssignedAnalyst] = '" + Me![scboAssignedAnalyst] + "'"
    'where = where & " AND [CompletedDate] = + Me![scboCompletedDate] "

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

End Sub

Again, if you are taking the recordnumber field, which I assume is a PK as a parameter, then why take any other parameters since this one parameter alone will only return one record anyway?

That would be

Dim iRN As Integer

iRN = Me.RecordNumber

"SELECT * FROM BHCS_Change_Request WHERE [RecordNumber] = " & iRN

Note no single quotes with a number.

mmcdonal

Because there are going to be numerous records in the database and they might not want to look up via a record number but that option is there. They might want to look up all the records that were entered on a specified date or all the records from a requesting facility.

As I mentioned it works great if I comment out the [scboRecordNumber] line and all of the scbo[*Date] lines.

Yes the record number is the PK and it is an 'autonumber'


Thanks,
Tony

Okay, so you want to do a "query by example" screen - all of the combo box entries are possible search criteria, but the user is not expected to choose values for all of them, just the ones he wants - is that right?

If so, then I can understand why your current code is failing to return any records. You have specified each and every combo box in the query - so the ones that the user does not populate will evaluate to an empty string. There will of course be no records on your database where most of the field values are empty!

What you need is to check for an entry from the user before adding each clause to the WHERE predicate. In simpler terms, something like this:

    where = where & " AND [RecordNumber] = " & Me![scboRecordNumber]
    If Me![scboEnteredBy].ListIndex <> -1 Then where = where & " AND [EnteredBy] = '" + Me![scboEnteredBy] + "'"
    If Me.scboEnteredDate.ListIndex <> -1 Then where = where & " AND [EnteredDate] = #" & Me.scboEnteredDate & "#"

and so on.

Hope this helps!

Richard