Open report based on a value

[boxsta]

Ok, so I'm no Access expert but lots of you guys are! So here's to hoping I get your help. Thanks in advance.

Here's what I want. In my main table/form I have a dropdown box that has various values in it i.e. customer - contract, customer other etc etc. I would like to create a report in a certain date range (I've figured this bit) & depending on the value from the dropdown box.



So for example: I want all contacts in my contacts table where their contract ends between xx/xx/xx & xx/xx/xx and who are 'customer - other' to show in a report.

If this involves more than the basics & I'm guessing it will please try to keep it simple

I guess this has probably been asked before so apologies in advance if I missed it, I did do a search but just couldnt find exactly what I wanted. Also, if there's a tutorial that would be great.

[June7]

Options:

1. RecordSource for the report is parameterized query. Review http://datapigtechnologies.com/flash...earchform.html

2. Use WHERE CONDITION argument of DoCmd.OpenReport to specifiy filter criteria. The argument would refer to controls on form for criteria input.

[boxsta]

Option 1 was just what I was looking for and I was doing well until the table displayed the results and there weren't any! Yet I know there are records that match the search criteria I put in one of the search fields.

Not sure if this makes a difference but when I do the Like [Forms]![Contract Search].[scontacttype] & "*" access kindly helps out with most of it by displaying a selection of optiions, however when I get to the scontacttype part it doesn't find that but it does give me a list of stuff I can choose from as I first type the s but all the options seem to be access related as opposed to a name I have created if that makes sense.

Otherwise I think I have followed the video exactly. I don't know what else I could've done wrong. Is there anything it could conflict with? I do have quite a lot going on, various forms, tables, relationships etc

[June7]

Access is not finding a control on the form Contract Search named scontacttype.

Want to provide project for analysis? Follow instructions at bottom of my post. Then identify the forms involved in issue.

[boxsta]

Ok here goes.

Thanks for your help :-)

[June7]

Okay, I found the Contract Search form. Controls for entering search criteria should be unbound. scontactype textbox has ControlSource.

Problem with the search is that the Contact Type ID field in Contacts is actually saving the ID key, not the text description. So when you enter text 'Prospect' as search criteria nothing will return because the value in Contacts is '1'.

Suggest comboboxes instead of textboxes. Users will see and select 'Prospect' but the search will be for the ID '1'. Need multi-column combobox. Review more tutorials at http://datapigtechnologies.com/AccessMain.htm, especially the 3 on comboboxes in Control Basics section.

[boxsta]

You are a legend, thank you so much

I'll give that a try later on and let you know how I get on.

[June7]

Review these about setting lookups in tables.
http://access.mvps.org/access/lookupfields.htm
http://www.accessmonster.com/Uwe/For...ds-Really-Evil

I seldom set lookups in tables as users do not work directly with tables and queries.
As developer, I want to see the real value when I view tables.

[boxsta]

Had a manic week! Sorry its taken me so long to give this a try.

Anyway, I've done as you suggested and everything seems to be working there is now a combo box so users can select say prospect for example and the query runs without errors but it always returns an empty table. I just can't get it to display any results!

Help please..................

[June7]

Show the query SQL and code or provide latest version of project.

Is the combobox multi-column? I suspect the query parameter is not getting the correct value from the combobox.

[boxsta]

Here's where I'm at.

[June7]

This issue is with form ContractSearch that opens searchquery? Two problems:

1. Like operator and wildcard not working with date values. You will note that the example in the tutorial does not use dates. The only way I can get the date search to work is to construct a field in the query that converts the date to a string: Format([Contract Start],"mmddyyyy"). Then the parameter is: Like Format(Forms![Contract Search]!sstartdate,"mmddyyyy") & "*".

2. Records with no data in the search fields. If data is not required and Null will be allowed, will have to deal with it. I put data into every record for PostalCode and ContractStart and ContractEnd and the search works.

[boxsta]

Many thanks for all your help, I'll try this later.

Much appreciated.