query works fine but demands parameters when adding simple criteria (1 Viewer)
- Thread starter Happy YN
- Start date
boblarson
Smeghead
- Local time
- Today, 15:24
- Joined
- Jan 12, 2001
- Messages
- 32,059
Sounds like you answered your own question. You say: "namely a value for a field which is anyway used a few times succcessfully in the same query!"
If you are using the same field in the same query, then you would need to place the criteria in each of the spots, not just one.
BL
hth
If you are using the same field in the same query, then you would need to place the criteria in each of the spots, not just one.
BL
hth
Not quite! ne of the fields in the query is a field from a table which is retrieved succesfully. I then have another field in the same query which uses an expression to do a calculation on the first field, again succesfully.However when I wish to specify a criteria for that second field such as >0 then it asks for parameters of that original first field! even though it is retrieving it and making calculations with it for each record individually. The query is rather complex but if anyone is able to help I 'll post it!
Thankx again
Thankx again
R
Rich
Guest
Filter the calculated field via a form/report or another query from the first.
tried that -thru another query (not sure how to filter in a report??)
basically i have transactions with a [datee] date field. I wish to group them by financial year. I first used this expression to determine the year (which ends jun 30)
fyear: (Year([datee])-IIf([datee]<DateSerial(Year([datee]),7,1),1,0)) BUT I don't want the records which do not have a complete year to june of this year so I did the same for date() to measure the date today. then I subtracted the two and I wanted to specify a criteria in that field to show only if its more than 0 because that would get rid of the last year which is not yet up to jun 30
this then is my sql
SELECT Expenses.ExpenseID, Expenses.AmountSpent, Expenses.Date AS datee, [tblExpensesCategories].ExpensesCategoryID AS Expr1, Expenses.EProperty, ExpenseTypes.Name, Properties.Property AS prop, (Year([datee])-IIf([datee]<DateSerial(Year([datee]),7,1),1,0)) AS fyear, Year(Date())-IIf(Date()<DateSerial(Year(Date()),7,1),1,0) AS fyearNow, IIf([fyearnow]-[fyear]>0,True,False) AS complete
FROM tblExpensesCategories, Properties INNER JOIN (ExpenseTypes INNER JOIN Expenses ON ExpenseTypes.ID = Expenses.ExpenseType) ON Properties.PID = Expenses.EProperty
WHERE (((Expenses.Date) Between [forms]![frmexpenses]![fromtext] And [forms]![frmexpenses]![totext]) AND ((Expenses.EProperty) Like [forms]![frmexpenses]![propcombo]) AND ((IIf([fyearnow]-[fyear]>0,True,False))=True) AND (([tblExpensesCategories].[ExpensesCategoryID]) Like [forms]![frmexpenses]![expcombo]))
ORDER BY ExpenseTypes.Name, Expenses.Date;
hope i have explained myself
thanx
basically i have transactions with a [datee] date field. I wish to group them by financial year. I first used this expression to determine the year (which ends jun 30)
fyear: (Year([datee])-IIf([datee]<DateSerial(Year([datee]),7,1),1,0)) BUT I don't want the records which do not have a complete year to june of this year so I did the same for date() to measure the date today. then I subtracted the two and I wanted to specify a criteria in that field to show only if its more than 0 because that would get rid of the last year which is not yet up to jun 30
this then is my sql
SELECT Expenses.ExpenseID, Expenses.AmountSpent, Expenses.Date AS datee, [tblExpensesCategories].ExpensesCategoryID AS Expr1, Expenses.EProperty, ExpenseTypes.Name, Properties.Property AS prop, (Year([datee])-IIf([datee]<DateSerial(Year([datee]),7,1),1,0)) AS fyear, Year(Date())-IIf(Date()<DateSerial(Year(Date()),7,1),1,0) AS fyearNow, IIf([fyearnow]-[fyear]>0,True,False) AS complete
FROM tblExpensesCategories, Properties INNER JOIN (ExpenseTypes INNER JOIN Expenses ON ExpenseTypes.ID = Expenses.ExpenseType) ON Properties.PID = Expenses.EProperty
WHERE (((Expenses.Date) Between [forms]![frmexpenses]![fromtext] And [forms]![frmexpenses]![totext]) AND ((Expenses.EProperty) Like [forms]![frmexpenses]![propcombo]) AND ((IIf([fyearnow]-[fyear]>0,True,False))=True) AND (([tblExpensesCategories].[ExpensesCategoryID]) Like [forms]![frmexpenses]![expcombo]))
ORDER BY ExpenseTypes.Name, Expenses.Date;
hope i have explained myself
thanx
