Full Version: Two criteria in an if statement??
UtterAccess Forums > Microsoft® Access > Access Forms
scuzz
Hi guys,
appologies but i did not know where else to put this post*
I was wondering if it is possible to have two criteria which must be met in order for an action to be taken.
I have two criteria i would like to connect together to run a piece of code:
If Me!Balance.Value = 0
And
Notnull(Me.PrebookedLessons) Then
Call ResetValues
End If
I had trouble when i got to the Notnull part, im not sure what that should be, i basically want to say the field isnt null (does contain a value). Can anyone shed some light on this?
Thanks
Fletch
Try
If Me!Balance.Value = 0 And Not IsNull(Me.PrebookedLessons) Then
Call ResetValues
End If
HTH frown.gif
scuzz
Nope, that didnt do it im afraid. I put a messagebox in the code where the Call ResetValue part was so that i could see if the code worked. But the message box displayed so i know that it didnt recognise the null or 0 value.
ScottGem
it should work. Step thru the code and check your values. Remember a blank is not a null.
Fletch
Ditto what ScottGem said frown.gif
If you saw the message box, then that means Me.Balance.Valus equals 0 and Me.PrebookedLessons is not Null. If these are not the conditions that you want to be met in order for the message box or anything else to be executed, then under what conditions do you want this If Then statement to evaluate to True.
scuzz
um........ confused.gif
think i've got myself in a muddle! I want the procedures to run if
1) the balance equates to 0
2) and there is a null in the prebooked lessons textbox.
AJS
Then take away the Not in the code babrandt posted.
AJ
Fletch
Bummer--hate muddles frown.gif
When try
If Me!Balance.Value = 0 And IsNull(Me.PrebookedLessons) Then
Call ResetValues
End If
That should run when Balance.Value = 0 and Me.PrebookedLessons IS null. frown.gif
Also, remember a null in Me.PrebookedLessons is different than a false value if PrebookedLessons is a check box datatype.
scuzz
I think that did the trick. the prebookedlessons is just a text box which allows the user to enter the number of prebooked lessons a pupil has.
hilst i have your attention....
Ive got two other queries one of them counts the number of times a name appears in a table and the other sums the amount of money paid by that pupil. Ive tried inserting the Nz into the SQL but it didnt produce 0's as i had hoped. Can anyone see where ive gone horribly wrong??
Fletch
It's hard to say where you've gone wrong without you showing us your SQL. However, one quick note, the Nz function looks like this
Nz(somevalue, value if null)
However, the value if null part is optional and if not used it will return an empty string (""). If you want it to return a 0, then you should have
Nz([your field name], 0)
If that's not it, please post your SQL frown.gif
iandouglas
I live & learn. I always though nz([Field containing null]) returned zero & have used nz as if this is so & not had problems. VBA help says: -
If the value of the variant argument is Null, the Nz function returns the number zero or a zero-length string (always returns a zero-length string when used in a query expression), depending on whether the context indicates the value should be a number or a string.
Oshall be more careful in future!
Fletch
Thanks for the clarification iandouglas. Rereading my post leads one to conclude that it always returns the empty string, when what I meant to say was that for queries it always returns "" unless otherwise specified since scuzz was asking about queries. Nice catch frown.gif
scuzz
Appologies, i realised i hadnt posted my SQL as i was driving to the gym! haha, oh well here it is now!
And its just the one query, not two!
SELECT Nz(Count([Payments Archive].Fullname),0) AS CountOfFullname, qryAllPupils.RecID, Nz(Sum([Payments Archive].[Amount Paid]),0) AS [SumOfAmount Paid]
FROM [Payments Archive] INNER JOIN qryAllPupils ON [Payments Archive].Fullname=qryAllPupils.Fullname
GROUP BY qryAllPupils.RecID;
scuzz
I tried to see if i could add Nz anywhere else, but parts of this query are displayed on records using subforms. For example i have a subform to show the SumOfAmountPaid, so i cant change the control source from the subform....can I??
ALaRiva
What's the problem with your query? Is it not returning the correct results? I've looked over the thread a couple times and couldn't find where you explained what problem you had with it.
Anthony
scuzz
Even though i tried this SQL:
SELECT Nz(Count([Payments Archive].Fullname),0) AS CountOfFullname, qryAllPupils.RecID, Nz(Sum([Payments Archive].[Amount Paid]),0) AS [SumOfAmount Paid]
FROM [Payments Archive] INNER JOIN qryAllPupils ON [Payments Archive].Fullname=qryAllPupils.Fullname
GROUP BY qryAllPupils.RecID;
It didnt return 0's when there was a null value as i had hoped.
Fletch
First off, Count should never return a null--if there are only null values in the field that it is counting, it returns a 0 anyway. Sum will return a null if there are ALL null values in the field. In that case, I see no reason why Nz won't work as you have it. It does sound a little fishy that you're joining on a field named Fullname since I would expect that field to have a textual name rather than a unique ID for the person (after all, how do you distinguish between 2 people with the same name?).
If you're still having trouble, can you post the DB with a pointer to the query that is giving you the problem. Thanks! frown.gif
scuzz
The reason that it is joined on Fullname is because DFish helped me out with it. It's his work, not mine, but now im having trouble making it do what i would like and im contemplating pulling it out and starting over. Perhaps adding the RecID field to my Payments archive table and counting that instead? That would eliminate the possibility of two pupils with the same name.
Fletch
It's hard to comment on the best path without better understanding the design of your tables. If RecID is a unique identifier (such as a primary key) for each pupil, however, then it probably makes sense to use it as primary/foreign key relationship for any table that holds related information about pupils. You might also consider splitting up the name field within the pupil table, to be fully normalized. Your payments archive table would have RecID for each entry instead of full name to indicate who had paid what when. Make sense? If you get stuck or want someone to take a look at it, I'm sure if you post the database someone will do just that. frown.gif
DoubleD
As long as you're using an INNER JOIN you will not get the zeros for Pupils who have not made payments. They will be excluded from the query. You would need to change that to an OUTER JOIN to include Pupils who have not made payments.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.