Inserting a number into a criteria

[Thompyt]

Lots of questions from me lately. Other users have come up with evolving needs.

I have a field Date_Closed and in its criteria a put a range of dates. Its is really putting todays date and back into the number of days historical data: Between Date()-7 And Now().

Since I have multiple queries using the same field (Date_Closed) I would like to have a form maybe where I can choose the number of days and it affects them automatically.

I am doing this to cut my queries by a third (so far)

Main Pivot Query Code:

TRANSFORM Nz(Count([CR_ID])-1,0) AS CR_IDs
SELECT Chng_ReqQry.Level, Nz(Count([CR_ID])-5,0) AS [HB Total], Nz(Count([Level])-5,0) AS [Level Totals]
FROM Chng_ReqQry
WHERE (((Chng_ReqQry.Sub_No)=0) AND ((Chng_ReqQry.Date_Closed) Between Date()-7 And Now()))
GROUP BY Chng_ReqQry.Level
PIVOT Chng_ReqQry.Votes;

Is there a way where I can get the totals for each column that shows up in the Main Pivot Query? So far I have to do this for each one:

[TTL Approved], [TTL CRs], [TTL Deferred], [TTL Denied], [HB Total], [TTL Pending], [TTL Withdrawn]........

[SELECT Nz(Count([Level]),0) AS [TTL Approved]
FROM Chng_ReqQry
WHERE (((Chng_ReqQry.Votes)="Approved" Or (Chng_ReqQry.Votes)="Approve") AND ((Chng_ReqQry.[Change Requested])<>"Do not delete") AND ((Chng_ReqQry.Date_Closed) Between Now()-7 And Now()) AND ((Chng_ReqQry.Sub_No)=0))
HAVING (((Nz(Count([Level]),0)) Is Not Null));

Then I have to make a query where it summarizes the pivot query and all the other columns totals.

[SELECT CR_Crosstab.Level, CR_Crosstab.Approved, CR_Crosstab.Denied, CR_Crosstab.Withdrawn, CR_Crosstab.Deferred, CR_Crosstab.Pending, CR_Crosstab.[HB Total] AS [Level Totals], [Total Approved].[TTL Approved], [Total CRS].[TTL CRs], [Total Deferred].[TTL Deferred], [Total Denied].[TTL Denied], [Total HB].[HB Total], [Total Pending].[TTL Pending], [Total Withdrawn].[TTL Withdrawn]
FROM CR_Crosstab, [Total Approved], [Total CRS], [Total Deferred], [Total Denied], [Total HB], [Total Pending], [Total Withdrawn]
GROUP BY CR_Crosstab.Level, CR_Crosstab.Approved, CR_Crosstab.Denied, CR_Crosstab.Withdrawn, CR_Crosstab.Deferred, CR_Crosstab.Pending, CR_Crosstab.[HB Total], [Total Approved].[TTL Approved], [Total CRS].[TTL CRs], [Total Deferred].[TTL Deferred], [Total Denied].[TTL Denied], [Total HB].[HB Total], [Total Pending].[TTL Pending], [Total Withdrawn].[TTL Withdrawn];

The easiest bet would be to total everything in the Pivot query. Can anyone show me how to accomplish this?

Rollup:

Request For information 1: Make a way to insert the desired date into all the queries linked to it (Would still need it if all the queries can go into one pivot query)

Request For information 2: Consolidate all the queries into the 1 pivot query

[rpeare]

I am not sure what your question is you start by asking (I think) if you can substitute a number of days into your 'between' statement, you can.

Assume your form is named FrmCriteria, on that form you have a field named DaysBack

you can check between today's date and the number of days back using a criteria of


Between dateadd("d", -(forms!frmcriteria!daysback), date()) and date())

or you can probably use just

between date() - forms!frmcriteria!daysback and date()

You can consolidate pivots (assuming they all have the same number of columns) using a UNION query

SELECT * FROM Query1
UNION ALL
SELECT * FROM Query2
etc...

[Thompyt]

Thanks,
I made the form Days_Back with an unbound control named Days. It is a combobox with an editable value list, and defaulted to 7.

In the Query I changed the date_closed range criteria to: between date() - forms!frmcriteria!daysback and date()

On the form Days_Back I have a cancel button to close the form, and a select button to select the criteria. How do I point it to the the query/Queries? (I have several queries that have the same date-closed field.)

Do I need to use VBA for the select button?

Private Sub Days_AfterUpdate()
query!CR_Crosstab!Date_Closed = ???????

[rpeare]

I don't do anything with macros so I would do it with vba

You can populate a list (combo box, list box) with queries or reports you want to be able to run based on the date criteria and just have the command in the ON CLICK event of a button be something like

docmd.openreport (forms!Days_Back!ReportCombo)

I haven't ever tried it this way but there's no reason it shouldn't.

[Thompyt]

I am trying to insert the selected days into the query.
I error out on : DoCmd.OpenReport "By Field Report", acViewReport, "", "", acNormal= Runtime Error 3070 The MS Database engine does not recognize forms!Days!Day as a valid field name or expression.

I used this in the criteria of the query: Field= Date_Closed, Criteria = Between Now()-Between Now()-(Like [Days] & "*") And Now()

I tried VBA:

Private Sub Cancel_By_Field_Report_Click()
    DoCmd.Close acForm, "Select Field"
End Sub
Private Sub Open_By_Field_Report_Click()
        DoCmd.OpenReport "By Field Report", acViewReport, "", "", acNormal
        DoCmd.Close acForm, "Select Field"
End Sub

[rpeare]

don't use reserved words in your object names

DAY is definitely a reserved word, DAYS may also be (but I don't think so) use a compound field/object name instead to be sure

i.e. do NOT Name your field DAY, name it DAYS_REVIEW or something access will choke when you use a reserved word where it's not expecting one.

[Thompyt]

I changed the names to no avail.

In the query under Date_Closed in the Criteria I have: Between Date()-(Like [Forms]![Days_Back]![Daze]) And Now()

In the Days_Back form I have the form with no Recourd Source

Combobox named Daze with no Control Source
Row Source: 7,14,21,28
Row Source Type: Value List
Default: 7

Select button with VBA:
[CODE][Private Sub Daze_Select_Click()
Me.Filter = "B_Dayz=" & Me.Daze
Me.FilterOn = True
DoCmd.OpenReport "Change Request Rollup", acViewReport, "", "", acNormal
End Sub/CODE]

Error out on DoCmd.OpenReport with Runtime error 3450. Bing/Google/Yahoo - no real information.

Hover over Me.Daze Value = 7 as selected

[rpeare]

instead of using a filter why not just use the date range as a criteria in your query. your filter as it is is going to say

B_Dayz=7

if the 7 day option is chosen and you're not even applying the filter when you open the report.

Do this.

Create a query
Add your date_closed field
create a new field with the formula
TodaysDate: Date()
create another new field with the formula
StartDate: date() - forms!days_back!daze
if that doesn't work try
Startdate: dateadd("d", -(forms!days_back!daze), date()

see if the formulas give you expected results in their respective columns.

if they do then it's just a matter of putting the Between criteria together which you can test with another column

DateRangeMatch: iif([date_closed] between (date() - forms!days_back!daze) and date(), 1, 0)

this should populate a 1 for a record that matches the date range and a 0 for a non match, if that works then you just have to plug date calculation into the criteria of date_closed

between (date() - forms!days_back!daze) and date()

[Thompyt]

rpeare,
I think the issue is revolving around a crosstab query. I can put in the code for Date_Closed criteria as : Between Now()-([Forms]![Days_Back]![Daze]) AND Now() in a reqular query and it works fine. Such as TTL Approved:

[SELECT Nz(Count([Level]),0) AS [TTL Approved]
FROM Chng_ReqQry
WHERE (((Chng_ReqQry.Votes)="Approved") AND ((Chng_ReqQry.[Change Requested])<>"Do not delete") AND ((Chng_ReqQry.Date_Closed) Between Now()-([Forms]![Days_Back]![Daze]) And Now()) AND ((Chng_ReqQry.Sub_No)=0))
HAVING (((Nz(Count([Level]),0)) Is Not Null));

If I put it in the CR_Crosstab or I add the crosstab query in a summery query (Where I roll-up all the TTL Queries) I get the usual - MS Access does not recognize [Forms]![Days_Back]![Daze] as a valid Field Name or expression.

[rpeare]

ahhhh the first time you mentioned the final product being a crosstab (other than query names) I just didn't think of it. Have you tried making your root query a parameter query defining your [forms]![days_back]![daze] as an integer in the parameter window? that might get rid of the pop up.

[Thompyt]

I tried making it a field in my root query as Dazes Criteria = forms!Days_Back!Daze, then when I put Daze in place of the digit and had daze as a field in the crosstab query, it did not recognize Dazes as a field. I even tried putting Daze in the base table as a field. Same thing.

[rpeare]

sample database please!

Just make something with garbage data that can replicate the problem and the final query/report you're trying to run.

[Thompyt]

Combined - Copy.zip

Cleaned up quickly the queries where the days_back form is linked to and works all begin with "Copy of TTL" and one "Copy of Summary"

[rpeare]

can you do a screen shot of the references you have enabled so I don't error out on this db.

[Thompyt]

I am using Office 13, 2010 uses 14 instead of where there are 15s