Search results

Here is a formula you can try: =LOOKUP(2,1/((A:A=F1)*(B:B<=G1)),C:C) To make it more efficient you could replace whole column ranges with known defined ranges or dynamic named ranges (if they are growing).

Ok. Great. I'll keep that in mind. Thanks again for the quick help.

The data is actually in ORACLE.

Oh, I see. Yes, that worked. Thanks. Can you tell me why, the SELECT worked fine with the %, but the Update did not? And why it works with *?

Hi pbaldy, I am not exactly sure what you mean. How do I set up the Update Query then? When I try to create the Update query, Access tells me that you can't run an update query on the * wildcard (Note:: Access 2003)

Update Query saying 0 Records will be updated Hi Guys, Not sure what I am missing here. I have a simple Select Query based on one table. In SQL View, the query is: SELECT SYSADM_CUSTOMER_ORDER.ID, SYSADM_CUSTOMER_ORDER.STATUS FROM SYSADM_CUSTOMER_ORDER WHERE (((SYSADM_CUSTOMER_ORDER.ID)...

Possibly? =SUMPRODUCT(COUNTIF(B27,"*"&Setup!$M$2:$M$15&"*"))

Try recording a macro of you creating the data validation. It should give you an idea of what the line would look like. To do the data validation, you would select Custom and then enter the formula in the Source field... e.g. =MOD(A1,1)=0

Can you post a sample workbook, showing your setup and what you require? It's not easy to tell with your sample in first post.

grabbed them up

The thing is what does having the #N/A mean? it's not wrong room, it's not the OOB or Moved and it's not a Room change... you can make it blank, leave it as #N/A (so that you know you need to look into it) or you can combine it with one of the above resulting strings... your choice....

How about? =IF(ISERROR(MATCH(D2,E$2:E$188,0)),IF(D2="OOB","OOB","Moved"),IF(AND(LEFT(E2,1)<>"D",LEFT(E2,3)<>"C-3"),"Wrong Bldg/Flr",IF(D2<>E2,"Room Change","")))copied down. Note there are a couple of #N/A errors produced. This is because the D column found a match in column E, but there is...

Maybe it is the order that you nest the IF statements? Because it seems that as it is, it is returning the results as per the formula as written? Note, IF statements work from Left to Right. As soon as one of the IF statements results in TRUE, then it returns that IF_TRUE result and stops...

Is that C - 305 actually a number formatted to show the C - in front? If so, then it could be a rounding issue, and you might need to employ a ROUND() function to get more precision in matches. If not, then there might be some leading or trailing spaces are even extra spaces around the dash...

Have you tried formula: =TEXT(A24+7/24,”m/d/yyyy h:mm:ss”) and =TEXT(A24+1+7/24,”m/d/yyyy h:mm:ss") instead? Maybe it uses the military time. Look at the query results within the MS Query app (before sending to Excel) to check date/time formatting...

Access World Forums

The only way to do it with formula is to use INDIRECT =SUMPRODUCT(--(INDIRECT("'C:\Documents and Settings\MyProfile\Desktop\Meeting\Lists\["&A2&".xls]"&A2&"'!$F$2"))) The only problem unfortunately, though, is that the source workbook would need to be open for INDIRECT to work.... so then if...

I think that should be AND() because you want both to be non-blank. =IF(AND(J2<>"",L2<>""),L2-J2,"N/A") or you can use something like.. =IF(COUNTBLANK(J2,L2)>0,"N/A",L2-J2)

containing, amazingly enough,

a decent amount