Query Help

[Afrosheen via AccessMonster.com]

Thanks for taking the time to read this. The help I've gotten here has been
invaluable.

I'm trying to create a query that will show records greater than DateAdd
("yyyy", -3, Date()). Any thing prior to this year - 3 we don't need to see.
What I have for the query is:

select DateAch & _
FROM [qryTapsArchived] & _
WHERE [DateAch] > DateAdd("yyyy", -3, Date()) & _
AND YesNoField <> 0


The DateArch is a field in the qryTapsArchived query. The yes/no field is =
to true. The error I'm getting is that it keeps asking for is: Expected Case.


This SQL statement is from the query its self and seems to work in the query,
but not in the VBA version:

SELECT tblTaps.StaffId, tblMain.Lname, tblMain.Fname, tblTaps.WorkPlan,
tblTaps.WorkRec, tblTaps.IntDue, tblTaps.IntRec, tblTaps.FinalDue, tblTaps.
FinalRec, tblTaps.RatingCyc, tblTaps.FiscalYr, tblTaps.Rating, tblTaps.Notes,
tblTaps.Archive, tblMain.IdPict, tblTaps.DateAch, FROM tblMain INNER JOIN
tblTaps ON tblMain.StaffId = tblTaps.StaffId,
WHERE (((tblTaps.Archive)=True) AND ((tblTaps.DateAch)>DateAdd("yyyy",-3,Date
())))

 

[John Spencer MVP]

It would help if you showed the code you were using to build the query string.

Dim strSQL as String

strSQL = "SELECT DateAch" & _
" FROM [qryTapsArchived]" & & _
" WHERE [DateAch] > DateAdd(""yyyy"", -3, Date())" & _
" AND YesNoField <> 0"

What is YesNo field - it does not exist in the query string you posted?
Why are you applying critera a second time, since the data seems to be
restricted to the same criteria in the base query.

I am assuming that the posted query is qryTapsArchived that you refer to in
the above query string. By the way waht you posted has syntax error in it as
there are extra commas (one before "FROM tblMain" and another before " WHERE"

I think that all you need is
StrSQL = "SELECT DateAch FROM qryTapsArchived"

However that is a guess since you have given us very little in the way of context.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Afrosheen via AccessMonster.com]

Thanks John for helping me out again. I hoped not to use the SQL statement
from the qryTapsArchived query anyway. All I'm trying to do is to have the
form open up {On Open} and show me all records current and previous up to 3
years ago. DateArch is where the information is stored. Anything beyond that
I don't need to see. So, I was hoping to come up with a routine like you did
to display that. I guess I failed. The yes/no field I really don't need
anyway. So now I'll tinker with what you've given me and see how I can
display the information I need.

It would help if you showed the code you were using to build the query string.

Dim strSQL as String

strSQL = "SELECT DateAch" & _
" FROM [qryTapsArchived]" & & _
" WHERE [DateAch] > DateAdd(""yyyy"", -3, Date())" & _
" AND YesNoField <> 0"

What is YesNo field - it does not exist in the query string you posted?
Why are you applying critera a second time, since the data seems to be
restricted to the same criteria in the base query.

I am assuming that the posted query is qryTapsArchived that you refer to in
the above query string. By the way waht you posted has syntax error in it as
there are extra commas (one before "FROM tblMain" and another before " WHERE"

I think that all you need is
StrSQL = "SELECT DateAch FROM qryTapsArchived"

However that is a guess since you have given us very little in the way of context.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks for taking the time to read this. The help I've gotten here has been
invaluable.

[quoted text clipped - 22 lines]

WHERE (((tblTaps.Archive)=True) AND ((tblTaps.DateAch)>DateAdd("yyyy",-3,Date
())))

 

[Afrosheen via AccessMonster.com]

I'm sorry to take up your time. After further investigation I found out I
have dame bramage.

I found that I had a hard query already built, so I didn't need the code. I
got the results i wanted through that.

Thanks again, John

Thanks John for helping me out again. I hoped not to use the SQL statement
from the qryTapsArchived query anyway. All I'm trying to do is to have the
form open up {On Open} and show me all records current and previous up to 3
years ago. DateArch is where the information is stored. Anything beyond that
I don't need to see. So, I was hoping to come up with a routine like you did
to display that. I guess I failed. The yes/no field I really don't need
anyway. So now I'll tinker with what you've given me and see how I can
display the information I need.

It would help if you showed the code you were using to build the query string.

[quoted text clipped - 28 lines]

 

[John W. Vinson]

Thanks John for helping me out again. I hoped not to use the SQL statement
from the qryTapsArchived query anyway. All I'm trying to do is to have the
form open up {On Open} and show me all records current and previous up to 3
years ago. DateArch is where the information is stored. Anything beyond that
I don't need to see.

You don't need ANY CODE AT ALL to do this.

Just use a Query as the Recordsource for the form with a criterion of

= DateAdd("yyyy", -3, Date())

on the DateArch field. The form will only show the last three years' data and
will still be editable.