Date Expression Question

[MCMike]

Good Afternoon,

I am having difficulty writing an expression for a query.

I would like Overdue: to return "Overdue" if the date in [Application-Next] is between current date and current date minus one month, (for example, return any dates between 30 May 2017 through today, 30 June 2017).


Something like the below:

  Overdue: IIf([Application-Last]Between Date() And DateAdd("m",-1,Date()), "Overdue")  

No matter what I try I can't get the syntax right.

Thank you so much for your support.

MCMike

[MCMike]

I understand that I mistakenly typed Application-Last in the expression example. I used Application-Next when trying to write the expression in the query and still received syntax errors. Thanks.

[BikeToWork]

Make sure that there is a space between ] and Between. Also, try switching the date parameters around so that DateAdd("m", -1, Date()) comes before Date() in the expression. Are you sure that [Application-Last] is of date datatype?

[NeoPa]

You give no indication of what you actually receive. Error message? An unexpected value? Hard to work when information isn't shared.

The only thing I see wrong is the lack of a third parameter. Surely putting the first value as the lower limit makes good sense, as does a space before Between, but I don't see either as problems.

As I say though, we don't know what you're seeing so it's just intelligent guessing really.

[PhilOfWalton]

I think BikeToWork has hit the nail on the head about Application-Next not being a date.

Try this

OverDue: IIf(IsDate([Application-Next]),IIf([Application-Next] Between Date() And DateAdd("m",-1,Date()),"Overdue"),"Not a Date")
 

Phil

[NeoPa]

Hi Phil.

That still leaves only the first two parameters for the IIf(). The OP could be confused by the results when this is not hit and it defaults to FALSE. We don't yet know what the OP considers to be the error. Could be simply that. Also, the field may be of DateTime type yet still contain a Null, which wouldn't be a DateTime per se. No Nz() so not handled if it is. All possibilities. All just guessing without any idea what the actual problem is that's been reported (almost).

[MCMike]

Sorry the delay in response, I have been away. I will deliver the error message in a bit. It was a syntax error but I will let you know exactly where in the expression it occurred and exactly what it said possibly later today but definitely tomorrow. Thank you.

[NeoPa]

Hi MCMike.

May I suggest that you check the ideas already raised and see if any are pertinent before you give us the error message and details. It may be one of them resolves it. We're not likely to think of saying the same thing again if it does turn out that way so excluding those before posting makes good sense.

Good luck.

[MCMike]

Indeed I will sir. Thank you.

[MCMike]

Ok,

Note: I am in fact working with a date field [Application-Next] from table OPS (confirmed date field in design view)

When I run a query with the original expression:

Overdue: IIf([Application-Next]Between Date() And DateAdd("m",-1,Date()), "Overdue")  

I receive the following error: The expression you entered contains invalid syntax. You may have entered an operand without an operator.

When I click OK, the initial expression header "Overdue:" gets highlighted black and that's it. No other indications in the expression as to where the error might lie.

Same error occurs when I modify the expression per suggestion from Phil with suggestion from Bike,

OverDue: IIf(IsDate([Application-Next]),IIf([Application-Next] Between Date() And DateAdd("m",-1,Date()),"Overdue"),"Not a Date")

The expression you entered contains invalid syntax. You may have entered an operand without an operator.

When I click OK, the initial expression header "Overdue:" gets highlighted black and that's it. No other indications in the expression as to where the error might lie.

Do you guys accept zipped database files? It is a small file and I'm certain could be easily analyzed with a small write-up by me.

Thanks,

MCMike

[BikeToWork]

Try a space between the ] and the Between.

[NeoPa]

Hi MCMike.

Some do. Not all either can or will. I'd be happy to. Make sure any linked tables are replaced (Copy/Paste) with local ones and test for the same problems just before you ZIP it to send.

In your first version, as well as the second IIf() of the second version, you have no third parameter. I just checked this and it shouldn't cause an issue.

@BikeToWork.
Check out the second version he tried. If you try to enter it that way Access corrects it for you automatically.

@MCMike.
That brings up a very good point. You should ensure you never post anything here that is not an exact match of what's in the system. That's not necessarily what you type in, particularly in a situation like this where it is fixed for you automatically. Always copy and paste what's in the database.

[MCMike]

NeoPa,

I will put the tables into the front end and test everything you suggest before zipping/sending. If I still need to, where do I send the zipped file?

Thanks,

MC

[NeoPa]

You attach it to your next post. This can only be done using the Advanced button.

[MCMike]

Thanks, NeoPa,

I don't think I can from work. Our network strips anything executable or just renders it as a useless text file full of garbage. I will have to put to disk and send from home. I will try but I doubt it will make it to you intact.

It's really a small piece of junk DB that I sort of inherited. My write-up should clear up what I am trying to do. I will send it later this evening or tomorrow afternoon.

MCMike

[MCMike]

I tried BikeToWork's suggestion of "Try a space between the ] and the Between". That worked along with the fact that I had to change some of my dates in [Application-Next] to be within the month minus 1 range because my test copy is over a month old. With those two changes, the expression returned some values of "Overdue" as hoped!

I am going to try using the "Overdue" values in yet one more conditional formatting attempt. If I can't get it I will send the database. But the below expression works:

Overdue: IIf([Application-Next] Between Date() And DateAdd("m",-1,Date()), "Overdue")

THANKS to ALL!

[NeoPa]

Hi.

As I pointed out in post #12 it's generally not possible to enter Between immediately after the ] in such a query. Besides, your second attempt from post #10 shows you already tried that and it didn't fix any issue. I suspect therefore, that what you were seeing was a result of some of your data.

Your last comment though, indicates the problem actually changed somewhere between then and now. Originally you were reporting an error message. Now it seems you're treating the earlier absence of expected results as an error (Unless I misinterpret your comments). Two completely different things. You need to be very clear about such things when you're reporting problems on a forum otherwise you'll be sending people off on wild goose chases.

Can you explain at exactly which point the error message disappeared?

[MCMike]

I apologize. I may have inadvertently reported that I tried to put a space after the ] before Between when in fact I had not yet tried it.

[NeoPa]

It shouldn't make any difference MCMike. What you reported was inaccurate from the very first. Just try to enter it without the space and you'll see, when you look at it again, that the space was added for you. It's not possible to have it without a space in a normal QueryDef.

That's why I made that comment in post #12 about posting the information accurately. Everyone's attention has been on something that is a result of posting what you think you typed in rather than COPYing and PASTing what you have there. Something that isn't there.