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
;
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.
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.
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.
Just so you know... I never completely understand what I'm doing and I'm okay with that. Some of us are doomed to be in a fog You put way more time into this than I deserve and I very much appreciate it. Thank you so much.
If I add
You put way more time into this than I deserve and I very much appreciate it. Thank you so much.
