Query based on multiple criteria from form w/ between dates

[i.am.sophie]

Hi All,

I have a form with multiple fields (e.g. exact date, store name, store type, coverage status etc.). The user can fill in any of them and then a query returns the results.

Everything works perfectly, but when I try to put the between dates in as well in SQL view, it keeps returning an error message (Expression is typed in incorrectly.)

I'm fairly new to Access and can't figure out how to include the From and To date in the below code:

SELECT Coverage.Week, Coverage.Area, Coverage.Name, Coverage.[Store Type], Coverage.[Dem Detail], Coverage.Date, Coverage.[Dem Type 2], Coverage.[Coverage Status], Coverage.[Date Covered], Coverage.[Dem Type 1]

FROM Coverage, Coverage AS Coverage_1

WHERE (((Coverage.Area) Like NZ([Forms]![Coverage Form]![Area],"") OR NZ([Forms]![Coverage Form]![Area],"")="")<>False)

AND (((Coverage.Name) Like NZ([Forms]![Coverage Form]![StoreName],"") OR NZ([Forms]![Coverage Form]![StoreName],"")="")<>False)

AND (((Coverage.[Store Type]) Like NZ([Forms]![Coverage Form]![StoreType],"") OR NZ([Forms]![Coverage Form]![StoreType],"")="")<>False)

AND (((Coverage.[Dem Detail]) Like "*" & NZ([Forms]![Coverage Form]![DemName],"") & "*" OR NZ([Forms]![Coverage Form]![DemName],"")="")<>False)
AND (((Coverage.Date) Like NZ([Forms]![Coverage Form]![ExactDate],"") OR NZ([Forms]![Coverage Form]![ExactDate],"")="")<>False)

AND (((Coverage.[Dem Type 2]) Like NZ([Forms]![Coverage Form]![DemType],"") OR NZ([Forms]![Coverage Form]![DemType],"")="")<>False)

AND (((Coverage.[Coverage Status]) Like "*" & NZ([Forms]![Coverage Form]![CoverageStatus],"") & "*" OR NZ([Forms]![Coverage Form]![CoverageStatus],"")="")<>False)

AND (((Coverage.[Dem Type 1]) Like NZ([Forms]![Coverage Form]![DemGroup],"") OR NZ([Forms]![Coverage Form]![DemGroup],"")="")<>False);

So I'd need something like this:

AND ((((((Coverage.Date) Between NZ([Forms]![Coverage Form]![FromDate]," ") and NZ([Forms]![Coverage Form]![ToDate]," ") OR NZ([Forms]![Coverage Form]![FromDate]," ")="")<>False) OR NZ([Forms]![Coverage Form]![ToDate]," ")="")<>False);

I can kind of see this is not going to work, but I have no idea how to fix it. Could you please help?

Thanks,
Sophie

 

[CJ_London]

a number of issues

AND (((Coverage.Date) Like NZ([Forms]![Coverage Form]![ExactDate],"")

1. Date is a reserved word (it is a function which returns todays date), so using it can cause unexpected errors - i.e. the error description can be inaccurate/misleading. Better to change this to CoverageDate or similar. If you can't then encase with square brackets - [Date] - but you will still get errors with things like conditional formatting etc.

2. Assuming this date is a datetype, then if exactdate is null, you are trying to compare it to a string. What to do here to correct it depends on what you want to happen if it is null. Datetypes are actually numbers - but what you see is it formatted as text depending on system settings or format properties of the control.

3. Like is used for string comparison where you are using wildchars (*? etc), nothing else. So you can change Like to =

So decide what you want to happen in 2 and bear in mind the other comments

Also, with your prolific use of *...

Although many use it, it is indicative of bad database design. Putting a * at the beginning of your search string means that access (or any other db for that matter - some don't even allow it) cannot use indexing so has to use sequential searching (check each record one at a time) which for small amounts of data is not an issue but for larger amounts will have an incredible effect on performance - without the initial * and the field indexed - it probably takes a second to query 100,000 records, with an initial * this could be a couple of minutes. So you have with a couple of keystrokes in your code, made your db very slow and inefficient.

People tend to use the initial * so they can find 'Andrew Smith' by searching for 'Smith'. This is indicative of a bad db design since if this is a regular requirement, the field should be split into two fields firstname and lastname.

Done properly, most users will know the beginning of what they are looking for. I don't include * at the beginning or end of a search string in code - I train users to type '*smith' or 'Andrew*' if they need to. This gives them much more flexibility and no coding for yourself because it means they can look for 'A*Smith' for example (and 'A*Smith' will use indexing).

 

[i.am.sophie]

Thanks for your reply.

I corrected Date to BookingDate as you suggested and also changed Like to =.

Regarding your second point: if the field is not filled out, I want the query to move on to the next field and check if there is anything in that one. If there is, it will still have to check all the other fields for data, if there isn't, it needs to disregard that field completely and move onto the next one. Hope this makes sense, I know I'm not the best in explaining things.

I have to put * at least before the DemName, the other (CoverageStatus) can be removed with modifying my original data a bit.

We are a marketing agency and have a lot of campaigns, say:
Campaign A:
1547 Cadbury Hot Chocolate Main Stand (this is one dem within the campaign)
1548 Cadbury Hot Chocolate Flatpack (this is another dem within the campaign)

Campaign B:
9875 Heinz Soup Cooking
9876 Heinz Soup Active Sell

Campaign C:
6584 Ritz Crackers & Cadbury Chocolate Main Stand
6598 Ritz Crackers & Cadbury Chocolate Temporary Stand
6599 Ritz Crackers & Cadbury Chocolate Flatpack

I need the * because someone might want to search for ALL Cadbury campaigns we did and it can be anywhere within the dem name. But as you can see, it would be troublesome even with one campaign as it contains multiple dems with different names. This cannot be changed for different reasons and would take too much time to manipulate this in my original Excel file.

Do you have any suggestions regarding the between dates? So I need the query to check if the from and to fields have been filled out. If yes, it needs to apply the criteria and also check the remaining fields. If not, it needs to disregard those fields and move onto the next one.

Thanks

 

[nfk]

So I need the query to check if the from and to fields have been filled out. If yes, it needs to apply the criteria and also check the remaining fields. If not, it needs to disregard those fields and move onto the next one.

I guess depending on how the gui works with the user input you could use SELECT CASE to alter the query to be executed:

Dim SelectedSearchString As String
Select Case SearchSelected

    Case "1" 
    SelectedSearchString = "SELECT 1"
    Case "2" 
    SelectedSearchString = "SELECT 2"
    Case "3" 
    SelectedSearchString = "SELECT 3"

End Select

Or not...

 

[CJ_London]

I need the * because someone might want to search for ALL Cadbury campaigns

my point exactly - they might want to - so let them enter *Cadbury* when they do.

And with regards database design, I'm just advising - Access is not a glorified Excel, the way it works with data is completely different. If you do not have a good db design you'll need to accept that there will be things you will not be able to do or not do efficiently or can only do with constant modifications to the code.

So I need the query to check if the from and to fields have been filled out. If yes, it needs to apply the criteria and also check the remaining fields. If not, it needs to disregard those fields and move onto the next one.

I think you need this which will return all bookings if both the controls in the form are left blank, everything on or after fromdate if todate is blank or everything on or before todate if fromdate is blank

 bookingdate between nz([Forms]![Coverage Form]![FromDate],#01/01/1900#)AND nz([Forms]![Coverage Form]![ToDate],#12/31/2300#)

 

[CJ_London]

@nfk - if you are going to post suggestions - don't post nonsense from your weird world

@sophie - ignore nfk, he is one of natures free spirits who likes to annoy people and then cry 'discrimination' when they argue back. Not very original, but I don't think he can help it

 

[vbaInet]

2. Assuming this date is a datetype, then if exactdate is null, you are trying to compare it to a string. What to do here to correct it depends on what you want to happen if it is null. Datetypes are actually numbers - but what you see is it formatted as text depending on system settings or format properties of the control.

3. Like is used for string comparison where you are using wildchars (*? etc), nothing else. So you can change Like to =

Good points CJ_London.

i.am.sophie, you will remember your post about this and it was advised that you should use the following format instead - for the reasons CJ_London has expanded on - and so that your query is a lot more optimised. The suggestion was that you use this format:

[FieldName] = Forms!FormName!ControlName OR [FieldName] = Forms!FormName!ControlName IS NULL

 

[CJ_London]

think your suggestion is slightly wrong - should be

 [FieldName] = Forms!FormName!ControlName OR 
  Forms!FormName!ControlName IS NULL

 

[vbaInet]

think your suggestion is slightly wrong - should be

 [FieldName] = Forms!FormName!ControlName OR 
  Forms!FormName!ControlName IS NULL

Yep, copy and paste and then you forget editing it

 

[i.am.sophie]

Thanks for all your suggestions, I'll rewrite the code how CJ_London suggested, with IS NULL instead of NZ.

One last note regarding the * - if they want to find a specific campaign (which will happen often), they can only type in only a part of the full name. Some people I trust will remember putting the * before and after the word, but there will be quite a lot users, whose Excel knowledge is close to zero, and probably never seen Access before. E.g. several times we held Excel trainings and also explained things to them one-by-one, but if I forget to sort an Excel sheet before sending it out, they email back asking for resorting the document because they can't do it themselves.

I suppose I could ask them in the description to put the two * in, but I really don't trust them to do it (this is based on previous experience) and then I'd have to answer all their emails one by one when they ask why it's not working.

I read on other forums too that it's not ideal, but I have to keep my audience's skills and abilities in mind as well.. :/

 

[CJ_London]

I can see I'm losing the argument. It's your business so you have to do what you have to do. My parting shot is just ask what you would advise if your user wanted to find Ritz and Cadbury?

 

[vbaInet]

Done properly, most users will know the beginning of what they are looking for. I don't include * at the beginning or end of a search string in code - I train users to type '*smith' or 'Andrew*' if they need to. This gives them much more flexibility and no coding for yourself because it means they can look for 'A*Smith' for example (and 'A*Smith' will use indexing).

I understand what CJ_London is trying to get across and it's valid, and I think that the approach you take would depend on a 'few' main factors:

* the type of data
* the size of data
* the kind of users that you're dealing with.

If the users are not keen to type "*" or don't have a solid grasp of wildcard searching then it becomes a problem.

In this scenario i.am.sophie has multiple fields to search on so if the user wants to drill down, they would engage the other search fields.

Considering the above, a different approach would be to:

* give your users the choice of searching for an exact match
* or searching for records that contain what is typed
... for example using a Combo Box that states, "Exact Match"; "Contains".

Based on the selection in the combo box, you build your search string in code and apply it to the form/query. I'm sure that most of your users would select "Contains" as default anyway.

 

[i.am.sophie]

Thanks for both of your replies.

vbaInet, that's a good idea, I'll see if the users think if it's necessary.

Thanks again!