Wildard in Query fed from a search form

[DeanFran]

I have built a search form to feed information to a query. The form uses combo boxes tied to table values, and all have wild cards built into them so if the user leaves the combo box they get all the records. I also have to text boxes representing start date and end date. I would like to allow the user to leave these blank and get all there as well. I have been looking through my one Access book, as well as searched all over the internet, but I cant seem to find the way to do this.

My filter criteria for the text based combo boxes are like this:

Like "*" & [Forms]![ReportDesignF]![Company] & "*"

My filter criteria for the Start and End Dates looks like this:

Between [Forms]![ReportDesignF]![StartDate] And [Forms]![ReportDesignF]![EndDate]+#11:59:59 PM#

In this case of the user leaves the date values blank, the query returns nothing. I would like to return all dates if that is the case. I am assuming it is my lack of knowledge of wild cards and how they work with date values.

 

[CJ_London]

how are you applying your filter? in the where parameter of the openquery command or within the query itself?

 

[DeanFran]

Within the query itself

 

[Galaxiom]

Add something like this to the Where clause

Or [Forms]![ReportDesignF]![StartDate] Is Null

 

[CJ_London]

or you can replace with

Like "*" & nz([Forms]![ReportDesignF]![Company]) & "*"

and

Between nz([Forms]![ReportDesignF]![StartDate]) And nz([Forms]![ReportDesignF]![EndDate],CDate("31/12/9999"))

Note there is no point adding the #11:59:59 PM# since it is still the same date and between returns true for mydate=enddate

 

[Galaxiom]

Note there is no point adding the #11:59:59 PM# since it is still the same date and between returns true for mydate=enddate

There would be a point if the table field includes a time component. Otherwise anything after the midnight at the beginning of the that day would be omitted.

 

[CJ_London]

true - I was assuming the table just had a date value without a time value

I take it back

 

[namliam]

For complicated searches like this dont reference the form like you are doing, instead do something called "filter by form"

have a look here if you can figure out where to go with this:
http://www.access-programmers.co.uk/forums/showthread.php?p=1300908#post1300908
Let me know any problems you run into.

 

[DeanFran]

Thanks for all the advice. So using the Is Null option works if the user leaves both the start and end dates empty. Now I'm pushing it to try and get it to work if the user enters a start date or an end date only. As a bit of background on this project, I'm early on in my learning curve with Access. At my job we track time devoted to work done on new projects, and the big boss presented us with and Excel spreadsheet to enter our time/project name/work category information in. I thought to myself "this is a perfect example of where a database would be a better option". I built one according to best practice as I understand them at this point. It works as I had hoped, the user sees no tables, or queries. All they see is a main navigation form with forms for work logging, managing companies, products, users and work categories, and summary reports for all of the above. The best feature from the bosses perspective IMO will be this work logging summary form, where he can create a custom report of work logged by company and/or product and/or user and/or work category and for certain dates As I say it all works, and I'll present it to him Monday. That said, this challenge of sorting out this query question regarding the dates is really pushing me past my present knowledge, which doesn't at this point include SQL at all, and almost no VBA. So the filter by form discussion was way over my head. Again thanks for the replies, I'll keep working on this, and checking back on this thread.

 

[namliam]

The filter by form, if you look at the sample in the thread really isnt all that involved...
Very limited SQL knowledge needed as well, (imnsho offcourse)

 

[DeanFran]

I'm going to dig into that discussion and attempt to understand it for sure. By the way, from your signature, I see the suggestion to add to a user's reputation by clicking on the scale icon. I don't see that option. Is this something new users don't see?

 

[CJ_London]

I'm pushing it to try and get it to work if the user enters a start date or an end date only

per my post

Between nz([Forms]![ReportDesignF]![StartDate]) And nz([Forms]![ReportDesignF]![EndDate],CDate("31/12/9999"))

 

[DeanFran]

CJ_London,

I can't believe I missed this in the thread. It works perfectly. So in this case adding the nz function is like saying either use the value entered or null?
From the MS Office Access Help web pages on Nz

The Nz function is useful for expressions that may include Null values. To force an expression to evaluate to a non-Null value even when it contains a Null value, use the Nz function to return zero, a zero-length string, or a custom return value.

After reading a bit about it, there appears to be a lot more to this function than that.

I don't really understand the CDate function other than it converts a text string to a date. If you don't mind can you explain what is going on here? Thank you so much for posting this.

 

[namliam]

the CDATE function makes sure that IF your Enddate is NULL you get a date, far far far in the future,
The nz in the first part basicaly makes the null value at the start be into the far far far past (30/12/1899 to be exact)
effectively "removing" the bottom or top end of the Between ... And ...

Making it Between "far far far in the past" and "far far far in the future" or what ever combo with "proper" dates... Ultimately though, filtering by a form and having your own code to manipulate SQL is very strong and will let you determine to the detail what you want to see.

 

[DeanFran]

Okay so basically this expression says if start date is null then it's "Date Zero" which for Office applications is December 30, 1899, and if end date is null make it Dec 31, 9999?

 

[namliam]

Okay so basically this expression says if start date is null then it's "Date Zero" which for Office applications is December 30, 1899, and if end date is null make it Dec 31, 9999?

Replace "Office Applications" by "MS Access" and you are correct, different (office) applications treat "date Zero" differently in excel for example it is 0/Jan/1900 or if you change a setting in the options 1/Jan/1904 (yes not 0/Jan/1904)

Dates (and times) are FUN in office

If you look at date (and time) in other applications Oracle, SQL Server, Unix and whatever else you can think of you will find a mariad of different ways of doing dates as well :banghead: Nothing beats a standard

 

[namliam]

Oh and please keep in mind that in MS Access and Excel and VBA dates are "required" to always be in US Format, to prevent any and all issues with it. Though CDate can manage it, to keep things uniform I would advice you to make it CDate("12/31/9999")

 

[CJ_London]

So in this case adding the nz function is like saying either use the value entered or null?

Not quite, what the nz function does is convert a null to the appropriate datatype in context - for text it is a zero length string (""), for numbers it is 0. There is another parameter you can use to substitute for these values. For example

nz(myField,"No connection") will return 'no connection' if myfield is null, otherwise it returns the myField value

nz(myField,-1) will return -1 (or true) if myField is null (regardless of whether myField is text or number) - equivalent of isnull(myField)

There are a number of 'C' functions to change datatypes Clng, CDate, CInt, CBool, etc. All they do is convert the text in the parameter to the appropriate datatype - subject to the string presenting properly - CDate("abc") would fail

Data is always of a particular type as defined in a table or Declared (e.g. in a dim statement, although there is a variant type which can be anything). Access vba and sql is quite good at converting different types to work in an equation e.g.

"A" & 1 & "B" will return A1B even tho' the 1 is numeric.

Dates are numeric and actually stored as a special form of double so

myNumber like "*10*"

will return all numbers with 10 in it (1034, 4310, etc)
but

myDate like "*10*"

will not return any october date or 10th of the month, but will return a list where there is a 12 in the numeric equivalant - today (10th Jan) is 41649 for example


So

Between nz([Forms]![ReportDesignF]![StartDate])

will return 0 (i.e. the 'earliest date' if a startdate has not been specified (note this will fail if the user enters a non date since StartDate is not null
And

nz([Forms]![ReportDesignF]![EndDate],CDate("31/12/9999"))

will return the highest possible date if enddate is null (don't think we will be around then, so you are pretty safe to use "31/12/2100" instead!) Will also fail if the user entersa non date since end date is not null

If you wanted to limit it to dates no later than today then you would use

nz([Forms]![ReportDesignF]![EndDate],Date())

Date returns the value of today

 

[Galaxiom]

Oh and please keep in mind that in MS Access and Excel and VBA dates are "required" to always be in US Format, to prevent any and all issues with it.

Though CDate can manage it, to keep things uniform I would advice you to make it CDate("12/31/9999")

The date handling capabilities actually come from Windows itself and are not as straightforward as "always US".

CDate defaults to the regional settings so applying it to a US formatted date string is not a good idea in regions with other formats.

Windows will handle that date because it recognises that it is only valid in US format but supplying a date that is valid in both dd/mm/yyyy and mm/dd/yyyy will result in the regional settings being applied.

Windows will also swallow an ISO date in any region if that is all that fits the pattern. The classic is a 29th Feb in a non leap year. For example "29/02/10" will be interpreted as 10th Feb 1929.

 

[Galaxiom]

Not quite, what the nz function does is convert a null to the appropriate datatype in context - for text it is a zero length string (""), for numbers it is 0.

That is the most prevalent Access/VBA myth of all time. It comes straight out of Microsoft's help pages. They have been informed of the mistake but don't apparently care.

There is no capability of a function to vary its return based on the context where it is applied.

Nz() actually returns the special Variant value Empty. Empty is the initialised value of a Variant. When fed to a numeric variable or field it will become zero, for a string it becomes the empty string.