Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Query Criteria Default into a constant date

Posted on 2004-09-01
17
Medium Priority
?
302 Views
Last Modified: 2008-02-20
Is is possible to do the following:

I need to put into a query criteria that will alway default into next Monday date plus 49 days all time, even if I am in a Monday date it will default into next Monday date.

Thanks

perennial
0
Comment
Question by:perennial
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 4
17 Comments
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11959504
This expression will give you the date you want:
Date()+(58-Weekday(Date(),7))
0
 
LVL 1

Author Comment

by:perennial
ID: 11959551
My query is an append query, it said it's going to append zero record. I know the table that I am pulling from have over 200 records.

??
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11959557
Can you post the whole SQL of the query including the criteria above?
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 1

Author Comment

by:perennial
ID: 11959625
Here is the sql:

INSERT INTO tblSS ( ISNo, ItemNo, [Size], DatSowd, WkSowd, WkGoal, WkCutQty )
SELECT Qs36fFdforrd.ISNo, Qs36fFdforrd.ItemNo, Qs36fFdforrd.Size, Qs36fFdforrd.DatSowd, Qs36fFdforrd.WkSowd, Qs36fFdforrd.LRINVQ, (([Size])*([LRINVQ])) AS WkCutQty
FROM Qs36fFdforrd
WHERE (((Qs36fFdforrd.DatSowd)=Date()+(58-Weekday(Date(),7))));
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 11959628
place this function in a module

Function GetMonDate(CurrentDate)
   If VarType(CurrentDate) <> 7 Then
      GetMonDate = Null
   Else
      Select Case Weekday(CurrentDate)
         Case 1       ' Sunday
            GetMonDate = CurrentDate - 6
         Case 2       ' Monday
            GetMonDate = CurrentDate + 7
         Case 3 To 7  ' Tuesday..Saturday
            GetMonDate = CurrentDate - Weekday(CurrentDate) + 2
       End Select
   End If
End Function

then you can use the function this way

GetMonDate(Date()) + 49

0
 
LVL 1

Author Comment

by:perennial
ID: 11959642
capricorn1;

Should I use it as a public function or just a function?
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11959643
My mistake, the 58 should be 59:

INSERT INTO tblSS ( ISNo, ItemNo, [Size], DatSowd, WkSowd, WkGoal, WkCutQty )
SELECT Qs36fFdforrd.ISNo, Qs36fFdforrd.ItemNo, Qs36fFdforrd.Size, Qs36fFdforrd.DatSowd, Qs36fFdforrd.WkSowd, Qs36fFdforrd.LRINVQ, (([Size])*([LRINVQ])) AS WkCutQty
FROM Qs36fFdforrd
WHERE (((Qs36fFdforrd.DatSowd)=Date()+(59-Weekday(Date(),7))));
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11959649
Using my expression with today's date gives 25th October as the query date.
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 1000 total points
ID: 11959652
sorry use this function instead, you can use either Public or just function

Function GetMonDate(CurrentDate)
   If VarType(CurrentDate) <> 7 Then
      GetMonDate = Null
   Else
      Select Case Weekday(CurrentDate)
         Case 1       ' Sunday
            GetMonDate = CurrentDate + 1
         Case 2       ' Monday
            GetMonDate = CurrentDate + 7
         Case 3 To 7  ' Tuesday..Saturday
            GetMonDate = CurrentDate + 7 - Weekday(CurrentDate) + 2
       End Select
   End If
End Function


 

try this

WHERE (((Qs36fFdforrd.DatSowd)=GetMonDate(Date())+49))));

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 11959692
if you set the date to sep 4 or sep 5

This      Date()+(59-Weekday(Date(),7)     will give you 11/1/2204 which is one week ahead.

           GetMonDate(Date())+49)     will give you 10/25/2004
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11959734
Hmm,  so it does.

This is correct though:
Date()+IIf(Weekday(Date(),2)=1,7,(7-Weekday(Date(),3)))+49
0
 
LVL 1

Author Comment

by:perennial
ID: 11959792
My apology, I wasn't clear on my explaination.

I have a batch data that the "DatSowd" is always on a Monday. What I would like is to move seven week worth of data and append into another table.

My arguement is that whenever the query run I want it to grap 7 monday from the table. Eg: for this week when the query run it would grap all data with the following "DatSowd"  --- 9/6/04, 9/13/04, 9/20/04, 9/27/04, 10/4/04, 10/11/04, 10/18/04.

Sorry...

perennial
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 1000 total points
ID: 11959814
Hmm - this should do it, I think:

INSERT INTO tblSS ( ISNo, ItemNo, [Size], DatSowd, WkSowd, WkGoal, WkCutQty )
SELECT Qs36fFdforrd.ISNo, Qs36fFdforrd.ItemNo, Qs36fFdforrd.Size, Qs36fFdforrd.DatSowd, Qs36fFdforrd.WkSowd, Qs36fFdforrd.LRINVQ, (([Size])*([LRINVQ])) AS WkCutQty
FROM Qs36fFdforrd
WHERE Qs36fFdforrd.DatSowd Between (Date()+IIf(Weekday(Date(),2)=1,7,(7-Weekday(Date(),3)))) And (Date()+IIf(Weekday(Date(),2)=1,7,(7-Weekday(Date(),3)))+42)
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 11959978
try this

WHERE Qs36fFdforrd.DatSowd Between GetMonDate(Date()) And GetMonDate(Date())+49
0
 
LVL 1

Author Comment

by:perennial
ID: 11960097
The both of you provide me with two great answer here. Thank you very much. If either one of you have no objection, I would like to split the points between you two?

Again, thank you very much both answers are excellent!!!

Perennial
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11960113
No problem here with me :)
0
 
LVL 1

Author Comment

by:perennial
ID: 11960145
Thanks....
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

598 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question