Need Help in adding another expression to my database

[kotascye]

im in need of some serious help....ive provided pics to help with the visualizing and all...EDIT:i tried to provide pics but apparently i need a post count of 10...so no pics

ok as u see in my daily roll call report i have 2 groups..."on program" and "graduates"

these 2 groups are creating in the query...as u see in the 2nd pic... the expression as followed

Expr1: IIf([Date Graduated]<Date(),Date(),[Date Graduated])

next you can see in my 3rd pic the report and the expression that gives the 2 groups there names...it is as followed

=IIf(IsNull([Date Out]),IIf(IsNull([Date Graduated]),"On Program","Graduates"),"Recent Departures")

i will clarify that i took out the names in the roll call but both groups are sorted by the date they came in going down the list


now i need to add another group "Staff Members" to my roll call...what would be best way to do this???

every way i have tried altering the query expression or the report expression result in a blank roll call....thanks and God Bless!!!

 

[plog]

now i need to add another group "Staff Members" to my roll call...what would be best way to do this???

I'm assuming you're trying to add this in that IIf statement you say is in you're 3rd pic. If so, move that logic to a function inside a module, pass it the data it needs and have it return whatever value it should be. You're already nesting Iif statements--any more than that and its time for a function.

Your query field for it would then look like this:

Groupings: getGroupings([Date Graduated])


And the function would look something like this:

Function getGroupings(DateGraduated As Date, DateOut As Date) As String
    ' takes Graduation Date (GraduationDate) and Date Out (DateOut) and returns grouping of it for roll call

Dim ret As String                   ' return value from function
ret="Recent Departures"
    ' default return value

If IsNull(DateOut) Then
    If IsNull(DateGraduated) Then ret="Graduates" Else "On Program"
End If
    
getGroupings=ret 

End Function

The above code is the equivalent of what you currently have. You would need to add the logic for "Staff Members" option.

 

[kotascye]

I'm assuming you're trying to add this in that IIf statement you say is in you're 3rd pic. If so, move that logic to a function inside a module, pass it the data it needs and have it return whatever value it should be. You're already nesting Iif statements--any more than that and its time for a function.

Your query field for it would then look like this:

Groupings: getGroupings([Date Graduated])


And the function would look something like this:

Function getGroupings(DateGraduated As Date, DateOut As Date) As String
    ' takes Graduation Date (GraduationDate) and Date Out (DateOut) and returns grouping of it for roll call

Dim ret As String                   ' return value from function
ret="Recent Departures"
    ' default return value

If IsNull(DateOut) Then
    If IsNull(DateGraduated) Then ret="Graduates" Else "On Program"
End If
    
getGroupings=ret 

End Function

The above code is the equivalent of what you currently have. You would need to add the logic for "Staff Members" option.

I added the staff members field to the resident data form...i was trying to go for a if "yes"

like this Expr1: IIf([Office Member]="Yes",[Office Member]) or add it to my existing expression like this

Expr1: IIf([Date Graduated]<Date(),Date(),[Date Graduated]),IIf([Staff Member]IsNotNull,[Staff Member])

it didnt accept it...or if it did i would have a blank roll call

 

[plog]

What was the point of iterating my comment? For one, its on this page for all to see so you don't really need to do it at all. For another, none of your new post made any reference to it at all.

Lastly, was there a question in any of that new post, or anything I am to respond to?

 

[kotascye]

idk, it made sense to me...i figured i could add a field in my resident data form for office staff and some how make an expression to group all the guys that are in my office staff on my roll call, but idk how to do this without screwing up the original expr1 in the query, so i give up...perhaps i could send u a pm with the pics???

 

[CazB]

Expr1: IIf([Date Graduated]<Date(),Date(),[Date Graduated]),IIf([Staff Member]IsNotNull,[Staff Member])

The problem with your IF is that you have already 'completed' the if before you start checking if they're staff!

so in the classic If, Then, Else you've got "IF they've already graduated, THEN use today's date, ELSE use the Date Graduated"... and the second IF is not seen as part of the same expression cos you've already used up all the parts of the statement, iyswim?

If you want it so also look to see if they're staff, then you need to NEST that IF within the other IF somehow

EDIT:
Without seeing how the data is structured, it's hard to know exactly what you'd need to do but from what you've told us, I think the easiest way of doing that would be if you swapped the expression around, and checked FIRST to see if they're staff, and if they're not, then check for the date?

Expr1: IIf([Staff Member]IsNotNull,[Staff Member], IIf([Date Graduated]<Date(),Date(),[Date Graduated]))

 

[Brianwarnock]

Nope that does not work, if the first iif is true expr1 is set to staff member if it is false it is set to some date.

None of this makes sense.

Brian

 

[CazB]

sorry, confused, thought that's what the OP was after based on the original EXPR1? If he wants 'blank' for Staffmembers then should it be

Expr1: IIf([Staff Member]IsNotNull,"", IIf([Date Graduated]<Date(),Date(),[Date Graduated]))

I personally would actually use Iif(not isnull(staffmember), "", rather than IIf([Staff Member]IsNotNull,"", but was just going with what the OP was using already...

 

[Brianwarnock]

I'm sure is not null is three separate words,

Field Is Not Null is the correct SQL syntax

Not IsNull(field) is VBA syntax and although works forces a function call and is therefore less efficient.

Brian

 

[CazB]

okey doke, I'm self-taught so forgive me when I get things wrong

 

[Brianwarnock]

That's fine, most of us are self taught and have learnt from the forum, it was Pat Hartman who pointed this out in a thread some time ago, and if I had still been working would have had a stack of queries to change.

Brian

 

[kotascye]

absolutely appreciate everyone's help here...ill try these answers i see tonight...i too am self taught...i can alter vb code all day long, i got a ton of examples from back in my underground forum days... but dont tell me to make up something from scratch...and im learning access and excel...fun stuff

so basically the only thing i had to use was those 2 expression...and based on how they looked...was trying to create something to work...and those being 2 expressions involving dates...i was like ....i give up, i need to take this to the interwebs...lulz..

much thanks and God Bless....

 

[kotascye]

anyone else wish to offer there advice on this???

i cant add isnull or isnotnull to the expression...i keep getting an invalid syntax..then it highlights those words....what do i have to provide in order to get the help i need???

 

[Brianwarnock]

You have not shown the syntax that is failing IsNull is a VBA function that can be used but the SQL syntax is Is Null as discussed earlier with cazb

IsNull(field)

(Field) Is Null

The Not is separated in both cases

Not IsNull. And Is Not Null

Why have you not gone with Plog's approach of using a function , I feel that that is the best approach.

Brian

 

[kotascye]

as soon as i can post pics i will...i dont know anything about functions...my database goes off a query and then a report based off that query....so as soon as i get my post count up ill post pics and then im sure you experts can shoot me in the right direction...

 

[Brianwarnock]

There is a "sticky" second thread in the query forum telling you how to post pics below the ten post limit

Brian