Multi combo box query

[biggcc]

Working on a sales guest registration database and need a some assistance with a query for my report. I have the following fields in the table:

Communities
RepName
Date1stVisit
MoveTime

I have created a query which works without a problem. I have also created a dialog form to use with a report that I've created. Everything works ok if I fill in all the boxes on my form with data but in some instances they want reports that are not filtered all the way down. On my form I have an unbound combo box for each of the fields above. I've tried several of the suggestions found here but cannot get it to work completely. The last thing I tried was the following criteria for each field I want to filter on:

like [forms]![repdialog]![communities]& "*" or is null
like [forms]![repdialog]![repname]& "*" or is null
>= [forms]![repdialog]![datebeg] and <= [forms]![repdialog]![dateend]
like [forms]![repdialog]![movetime]& "*" or is null

This sort of works but I must be missing something because it gives me too much data. For instance if a just wanted to fill in the REPNAME box on the form it should give me every record that has that name in it but I get more that that - I think because of the is null comments. Any one help me correct this?

Thanks,
Chester Campbell

 

[ThreeCrow]

like [forms]![repdialog]![communities]& "*" or is null
like [forms]![repdialog]![repname]& "*" or is null
>= [forms]![repdialog]![datebeg] and <= [forms]![repdialog]![dateend]
like [forms]![repdialog]![movetime]& "*" or is null

Should Be:
like IIf(IsNull([forms]![repdialog]![communities]),"*",[forms]![repdialog]![communities])

like IIf(IsNull([forms]![repdialog]![repname]),"*",[forms]![repdialog]![repname])

>= [forms]![repdialog]![datebeg] and <= [forms]![repdialog]![dateend]

like IIf(IsNull([forms]![repdialog]![movetime]),"*",([forms]![repdialog]![movetime])

 

[biggcc]

Thanks for the response.

I tried your suggestions and I'm still having problems. What I'm looking for this query/form to do is return data based upon the selected items or combination of items selected on the form. I.E. If I select a community and nothing else it should return all entries in the database for the selected community. Or if I select a sales rep and a move time it should give me just the entries that contain both the sales rep and move time selected.

Using your suggested criteria statements it does filter correctly on some of the possible choices but not all. Seems to have problems if I leave one or more fields blank.

Any suggestions would be greatly appreciated.

 

[suepowell]

Hi,

I have done this in several instances.

I presume if you have a dialog form to select the criteria you are calling the report from a button on the form.

The following is a report open statement I have used which allows me to fill in some or all of the combo boxes on the dialog form

My combo boxes in this case are:Startdate enddate partnumber and companyname

DoCmd.OpenReport "rptorderfinder", acPreview, , "(((OrderDate) Between ([Forms]![frmOrderFinderSelector]![StartDate]) And ([Forms]![frmOrderFinderSelector]![EndDate])) or(([Forms]![frmOrderFinderSelector]![EndDate]) is null and ([Forms]![frmOrderFinderSelector]![startDate]) is null )) and ((tblparts_partnumber) = ([Forms]![frmOrderFinderSelector]![PartNumber]) or ([Forms]![frmOrderFinderSelector]![PartNumber]) is null) and (([companyname] = [Forms]![frmOrderFinderSelector]![companyname]) or ([Forms]![frmOrderFinderSelector]![companyname] is null))"

This looks huge and worrying but if you print it out and break it down you will see that the first part is selecting a date between given dates, or all dates if these fields are not filled in.

The next bit is selecting a particular part number if it is input or all of them if nothing is selected.

The final bit is selecting a particular companyname if required.

I hope you can sort this out and it helps.

If not there is a way of putting these criteria into a query, let me know and I will try to remeber the syntax.

Sue

 

[ThreeCrow]

Using your suggested criteria statements it does filter correctly on some of the possible choices but not all. Seems to have problems if I leave one or more fields blank.

Which choises Fail?

 

[biggcc]

Thanks SuePowell and ThreeCrow

I'm checking everything right now again and will let you know what I find.

Thanks,

 

[biggcc]

ThreeCrow I tested the selections with the scripts you provided and it seems that the problem is with the beginning and ending date range.

If I run my report and select anything but leave the date range field blank it does not seem to work. If I fill in any criteria plus a date range it returns my records.

Suggestions?

Thanks,

 

[neileg]

You need to surround your date criteria with ##.

>= #[forms]![repdialog]![datebeg]# and <= #[forms]![repdialog]![dateend]#

 

[biggcc]

Thanks Neileg for the suggestion. Tried it but it gave me and error when I tried to run the report. Something about an expression was too complicated - Sorry I didn't write it down.

Any other ideas?

Thanks

 

[ThreeCrow]

Chester

When choosing your date,

will be a date range or
a beg date and any end date

 

[biggcc]

It will be a range with a start date and end date even if the end date is today they will enter it. This database is used by our residential sales team so they will run this report to get a list of prospective buyers that have visited the site during a certain period as part of a follow-up procedure.

I've added additional sort fields like price, how they hear about us, etc. and they all seem to work ok using your script. Just the date is causing me a headache.

Thanks again for all your help,
CC

 

[ThreeCrow]

Chester

Here you are, this should fill your needs, hopefuly you do not have records prior to 1-1-1900. This will work with or without a datebeg or dateend

>= IIf(IsNull([forms]![repdialog]![datebeg]),#1-1-1900#,[forms]![repdialog]![datebeg]) and <= IIf(IsNull([forms]![repdialog]![dateend]),Date(),[forms]![repdialog]![dateend])

 

[biggcc]

Thanks!

Thanks ThreeCrow for all your assistance. My report is working great and all the sales people are happy so that's a good thing.

Thanks again,
Chester Campbell

 

[biggcc]

Question?

Everything looks to be working correctly but one thing. When I choose just a salesperson's name and nothing else it's not giving me all the entries for that person. I tested this with one salesperson using another query and compared the findings with the report. My concern is that somehow my query/form is not releasing my selections each time I run the report. I know there is a way to do it but wanted to know if you could think of anything else it could be.

Thanks,
Chester Campbell

 

[ThreeCrow]

This could be from a number of things, I would have to see your database.

Can you post a stripdown of you database.

if not send direct to me,

 

[biggcc]

Stripped Database

Here is a stripped down model of our database. I deleted all reports/queries/forms that did not apply. Let me know if you have any questions.

FYI: I tested the report again with this database to see if it was bringing all the data back and it also let some entries off. As an example look at entries for Joseph Hitt. For some reason its excluding some entries if I just select the salesperson's name on the form.

I appreciate your assistance.

Thanks,
Chester Campbell

 

[ThreeCrow]

Chester

Apoligize for taking so long. I run your RepDialog and all worked fine, brought up every record called for.

I even put the database on other computers, and still no problems, all filtered fine.

At this time I have no idea, I am stumped.

Maybe more Indexing.

Sorry I could not be of more help.

 

[neileg]

I can't get anything to work at all! There seems to be a criterion in the RepDialog Query
[Master].[Date1stVisit] >=II

What does the "II" mean?

 

[ThreeCrow]

Neil

where did you find this

 

[neileg]

Neil

where did you find this

The error message that pops up when I try to open the RepDialog query.