Complex Query Not Working

[Ravenray]

Complex Query not working....Totally Lost now

Ok, I'm totally lost now on how to create this in my Query.

IIf(Date()<DateAdd("m",1,[DateAssesed]) And [DateVerified]=Null,Yes,No)

I'm not sure how or where to put this. If someone can help me out on this, I'll apreciate it. Thank You.

 

[Pat Hartman]

If you are trying to return a true or false value, put this in the field cell of an empty column:

IIf(Date()<DateAdd("m",1,[DateAssesed]) And IsNull([DateVerified]), -1, 0)


If you are using this as selection criteria, put this in the criteria cell of an empty column:

Date() < DateAdd("m",1,[DateAssesed]) And [DateVerified] Is Null

I modified the syntax of your IIf() because you CANNOT test a field for null by simply using the expression "fldX = Null". There are lots of posts here on null values and lots written in help. In VBA, use the IsNull() function, in SQL use the keywords Is Null.

 

[Ravenray]

Pat,

Ahhh, that was my mistake on my iif statement. I now understand master. I forgot to look for that syntax in the help file.

On regards of a criteria in the query, is it possible that when the value is -1 to not display, and when the value is 0 to display that record?

 

[Ravenray]

Whoops, never mind. I got it. Thank you very much. Now I will know how to handle null values also.

 

[Ravenray]

Date() < DateAdd("m",1,[DateAssesed]) And [DateVerified] Is Null

Seems not work work properly. Is there a field name from one of my tables that I need to put this in?

 

[Ravenray]

OMG!!!! I was thinking so wrong on my criteria's...LOL

It's actually like this

Field: [DateVerified]
Table: tbl
Criteria: is not null

Field: Dateadd("m",1,[DateAssessed])
Criteria:
or: >=Date()

A descripition of what this does. I have an attendance sheet. When the people have been Assessed, they are allowed 1 month to attend the class, if they don't bring in the verification within that month, they are dropped. But when they do bring in verification, they can then be admitted back into the class.