473,326 Members | 2,192 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

How to update date criteria for multiple queries at once?

Hi all,

I've googled and googled but I can google no more, so I am here to ask for help on an issue I am having.

I have a macro that runs a series of queries. Before the macro can be run, the date criteria of maybe six or seven queries needs to be updated to reflect the current week ending date. The simple way that I usually do this is to make a form and have the queries grab the date criteria entered on the form and then have a button to run the macro. The roadblock I am facing is that the macro is actually run overnight on Sunday night using a scheduled task and the user isn't actually here to enter in the date criteria. The user currently has to update all the queries manually with the correct date criteria every week. I'd rather she not have to find the several queries and update the date manually beforehand, especially since the number of queries she will have to update is going to expand in the near future.

Does anyone have any suggested ways of overcoming this? Perhaps there is a way I can store a date in a variable that I can have the user define every week and then use the variable as my criteria in my queries?

Let me know if more information is needed. Some step-by-step help is appreciated.

Thanks in advance,

Raza Zahur
Feb 16 '11 #1
9 6199
gnawoncents
214 100+
So if I understand correctly, you need a form field to auto update to reflect the current week ending date. Is that correct?

If so, are we talking about the work week (Friday) or the calendar week (Saturday)? Also, if it is the work week, do we need to consider holidays? This can all be done with coding, but we need a bit more data. Thanks!
Feb 16 '11 #2
Rabbit
12,516 Expert Mod 8TB
You could store it in a table.
Feb 16 '11 #3
gnawoncents: The macro is run on Monday morning (at like midnight). The week ending date is always the Saturday of each week, regardless of holidays. I also, however, want to avoid putting in the logic of just taking the Saturday of the week as the criteria since there are situations where we run it, either as a scheduled task or manually, for a week ending date that is not the current week.

Rabbit: I've thought of this and tried to execute it but had trouble executing it. I set it up so the user could input the week ending date in the table. However, I didn't know how to qualify the criteria in my queries. I tried to build it but I never got it to work. Perhaps you can help me with this step-by-step? After I put it in the table, what do I input as the criteria for the query?
Feb 16 '11 #4
Rabbit
12,516 Expert Mod 8TB
Something like
Expand|Select|Wrap|Line Numbers
  1. SELECT FieldName FROM TableName WHERE DateField = 
  2. (SELECT DateField FROM DateTable)
Feb 16 '11 #5
Rabbit: I don't have time to try that again today, I will attempt it again tomorrow and post my success or failure.

Thanks for the help.
Feb 16 '11 #6
NeoPa
32,556 Expert Mod 16PB
Why not just use Date()+7-Weekday(Date,vbSunday) in your queries instead.

The vbSunday parameter (FirstDayOfWeek) is actually the default value. I only include it to illustrate what needs to be changed if you're not looking for the end of the week falling on a Saturday.
Feb 17 '11 #7
NeoPa
32,556 Expert Mod 16PB
Raza:
gnawoncents: The macro is run on Monday morning (at like midnight). The week ending date is always the Saturday of each week, regardless of holidays. I also, however, want to avoid putting in the logic of just taking the Saturday of the week as the criteria since there are situations where we run it, either as a scheduled task or manually, for a week ending date that is not the current week.
Ah. That changes things somewhat. Well, quite a lot actually. A different question from the original then.

That makes the solution proposed by Rabbit quite appropriate. I would use SQL similar to :
Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM   [TableName] AS t1
  3.      , [DateTable] AS t2
  4. WHERE  t1.DateField<=t2.DateField
You could even make it easier for the operator to update this by designing a form to allow the record (as this particular table would typically have only one record in it, ever) to be updated. This would default to a value reflecting the end of the current week, but the operator could override if they required.
Feb 17 '11 #8
NeoPa: That works for me in a regular query, but I probably should have also mentioned that I am trying to qualify the dates of aggregate queries. Is this going to work if I'm using the criteria in an aggregate query? I attempted to do it in this sql code:


Expand|Select|Wrap|Line Numbers
  1. INSERT INTO [Raw Data Ending Inventory] ( DC_NUM, [Whse Area Cd], WHSE_CLSS_CD, BU, [PCS CD], NG_ITEM_NUM, [Item Desc], [PCS Cat Cd], COST_AMT1, COST_AMT, [Cost Ty Cd], BILL_OH_WGT_QTY, DMG_OH_WGT_QTY, HOLD_OH_WGT_QTY, [TOT WMS Var Inv], SumOfBILL_OH_QTY, DMG_OH_QTY, SumOfHOLD_OH_QTY, [TOT WMS Non Var Inv], [TOT WMS Inv], [Rcv Var Wht Ind], [PCS Desc], [EXT cost (IK and IC)], [EXT cost], Category, INVN_DT, [Combo Cd], [Whse combo desc], Province, [Item Category], [PCS Cat desc], [PCS 4 Digit CD], [PCS 6 Digit CD], [PCS 4 Digit Desc], [PCS 6 Digit Desc] )
  2. SELECT NDW_DC_ITEM_INVN.DC_NUM, "aa" AS [Whse Area Cd], "AA" AS WHSE_CLSS_CD, "fdsa" AS BU, "abcdefgh" AS [PCS CD], NDW_DC_ITEM_INVN.NG_ITEM_NUM, "asdf" AS [Item Desc], "aa" AS [PCS Cat Cd], 0.00000001 AS COST_AMT1, 0.00000001 AS COST_AMT, "as" AS [Cost Ty Cd], NDW_DC_ITEM_INVN.BILL_OH_WGT_QTY, NDW_DC_ITEM_INVN.DMG_OH_WGT_QTY, NDW_DC_ITEM_INVN.HOLD_OH_WGT_QTY, Sum((([BILL_OH_WGT_QTY]+[DMG_OH_WGT_QTY]+[HOLD_OH_WGT_QTY]))) AS [TOT WMS Var Inv], Sum(NDW_DC_ITEM_INVN.BILL_OH_QTY) AS SumOfBILL_OH_QTY, NDW_DC_ITEM_INVN.DMG_OH_QTY, Sum(NDW_DC_ITEM_INVN.HOLD_OH_QTY) AS SumOfHOLD_OH_QTY, Sum((([BILL_OH_QTY]+[DMG_OH_QTY]+[HOLD_OH_QTY]))) AS [TOT WMS Non Var Inv], 0 AS [TOT WMS Inv], "a" AS [Rcv Var Wht Ind], "asdf" AS [PCS Desc], 0.00000001 AS [EXT cost (IK and IC)], 0.000000001 AS [EXT cost], "asdf" AS Category, NDW_DC_ITEM_INVN.INVN_DT, 0 AS [Combo Cd], "aaa" AS [Whse combo desc], "asd" AS Province, "asd" AS [Item Category], "asdf" AS [PCS Cat desc], "asdf" AS [PCS 4 Digit CD], "asdf" AS [PCS 6 Digit CD], "asdf" AS [PCS 4 Digit Desc], "asdf" AS [PCS 6 Digit Desc]
  3. FROM NDW_DC_ITEM_INVN
  4. GROUP BY NDW_DC_ITEM_INVN.DC_NUM, "aa", "AA", "fdsa", "abcdefgh", NDW_DC_ITEM_INVN.NG_ITEM_NUM, "asdf", "aa", 0.00000001, 0.00000001, "as", NDW_DC_ITEM_INVN.BILL_OH_WGT_QTY, NDW_DC_ITEM_INVN.DMG_OH_WGT_QTY, NDW_DC_ITEM_INVN.HOLD_OH_WGT_QTY, NDW_DC_ITEM_INVN.DMG_OH_QTY, 0, "a", "asdf", 0.00000001, 0.000000001, "asdf", NDW_DC_ITEM_INVN.INVN_DT, 0, "aaa", "asd", "asd", "asdf", "asdf", "asdf", "asdf", "asdf"
  5. HAVING (((NDW_DC_ITEM_INVN.INVN_DT)=#1/1/2011#));
I tried to do it by adding my DateTable to the from statement and replacing the HAVING statement with HAVING (((NDW_DC_ITEM_INVN.INVN_DT)=datetable.wedate)). The error I got is "You tried to execute a query that does not include the specified expression 'ndw_dc_item_invn.invn_dt=datetable.wedate' as part of an aggregate function." I tried using Where instead of Having (don't know the difference) but that didn't work either.

Any suggestions? Should I run the aggregate query first without the date qualifier and then make another query based off of that?

Thanks.
Feb 17 '11 #9
NeoPa
32,556 Expert Mod 16PB
WHERE provides filtering for the incoming (pre-processed) data.
HAVING provides filtering for the outgoing (processed/aggregated) data.

In this case you would do better to specify this in your WHERE clause (which clearly you need to add).

In some cases, depending on your table structure (which we know almost nothing of), it is possible to link the tables together using an INNER JOIN. This would then obviate the need for using the WHERE clause. I probably should have used this form in my earlier illustration. Anyway, here it is now in case it helps :

Expand|Select|Wrap|Line Numbers
  1. SELECT *
  2. FROM   [TableName] AS t1
  3.        INNER JOIN
  4.        [DateTable] AS t2
  5.   ON   t1.DateField<=t2.DateField
Feb 18 '11 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

11
by: David B | last post by:
I have 2 querys with the following in the date criteria Between DateAdd("m",-6,(!!)) And (!!) Between DateAdd("m",-24,(!!)) And DateAdd("m",-6,(!!)) Problem is they both look at the same...
2
by: DaveDiego | last post by:
I'm building a report that has a count of cases for employees. I have separate queries that count, OpenCases, NewCases, TotalCases, Etc. I would like to put all those counts into a record for each...
4
by: Dave Edwards | last post by:
I understand that I can fill a datagrid with multiple queries, but I cannot figure out how to fill a dataset with the same query but run against multiple SQL servers, the query , table structure...
8
by: beretta819 | last post by:
Ok, so I apologize in advance for the wordiness of what follows... (I am not looking for someone to make this for me, but to point me in the right direction for the steps I need to take.) I was...
7
by: vaiism | last post by:
I am creating a report that outputs the contact information and details about a water treatment plant, and needs to include information about people who work there. If I tie all the information...
1
matrekz42
by: matrekz42 | last post by:
Good morning Gurus, I'm trying to execute the following code to run multiple queries, and update the progress bar on a form, but it doesnt seem to respond. Do I have too many queries, what am I...
0
by: zeusspandex | last post by:
Im creating a cross tab query which sorts via and sums the for each type of . I want to be able to add a date criteria, so the user can specify that the query processes data between two dates. ...
1
by: zeusspandex | last post by:
Hi, i have the following SQL query and would like to add date criteria to it. the field being from the table and the criteria being Between !! And !! query: SELECT , ...
2
by: DigiLife | last post by:
Greetings all, I am trying to create a form in access 2007 that will return multiple queries (count). I have an appointment table and based off of the date selection I want a grid similar to "Cheap...
5
by: sh55555 | last post by:
I have built a query in Access using the DateSerial function to convert dates such as 20100401 to 04/01/2010. The query results work fine and the date is displayed correctly. I am now trying to...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.