Multiple filters using AND?

[rainman89]

Hi all,
i tried to create a filter for a user to see the information that they have entered on that particular day. they click a button and it will sort by their ID number and the date.. i have the ID portion working properly, since it was easily done with the button wizard(wish it was all that simple) but i wanted to add the date portion too it. i tried

Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "DARview"
 
    stLinkCriteria = "[employeeID]=" & Me![employeeID] And "[dateAdded]=" & "#" & Me![dateAdded] & "#"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

but as u may be able to tell i am getting a type mismatch.. can date not be stored as a string?

 

[MarkK]

Try...

DoCmd.OpenForm _
   "DARView", , , _
   "employeeID = " & Me.employeeID & " " & _
   "AND dateAdded = #" & Me.dateAdded & "#"

 

[pbaldy]

The word "And" is dangling out in the middle of nowhere. It needs to be inside the quotes.

 

[rainman89]

Thanks lagbolt, but im still recieving the type mismatch error

DoCmd.OpenForm "DARview", , , "employeeID = " & Me.employeeID & " " & "AND dateAdded = #" & Me.dateAdded & "#"

 

[suvarna]

Dear rainman89,
the string should be like this
stLinkCriteria = "[employeeID]=" & Me![employeeID] And "[dateAdded]=" & "'#" & Me![dateAdded] & "#'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Observe that the date field value should be enclosed in single quotes

 

[pbaldy]

Suvarna, that is simply incorrect.

Rainman, what is the data type of employeeID in the table? If it's text, it would need to be surrounded by single quotes. The date value needs to be surrounded by #, as you already have.

 

[johndoomed]

Does your code work with either filter? With filter on employeeID or date on its own?

I've had problems with different date formats. You might have the same problem.

I use this module to ensure the date is US-format. Which you need in order to use date in filter.

Function MakeUSDate(x As Variant)
    If Not IsDate(x) Then Exit Function
    MakeUSDate = "#" & Month(x) & "/" & Day(x) & "/" & Year(x) & "#"
End Function

The code on the commandbutton:

    Date1 = MakeUSDate([datefield1])
    Date2 = MakeUSDate([datefield2])

    Me.Filter = "[Dato] Between " & Date1 & " and " & Date2

This as an example.

 

[rainman89]

Rainman, what is the data type of employeeID in the table? If it's text, it would need to be surrounded by single quotes.

employeeID is a number field. and works when it is used alone. See below.

Does your code work with either filter? With filter on employeeID or date on its own?
I've had problems with different date formats. You might have the same problem.

Yes johndoomed,
the filter works on the employeeID portion.
The date, however is a different story. I thought about the date formats last nite while laying in bed,(Sad that that is all i can think of before going to sleep) and will try it this morning!
I will update on my success/failure. thank you

UPDATE: FAILURE!!
Made sure they were in the same format... but still getting a type mismatch

would it have something to do with the fact that the employeeID is in the main form while the dateAdded is inthe subform??? thinking that it does

 

[pbaldy]

Yes, you would have to refer to the subform:

http://www.mvps.org/access/forms/frm0031.htm

 

[rainman89]

Thanks pbaldy, another useful website!
but to no alas

    stLinkCriteria = "[employeeID] = " & Me.Parent.employeeID & " " & "AND [dateAdded] = #" & Me.DARsubform.Form.dateAdded & "#"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

gives me " is an invalid name

 

[pbaldy]

Where exactly is this code in relation to the 2 values? You're referring to a parent and a subform of the object containing the code; is that correct?

 

[rainman89]

This code is in the onclick button on another form. they click the button to see their entries for the day

 

[pbaldy]

Not sure we're on the same page, but either way it would be safest to switch to the full form reference to both of those values. IOW, instead of

Me.Whatever

use

Forms!Whatever

The full references are also on that link.

 

[rainman89]

Private Sub Command42_Click()
On Error GoTo Err_Command42_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "DARview"
    stLinkCriteria = "[employeeID] = " & Me.Parent.employeeID & " " & "AND [dateAdded] = #" & Me.DARsubform.Form.dateAdded & "#"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
   
Exit_Command42_Click:
    Exit Sub
Err_Command42_Click:
    MsgBox Err.Description
    Resume Exit_Command42_Click  
End Sub

This is the code that is located in the onclick function of a button that is on my timesheet form. as u can see it calls the DARview form that has a DARsubform.
The information for the dateAdded is located in the DARsubform( ie there is a dateAdded field in the subform that is retrieved by a query) that is supposed to be linked to the dateAdded field in the timsheet form

 

[pbaldy]

You need to be referring to the employee id and date added on the form the code is in (the timesheet form), not the form being opened. That form isn't open yet, so how can you get values from it?

 

[rainman89]

IF that is the case then how come the employeeID will work?

 

[pbaldy]

You were originally referring to Me.employeeID, which would have been on the same form. You simply need to do the same thing with the date.

 

[pbaldy]

Can you post a sample db?

 

[rainman89]

I am missing something here. if i post my db would u look at it? or can i email it to u?

 

[pbaldy]

You must have missed this one:

Can you post a sample db?