Date query, subtract 3 months

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to run a query that reads a fiscal quarter and year off a date (called
InStoreDate). I have managed to use DatePart("q".... to identify the quarter
and similarly the year of the actual date.
Problem is I need the fiscal quarter and year, i.e. 3 months earlier. I'm
thinking the easiest way is to create a bogus (hidden) date that subtracts 3
months from 'InStoreDate' and then use DatePart.... to get the fiscal quarter
and year.
Thing is, don't know how to create this 'bogus' date.
Any help or alternative suggestions greatly appreciated as always.
 
To get the "bogus" date, create a calculated field in the query and subtract
3 months.

Example Field:
BogusDate:DateAdd("m", -3, [InStoreDate])

This will create a field in the query's output called BogusDate. It will
have a date 3 months earlier than InStoreDate.
 
Thanks Wayne,
it works fine.
But now I'm trying to fit this into a parameter query where it does not seem
to work. I'm guessing it has something to do with a parameter query on a
calculated or derived field.
This might be overkill but I've added the SQL script of the entire query
below.....

PARAMETERS [Forms]![Artwork query]![Company] Text ( 255 ), [Forms]![Artwork
query]![Category] Text ( 255 ), [Forms]![Artwork query]![StartDate] DateTime,
[Forms]![Artwork query]![EndDate] DateTime;
SELECT Company.Company, [Artwork approval].InStoreDate,
DateAdd("m",-3,[InStoreDate]) AS BogusDate, DatePart("q",[BogusDate]) AS
FiscalQuarter, Year([BogusDate]) AS FiscalYear, DatePart("q",[InStoreDate])
AS InStoreQuarter, Year([InstoreDate]) AS InStoreYear, [Artwork
approval].Retailer, [Artwork approval].Character, [Artwork
approval].Category, [Artwork approval].Units, [Artwork
approval].SellingPrice, [Artwork approval].Royalty
FROM Company INNER JOIN [Artwork approval] ON Company.CompanyID=[Artwork
approval].CompanyID
WHERE (((Company.Company) Like Forms![Artwork query]!Company & "*") And
((DatePart("q",[BogusDate])) Like Forms![Artwork query]!Qrtr & "*") And
((Year([BogusDate])) Like Forms![Artwork query]!Year & "*") And (([Artwork
approval].Category) Like Forms![Artwork query]!Category & "*") And
((Forms![Artwork query]!StartDate) Is Null))
Or (((Company.Company) Like Forms![Artwork query]!Company & "*") And
((DatePart("q",[BogusDate])) Like Forms![Artwork query]!Qrtr & "*") And
((Year([BogusDate])) Like Forms![Artwork query]!Year & "*") And (([Artwork
approval].Category) Like Forms![Artwork query]!Category & "*") And
((Forms![Artwork query]!EndDate) Is Null))
Or (((Company.Company) Like Forms![Artwork query]!Company & "*") And
(([Artwork approval].InStoreDate) Between Forms![Artwork query]!StartDate And
Forms![Artwork query]!EndDate) And ((DatePart("q",[BogusDate])) Like
Forms![Artwork query]!Qrtr & "*") And ((Year([BogusDate])) Like
Forms![Artwork query]!Year & "*") And (([Artwork approval].Category) Like
Forms![Artwork query]!Category & "*"));


Wayne Morgan said:
To get the "bogus" date, create a calculated field in the query and subtract
3 months.

Example Field:
BogusDate:DateAdd("m", -3, [InStoreDate])

This will create a field in the query's output called BogusDate. It will
have a date 3 months earlier than InStoreDate.

--
Wayne Morgan
MS Access MVP


Graeme at Raptup said:
I need to run a query that reads a fiscal quarter and year off a date
(called
InStoreDate). I have managed to use DatePart("q".... to identify the
quarter
and similarly the year of the actual date.
Problem is I need the fiscal quarter and year, i.e. 3 months earlier. I'm
thinking the easiest way is to create a bogus (hidden) date that subtracts
3
months from 'InStoreDate' and then use DatePart.... to get the fiscal
quarter
and year.
Thing is, don't know how to create this 'bogus' date.
Any help or alternative suggestions greatly appreciated as always.
Click to expand...
Click to expand...
 
At first glance, I don't see a problem with the SQL. I would recommend
breaking it down. Start by removing the WHERE clause and see if the rest
works.

Do you really need all of the Like tests? I would expect the quarter or year
to be equal or not equal.

Do you get any error messages? What is it doing or not doing?
 
The reason I have them as Like tests is that I though I needed them so that
the user has the option not to enter any value (i.e. null values).
The message is not so much an error message than the query does not work.
What is happening is that I get a prompt "Enter parameter value" for
'BogusDate'.
Now 'Bogus Date' is a derived or calculated field and I'm thinking that is
where the problem lies.
If I enter on the prompt (as a null value) the query returns no records.

Wayne Morgan said:
At first glance, I don't see a problem with the SQL. I would recommend
breaking it down. Start by removing the WHERE clause and see if the rest
works.

Do you really need all of the Like tests? I would expect the quarter or year
to be equal or not equal.

Do you get any error messages? What is it doing or not doing?

--
Wayne Morgan
MS Access MVP


Graeme at Raptup said:
Thanks Wayne,
it works fine.
But now I'm trying to fit this into a parameter query where it does not
seem
to work. I'm guessing it has something to do with a parameter query on a
calculated or derived field.
This might be overkill but I've added the SQL script of the entire query
below.....

PARAMETERS [Forms]![Artwork query]![Company] Text ( 255 ),
[Forms]![Artwork
query]![Category] Text ( 255 ), [Forms]![Artwork query]![StartDate]
DateTime,
[Forms]![Artwork query]![EndDate] DateTime;
SELECT Company.Company, [Artwork approval].InStoreDate,
DateAdd("m",-3,[InStoreDate]) AS BogusDate, DatePart("q",[BogusDate]) AS
FiscalQuarter, Year([BogusDate]) AS FiscalYear,
DatePart("q",[InStoreDate])
AS InStoreQuarter, Year([InstoreDate]) AS InStoreYear, [Artwork
approval].Retailer, [Artwork approval].Character, [Artwork
approval].Category, [Artwork approval].Units, [Artwork
approval].SellingPrice, [Artwork approval].Royalty
FROM Company INNER JOIN [Artwork approval] ON Company.CompanyID=[Artwork
approval].CompanyID
WHERE (((Company.Company) Like Forms![Artwork query]!Company & "*") And
((DatePart("q",[BogusDate])) Like Forms![Artwork query]!Qrtr & "*") And
((Year([BogusDate])) Like Forms![Artwork query]!Year & "*") And (([Artwork
approval].Category) Like Forms![Artwork query]!Category & "*") And
((Forms![Artwork query]!StartDate) Is Null))
Or (((Company.Company) Like Forms![Artwork query]!Company & "*") And
((DatePart("q",[BogusDate])) Like Forms![Artwork query]!Qrtr & "*") And
((Year([BogusDate])) Like Forms![Artwork query]!Year & "*") And (([Artwork
approval].Category) Like Forms![Artwork query]!Category & "*") And
((Forms![Artwork query]!EndDate) Is Null))
Or (((Company.Company) Like Forms![Artwork query]!Company & "*") And
(([Artwork approval].InStoreDate) Between Forms![Artwork query]!StartDate
And
Forms![Artwork query]!EndDate) And ((DatePart("q",[BogusDate])) Like
Forms![Artwork query]!Qrtr & "*") And ((Year([BogusDate])) Like
Forms![Artwork query]!Year & "*") And (([Artwork approval].Category) Like
Forms![Artwork query]!Category & "*"));
Click to expand...
Click to expand...
 
((DatePart("q",[BogusDate])) Like Forms![Artwork query]!Qrtr & "*")
definitely won't work, as DatePart returns a numeric value, and you can't
use Like with numeric values.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Graeme at Raptup said:
The reason I have them as Like tests is that I though I needed them so
that
the user has the option not to enter any value (i.e. null values).
The message is not so much an error message than the query does not work.
What is happening is that I get a prompt "Enter parameter value" for
'BogusDate'.
Now 'Bogus Date' is a derived or calculated field and I'm thinking that is
where the problem lies.
If I enter on the prompt (as a null value) the query returns no records.

Wayne Morgan said:
At first glance, I don't see a problem with the SQL. I would recommend
breaking it down. Start by removing the WHERE clause and see if the rest
works.

Do you really need all of the Like tests? I would expect the quarter or
year
to be equal or not equal.

Do you get any error messages? What is it doing or not doing?

--
Wayne Morgan
MS Access MVP


Graeme at Raptup said:
Thanks Wayne,
it works fine.
But now I'm trying to fit this into a parameter query where it does not
seem
to work. I'm guessing it has something to do with a parameter query on
a
calculated or derived field.
This might be overkill but I've added the SQL script of the entire
query
below.....

PARAMETERS [Forms]![Artwork query]![Company] Text ( 255 ),
[Forms]![Artwork
query]![Category] Text ( 255 ), [Forms]![Artwork query]![StartDate]
DateTime,
[Forms]![Artwork query]![EndDate] DateTime;
SELECT Company.Company, [Artwork approval].InStoreDate,
DateAdd("m",-3,[InStoreDate]) AS BogusDate, DatePart("q",[BogusDate])
AS
FiscalQuarter, Year([BogusDate]) AS FiscalYear,
DatePart("q",[InStoreDate])
AS InStoreQuarter, Year([InstoreDate]) AS InStoreYear, [Artwork
approval].Retailer, [Artwork approval].Character, [Artwork
approval].Category, [Artwork approval].Units, [Artwork
approval].SellingPrice, [Artwork approval].Royalty
FROM Company INNER JOIN [Artwork approval] ON
Company.CompanyID=[Artwork
approval].CompanyID
WHERE (((Company.Company) Like Forms![Artwork query]!Company & "*") And
((DatePart("q",[BogusDate])) Like Forms![Artwork query]!Qrtr & "*") And
((Year([BogusDate])) Like Forms![Artwork query]!Year & "*") And
(([Artwork
approval].Category) Like Forms![Artwork query]!Category & "*") And
((Forms![Artwork query]!StartDate) Is Null))
Or (((Company.Company) Like Forms![Artwork query]!Company & "*") And
((DatePart("q",[BogusDate])) Like Forms![Artwork query]!Qrtr & "*") And
((Year([BogusDate])) Like Forms![Artwork query]!Year & "*") And
(([Artwork
approval].Category) Like Forms![Artwork query]!Category & "*") And
((Forms![Artwork query]!EndDate) Is Null))
Or (((Company.Company) Like Forms![Artwork query]!Company & "*") And
(([Artwork approval].InStoreDate) Between Forms![Artwork
query]!StartDate
And
Forms![Artwork query]!EndDate) And ((DatePart("q",[BogusDate])) Like
Forms![Artwork query]!Qrtr & "*") And ((Year([BogusDate])) Like
Forms![Artwork query]!Year & "*") And (([Artwork approval].Category)
Like
Forms![Artwork query]!Category & "*"));
Click to expand...
Click to expand...
Click to expand...
 
Thanks Doug,
I do know that this works with ((DatePart("q",[InStoreDate])) Like....
So I'm not too sure about that. BogusDate is derived (calculated) whereas
InStoreDate is an actual field from a table, if that makes a difference?
However, is there another way of solving my problem? Instead of using Like
can I use something else?

Douglas J. Steele said:
((DatePart("q",[BogusDate])) Like Forms![Artwork query]!Qrtr & "*")
definitely won't work, as DatePart returns a numeric value, and you can't
use Like with numeric values.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Graeme at Raptup said:
The reason I have them as Like tests is that I though I needed them so
that
the user has the option not to enter any value (i.e. null values).
The message is not so much an error message than the query does not work.
What is happening is that I get a prompt "Enter parameter value" for
'BogusDate'.
Now 'Bogus Date' is a derived or calculated field and I'm thinking that is
where the problem lies.
If I enter on the prompt (as a null value) the query returns no records.

Wayne Morgan said:
At first glance, I don't see a problem with the SQL. I would recommend
breaking it down. Start by removing the WHERE clause and see if the rest
works.

Do you really need all of the Like tests? I would expect the quarter or
year
to be equal or not equal.

Do you get any error messages? What is it doing or not doing?

--
Wayne Morgan
MS Access MVP


message Thanks Wayne,
it works fine.
But now I'm trying to fit this into a parameter query where it does not
seem
to work. I'm guessing it has something to do with a parameter query on
a
calculated or derived field.
This might be overkill but I've added the SQL script of the entire
query
below.....

PARAMETERS [Forms]![Artwork query]![Company] Text ( 255 ),
[Forms]![Artwork
query]![Category] Text ( 255 ), [Forms]![Artwork query]![StartDate]
DateTime,
[Forms]![Artwork query]![EndDate] DateTime;
SELECT Company.Company, [Artwork approval].InStoreDate,
DateAdd("m",-3,[InStoreDate]) AS BogusDate, DatePart("q",[BogusDate])
AS
FiscalQuarter, Year([BogusDate]) AS FiscalYear,
DatePart("q",[InStoreDate])
AS InStoreQuarter, Year([InstoreDate]) AS InStoreYear, [Artwork
approval].Retailer, [Artwork approval].Character, [Artwork
approval].Category, [Artwork approval].Units, [Artwork
approval].SellingPrice, [Artwork approval].Royalty
FROM Company INNER JOIN [Artwork approval] ON
Company.CompanyID=[Artwork
approval].CompanyID
WHERE (((Company.Company) Like Forms![Artwork query]!Company & "*") And
((DatePart("q",[BogusDate])) Like Forms![Artwork query]!Qrtr & "*") And
((Year([BogusDate])) Like Forms![Artwork query]!Year & "*") And
(([Artwork
approval].Category) Like Forms![Artwork query]!Category & "*") And
((Forms![Artwork query]!StartDate) Is Null))
Or (((Company.Company) Like Forms![Artwork query]!Company & "*") And
((DatePart("q",[BogusDate])) Like Forms![Artwork query]!Qrtr & "*") And
((Year([BogusDate])) Like Forms![Artwork query]!Year & "*") And
(([Artwork
approval].Category) Like Forms![Artwork query]!Category & "*") And
((Forms![Artwork query]!EndDate) Is Null))
Or (((Company.Company) Like Forms![Artwork query]!Company & "*") And
(([Artwork approval].InStoreDate) Between Forms![Artwork
query]!StartDate
And
Forms![Artwork query]!EndDate) And ((DatePart("q",[BogusDate])) Like
Forms![Artwork query]!Qrtr & "*") And ((Year([BogusDate])) Like
Forms![Artwork query]!Year & "*") And (([Artwork approval].Category)
Like
Forms![Artwork query]!Category & "*"));
Click to expand...
Click to expand...
Click to expand...
 
The standard way to handle a Null value to return all entries is

Example:
WHERE FieldName = Forms!frmMyForm!txtMyTextbox Or
Forms!frmMyForm!txtMyTextbox Is Null

--
Wayne Morgan
MS Access MVP


Graeme at Raptup said:
The reason I have them as Like tests is that I though I needed them so
that
the user has the option not to enter any value (i.e. null values).
The message is not so much an error message than the query does not work.
What is happening is that I get a prompt "Enter parameter value" for
'BogusDate'.
Now 'Bogus Date' is a derived or calculated field and I'm thinking that is
where the problem lies.
If I enter on the prompt (as a null value) the query returns no records.

Wayne Morgan said:
At first glance, I don't see a problem with the SQL. I would recommend
breaking it down. Start by removing the WHERE clause and see if the rest
works.

Do you really need all of the Like tests? I would expect the quarter or
year
to be equal or not equal.

Do you get any error messages? What is it doing or not doing?

--
Wayne Morgan
MS Access MVP


Graeme at Raptup said:
Thanks Wayne,
it works fine.
But now I'm trying to fit this into a parameter query where it does not
seem
to work. I'm guessing it has something to do with a parameter query on
a
calculated or derived field.
This might be overkill but I've added the SQL script of the entire
query
below.....

PARAMETERS [Forms]![Artwork query]![Company] Text ( 255 ),
[Forms]![Artwork
query]![Category] Text ( 255 ), [Forms]![Artwork query]![StartDate]
DateTime,
[Forms]![Artwork query]![EndDate] DateTime;
SELECT Company.Company, [Artwork approval].InStoreDate,
DateAdd("m",-3,[InStoreDate]) AS BogusDate, DatePart("q",[BogusDate])
AS
FiscalQuarter, Year([BogusDate]) AS FiscalYear,
DatePart("q",[InStoreDate])
AS InStoreQuarter, Year([InstoreDate]) AS InStoreYear, [Artwork
approval].Retailer, [Artwork approval].Character, [Artwork
approval].Category, [Artwork approval].Units, [Artwork
approval].SellingPrice, [Artwork approval].Royalty
FROM Company INNER JOIN [Artwork approval] ON
Company.CompanyID=[Artwork
approval].CompanyID
WHERE (((Company.Company) Like Forms![Artwork query]!Company & "*") And
((DatePart("q",[BogusDate])) Like Forms![Artwork query]!Qrtr & "*") And
((Year([BogusDate])) Like Forms![Artwork query]!Year & "*") And
(([Artwork
approval].Category) Like Forms![Artwork query]!Category & "*") And
((Forms![Artwork query]!StartDate) Is Null))
Or (((Company.Company) Like Forms![Artwork query]!Company & "*") And
((DatePart("q",[BogusDate])) Like Forms![Artwork query]!Qrtr & "*") And
((Year([BogusDate])) Like Forms![Artwork query]!Year & "*") And
(([Artwork
approval].Category) Like Forms![Artwork query]!Category & "*") And
((Forms![Artwork query]!EndDate) Is Null))
Or (((Company.Company) Like Forms![Artwork query]!Company & "*") And
(([Artwork approval].InStoreDate) Between Forms![Artwork
query]!StartDate
And
Forms![Artwork query]!EndDate) And ((DatePart("q",[BogusDate])) Like
Forms![Artwork query]!Qrtr & "*") And ((Year([BogusDate])) Like
Forms![Artwork query]!Year & "*") And (([Artwork approval].Category)
Like
Forms![Artwork query]!Category & "*"));
Click to expand...
Click to expand...
Click to expand...
 
Ok,
can't figure this. If I use "InStoreDate" (even using Like) the query works,
but as soon as I use "BogusDate" to display by Fiscal Quarter and Year I am
prompted to enter a parameter value for Bogus Date.
The query below definitely works;
PARAMETERS [Forms]![Artwork query]![Company] Text ( 255 ), [Forms]![Artwork
query]![Category] Text ( 255 ), [Forms]![Artwork query]![StartDate] DateTime,
[Forms]![Artwork query]![EndDate] DateTime;
SELECT Company.Company, [Artwork approval].InStoreDate,
DateAdd("m",-3,[InStoreDate]) AS BogusDate, DatePart("q",[BogusDate]) AS
FiscalQuarter, Year([BogusDate]) AS FiscalYear, DatePart("q",[InStoreDate])
AS InStoreQuarter, Year([InstoreDate]) AS InStoreYear, [Artwork
approval].Retailer, [Artwork approval].Character, [Artwork
approval].Category, [Artwork approval].Units, [Artwork
approval].SellingPrice, [Artwork approval].Royalty
FROM Company INNER JOIN [Artwork approval] ON Company.CompanyID = [Artwork
approval].CompanyID
WHERE (((Company.Company) Like [Forms]![Artwork query]![Company] & "*") AND
((DatePart("q",[InStoreDate])) Like [Forms]![Artwork query]![Qrtr] & "*") AND
((Year([InstoreDate])) Like [Forms]![Artwork query]![Year] & "*") AND
(([Artwork approval].Category) Like [Forms]![Artwork query]![Category] & "*")
AND (([Forms]![Artwork query]![StartDate]) Is Null)) OR (((Company.Company)
Like [Forms]![Artwork query]![Company] & "*") AND
((DatePart("q",[InStoreDate])) Like [Forms]![Artwork query]![Qrtr] & "*") AND
((Year([InstoreDate])) Like [Forms]![Artwork query]![Year] & "*") AND
(([Artwork approval].Category) Like [Forms]![Artwork query]![Category] & "*")
AND (([Forms]![Artwork query]![EndDate]) Is Null)) OR (((Company.Company)
Like [Forms]![Artwork query]![Company] & "*") AND (([Artwork
approval].InStoreDate) Between [Forms]![Artwork query]![StartDate] And
[Forms]![Artwork query]![EndDate]) AND ((DatePart("q",[InStoreDate])) Like
[Forms]![Artwork query]![Qrtr] & "*") AND ((Year([InstoreDate])) Like
[Forms]![Artwork query]![Year] & "*") AND (([Artwork approval].Category) Like
[Forms]![Artwork query]![Category] & "*"));

Wayne Morgan said:
The standard way to handle a Null value to return all entries is

Example:
WHERE FieldName = Forms!frmMyForm!txtMyTextbox Or
Forms!frmMyForm!txtMyTextbox Is Null

--
Wayne Morgan
MS Access MVP


Graeme at Raptup said:
The reason I have them as Like tests is that I though I needed them so
that
the user has the option not to enter any value (i.e. null values).
The message is not so much an error message than the query does not work.
What is happening is that I get a prompt "Enter parameter value" for
'BogusDate'.
Now 'Bogus Date' is a derived or calculated field and I'm thinking that is
where the problem lies.
If I enter on the prompt (as a null value) the query returns no records.

Wayne Morgan said:
At first glance, I don't see a problem with the SQL. I would recommend
breaking it down. Start by removing the WHERE clause and see if the rest
works.

Do you really need all of the Like tests? I would expect the quarter or
year
to be equal or not equal.

Do you get any error messages? What is it doing or not doing?

--
Wayne Morgan
MS Access MVP


message Thanks Wayne,
it works fine.
But now I'm trying to fit this into a parameter query where it does not
seem
to work. I'm guessing it has something to do with a parameter query on
a
calculated or derived field.
This might be overkill but I've added the SQL script of the entire
query
below.....

PARAMETERS [Forms]![Artwork query]![Company] Text ( 255 ),
[Forms]![Artwork
query]![Category] Text ( 255 ), [Forms]![Artwork query]![StartDate]
DateTime,
[Forms]![Artwork query]![EndDate] DateTime;
SELECT Company.Company, [Artwork approval].InStoreDate,
DateAdd("m",-3,[InStoreDate]) AS BogusDate, DatePart("q",[BogusDate])
AS
FiscalQuarter, Year([BogusDate]) AS FiscalYear,
DatePart("q",[InStoreDate])
AS InStoreQuarter, Year([InstoreDate]) AS InStoreYear, [Artwork
approval].Retailer, [Artwork approval].Character, [Artwork
approval].Category, [Artwork approval].Units, [Artwork
approval].SellingPrice, [Artwork approval].Royalty
FROM Company INNER JOIN [Artwork approval] ON
Company.CompanyID=[Artwork
approval].CompanyID
WHERE (((Company.Company) Like Forms![Artwork query]!Company & "*") And
((DatePart("q",[BogusDate])) Like Forms![Artwork query]!Qrtr & "*") And
((Year([BogusDate])) Like Forms![Artwork query]!Year & "*") And
(([Artwork
approval].Category) Like Forms![Artwork query]!Category & "*") And
((Forms![Artwork query]!StartDate) Is Null))
Or (((Company.Company) Like Forms![Artwork query]!Company & "*") And
((DatePart("q",[BogusDate])) Like Forms![Artwork query]!Qrtr & "*") And
((Year([BogusDate])) Like Forms![Artwork query]!Year & "*") And
(([Artwork
approval].Category) Like Forms![Artwork query]!Category & "*") And
((Forms![Artwork query]!EndDate) Is Null))
Or (((Company.Company) Like Forms![Artwork query]!Company & "*") And
(([Artwork approval].InStoreDate) Between Forms![Artwork
query]!StartDate
And
Forms![Artwork query]!EndDate) And ((DatePart("q",[BogusDate])) Like
Forms![Artwork query]!Qrtr & "*") And ((Year([BogusDate])) Like
Forms![Artwork query]!Year & "*") And (([Artwork approval].Category)
Like
Forms![Artwork query]!Category & "*"));
Click to expand...
Click to expand...
Click to expand...
 
I recreated the tables, fields, and form listed in the query.

If I remove the criteria for Fiscal Quarter and Fiscal Year, it appears to
work (it doesn't prompt for BogusDate)

So, to work around this, I replaced all instances of "[BogusDate]" in the
WHERE clause with "DateAdd("m",-3,[InStoreDate])". This seems to solve the
problem. The calculated field's name isn't being carried through to the
WHERE clause.

New SQL:
PARAMETERS [Forms]![Artwork query]![Company] Text ( 255 ), [Forms]![Artwork
query]![Category] Text ( 255 ), [Forms]![Artwork query]![StartDate]
DateTime, [Forms]![Artwork query]![EndDate] DateTime;
SELECT Company.Company, [Artwork approval].InStoreDate,
DateAdd("m",-3,[InStoreDate]) AS BogusDate, DatePart("q",[BogusDate]) AS
FiscalQuarter, Year([BogusDate]) AS FiscalYear, DatePart("q",[InStoreDate])
AS InStoreQuarter, Year([InstoreDate]) AS InStoreYear, [Artwork
approval].Retailer, [Artwork approval].Character, [Artwork
approval].Category, [Artwork approval].Units, [Artwork
approval].SellingPrice, [Artwork approval].Royalty
FROM Company INNER JOIN [Artwork approval] ON Company.CompanyID = [Artwork
approval].CompanyID
WHERE (((Company.Company) Like [Forms]![Artwork query]![Company] & "*") AND
((DatePart("q",DateAdd("m",-3,[InStoreDate]))) Like [Forms]![Artwork
query]![Qrtr] & "*") AND ((Year(DateAdd("m",-3,[InStoreDate]))) Like
[Forms]![Artwork query]![Year] & "*") AND (([Artwork approval].Category)
Like [Forms]![Artwork query]![Category] & "*") AND (([Forms]![Artwork
query]![StartDate]) Is Null)) OR (((Company.Company) Like [Forms]![Artwork
query]![Company] & "*") AND ((DatePart("q",DateAdd("m",-3,[InStoreDate])))
Like [Forms]![Artwork query]![Qrtr] & "*") AND
((Year(DateAdd("m",-3,[InStoreDate]))) Like [Forms]![Artwork query]![Year] &
"*") AND (([Artwork approval].Category) Like [Forms]![Artwork
query]![Category] & "*") AND (([Forms]![Artwork query]![EndDate]) Is Null))
OR (((Company.Company) Like [Forms]![Artwork query]![Company] & "*") AND
(([Artwork approval].InStoreDate) Between [Forms]![Artwork
query]![StartDate] And [Forms]![Artwork query]![EndDate]) AND
((DatePart("q",DateAdd("m",-3,[InStoreDate]))) Like [Forms]![Artwork
query]![Qrtr] & "*") AND ((Year(DateAdd("m",-3,[InStoreDate]))) Like
[Forms]![Artwork query]![Year] & "*") AND (([Artwork approval].Category)
Like [Forms]![Artwork query]![Category] & "*"));

--
Wayne Morgan
MS Access MVP


Graeme at Raptup said:
Ok,
can't figure this. If I use "InStoreDate" (even using Like) the query
works,
but as soon as I use "BogusDate" to display by Fiscal Quarter and Year I
am
prompted to enter a parameter value for Bogus Date.
The query below definitely works;
PARAMETERS [Forms]![Artwork query]![Company] Text ( 255 ),
[Forms]![Artwork
query]![Category] Text ( 255 ), [Forms]![Artwork query]![StartDate]
DateTime,
[Forms]![Artwork query]![EndDate] DateTime;
SELECT Company.Company, [Artwork approval].InStoreDate,
DateAdd("m",-3,[InStoreDate]) AS BogusDate, DatePart("q",[BogusDate]) AS
FiscalQuarter, Year([BogusDate]) AS FiscalYear,
DatePart("q",[InStoreDate])
AS InStoreQuarter, Year([InstoreDate]) AS InStoreYear, [Artwork
approval].Retailer, [Artwork approval].Character, [Artwork
approval].Category, [Artwork approval].Units, [Artwork
approval].SellingPrice, [Artwork approval].Royalty
FROM Company INNER JOIN [Artwork approval] ON Company.CompanyID = [Artwork
approval].CompanyID
WHERE (((Company.Company) Like [Forms]![Artwork query]![Company] & "*")
AND
((DatePart("q",[InStoreDate])) Like [Forms]![Artwork query]![Qrtr] & "*")
AND
((Year([InstoreDate])) Like [Forms]![Artwork query]![Year] & "*") AND
(([Artwork approval].Category) Like [Forms]![Artwork query]![Category] &
"*")
AND (([Forms]![Artwork query]![StartDate]) Is Null)) OR
(((Company.Company)
Like [Forms]![Artwork query]![Company] & "*") AND
((DatePart("q",[InStoreDate])) Like [Forms]![Artwork query]![Qrtr] & "*")
AND
((Year([InstoreDate])) Like [Forms]![Artwork query]![Year] & "*") AND
(([Artwork approval].Category) Like [Forms]![Artwork query]![Category] &
"*")
AND (([Forms]![Artwork query]![EndDate]) Is Null)) OR (((Company.Company)
Like [Forms]![Artwork query]![Company] & "*") AND (([Artwork
approval].InStoreDate) Between [Forms]![Artwork query]![StartDate] And
[Forms]![Artwork query]![EndDate]) AND ((DatePart("q",[InStoreDate])) Like
[Forms]![Artwork query]![Qrtr] & "*") AND ((Year([InstoreDate])) Like
[Forms]![Artwork query]![Year] & "*") AND (([Artwork approval].Category)
Like
[Forms]![Artwork query]![Category] & "*"));

Wayne Morgan said:
The standard way to handle a Null value to return all entries is

Example:
WHERE FieldName = Forms!frmMyForm!txtMyTextbox Or
Forms!frmMyForm!txtMyTextbox Is Null

--
Wayne Morgan
MS Access MVP


Graeme at Raptup said:
The reason I have them as Like tests is that I though I needed them so
that
the user has the option not to enter any value (i.e. null values).
The message is not so much an error message than the query does not
work.
What is happening is that I get a prompt "Enter parameter value" for
'BogusDate'.
Now 'Bogus Date' is a derived or calculated field and I'm thinking that
is
where the problem lies.
If I enter on the prompt (as a null value) the query returns no
records.

:

At first glance, I don't see a problem with the SQL. I would recommend
breaking it down. Start by removing the WHERE clause and see if the
rest
works.

Do you really need all of the Like tests? I would expect the quarter
or
year
to be equal or not equal.

Do you get any error messages? What is it doing or not doing?

--
Wayne Morgan
MS Access MVP


message Thanks Wayne,
it works fine.
But now I'm trying to fit this into a parameter query where it does
not
seem
to work. I'm guessing it has something to do with a parameter query
on
a
calculated or derived field.
This might be overkill but I've added the SQL script of the entire
query
below.....

PARAMETERS [Forms]![Artwork query]![Company] Text ( 255 ),
[Forms]![Artwork
query]![Category] Text ( 255 ), [Forms]![Artwork query]![StartDate]
DateTime,
[Forms]![Artwork query]![EndDate] DateTime;
SELECT Company.Company, [Artwork approval].InStoreDate,
DateAdd("m",-3,[InStoreDate]) AS BogusDate,
DatePart("q",[BogusDate])
AS
FiscalQuarter, Year([BogusDate]) AS FiscalYear,
DatePart("q",[InStoreDate])
AS InStoreQuarter, Year([InstoreDate]) AS InStoreYear, [Artwork
approval].Retailer, [Artwork approval].Character, [Artwork
approval].Category, [Artwork approval].Units, [Artwork
approval].SellingPrice, [Artwork approval].Royalty
FROM Company INNER JOIN [Artwork approval] ON
Company.CompanyID=[Artwork
approval].CompanyID
WHERE (((Company.Company) Like Forms![Artwork query]!Company & "*")
And
((DatePart("q",[BogusDate])) Like Forms![Artwork query]!Qrtr & "*")
And
((Year([BogusDate])) Like Forms![Artwork query]!Year & "*") And
(([Artwork
approval].Category) Like Forms![Artwork query]!Category & "*") And
((Forms![Artwork query]!StartDate) Is Null))
Or (((Company.Company) Like Forms![Artwork query]!Company & "*") And
((DatePart("q",[BogusDate])) Like Forms![Artwork query]!Qrtr & "*")
And
((Year([BogusDate])) Like Forms![Artwork query]!Year & "*") And
(([Artwork
approval].Category) Like Forms![Artwork query]!Category & "*") And
((Forms![Artwork query]!EndDate) Is Null))
Or (((Company.Company) Like Forms![Artwork query]!Company & "*") And
(([Artwork approval].InStoreDate) Between Forms![Artwork
query]!StartDate
And
Forms![Artwork query]!EndDate) And ((DatePart("q",[BogusDate])) Like
Forms![Artwork query]!Qrtr & "*") And ((Year([BogusDate])) Like
Forms![Artwork query]!Year & "*") And (([Artwork approval].Category)
Like
Forms![Artwork query]!Category & "*"));
Click to expand...
Click to expand...
Click to expand...
 
Thanks Wayne,
works like a dream

Graeme at Raptup said:
Ok,
can't figure this. If I use "InStoreDate" (even using Like) the query works,
but as soon as I use "BogusDate" to display by Fiscal Quarter and Year I am
prompted to enter a parameter value for Bogus Date.
The query below definitely works;
PARAMETERS [Forms]![Artwork query]![Company] Text ( 255 ), [Forms]![Artwork
query]![Category] Text ( 255 ), [Forms]![Artwork query]![StartDate] DateTime,
[Forms]![Artwork query]![EndDate] DateTime;
SELECT Company.Company, [Artwork approval].InStoreDate,
DateAdd("m",-3,[InStoreDate]) AS BogusDate, DatePart("q",[BogusDate]) AS
FiscalQuarter, Year([BogusDate]) AS FiscalYear, DatePart("q",[InStoreDate])
AS InStoreQuarter, Year([InstoreDate]) AS InStoreYear, [Artwork
approval].Retailer, [Artwork approval].Character, [Artwork
approval].Category, [Artwork approval].Units, [Artwork
approval].SellingPrice, [Artwork approval].Royalty
FROM Company INNER JOIN [Artwork approval] ON Company.CompanyID = [Artwork
approval].CompanyID
WHERE (((Company.Company) Like [Forms]![Artwork query]![Company] & "*") AND
((DatePart("q",[InStoreDate])) Like [Forms]![Artwork query]![Qrtr] & "*") AND
((Year([InstoreDate])) Like [Forms]![Artwork query]![Year] & "*") AND
(([Artwork approval].Category) Like [Forms]![Artwork query]![Category] & "*")
AND (([Forms]![Artwork query]![StartDate]) Is Null)) OR (((Company.Company)
Like [Forms]![Artwork query]![Company] & "*") AND
((DatePart("q",[InStoreDate])) Like [Forms]![Artwork query]![Qrtr] & "*") AND
((Year([InstoreDate])) Like [Forms]![Artwork query]![Year] & "*") AND
(([Artwork approval].Category) Like [Forms]![Artwork query]![Category] & "*")
AND (([Forms]![Artwork query]![EndDate]) Is Null)) OR (((Company.Company)
Like [Forms]![Artwork query]![Company] & "*") AND (([Artwork
approval].InStoreDate) Between [Forms]![Artwork query]![StartDate] And
[Forms]![Artwork query]![EndDate]) AND ((DatePart("q",[InStoreDate])) Like
[Forms]![Artwork query]![Qrtr] & "*") AND ((Year([InstoreDate])) Like
[Forms]![Artwork query]![Year] & "*") AND (([Artwork approval].Category) Like
[Forms]![Artwork query]![Category] & "*"));

Wayne Morgan said:
The standard way to handle a Null value to return all entries is

Example:
WHERE FieldName = Forms!frmMyForm!txtMyTextbox Or
Forms!frmMyForm!txtMyTextbox Is Null

--
Wayne Morgan
MS Access MVP


Graeme at Raptup said:
The reason I have them as Like tests is that I though I needed them so
that
the user has the option not to enter any value (i.e. null values).
The message is not so much an error message than the query does not work.
What is happening is that I get a prompt "Enter parameter value" for
'BogusDate'.
Now 'Bogus Date' is a derived or calculated field and I'm thinking that is
where the problem lies.
If I enter on the prompt (as a null value) the query returns no records.

:

At first glance, I don't see a problem with the SQL. I would recommend
breaking it down. Start by removing the WHERE clause and see if the rest
works.

Do you really need all of the Like tests? I would expect the quarter or
year
to be equal or not equal.

Do you get any error messages? What is it doing or not doing?

--
Wayne Morgan
MS Access MVP


message Thanks Wayne,
it works fine.
But now I'm trying to fit this into a parameter query where it does not
seem
to work. I'm guessing it has something to do with a parameter query on
a
calculated or derived field.
This might be overkill but I've added the SQL script of the entire
query
below.....

PARAMETERS [Forms]![Artwork query]![Company] Text ( 255 ),
[Forms]![Artwork
query]![Category] Text ( 255 ), [Forms]![Artwork query]![StartDate]
DateTime,
[Forms]![Artwork query]![EndDate] DateTime;
SELECT Company.Company, [Artwork approval].InStoreDate,
DateAdd("m",-3,[InStoreDate]) AS BogusDate, DatePart("q",[BogusDate])
AS
FiscalQuarter, Year([BogusDate]) AS FiscalYear,
DatePart("q",[InStoreDate])
AS InStoreQuarter, Year([InstoreDate]) AS InStoreYear, [Artwork
approval].Retailer, [Artwork approval].Character, [Artwork
approval].Category, [Artwork approval].Units, [Artwork
approval].SellingPrice, [Artwork approval].Royalty
FROM Company INNER JOIN [Artwork approval] ON
Company.CompanyID=[Artwork
approval].CompanyID
WHERE (((Company.Company) Like Forms![Artwork query]!Company & "*") And
((DatePart("q",[BogusDate])) Like Forms![Artwork query]!Qrtr & "*") And
((Year([BogusDate])) Like Forms![Artwork query]!Year & "*") And
(([Artwork
approval].Category) Like Forms![Artwork query]!Category & "*") And
((Forms![Artwork query]!StartDate) Is Null))
Or (((Company.Company) Like Forms![Artwork query]!Company & "*") And
((DatePart("q",[BogusDate])) Like Forms![Artwork query]!Qrtr & "*") And
((Year([BogusDate])) Like Forms![Artwork query]!Year & "*") And
(([Artwork
approval].Category) Like Forms![Artwork query]!Category & "*") And
((Forms![Artwork query]!EndDate) Is Null))
Or (((Company.Company) Like Forms![Artwork query]!Company & "*") And
(([Artwork approval].InStoreDate) Between Forms![Artwork
query]!StartDate
And
Forms![Artwork query]!EndDate) And ((DatePart("q",[BogusDate])) Like
Forms![Artwork query]!Qrtr & "*") And ((Year([BogusDate])) Like
Forms![Artwork query]!Year & "*") And (([Artwork approval].Category)
Like
Forms![Artwork query]!Category & "*"));
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query Problem 4
End of Quarter Based on Fiscal Year 2
Criteria for results in report of previous year 5
How do I expand a funtion's maximum character length? eg CUBESET 1
between FY date caculation 2
Automatically Update Current Fiscal Quarter 1
Create an input form 4
fiscal year 6

Back
Top