form query with multiple inputs

  • Thread starter Thread starter mrs
  • Start date Start date
M

mrs

Hello all,

This is going to be a little long.

I created a form using Form Wizard using two tables. tblEvents an
tblProductType. tblEvents has ALL fields included and tblProductTyp
only one, ProductType. Both tbls are joined.

When creating the form, I tell the wizard to view the data b
tblProductType and the subform is a datasheet.

I removed the default tblProductType text box and replaced it with
combo box that list all ProductTypes. This works great. However,
want to add two more controls that are MSComCtl2.DTPicker. I then ad
a buttton to the form and delete the OnChange event from the drop dow
box and add a OnClick event to the button. When I press the button
want to search tbleEvents with the dropdown box info and bot
MSComCtl2.DTPicker.

Here is the code:

Private Sub Command26_Click()
' Find the record that matches the control.
Dim rs As Object
Dim mystartdatestr, mycompdatestr, temp As String

mystartdatestr = DatePart("m", [mystartdate]) & "/" & DatePart("d"
[mystartdate]) & "/" & DatePart("yyyy", [mystartdate])
mycompdatestr = DatePart("m", [mycompdate]) & "/" & DatePart("d"
[mycompdate]) & "/" & DatePart("yyyy", [mycompdate])

Set rs = Me.Recordset.Clone
rs.FindFirst "([ProductID] = " & Str(Me![Combo13]) & ") AN
([tblEvents].[StartDate] = " & mystartdatestr & ") AN
([tblEvents].[CompletedByDate] = " & mycompdatestr & ")"
Me.Bookmark = rs.Bookmark
End Sub

I get a message that says it doesn't recogniz
([tblEvents].[StartDate]. How can I remedy this?

Thank you in advance
 
A possible problem in you SQL statement is the delimiters you are using, or
rather not using.

A string value has to be surrounded by single or double quotes, althought I
presume Combo13 is a number. Dates have to be surrounded by # characters.
Try adding these to your date strings.

Rod Scoullar.
 
I removed the default tblProductType text box and replaced it with a
combo box that list all ProductTypes. This works great. However, I
want to add two more controls that are MSComCtl2.DTPicker. I then add
a buttton to the form and delete the OnChange event from the drop down
box and add a OnClick event to the button. When I press the button I
want to search tbleEvents with the dropdown box info and both
MSComCtl2.DTPicker.
Click to expand...

Date/Time field criteria must be in m/d/y format (as you have done)
and must be delimited by # characters. Try:

mystartdtstr = Format([mystartdate], "\#mm/dd/yyyy\#")

The Format() function will build a text string (more simply than your
string operations).
 
Back
Top