Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Query Criteria Default into a constant date

Posted on 2004-09-01
17
Medium Priority
?
304 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
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

564 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