Creating a filter query

[yanni12]

I have a date field in a certain query. I am creating a advanced filter Query to search one date less than another. I was able to accomplish this. The problem I am having is the blank data fields. In my table the field has dates but a few blanks. I am trying to query a date less than another date or a blank. I am having trouble pulling the blank items in. How do I get a query to filter by blank fields also?

[NeoPa]

Without the SQL of your query your question doesn't make enough sense. The SQL's always a good idea to post so why not do that and we'll see if we can work out what you meant to ask.

[yanni12]

I in the Advanced filter section design view. For example
field: Asset status report date
Table: Special Servicing loan query
Sort:
Show:
Criteria: < [Most recent Special servicer transfer date]
or: ???? this is where i want to also search the column for blank fields

[NeoPa]

From the Design view select View | SQL. Copy and paste that SQL into a post here and we'll have some clues to work with.

[yanni12]

SELECT [Special Servicing Loan Performance Filter].[Asset Status Report Date], *
FROM [Special Servicing Loan Performance Filter]
WHERE ((([Special Servicing Loan Performance Filter].[Asset Status Report Date])<[Most Recent Special Servicer Transfer Date] Or ([Special Servicing Loan Performance Filter].[Asset Status Report Date])=IsEmpty("«varexpr»")));

[NeoPa]

Try changing that to :

SELECT [Special Servicing Loan Performance Filter].*
FROM   [Special Servicing Loan Performance Filter]
WHERE  (Nz([Asset Status Report Date],#1/1/1900#)<[Most Recent Special Servicer Transfer Date])

Simply copy and paste from this page into your query in SQL view mode. When you switch to design mode it should show how the new version looks.

[yanni12]

This worked well. Thank you for your help.

[NeoPa]

Pleased to help Yanni :-)