Moving an expression generated value into table through form

[Guest]

I can certainly post the SQL of the query but I honestly don't know what that
is..(I am demonstrating my ignorance) Flying by the seat of my pants here.

That dateadd expressionis out of the filter of the report I am attempting to
replace with a query. I am putting it in the "Criteria" of a Date/Time field
in the query..so obviously what worked as a filter in a report filter
expression doesn't necessarily work in a Query.

the purpose is to return those records with current dates 6 months ago
during the current month.

On another aspect, in a search of this site for your idea of an unbound
form to automate the query...I think I found a great example I will attempt
tomorrow on Al Greens Access Tips as recommended by one of your fellow MVPs.
Sure do appreciate your continued assistance..hope I'll be able to help
someone in the same manner sosme day. Thank you.

Having a difficult time with the query....couldn't get things to work so I
have gone back several times and added one or two fields at a time and then
run the query to isolate what I am doing wrong.

I made a query to combine the director and department info. I am using that
query and a query isolating date field [current test] and the parent table to
create a parameter query based on the director (and it works!!!...first
parameter query).

I then add the following to criteria for the [Current Test]:

Month(DateAdd("m",-6,Date()))

The query which worked fine before returns nothing when that expression is
added. What gives?

<looking under the monitor for the query>

I have no idea, Jeff. Care to post the SQL of the query? What field
are you searching with this value (which will just be an integer
number, 1 to 12)? If it's a date/time field it will certainly NOT
work.


John W. Vinson[MVP]

 

[John Vinson]

I can certainly post the SQL of the query but I honestly don't know what that
is..(I am demonstrating my ignorance) Flying by the seat of my pants here.

Open the Query in design view, and either select View... SQL from the
Menu, or use the leftmost dropdown box in the toolbar. It looks like a
picture of a datasheet, and if you just click it that's what you'll
see, but it's actually a dropdown with three options - design mode (a
triangle and straightedge), a datasheet, and SQL. Use the SQL option
and copy and paste the cryptic text to a message here.

That dateadd expressionis out of the filter of the report I am attempting to
replace with a query. I am putting it in the "Criteria" of a Date/Time field
in the query..so obviously what worked as a filter in a report filter
expression doesn't necessarily work in a Query.

Ummm... no. Setting a Filter on a date/time field to 5 will return all
records for which the date field is equal to January 4, 1900, since
Date/Time fields are stored as a number of days since midnight,
December 30, 1899.

the purpose is to return those records with current dates 6 months ago
during the current month.

Use a criterion of BETWEEN DateSerial(Year(Date()), Month(Date()) - 6,
1) AND DateSerial(Year(Date()), Month(Date() - 5, 0)

On another aspect, in a search of this site for your idea of an unbound
form to automate the query...I think I found a great example I will attempt
tomorrow on Al Greens Access Tips as recommended by one of your fellow MVPs.

Excellent. Do a google search for my friend Jeff Conrad's incredible
six-page list of Access resources while you're at it - there's a lot
out there!

Sure do appreciate your continued assistance..hope I'll be able to help
someone in the same manner sosme day. Thank you.

You're most welcome.

John W. Vinson[MVP]

 

[Guest]

I have successfully followed Martin Green's "Customizing Access Parameter
Queries" creating a dialog box with a combo box that selects the parameter
for the query on Director..it WORKS!!

I cannot get the date parameter to work.

SELECT [All Employees].Last, [All Employees].First, [Child Query].[Current
TB Test Date], qryDirectorAssignments.[Acct #]
FROM ([All Employees] INNER JOIN [Child Query] ON [All Employees].ID =
[Child Query].ParentTable_ID) INNER JOIN qryDirectorAssignments ON [Child
Query].Dept = qryDirectorAssignments.[Acct #]
WHERE
(((qryDirectorAssignments.Dir_ID)=[Forms]![frmReportMenu]![cboDirectors]));

There are two date criteria I build reports on.

Based on (Date())...those [Current TB Test Dates]

1. 6 months prior OR 12 months prior
([dept]=742 or [dept]=762) and [active]=yes and ([Bi-annual Test]=yes and
month([current tb test date])=month(DateAdd("m",-6,Date()))) or ([dept]=742
or [dept]=762) and [active]=yes and ([Annual Test]=yes and month([current tb
test date])=month(DateAdd("m",-12,Date())))

2. 5 months prior OR 11 months prior
([dept]=742 or [dept]=762) and [active]=yes and ([Bi-annual Test]=yes and
month([current tb test date])=month(DateAdd("m",-5,Date()))) or ([dept]=742
or [dept]=762) and [active]=yes and ([Annual Test]=yes and month([current tb
test date])=month(DateAdd("m",-11,Date())))

I have consistently found dealing with the dates a struggle

I have the Director/Dept parameter working with a dialog box, once I get the
Date issue solved I am off and running....adding the [active], [annual], and
[bi-annual] parameters.

I tried :
BETWEEN DateSerial(Year(Date()), Month(Date()) - 6,
1) AND DateSerial(Year(Date()), Month(Date() - 5, 0)

Which returns a "too many arguments" error

Thanks again John.

 

[Guest]

I WON, I got the date criteria to work..now to translate it inot the dialog
box, I also added code to bring up the report based on the query in preview
mode, and close the query at the same time...it's the small victories in life
that count.!!

I have successfully followed Martin Green's "Customizing Access Parameter
Queries" creating a dialog box with a combo box that selects the parameter
for the query on Director..it WORKS!!

I cannot get the date parameter to work.

SELECT [All Employees].Last, [All Employees].First, [Child Query].[Current
TB Test Date], qryDirectorAssignments.[Acct #]
FROM ([All Employees] INNER JOIN [Child Query] ON [All Employees].ID =
[Child Query].ParentTable_ID) INNER JOIN qryDirectorAssignments ON [Child
Query].Dept = qryDirectorAssignments.[Acct #]
WHERE
(((qryDirectorAssignments.Dir_ID)=[Forms]![frmReportMenu]![cboDirectors]));

There are two date criteria I build reports on.

Based on (Date())...those [Current TB Test Dates]

1. 6 months prior OR 12 months prior
([dept]=742 or [dept]=762) and [active]=yes and ([Bi-annual Test]=yes and
month([current tb test date])=month(DateAdd("m",-6,Date()))) or ([dept]=742
or [dept]=762) and [active]=yes and ([Annual Test]=yes and month([current tb
test date])=month(DateAdd("m",-12,Date())))

2. 5 months prior OR 11 months prior
([dept]=742 or [dept]=762) and [active]=yes and ([Bi-annual Test]=yes and
month([current tb test date])=month(DateAdd("m",-5,Date()))) or ([dept]=742
or [dept]=762) and [active]=yes and ([Annual Test]=yes and month([current tb
test date])=month(DateAdd("m",-11,Date())))

I have consistently found dealing with the dates a struggle

I have the Director/Dept parameter working with a dialog box, once I get the
Date issue solved I am off and running....adding the [active], [annual], and
[bi-annual] parameters.

I tried :
BETWEEN DateSerial(Year(Date()), Month(Date()) - 6,
1) AND DateSerial(Year(Date()), Month(Date() - 5, 0)

Which returns a "too many arguments" error

Thanks again John.

Open the Query in design view, and either select View... SQL from the
Menu, or use the leftmost dropdown box in the toolbar. It looks like a
picture of a datasheet, and if you just click it that's what you'll
see, but it's actually a dropdown with three options - design mode (a
triangle and straightedge), a datasheet, and SQL. Use the SQL option
and copy and paste the cryptic text to a message here.


Ummm... no. Setting a Filter on a date/time field to 5 will return all
records for which the date field is equal to January 4, 1900, since
Date/Time fields are stored as a number of days since midnight,
December 30, 1899.


Use a criterion of BETWEEN DateSerial(Year(Date()), Month(Date()) - 6,
1) AND DateSerial(Year(Date()), Month(Date() - 5, 0)


Excellent. Do a google search for my friend Jeff Conrad's incredible
six-page list of Access resources while you're at it - there's a lot
out there!


You're most welcome.

John W. Vinson[MVP]

 

[John Vinson]

I WON, I got the date criteria to work..now to translate it inot the dialog
box, I also added code to bring up the report based on the query in preview
mode, and close the query at the same time...it's the small victories in life
that count.!!

Hooray!

One question (and possible simplification) though: why are you opening
the query *at all*? It's not necessary to open the query in datasheet
view; simply use it as the recordsource for the Report, and open the
Report.

John W. Vinson[MVP]