Access equivalent of Excel's "SEARCH" and "IS

[jj2ford]

I am a new user to Access and I'm trying to build a query in Access that will work much faster than me doing a monthly report in Excel manually, but I am having trouble with the limited amount of Functions in Access. So, I was wondering if there is an equivalent in Access for the "SEARCH" and "ISNUMBER" functions? My formula in Excel starts like this:

IF(ISNUMBER(SEARCH("CUST",$G4)).......

I tried doing this in Access using the expression builder and it doesn't work. If anyone could help me out with this I'd really appreciate it.

Thanks!

 

[Norie]

Limited amount of functions, you are joking aren't you.

What are you actually trying to do with this expression?

Are you trying to return only the records where a field has CUST in it?

If you are that can easily be done by using *CUST* for the criteria for the field.

If you want to return a particular value if a field contains CUST then you can use InStr and Iif

Expr:Iif(InStr([YourField], "CUST")>0, "Value1", "Value2")

 

[jj2ford]

Thanks for the reply. What I am actually trying to do with this expression is a little complicated. I have a set of data that looks like the information below:

Category Description Qty Cubic Ft EXPRESSION
Chairmats 45 x 64 Round 12 5
Chairmats 45 x 64 Cust. Rect 12 5

I need to take Qty x Cubic Ft. for ALL items that have a category of "Chairmats" and that have "Cust" in the description field. My formula in Excel looks like this:

=IF([Category]="CHAIRMATS",IF(ISNUMBER(SEARCH("CUST",[Description])),0,IF([Qty]>0,[Qty]*[Cubic Ft],0)),0)

So with this formula, it would calculate the Expression column as 60 for the first example and 0 for the second. Let me know if this makes sense and you know of a way to do this in Access. THANKS!!

 

[jj2ford]

Sorry my data above is so messy. I don't know how to paste from Excel. But basically there are 5 columns. Let me know if you need me to try something else.

 

[Norie]

Doesn't the Excel formula return 0 for data with CUST in it?

That's what the 2nd if is doing anyway.

 

[jj2ford]

Yes, Excel would return a 0 for the data with CUST in it. I only need to multiply Qty. x Cubic Ft. when CUST in NOT in it. Sorry for the confusion. So, when CUST is NOT in the Description I need to multiply the two. Thanks.

 

[Norie]

That's what I thought, just wanted clarification.

Try this expression.

IIf(InStr([Description],"cust"),0,[qty]*[cubic ft])

 

[jj2ford]

The expression worked great. Thanks, this is a big help.

 

[DOCELEPHANT]

I tried looking for the syntax to find data from one field within a string of data from another (a field reference instead of a fixed search). This post was on the first page of Google results for most of the search terms I could think of so I thought I'd post my solution here in case others come looking for the same thing.

I put this into the Criteria for a Select query:

InStr(1,[field_to_search_in],[field_that_has_search_criteria])>"0"

In a Select query, this should return only rows where some or all of the first field matches all of the second.