• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 306
  • Last Modified:

Query Criteria Default into a constant date

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
perennial
Asked:
perennial
  • 7
  • 6
  • 4
2 Solutions
 
shanesuebsahakarnCommented:
This expression will give you the date you want:
Date()+(58-Weekday(Date(),7))
0
 
perennialAuthor Commented:
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
 
shanesuebsahakarnCommented:
Can you post the whole SQL of the query including the criteria above?
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
perennialAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
perennialAuthor Commented:
capricorn1;

Should I use it as a public function or just a function?
0
 
shanesuebsahakarnCommented:
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
 
shanesuebsahakarnCommented:
Using my expression with today's date gives 25th October as the query date.
0
 
Rey Obrero (Capricorn1)Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
shanesuebsahakarnCommented:
Hmm,  so it does.

This is correct though:
Date()+IIf(Weekday(Date(),2)=1,7,(7-Weekday(Date(),3)))+49
0
 
perennialAuthor Commented:
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
 
shanesuebsahakarnCommented:
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
 
Rey Obrero (Capricorn1)Commented:
try this

WHERE Qs36fFdforrd.DatSowd Between GetMonDate(Date()) And GetMonDate(Date())+49
0
 
perennialAuthor Commented:
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
 
shanesuebsahakarnCommented:
No problem here with me :)
0
 
perennialAuthor Commented:
Thanks....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 7
  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now