I have an option box to return only current year or all transactions
Code:
If Me.frameLimit = 1 Then
TempVars("datelimit") = "Year([CkDate]) = " & Year(Date)
Else: TempVars("datelimit") = "1=1"
End If
I'd like to use this limit in the subform query but putting [TempVars]![datelimit] in CkDate returns a "too complicated to compute" error. Obviously I'm doing somthing terribly wrong.
If I use the criteria in CkDate -- Year([CkDate])=2025 -- it runs fine.
How exactly are you opening/running the query? Try adding the TempVar as a column to see what you actually have. Can you post the SQL statement for your query? I'm not sure I am clear on how you're using the criteria. Using WHERE FieldName = Year(FieldName)=2025 seems wrong.
Ok I changed the filter and it returns 2025 but I still can't get it to work in my query.
Code:
SELECT [2qryRegister].TransactionID, [2qryRegister].CkDate, [2qryRegister].AccountID, [2qryRegister].AccountName, [2qryRegister].Num, [2qryRegister].Payee, [2qryRegister].Amount, [2qryRegister].PrintCk, [2qryRegister].Cleared, [2qryRegister].Remaining, [TempVars]![DateLimit] AS [Year]
FROM 2qryRegister;
Putting it as criteria on CkDate will not compute. Setting up a separate field Year (as above) returns ߩ. I have never seen that before... is it a font error? What is it? Now I just feel stupid!
Oh Wait Wait!!! I didn't follow your instructions all the way! It Works! Thank you so much!
Actually it has to be: Year([CkDate])=[TempVars]![datelimit]
Me again If I add
WHERE (((Year([CkDate]))=[TempVars]![datelimit])) OR (([TempVars]![datelimit] Is Null));
This returns everything. I think I'm not using the null value properly.
I'm answering from my phone so I don't have Access to test. If you can use TempVars in criteria then you want something like:
SQL:
SELECT
TransactionID,
CkDate,
AccountID,
AccountName,
Num,
Payee,
Amount,
PrintCk,
Cleared,
Remaining,
[TempVars]![DateLimit] AS [Year]
FROM 2qryRegister
WHERE Year(CkDate) = [TempVars]![DateLimit]
OR [TempVars]![DateLimit] IS NULL
;
I'd like to use this limit in the subform query but putting [TempVars]![datelimit] in CkDate returns a "too complicated to compute" error. Obviously I'm doing somthing terribly wrong.
If I use the criteria in CkDate -- Year([CkDate])=2025 -- it runs fine.
It shouldn't run fine. In one case you are populating "datelimit" with only the Year and in the second case, you are giving it a Boolean value. If something isn't actually a date, you do yourself no service by giving it a name that implies the contents is a date. See how it has already confused you.
Well, it honestly didn't make sense to me but it does seem to work. Tell me what you would do, please. I would like the default to be the current year but be able to do all when needed.
First I would change the name of the field to something like DataYear. Make it numeric and not a date. Then you can use Year(Date()) to populate the default search or allow the user to enter a specific year if he doesn't want the current year.
NEVER use Year, Month, Date, Now etc or ANY function name as a column name. You also need to avoid properties like Name. If you define a field named "Name", I think Access will warn you but then allow the bad name to be used. So, in your form bound to a table with a column named "Name", what do you get when you reference Me.Name?
I have no field named "name" nor do I have a field named "year". You're suggesting I add a field "DataYear" in addition to CkDate? So then the field would need to be tied to the CkDate... So the field would be DataYear: Year([CkDate]) which is where I am already. I think I don't understand.
I have no field named "name" nor do I have a field named "year". You're suggesting I add a field "DataYear" in addition to CkDate? So then the field would need to be tied to the CkDate... So the field would be DataYear: Year([CkDate]) which is where I am already. I think I don't understand.
If CKDate is defined as a date and that is the field we are talking about then you can leave it as is but use Year(CKDate) to extract the year only
Where Year(CKDate) = Forms!yourform!DataYear
Your initial post was setting a TempVar to Year or True. You can't use a tempvar that way. If you want the selection to be optional so you select a specific year or all data then the Where clause would be:
Where Year(CKDate) = Forms!yourform!DataYear OR Forms!yourform!DataYear Is Null
Instead of using a reference to a form field, you can substitute a reference to a temp var BUT you can't use the first expression if the TempVar might be null, you must use the second where you specifically check for null.
Unless you want to have an application wide value put in the tempvar, I would use a form field reference in the query.
How a WHERE clause works:
When running a query. if it has a WHERE clause, the expression in the clause is evaluated against every row of the table in the FROM clause.
if the expression evaluates to TRUE then the row is returned in the query result; if not, then the row is not returned.
As a simple example:
SQL:
SELECT * FROM YourTable WHERE IDField <= 10;
Here each row of the table is checked whether its IDField satisfies the expression, and only the first ten records are returned (if there are no missing values for IDField).
However, the expression in the WHERE clause needn't reference a field in the record. Consider what you were trying to do with 1 = 1:
SQL:
SELECT * FROM YourTable WHERE 1 = 1;
Each row is evaluated and since 1 = 1 is TRUE the row is returned, ie all records are returned. But the expression had nothing to do with any of the fields in the record.
Next, you need to consider the evaluation of boolean expressions (expressions that give TRUE or FALSE as their result, like a WHERE clause) with AND and OR.
Using constant TRUE/FALSE values to simplify explanation:
Code:
TRUE AND TRUE = TRUE
TRUE AND FALSE = FALSE
FALSE AND TRUE = FALSE
FALSE AND FALSE = FALSE
TRUE OR TRUE = TRUE
TRUE OR FALSE = TRUE
FALSE OR TRUE = TRUE
FALSE OR FALSE = FALSE
So this brings us back to your WHERE clause that works:
SQL:
...
WHERE Year(CkDate) = [TempVars]![DateLimit]
OR [TempVars]![DateLimit] IS NULL
...
You have two expressions joined by an OR:
1. Year(CkDate) = [TempVars]![DateLimit]
2. [TempVars]![DateLimit] IS NULL
The first depends on a field in each record (CkDate). If [TempVars]![DateLimit] contains a value then only matching rows where the Year of CkDate is the same will be returned. It doesn't matter that the expression in the OR part of the WHERE clause will return FALSE since, with an OR, only one of the expressions has to return TRUE.
If [TempVars]![DateLimit] is NULL then none of the records will satisfy the first expression, but the second expression [TempVars]![DateLimit] IS NULL will always be TRUE - it doesn't depend on any of the fields in the record; and since with an OR'd expression only one of the expressions need evaluate to TRUE for the whole expression to evaluate to TRUE, then all records will be returned.
So this is the trick when you are creating a parameter that you want to pass to a query: if your parameter (textbox, other control, or TempVar in your case) contains a value then compare the value to the specific field in the record, otherwise OR an expression that evaluates to TRUE, ie [TempVars]![DateLimit] IS NULL
Hope that's clearer than mud! Shout if you still need further explanation.
If I add
