• Hi Guest: Welcome to TRIBE, the online home of TRIBE MAGAZINE. If you'd like to post here, or reply to existing posts on TRIBE, you first have to register. Join us!
14K Cannabis seed slider pendants by tribe

MS Access Question re: queries and criteria

the_fornicator

TRIBE Member
I can usually stumble through Access stuff, but I have encountered an issue that has me absolutely stumped. I was tasked to debug a query because it's not working.

After a few minutes of poking around, I discovered that something is wonky with a basic constraint/criteria and it only seems to happen with a particular INT field in one of the tables in the query -let's call the problem table, ATABLE.

I can apply constraints to every other field/column in ATABLE without issue and everything works as one would expect with a single table, single condition query.

ATABLE has several fields. I'll create a query, drop ATABLE into the editor, select a few columns, and for the StatementYear column, in the "Criteria" row, I'll put the following (without double quotes): "=2013"

I should mention that StatementYear is defined as: ATABLE.StatementYear(null, int).

When I press run, the result set that is returned has years from 2004, 2005, and 2013 returned. 56 rows to be exact (there are 1977 records in ATABLE).

The funny thing is that when I run the exact same query in SQL Manager, the query works just dandy. And, low and behold, 56 rows are returned.

The primary/foreign keys of the 56 rows that are returned in Access are the same as the result set that is returned in SQL Manager. The Access file just shows that the StatementYear for the returned rows are in 2004, 2005, and 2013.

I've tried the following in the criteria field (exclude asterisk):
* 2013
* =2013
* CINT(ATABLE.STATEMENTYEAR) = CINT(2013)

I've tried the following and the same result set is returned (56 rows) but the statementyear in the result set still show the same as when I entered 2013 into the criteria field (i.e. result set had records with a statement year of 2004, 2005, and 2013).
* YEAR(now())-1
* YEAR(date()-1)

Thoughts? Again, criteria/conditions on any other column in ATABLE work just fine, but this StatementYear(null, int) is causing some a bit of grief.

Thanks!

edit: the query in SQL view looks just as you'd think (included it for shits and giggles)

SELECT field1, field2, field3
FROM ATABLE
WHERE (((dbo_ATABLE.StatementYear) = 2013));
 
Last edited:

the_fornicator

TRIBE Member
I also tried the following with no success (result set still had StatementYear values of 2004, 2005, and 2013):

* In (2013)
* > 2012
 
Top