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
LVL 1
perennialAsked:
Who is Participating?
 
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
 
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
shanesuebsahakarnCommented:
Can you post the whole SQL of the query including the criteria above?
0
 
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
 
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.

All Courses

From novice to tech pro — start learning today.