QBF And Date fields problem

[dubiic]

Hello!

I'm new to access, but i'm facing kind of difficult task now for my level. I have made quite complicated query by form. Which works as autofilter in excel in realy large table. My demand from access would be if all criteria fields in form would be empty then query gives out whole table. I have red a lot about dealing with Null values and works just fine with adding & "*" or using IIF funcion.
Problem: I have 2 date fields in my form. txtstartdate; txtendate. Atm in my query criteria looks following:

Between [Forms]![Custom]![txtstartDate] And [Forms]![Custom]![txtenddate]

Works just fine but there are 2 problems. First one is, that i cant leave this criteria alone if im not interested to sort by date, I just want all records from it if form field is empty. And in this case date is demanding thing in table, since it is not always there query wont show lines with empty date field.
As i understand IIF is no good cause of between in this case. * is impossible too . Have been looking around for a while now and no luck.

Thanks a lot in advance!

 

[CyberLynx]

Is this query generated and fired from Code?
What is the entire Query?

Perhaps try using the Nz() function to deal with NULL.

 

[dubiic]

Im new to access so i made all this just in design view by making expressions in criteria fields. If it will be necessary to get in code, I will try to master it
sql view looks like this, dont know if it helps, looks scary, but thats cause there are just loads of things in it.

SELECT body.VIN, body.MNET, body.Model, body.DLR, body.[Import date], body.Bojājums, body.Impnumber, body.[Export date], body.[CMR datums], body.[CMR orģināls]
FROM body
WHERE (((body.Model) Like [Forms]![Custom]![txtmodel] & "*") AND ((body.DLR) Like [Forms]![Custom]![txtDLR] & "*") AND ((body.[Import date]) Between [Forms]![Custom]![txtstartDate] And [Forms]![Custom]![txtenddate]) AND ((body.Bojājums)=[Forms]![Custom]![txtboj] & "*") AND ((body.Impnumber) Like [Forms]![Custom]![txtimpnr] & "*") AND ((body.[Export date]) Between [Forms]![Custom]![expstart] And [Forms]![Custom]![expend]) AND ((body.[CMR datums]) Between [Forms]![Custom]![cmr1] And [Forms]![Custom]![cmr2]) AND ((body.[CMR orģināls])=[Forms]![Custom]![txtcmr] & "*"));

 

[CyberLynx]

Show your query.

I have always found it just easier to generate the Query in code when dealing with Search Forms especially when there are a good number of Controls on the Form that can contribute to the search.

I am sure there are sample search forms posted in this Forum.

whoops...you beat me to it

And now that I've see your Query, I would generate this one through code. More flexible that way in my opinion.
.

 

[dubiic]

I realy don't know how to start it. Im not familiar with code realy. If there are any good resources or tips i would be up to it
Im trying to get that Nz() working. If i understand right i have to change that Null value to emty string " " and then i can add "*" for everything.

 

[namliam]

first of all use the code tags... [ code ] to start, [ /code ] to end
Without the spaces...

SELECT body.VIN, body.MNET, body.Model, body.DLR, body.[Import date], body.Bojājums, body.Impnumber, body.[Export date], body.[CMR datums], body.[CMR orģināls]
FROM body
WHERE (((body.Model) Like [Forms]![Custom]![txtmodel] & "*") 
AND ((body.DLR) Like [Forms]![Custom]![txtDLR] & "*") 
AND ((body.[Import date]) Between [Forms]![Custom]![txtstartDate] And [Forms]![Custom]![txtenddate]) 
AND ((body.Bojājums)=[Forms]![Custom]![txtboj] & "*") AND ((body.Impnumber) Like [Forms]![Custom]![txtimpnr] & "*") 
AND ((body.[Export date]) Between [Forms]![Custom]![expstart] And [Forms]![Custom]![expend]) 
AND ((body.[CMR datums]) Between [Forms]![Custom]![cmr1] And [Forms]![Custom]![cmr2]) 
AND ((body.[CMR orģināls])=[Forms]![Custom]![txtcmr] & "*"));

NZ([Forms]![Custom]![expstart], date()) is what you are looking for for your between....

Now offcourse you need to allow for any value... so add something like
"Or [Forms]![Custom]![expstart] is null "

To your between otherwize you will get stuck.
Actually stuff like this is better done in code where you add applicable where clauses to the where statement of the SQL. But that is a bit much for now I think.
Because what hapens if you are search for "Apple" and only Apple but because you have the * added it will also return Apply Pie

Nice as apple pie is, do you want it in your query?

 

[gemma-the-husky]

What are yuo trying to do?

if your users want to play around with the data, its easier sometimes to let them have it, than try and build everything for them in Access

just produce a spreadsheet with ALL the data they could ever want to look at, and dump it to a spreadsheet - As long as your users know its just a snapshot for them to "play" with, then they can sort/autofilter however they want.

 

[dubiic]

Ye, now I have learned that all this it is not the most efficient work, however this time I would like to complete it. My idea was to make it more user friendly then it wouuld be with using access build in options. And it realy is, if only i could deal with these 3 date interval nulls.

Namlian: I didn't quite understand your ideas NZ([Forms]![Custom]![expstart], date()). If I look at this funtion then date() would be in Null case which is not what im looking for. Or maybe I misunderstood something

 

[namliam]

The point is the "Between x and y" doesnt allow X or Y to be null. So you need to have some value there.

Between Date() and Date() would atleast put values there, tho it will not return anything probably... Or if it (possibly) does change it to any old date that will not return data.
Now change your where statement for the between to

Other query stuff ....
and (Between x and y or textbox is null)
More query stuff....

You will get a true back from "textbox is null" or the between depending on if there was a Null entered or actual values.

 

[dubiic]

Still looking for solution here, driving me nuts this. Last thing I have to deal with in this database.

 

[boblarson]

Actually, Namliam's suggestion about using NZ([Forms]![Custom]![expstart], date()) is good and would work for you. But, if you wish you can follow Cyberlynx's suggestion about generating the SQL via code. Here's a sample from my website that will show you, if you go into the code window, how I handle the possibility of using one or both dates in a range:

http://downloads.btabdevelopment.com/Samples/combos/FormSampleFromMultipleCriteria.zip

 

[dubiic]

I am trying to get in your solution now, thanks!
Think it is something in my access skills that i didn't understand mamlian and how can i benefit from date() in this situation
In between date() and date() I would get out 0 records obviously cause there are none.

I could make expression with idea: If null between 01.01.1900 AND 31.12.2100
which would include all possible records in table. But then there should be actual records in my table, there is possibility that 50% from date fields can be empty.

 

[namliam]

I could make expression with idea: If null between 01.01.1900 AND 31.12.2100
which would include all possible records in table. But then there should be actual records in my table, there is possibility that 50% from date fields can be empty.

Try re-reading this post or this one

If your form parameters contain NULL you want to return all records... you can accomplish this simply by having something like
Between nz(FORM.textbox1,Date()) and NZ(FORM.textbox2 , Date())
This will allow you to handle both filled and null values. Like you said, NULL values replaced by DATE() will not return anything... Or shouldnt ....
Maybe you can make sure by doing DATE()+365, putting the date a year into the future.

Now instead of having ONLY the between.... you add an OR to it...
( Between ... and ... OR Form.Textbox1 is null)

Now your query will return a record IF it is between Your textbox1 and textbox2. BUT if textbox 1 is null it will "validate every record!

 

[dubiic]

Great Now it makes big sense to me. I should figure out how to set first date() like 4 years back and 2nd date() 1 year in future

 

[namliam]

No this is not what I am suggesting but that is also possible.

You can do somewith with
NZ(Textbox1, #01-01-2001#)
To replace a null value with a fixed date like in this case Jan 1st 2001
NOTE: This has to be in american format, MM-DD-YYYY

and
NZ(Textbox2, date() + 365)
To add a year to today.

That will also work, tho specific to dates only.
The OR thing I posted above should work for Strings, numbers ... anything really.

Or better still is to go with Bob's SQL coding thing... But that is pretty complex if you have never touched SQL and/or VBA.

 

[namliam]

Hey!

You already helped me a lot and I finnaly got something to work. I decided to use this way:

Between Nz([Forms]![Custom]![expstart];#01.01.1950.#) And Nz([Forms]![Custom]![expend];Date()+365) or [body]![Export date] is Null

This works fine, apart from a fact that now it always gives out values where there is no export date. Any sugestions what i have done wrong ?

Thanks in advance,
realy appreciate your effort

You are mixing up solutions again...

Offcourse it will return records where there is no "export date" because in your query it says so... "OR [body]![Export date] is Null"
You should infact check on the form to be null, not your export date.

I really think your between should look something like

[B]([/B] Between Nz([Forms]![Custom]![expstart];[B]Date() + 365[/B]) And Nz([Forms]![Custom]![expend];Date()+365) or [B][Forms]![Custom]![expstart][/B] is Null [B])[/B]

Tip:
You really shouldnt use any spaces in your column names...

 

[dubiic]

Everything is working great!
Namilan +10 if I could Thanks a lot.

 

[namliam]

Really, dont use PMs dubiic, there are other people out here that will help you too if I should become unavailable for some reason.

Jeez, Now our "Or" overrides all other criterias.
If there is no date specified it will just show All, no matter which other filters are applied
I feel kinda guilty, but would be excellent to finish it.

That is because you are MISSING the brackets I put around your statement.
You need the brackets to make one compleet check for the dates ...

Consider this:
Where 1 = 2
and 2 = 3
and 3 = 4 or 3=3

Now your OR is true it is all evaluated "sequentially" meaning access puts together all the AND's, which all need to end up true. Then adds the or...

Now do it like so
Where 1 = 2
and 2 = 3
and ( 3 = 4 or 3=3 )

And all of a sudden access knows that the 3=4 or 3=3 part belongs together.
Brackets are your friend