Wildcard in DLookup possible?

[fredalina]

Is it possible to use a wildcard "*" within the Criteria portion of a DLookup function in a module?

Some background: I have a group of Excel spreadsheets that I would like to put into Access, however the data is not at all normalized. The data is currently set up with a group of items to the left, and along the top a row of months for each period in the fiscal year, with the data shown below. The sort of thing you'd find after performing a Crosstab query on normalized data. Each spreadsheet is for one fiscal year.

Unfortunately, some of the items along the left contain text for the fiscal year in question. i.e. "FY09 Budget". Because I will ultimately include all fiscal years in one table and thus all budget information in that table, I need to include that item.

This code works:

budget = DLookup(mth, fromtbl, "[fieldname] = Sales Plan - FY09 Official Budget")

This code does not:

budget = DLookup(mth, fromtbl, "[fieldname] = 'Sales Plan - *'")

Is it possible to use a wildcard, or perhaps to match MOST of the field but not entirely? Any other suggestions? I suppose I could hard-code it if I have to and change the code for the FY each time, but it would be nice to be able to code it.

Thanks!

 

[boblarson]

So, a DLookup only returns the first item that matches. So how are you going to pass the year to it? You could do something like this:

Function getBudget(mth As String, fromtbl As String, FY As string) As Currency
Dim sCriteria As String
 
sCriteria = "[fieldname] = Sales Plan - " & FY & " Official Budget"
budget = DLookup(mth, fromtbl, sCriteria)
 
getBudget = budget
End Function

Something like that? You would pass the year you wanted along with the other arguments and it would return the value for you.

 

[fredalina]

Thanks! It looks like I'll have to go this route.

 

[fredalina]

On second thought, this won't work for all of the items in this situation. I really need to be able to do a DLookup or otherwise search for and find something with a wildcard, or a portion of a field.

This is actually nested within 2 loops, and one of the looping structures loops through Item Type. If I'm running a grocery store and I need to know what the regional sales are for each product type (i.e. fruit, paper goods, milk, etc), but the *name* for the regional sales is different for each product type, i.e. "Regional Sales - Fruit" and "Regional Sales - Paper", I will need to be able to identify the string "Regional Sales". All of the product types are in one document and are extremely similar but for this one bit they have different names. (Assume that I am only given regional sales in one chunk and it's not just a summation of the smaller pieces).

Suggestions?