Solved

Query Criteria Default into a constant date

Posted on 2004-09-01
17
276 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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 250 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 250 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

803 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