Simple Form Filter Based on Two Fields

[CharlieT302]

Hi Folks,

I would like to create a simple filter that works similar in concept to the "Advanced Filter" found in the Records menu.

Creating it on one field is fine, but I can't get it to work with two fields. I have two unbound objects in the form header: ID Search and ItemSearch.

My code (below) works fine with either of them, but not with both. I basically need an "And" statement in the criteria.

Here is what I have:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Sales Form"

stLinkCriteria = "Val([empid])=" & Me![Id Search]
DoCmd.RunCommand acCmdApplyFilterSort

Exit_Filter_Month_Click:
Exit Sub


This works fine. Note, the Val statement is there because the "empid" field is a text field, that the unbound field; ID Search, stores the user's entry as a number.

However, if I add:
And "Val([ItemNumber])=" & Me![Item Search] to the criteria statement, I get a type mismatch error.

All I need is an "And" statment to be able to match both fields. This code is attached to a push button.

Any Ideas? This seems like it should be a simple matter.

Thanks

 

[PHV]

And what about this ?
stLinkCriteria = "Val([empid])=" & Me![Id Search] & " And Val([ItemNumber])=" & Me![Item Search]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CharlieT302]

Thanks,

This worked fine. Thank you.

However, I have a similar problem using a date field:

stLinkCriteria = "[incdate]=" & Me![IncSearch]

Incdate is the actual field, and IncSearch is the unbound object providing the filter.

When I use this, no records come up.

 

[PHV]

stLinkCriteria = "[incdate]=#" & Format(Me![IncSearch], "yyyy-mm-dd") & "#"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CharlieT302]

Thanks PHv,

That was great. Why did that work?

Could you briefly explain the additions into the code. I haven't seen that before.

 

[PHV]

JetSQL rules for constants delimiters:
NumericValue
'TextValue'
#DateTimeValue#

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CharlieT302]

I am a little confused. What was the purpose of the # signs?

I am assuming that the Format code you put in should match the way the data is stored in the table?

ie. "yyyy-mm-dd" vs. "mm-dd-yy

 

[PHV]

You confuse data storage and data presentation.
In the table a DateTime field is stored as floating point number, integral part being the date (number of days since dec 30, 1899) and decimal part being the number of seconds since midnight.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886