Report by selecting to criteria

[aymanmb]

can I reun my report based on 2 criteria from one query.

I learned how to do that on one criteria e.g. date (between and), I tried to put a second criteria e.g. Company name but it does not work

thanks for any tip

 

[Dennisk]

Without seeling the actual SQL statement.

You can have criteria for every column in your query, but if some criteria is sometimes not entered then you have to use the following

e.g.

Forms!MyForm!myControl OR Forms!MyForm!myControl IS NULL

The query builder will expand this for you, placing the OR in a seperate column and it looks quite horrendous.

 

[Wiz47]

Without seeling the actual SQL statement.

You can have criteria for every column in your query, but if some criteria is sometimes not entered then you have to use the following

e.g.

Forms!MyForm!myControl OR Forms!MyForm!myControl IS NULL

The query builder will expand this for you, placing the OR in a seperate column and it looks quite horrendous.

The other alternative is to build a query that searches for both but disregards one or the other if it is null. Something like ...

WHERE (([FieldName1]=Forms!YourFormName!YourControlName Or Forms!YourFormName!YourControlName Is Null)=True) And (([FieldName2]=Forms!YourFormName!YourControlName Or Forms!YourFormName!YourControlName Is Null)=True)

If both fields are left blank in the form, then it will return ALL records without filtering. You can continue this query for as many fields in the form as you like.

 

[aymanmb]

Thanks, actually the example I have is a data input form for the user to enter a date range (which i did successfully) and a customer name (I did not succeede to do both). this form will feed a report query to filter the report to that specific date range for that customer only.

the criteria I use for the date range (suuccessfuly) in the report query field of "EndDate" is:

Between [Forms]![DateInput]![txtStartingDate] And [Forms]![DateInput]![txtEndingDate]

I then under the customer field I entered:
Forms![DateInput]![txtCompanyName]

but it continue to show the date range successfuly with all customers (customer not filtered).

I am sure my code is missing something,

any suggestion

 

[Wiz47]

Thanks, actually the example I have is a data input form for the user to enter a date range (which i did successfully) and a customer name (I did not succeede to do both). this form will feed a report query to filter the report to that specific date range for that customer only.

the criteria I use for the date range (suuccessfuly) in the report query field of "EndDate" is:

Between [Forms]![DateInput]![txtStartingDate] And [Forms]![DateInput]![txtEndingDate]

I then under the customer field I entered:
Forms![DateInput]![txtCompanyName]

but it continue to show the date range successfuly with all customers (customer not filtered).



I am sure my code is missing something,

any suggestion

WHERE (([Field Name for Customer from Table]=Forms!DateInput!txtCompanyName Or Forms!DateInput!txtCompanyName Is Null)=True) And ([EndDate] Between Forms!DateInput!txtStartingDate and Forms!DateInput!txtEndingDate);

The above code presumes you have a textbox or combo box in your form to select the customer as well as the date range. If that is the case, then when you enter a date range and pick or enter a customer, the query will filter for that date range and that name. If you leave the name blank (Null), it will return ALL customers for that date range.

 

[aymanmb]

thanks, but where should I put that code.

I tried to put it in the report query criteria but gave me message of undefined where. I also tried to put it in the OnOpen event of the report but gave me message of can not find macro where ......

thanks for any tipes

 

[Wiz47]

thanks, but where should I put that code.

I tried to put it in the report query criteria but gave me message of undefined where. I also tried to put it in the OnOpen event of the report but gave me message of can not find macro where ......

thanks for any tipes

First, make it a stand alone query by adding a SELECT and FROM statement and saving it with a name.

SELECT YourTableName.*

FROM YourTableName

WHERE (Repeat the code I quoted in the previous post after changing that field name to the field for the customer from your table)

Once you have created this as a stand alone query, add it as the RECORD SOURCE for your report. You can do that by right clicking on the open report, selecting REPORT DESIGN, then right clicking again and selecting PROPERTIES, then DATA. The first choice should be RECORD SOURCE. Put the name you gave the stand alone query in that block. This tells the report what data to look for or filter when it opens.

 

[aymanmb]

how do you do that (where do you write the code in the stand alone query).

and why can't I use the report query since I have half of it working (date range)

sorry for my beginner's question

 

[Wiz47]

how do you do that (where do you write the code in the stand alone query).

and why can't I use the report query since I have half of it working (date range)

sorry for my beginner's question

To make a stand alone query, go to the main db window and select QUERIES then select CREATE QUERY IN DESIGN VIEW. Two windows should open - close the smaller window that says "Show Table" and rightclick on the other window and choose SQL VIEW. Now either write in or copy and paste the code that you saw in my post. Remember to change the SELECT and FROM statements to YOUR table name . When you exit, it will ask you if you want to save the query. Save it and give it a name of your choosing. Once you have done that, you can reference it as your RECORD SOURCE in the report.

I'm not exactly sure where you are referencing your current query, but it is far simpler to reference it in the RECORD SOURCE for the report. That way you always know where it is and can access it at will if you want to add or change something.

If it's easier for you, just upload a trimmed down version of the db and I'll look at it. I have today off, so I'll have a bit of time to do that.

 

[aymanmb]

thanks for your patience. I tried it but something is wrong because the report is dependant on 2 tables "tbl_Orders" and tbl_Customers". whenI am done with your suggestion, Access keeps asking me for too many paremeters values which it is supposed to be got from the DataInput form.

Can you help me modify my existing report query instead of creating a new one. I just want to modify the report query to filter two things:

Date range: I did thanks to memebers of this forum
Comapny Name: I am not able to

thanks a lot

 

[Wiz47]

thanks for your patience. I tried it but something is wrong because the report is dependant on 2 tables "tbl_Orders" and tbl_Customers". whenI am done with your suggestion, Access keeps asking me for too many paremeters values which it is supposed to be got from the DataInput form.

Can you help me modify my existing report query instead of creating a new one. I just want to modify the report query to filter two things:

Date range: I did thanks to memebers of this forum
Comapny Name: I am not able to

thanks a lot

How are you referencing the two so that one table knows the EndDate for the specific customer from the other table?

 

[Wiz47]

I think the best solution here would be for you to upload a stripped down version of your db so that I and others could have a look at it. It is very difficult to determine what the solution is from your descriptions. I want to help you, but I'll need to see what the database looks like to do that. It could very easily be a join issue for the tables, but without seeing them in design view, I don't know what criteria to Join (or if they can be joined).

 

[aymanmb]

I finally managed to do it,

thanks a lot for all the support