Date Field plus X days

  • Thread starter Thread starter FJB
  • Start date Start date
F

FJB

I have searched and not found THE answer to this problem. I have a date
field which I need to find the date a week later, i.e. Date to Close +
7 = DateToCheck. No matter what I try in the query to insert the
expression, whenever I run the query I am asked to supply a parameter.
This query has no parameters associated. What am I doing wrong?

Thanks for your help. You've have gotten me out of some tight spots
before, I am counting on you now.

Frank
 
I have searched and not found THE answer to this problem. I have a date
field which I need to find the date a week later, i.e. Date to Close +
7 = DateToCheck. No matter what I try in the query to insert the
expression, whenever I run the query I am asked to supply a parameter.
This query has no parameters associated. What am I doing wrong?
Click to expand...

I have no idea, since you don't say what you're doing. It's always
polite to post the actual SQL of queries when you're having trouble.
Thanks for your help. You've have gotten me out of some tight spots
before, I am counting on you now.
Click to expand...

I would suggest simply typing

DateToCheck: DateAdd("d", 7, [Date to Close])

in a vacant Field cell of the query. This field should almost
certainly NOT be stored in your table at all, if that's what you're
trying to do.

John W. Vinson[MVP]
 
FJB said:
I have searched and not found THE answer to this problem. I have a date
field which I need to find the date a week later, i.e. Date to Close +
7 = DateToCheck. No matter what I try in the query to insert the
expression, whenever I run the query I am asked to supply a parameter.
This query has no parameters associated. What am I doing wrong?

Thanks for your help. You've have gotten me out of some tight spots
before, I am counting on you now.
Click to expand...


When you have a question about a query, you should post a
Copy/Paste of the query's SQL view so we can see what you're
talking about.

I think(?) you can enter this in an empty field in your
query:

However it would be more rigorous to use:
DateToCheck: DateAdd("d", 7, [Date to Close])
 
"Date to Close + 7 = DateToCheck"

variable to assign must be on the left side of the equal sign

since [Date To Close] includes spaces, it must be delimited
with brackets

It is a good idea not to use spaces or special characters in
fieldnames. It is best to use letters, numbers (sparingly,
you don't want repeating groups), and the underscore
character... and never START a fieldname with a numeric
character ;)

John gave you the equation to use on the grid; in SQL, this
would be

DateAdd("d", 7, [Date to Close]) AS DateToCheck

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com


John said:
I have searched and not found THE answer to this problem. I have a date
field which I need to find the date a week later, i.e. Date to Close +
7 = DateToCheck. No matter what I try in the query to insert the
expression, whenever I run the query I am asked to supply a parameter.
This query has no parameters associated. What am I doing wrong?
Click to expand...


I have no idea, since you don't say what you're doing. It's always
polite to post the actual SQL of queries when you're having trouble.

Thanks for your help. You've have gotten me out of some tight spots
before, I am counting on you now.
Click to expand...


I would suggest simply typing

DateToCheck: DateAdd("d", 7, [Date to Close])

in a vacant Field cell of the query. This field should almost
certainly NOT be stored in your table at all, if that's what you're
trying to do.

John W. Vinson[MVP]
Click to expand...
 
This is the SQL view of the query to which I would like to add another
field asoriginally explained>

SELECT [SAR Filings].[AMLOC Decision Date], [SAR Filings].[AMLOC
Decision], [SAR Filings].[AMLOC?], [SAR Filings].[Early AMLOC], [SAR
Filings].[Relationship Name], [SAR Filings].[Client Number], [SAR
Filings].[ICMS Case Number], [SAR Filings].[FIU Investigator], [SAR
Filings].[SSN/TIN], [SAR Filings].[Account Number], [SAR Filings].[To
Close By], [SAR Filings].[Number of SARs filed], [SAR Filings].LOB,
[SAR Filings].[Other LOB Relationships], [SAR Filings].[Reporting
Region], [SAR Filings].[SAR Filed Date], [SAR Filings].[Previous SAR
Date 1], [SAR Filings].[Previous SAR Date 2], [SAR Filings].[Case
Category], [SAR Filings].[Case Category 2], [SAR Filings].Rank, [SAR
Filings].[Risk Level], [SAR Filings].[Risk Category], [SAR
Filings].Occupation, [SAR Filings].Employer, [SAR Filings].Comments,
[SAR Filings].[LE Letter], [SAR Filings].Employee, Group_Region.Group,
Group_Region.[Region Name], [SAR Filings].[Cost Center], [SAR
Filings].[Current Relationship Status], [STI Branch Listing-OLD].NAME,
[STI Branch Listing-OLD].CITY, [STI Branch Listing-OLD].STATE, [SAR
Filings].[Prior SAR Category #1], [SAR Filings].[Prior SAR Category
#2], [SAR Filings].[Current Relationship Status], [SAR
Filings].Description, [SAR Filings].[Date Account Opened]
FROM (Group_Region INNER JOIN [SAR Filings] ON Group_Region.[Reporting
Region] = [SAR Filings].[Reporting Region]) INNER JOIN [STI Branch
Listing-OLD] ON [SAR Filings].[Cost Center] = [STI Branch
Listing-OLD].CC
WHERE ((([SAR Filings].[AMLOC Decision Date]) Is Not Null) AND (([SAR
Filings].[AMLOC?])=Yes) AND (([SAR Filings].[To Close By]) Is Null)) OR
((([SAR Filings].[AMLOC Decision Date]) Is Not Null) AND (([SAR
Filings].[Early AMLOC])=Yes))
ORDER BY [SAR Filings].[AMLOC Decision Date], [SAR
Filings].[Relationship Name];
 
For some reason, I cannot see your original message, only
John's response...

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)
 
This is the SQL view of the query to which I would like to add another
field asoriginally explained>
Click to expand...

Did you even try my previous answer, amplified by Crystal's
explanation?

SELECT [SAR Filings].[AMLOC Decision Date], [SAR Filings].[AMLOC
Decision], [SAR Filings].[AMLOC?], [SAR Filings].[Early AMLOC], [SAR
Filings].[Relationship Name], [SAR Filings].[Client Number], [SAR
Filings].[ICMS Case Number], [SAR Filings].[FIU Investigator], [SAR
Filings].[SSN/TIN], [SAR Filings].[Account Number], [SAR Filings].[To
Close By], [SAR Filings].[Number of SARs filed], [SAR Filings].LOB,
[SAR Filings].[Other LOB Relationships], [SAR Filings].[Reporting
Region], [SAR Filings].[SAR Filed Date], [SAR Filings].[Previous SAR
Date 1], [SAR Filings].[Previous SAR Date 2], [SAR Filings].[Case
Category], [SAR Filings].[Case Category 2], [SAR Filings].Rank, [SAR
Filings].[Risk Level], [SAR Filings].[Risk Category], [SAR
Filings].Occupation, [SAR Filings].Employer, [SAR Filings].Comments,
[SAR Filings].[LE Letter], [SAR Filings].Employee, Group_Region.Group,
Group_Region.[Region Name], [SAR Filings].[Cost Center], [SAR
Filings].[Current Relationship Status], [STI Branch Listing-OLD].NAME,
[STI Branch Listing-OLD].CITY, [STI Branch Listing-OLD].STATE, [SAR
Filings].[Prior SAR Category #1], [SAR Filings].[Prior SAR Category
#2], [SAR Filings].[Current Relationship Status], [SAR
Filings].Description, [SAR Filings].[Date Account Opened],
DateAdd("d", 7, [Date To Close]) AS [Date To Check]
FROM (Group_Region INNER JOIN [SAR Filings] ON Group_Region.[Reporting
Region] = [SAR Filings].[Reporting Region]) INNER JOIN [STI Branch
Listing-OLD] ON [SAR Filings].[Cost Center] = [STI Branch
Listing-OLD].CC
WHERE ((([SAR Filings].[AMLOC Decision Date]) Is Not Null) AND (([SAR
Filings].[AMLOC?])=Yes) AND (([SAR Filings].[To Close By]) Is Null))
OR
((([SAR Filings].[AMLOC Decision Date]) Is Not Null) AND (([SAR
Filings].[Early AMLOC])=Yes))
ORDER BY [SAR Filings].[AMLOC Decision Date], [SAR
Filings].[Relationship Name];

Bear in mind I cannot see your tables, and have only the sketchiest
idea of what you actually WANT - but this is what you ASKED for (which
may of course be different).

John W. Vinson[MVP]
 
Back
Top