Group by in Query and Filter in Form 1

[Leventcos21]

Hi, I currently have a report where I am quering with a Group by. One field is a date, which I am doing a filter on a DATE TO and DATE FROM. I currently have this expression on my query.

 Between [Beginning Date] And [End Date]

I want to get away from this and I created two unbound fields on the form. However, I am not able to filter and get the results I am looking for because, I can't GROUPBY date. How can I work around this?

Thanks

 

[IGPCS]

you can try this dont group by date try to use where

Between [youform]![Beginning Date] And [yourform]![End Date]

hope you got this

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[Leventcos21]

When I try this I get a parameter popup which I am trying to avoid. I am trying to get this qury to work so that the users use the unbound text boxes rather than a parameter

 

[IGPCS]

thats right these are to unbound objects such as text boxex or dtpickers dosnt matter


if your using the query builder right click on the date criteria and choose build.. in there type "between" and then choose forms and choose the form name the unbound objects are and then choose the first and then type "and" and then choose in the forms the second this should work correctlly

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[Leventcos21]

Thanks for the reply,

when I do that, I get this error:

Run-time error '3126'
Invalid bracketing of name [tblNCR.InitiatedDate]

Here is my code:

            CriteriaStrg = (CriteriaStrg + " AND ") & "[tblNCR.InitiatedDate] >= #" & _
                Format(Me.txtFromDate, "dd mmm yyyy") & "#"

I think I know why because the total is a WHERE and the show is not checked I am not able to run the query. How can I get around this?

 

[PHV]

What is your actual SQL code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Leventcos21]

I have one for txtFromDate and txtToDate
I know when I do a normal filter on dates it retrieves the correct data. For this specific report, I am trying to determine which items have the most complaints for a specific time range and put them in order.

    If Not IsNothing(Me.txtFromDate) Then
        ' If Customer report
        If Me.optReport = 1 Or Me.optReport = 2 Or Me.optReport = 3 Then
            ' Build predicate on FirstDate field
            CriteriaStrg = (CriteriaStrg + " AND ") & "[qryRptTypeConcernComplaint.InitiatedDate] >= #" & _
                Format(Me.txtFromDate, "dd mmm yyyy") & "#"
        Else
            ' Build predicate on OrderDate field
            CriteriaStrg = (CriteriaStrg + " AND ") & "[InitiatedDate] >= #" & _
                Format(Me.txtFromDate, "dd mmm yyyy") & "#"
        End If
    End If

 

[PHV]

As you don't have special character in the table/field names, why not simply get rid of all the brackets ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Leventcos21]

Then I get a enter a parameter value, when I do that. Like I said if I were to have it as a straight query, it works fine. I think it is because the InitiatedDate is not being pulled in?

 

[PHV]

Again, what is the WHOLE SQL code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886