Date range query

[Johnmad86]

Hi,

Hopefully someone can help me on this. There are many similar posts and I've spent the last few days trawling the internet to see if I can find the solution to my problem, but I can't seem to get it right, or find an answer to this part of it.

I have a single table with customer information, one of the fields is a date field "LastContacted".

I'm creating a search form with 2 date fields (txtDate1 & txtDate2) to search a date range of the LastContacted field, and I need to write this into the query that the search form uses.

I have written this using Nz so that it can still return results if the search boxes are left blank:

Between Nz([Forms]![frm_AdvancedSearch]![txtDate1],#01/01/1989#) And Nz([Forms]![frm_AdvancedSearch]![txtDate2],#01/01/2999#)

This seems to work and it returns lines from the table where there is a date entered. However some of the fields in the table have no entry in the LastContacted field. Does anyone know how to code this query so that it also returns lines where the LastContacted field is blank in the table?

I have tried:

like "*" & (Between Nz([Forms]![frm_AdvancedSearch]![txtDate1],#01/01/1989#) And Nz([Forms]![frm_AdvancedSearch]![txtDate2],#01/01/2999#)) & "*"

but this returns errors when I try to run it.

I'm using Access 2010.

thanks in advance
John

 

[Johnmad86]

I think I may have just solved it. It helps when writing things down.

In the query I have put an entry in the "or" line of the query
Or: Is Null

this now returns all results as I want

 

[Brianwarnock]

Won't that always return all records where there is no last contacted date, is that what you want?

Brian

 

[Johnmad86]

Hi Brian, thanks for the reply.

Hmm, I'm not sure.... While testing, it now returns all values regardless of any other search filters that I've applied to it.

basically I want the query to not filter by LastContacted if the "From" & "To" search boxes in the search form are empty, but also display records where there is no lastContacted date in the table.
There are other search fields, which filter and search (first/last name etc).

e.g.
If the search form has nothing filled in and the query runs it should display all records.

If the search form has last name filled in, it should display all records that match the last name - but not just the records that also have an entry in the LastContacted field, which is what happens before I add the "or Is Null" criteria to the LastContacted query field.

I must be overlooking some simple coding that can get around this, but unfortunately my coding skills are so far limited to butchering other people's that I find online...

can you point me in the right direction?

 

[Brianwarnock]

In the design grid on the row that you added the Is Null you need to repeat all of the other criteria,
You then end up with a Where clause that states

Where (date selected plus other criteria) Or (date is null plus other criteria)

Hope that is clear and makes sense.

Brian

 

[Johnmad86]

Amazing, thank you so much!
Its easy when you know how.

 

[Brianwarnock]

Amazing, thank you so much!
Its easy when you know how.

That's why there is no such thing as a dumb question, a uni lecturer said
"Nothing is obvious to those who do not know"

Happy to have helped

Brian

 

[Johnmad86]

I take it back.
The query now works for the other fields, but the date range doesn't seem to filter at all now.
I'll post the whole SQL query that is now running:

SELECT Candidates.Company, Candidates.LastName, Candidates.FirstName, Candidates.Sector, Candidates.JobTitle, Candidates.WorkPhone, Candidates.PhoneMobile1, Candidates.PhoneMobile2, Candidates.LastContacted
FROM Candidates
WHERE (((Candidates.Company) Like "*" & [forms]![ISISNavigationMain]![navigationSubform].[Form]![txtCompany] & "*") AND ((Candidates.LastName) Like "*" & [forms]![ISISNavigationMain]![navigationSubform].[Form]![txtLastName] & "*") AND ((Candidates.FirstName) Like "*" & [forms]![ISISNavigationMain]![navigationSubform].[Form]![txtFirstName] & "*") AND ((Candidates.Sector) Like "*" & [forms]![ISISNavigationMain]![navigationSubform].[Form]![txtSector] & "*") AND ((Candidates.JobTitle) Like "*" & [forms]![ISISNavigationMain]![navigationSubform].[Form]![txtJobTitle] & "*") AND ((Candidates.LastContacted) Between Nz([Forms]![ISISNavigationMain]![navigationSubform].[Form]![txtDate1],#1/1/1989#) And Nz([Forms]![ISISNavigationMain]![navigationSubform].[Form]![txtDate2],#1/1/2999#))) OR (((Candidates.Company) Like "*" & [forms]![ISISNavigationMain]![navigationSubform].[Form]![txtCompany] & "*") AND ((Candidates.LastName) Like "*" & [forms]![ISISNavigationMain]![navigationSubform].[Form]![txtLastName] & "*") AND ((Candidates.FirstName) Like "*" & [forms]![ISISNavigationMain]![navigationSubform].[Form]![txtFirstName] & "*") AND ((Candidates.Sector) Like "*" & [forms]![ISISNavigationMain]![navigationSubform].[Form]![txtSector] & "*") AND ((Candidates.JobTitle) Like "*" & [forms]![ISISNavigationMain]![navigationSubform].[Form]![txtJobTitle] & "*") AND ((Candidates.LastContacted) Is Null));

 

[CazB]

sorry, bit of a latecomer to this one, but wanted to check what it is you are after before I leap in

For the dates: Leaving aside all the other criteria, when do you want it to show records that have no last contacted date? is it all the time, or just in certain circumstances? If so, what circumstances?

 

[Brianwarnock]

I cannot see any reason for the date selection to stop working, I was constructing a better response but my pc has frozen! Now on iPad.

Brian

 

[Johnmad86]

Hi Caz,

For the LastContacted field, I need the query to return all results if both search date boxes are left blank.

- If the "from" box has a date in but "to" is left blank the query needs to return only values that have a date after the date entered

- and if the "from" box is blank and the "to" box has a date in, it needs to return only values that have a date before the date entered

What I thought was a simple query is turning out to be much more complex than I first thought!

Thanks for your help on this

 

[Brianwarnock]

I thought that you had that bit working and that the problem arose where the lastcontact field in the table was Null , when you wanted those records returned if other criteria were met.

BTW. Another way to tackle this would be to go back to the design grid and remove the criteria row with the Is Null , and then append the Or Is Null to the Between etc

Between ... And..... OR Is Null
The SQL will look simpler but both approaches are valid and worked on my simple test DB , until my old PC decided to go on strike.

Brian

 

[CazB]

OK, I have a similar query in my training database.... I found this worked for me!

In the 'Field' line of a column
Expr3: IIf(IsNull([forms]![ISISNavigationMain]![navigationSubform].[Form]![txtdate1]),True,[Candidates].[LastContacted]>=[forms]![ISISNavigationMain]![navigationSubform].[Form]![txtdate1])
then in the Criteria line True

and then in a separate column
Expr4: IIf(IsNull([forms]![ISISNavigationMain]![navigationSubform].[Form]![txtdate2]),True,[Candidates].[LastContacted]<=[forms]![ISISNavigationMain]![navigationSubform].[Form]![txtdate2])
and again in the criteria line True

Give that a go? I didn't nz it cos when I did that it was only returning the ones that had values in the date...

 

[Johnmad86]

Caz, thank you so much for writing that for me. I've just put it in and tested and it works perfectly!

Brian, thanks I can see how doing that would make it looks simpler. I'm just starting to get my head around writing SQL like this and you've been very helpful

 

[Johnmad86]

Ok, I've got it working how I originally planned and may be biting off more than I can chew now, but I was hoping to put in a check box on the search form, so that if it is checked (i.e. True) the results only display rows where there is an entry for MobilePhone1.

From searching online Dlookup seems to be an option, but I'm a bit stuck as to the format.

Would this be a viable solution?

Thanks in advance
John

 

[CazB]

Dunno if this will work, but it might? Just call the check box chkmobile

IsNull([Candidates].[MobilePhone1]=[forms]![ISISNavigationMain]![navigationSubform].[Form]![chkMobile])
and again in the criteria line True

 

[Johnmad86]

Its ok, I managed to get it working:


IIF([forms]![ISISNavigationMain]![navigationSubform].[Form]![txtHasMobile]=-1 and ([Candidates].[PhoneMobile1] is not null,True,False)=[forms]![ISISNavigationMain]![navigationSubform].[Form]![txtHasMobile]

took a while and a lot of messing but got there in the end.

Thanks again for the help
John