Using query criteria to look at two different forms

[slenish]

Hello,

I am having a problem with a query attatched to a report. I have two different forms that I want to be able to run the same report with.

Example: Form(A) is open I input a date range in box1 and box2 then press the Run Report Button. When the report goes to open its looking for the date range from box1 and box2 on Form(B)

If i just press ok and bypass the question boxes it will still run the report and work fine, but I would like to make it so that it will only look at the Criteria from the Form that is currently being used.

Thanks in advance

[MMcCarthy]

Hi Slenish

Are you using VBA to set the report criteria or have you put the criteria links in the query?

I don't really understand what you mean here - "but I would like to make it so that it will only look at the Criteria from the Form that is currently being used. " - can you explain further.

Mary

[slenish]

Hello Mary,

Thanks for the reply :D

I have the criteria links set in the Query.

Example: this is set in the query

DateField
Total = Where
Criteria = Between Forms!DateForm!StartDate And Forms!DateForm!EndDate
 

[MMcCarthy]

OK remove the criteria from the query and change the code that runs the report to something like the following...

' Code for button to run report
Private sub btnMyReport_Click()
Dim strDocument As String
Dim strLinkCriteria As String
 
    strDocument = "MyReportName"
    strLinkCriteria = "[DateField] BETWEEN #" & Me!StartDate & "# AND  #" & Me!EndDate & "#"
 
    DoCmd.OpenReport strDocument, , , strLinkCriteria
 
End Sub
 

[MMcCarthy]

Just a further note to say that using the code to set the criteria like this means you can run the same report from any form.

[slenish]

Hi Mary,

Thanks a lot for the help. Well I am still having a problem. I used the code that you gave me and put it in to the report button. Problem im having now is that when i run the report it shows all the dates and not the range that I select. Originally i had this set to where on the query but if i set it to where and leave the criteria blank it just deletes the field so im not sure how to fix this? Also if i wanted to add other fields like a name field along with the date would I add it in to the strLinkCriteria? Or would I make another new line like strLinkCriteria2?

Thanks again :D

[MMcCarthy]

Problem im having now is that when i run the report it shows all the dates and not the range that I select. Originally i had this set to where on the query but if i set it to where and leave the criteria blank it just deletes the field so im not sure how to fix this?

Can you change the query layout to SQL and post the code in here so I can see what you are doing?

Also if i wanted to add other fields like a name field along with the date would I add it in to the strLinkCriteria? Or would I make another new line like strLinkCriteria2?

Add it to the strLinkCriteria. Use the AND operator e.g.

 
strLinkCriteria = "([DateField] BETWEEN #" & Me!StartDate & "# AND #" & Me!EndDate & "#) " & _
"AND (NameField = '" & Me!NameField & "')"
 

[slenish]

Here is the SQL from the query

SELECT MainTbl.Date, MainTbl.Name, MainTbl.Er1, MainTbl.Er2, NameTbl.Status
FROM MainTbl INNER JOIN NameTbl ON (MainTbl.Name = NameTbl.Name) AND (MainTbl.Date = NameTbl.Date)
WHERE (((NameTbl.Status)="Active"));
 

[MMcCarthy]

OK the first thing I would point out is using Date as a field name is bad as it's a protected name (function name) in access and can cause problems. So change the name of this field to something else.

So for now I'm going to rename the field to MyDate. First question is .. is this field actually on the report? Secondly is this the field you want the date range criteria to work on?

[slenish]

Actually my date field is not called date i just made it a general name to show you, so no worries there. :D

The date field and the name field is what i want the criteria to work on. Also yes the date field is on the report as well. Reason is so when the person views the different pages of the report the date will change depending on what page they are on.

As of right now the report works great with no problems just trying to make it work from two different locations.

Thanks again for the help with this hate to take up to much of your time.

[MMcCarthy]

Ok so I gather the following statements are true ..

1. There is no criteria currently on the query underlying the report
2. If you enter a StartDate and EndDate on the form and run the report using the code I gave you it is not limiting the records to between these two dates

Are both these statements true?

[slenish]

Yes they are both true. :)

[MMcCarthy]

OK, try changing the strLinkCriteria to this ...

strLinkCriteria = "[DateField] BETWEEN #" & Format(Me!StartDate, "dd mmmm yyyy") & "# AND  #" & Format(Me!EndDate, "dd mmmm yyyy") & "#"
 

Access can have problems with date formats, hopefully this will fix the problem.

[slenish]

Ok im still not having any luck here is what i have right now.

strDocName = "JReport"
    strLinkCriteria = "([Date] BETWEEN #" & Format(Forms![Entry2]![Entry3]!StartDate, "mm/dd/YYYY") & "# AND  #" & Format(Forms![Entry2]![Entry3]!EndDate, "mm/dd/YYYY") & "#) " & _
    "AND ([EName] ='" & Forms![Entry2]![Entry3]!txtName & "')"
 
   DoCmd.OpenReport strDocName, acViewPreview, strLinkCriteria
 

[MMcCarthy]

Q1. Where have you got that code? Behind the button on the form?

Q2. What is this reference "Forms![Entry2]![Entry3]!txtName"?

[slenish]

Yes the code is behind the button on the form.

The "Forms![Entry2]![Entry3]!txtName" is the name entry field on the form I can remove it for now, but i will need this as a factor when all is said and done. I asked you about how to add this in like 7 posts ago :)

[MMcCarthy]

But thats not the code I gave you. If txtName is a textbox on a subform called Entry3 then the code is ...

Me!Entry3.txtName

and not

Forms![Entry2]![Entry3]!txtName

If there is some particular reason you have for using this format it's still not correct. The correct way of writing this would be ..

Forms![Entry2]![Entry3].Form!txtName

[slenish]

I do appologize, but i have used the this type of format (Forms!Form1!form2!txtBox) for many other things and it works just fine also i have used your last example as well (forms!Form1!form2!.Form!txtBox) on occasion.

For some reason though none of the above is working for this report haha! its getting to be so frustrating that its funny.

I appreciate your help with this. I will continue to play around with the code examples you have given me and see what i can figure out. For now im heading to bed its pretty late where i am.

If im successful i will let you know.

Thanks again :D

[Mihail]

Hi all !
Slenish, Take a look here:
http://bytes.com/topic/access/answer...control-values
I think you find a possible answer.

[NeoPa]

Slenish,

Let me try to help here by pointing out then when working with someone else it is always very important to be precise. Posting SQL which is similar to, but not exactly, what you are using will cause problems. Testing code which is similar to, but not the same as, code that Mary suggests will cause problems.

What I'm saying here is that your problems and your frustration are entirely down to your approach and attitude towards working with someone else. If you take this lesson on board you will find it so much simpler to get your problems resolved.

Let me recap some of what Mary has already posted. All of this is true.

1. Filtering a bound object (EG. Report or Form) is not remotely complicated. People do it every day without problems.
2. Controlling this from behind a form control (A Command Button for instance) often requires reference to items (values of controls particularly) from that same form. The correct way to refer to these controls is as properties of the Me object. It is certainly possible to use a longer form, even via the Forms() collection if you're really masochistic, but there are good reasons why this is not considered a sensible approach (Development time; readability; maintainability; consistency; etc to name but a few).
3. The standard way to handle such filtering, in its most basic form, is to ensure the object in question is not itself filtered, and is bound to a table or query which is not filtered. Exceptions to this may be filtering which is separate from, and does not impact upon, any filtering you may later rely on the form to provide. [Status]='Active' may be an example of this in your case.
   
   With this logically open query we can now apply the filtering determined by the code behind the form. The LinkCriteria (or whatever name you choose) variable then contains a string which matches the string you might find in a SQL WHERE clause.
4. Date literals in SQL do not follow localised format conventions. The simplest format for a date in a SQL string is the standard (m/d/yyyy), but any unambiguous format will work reliably. Mary's suggestion of "dd mmmm yyyy" for instance, is entirely unambiguous for example.
5. I know your SQL posted earlier is not your actual SQL, but if it were then the following should be an answer to your original question - assuming the information you've provided so far is reliable :
   

   With Me
       strLinkCriteria = "([Date] Between #" & _
                         Format(CDate(.StartDate),'m/d/yyyy') & _
                         "# And  #" & _
                         Format(CDate(.EndDate),'m/d/yyyy) & _
                         "#) AND ([EName]='" & .txtName & "')"
   End With

[slenish]

Just wanted to update this and once again say thanks to Mary for all of the help the other day. I figured out what the problem was which was actually just something very simple.

Mary was right on with the code examples that she gave me but the problem turned out to be in the DoCmd statement, I was missing a comma(,) before the strLinkCriteria.

so instead of this

DoCmd.OpenReport strDocName, acViewPreview, strLinkCriteria 
 

I should have had this

[DoCmd.OpenReport strDocName, acViewPreview,, strLinkCriteria 
 

The extra comma puts the strLinkCriteria in to the where condition so it will pass the variables.

Thanks again for all the help :D

Also thank for the added support Mihail and NeoPa. I know i will find uses out of both of them sooner rather than later. :D