I see how this might work if I only had a few names in the [List Name] table.
However, my [List Name] table contains over 500 entries. My main table has
data imported every day with new but similar names, some names have the
underscore with date number at the end, _28, _0528, _528, while others do
not have any underscore with date at the end. I need to be able to pull them
all. The[List Name] table holds the generic version of what some of my newly
imported names should be. So, I can not use the names in any of my formulas,
queries, macros or immediate window because of the large umber of
new/similare names that get imported everyday. I need to use Operators and
Field names ie [List Name], to break down the newly imported names with
underscore & date to the generic name found in my [List Name] table.
This is what the imported data can look like. 500+ every day.
Day Before YESTERDAY TODAY
dddd_e_27 dddd_e_28 dddd_e_29
dddd_hi_e_27 dddd_hi_e_28 dddd_hi_e_29
cccc_e_0527 cccc_e_0528 cccc_e_0529
cccc_hi_e_0527 cccc_hi_e_0528 cccc_hi_e_0529
gggg_e_527 gggg_e_528 gggg_e_529
gggg_hi_e_527 gggg_hi_e_528 gggg_hi_e_529
D865975 D865975 D865975
This is what the names should look like from this Query only.
Day Before YESTERDAY TODAY
dddd_e dddd_e dddd_e
dddd_hi_e dddd_hi_e dddd_hi_e
cccc_e cccc_e cccc_e
cccc_hi_e cccc_hi_e cccc_hi_e
gggg_e gggg_e gggg_e
gggg_hi_e gggg_hi_e gggg_hi_e
D865975 D865975 D865975
As you can see from the illistration above, the result I am looking for has
the full name on everything but for those names that had an underscore & date
should now no longer have that undrscore & date.
Sorry for the long explaination but either I am not asking my question
correctly or I am not understanding your solution. Thank you for your
continuing help.
S.Clark said:
In the Immediate Window, you can't use field names. But, in the query, you
should be, as long as the table with that field name is in the query.
Select Left$([List Name],instrrev([List Name], "_")-1) from [TableName]
--
Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
:
when I enter this, substituting "dddd_hi_e_528" with [list name], i get a
syntax error. Access has all of the names listed in a table. I need to drop
anything from the end of the names from the underscore right, so
"dddd_hi_e_528" would show as "dddd_hi_e". I can not include the table names
in the formula due to there being too many names. I need the Excel formula
provided translated into access without including the names in the formula.
:
Try this in the Immediate Window (Ctrl+G)
x = "dddd_hi_e_528"
? left$(x,instrrev(x, "_")-1)
--
Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting
:
I have the following formula in excel to find the base name in the cell.
=IF(ISERROR(IF(IF(FIND("_",RIGHT(G77,3),1)=1,FIND("_",RIGHT(G77,3),1),G77)=1,LEFT(G77,LEN(G77)-3),G77))=TRUE,G77,LEFT(G77,LEN(G77)-3))
Anywhere G77 shows up in the above Excel formula needs to be pointing to
[list name] in access.
Examples of names being used in both Excel & Access;
aaaa_hi_e_28
bbbb_e_28
da87579
cccc_e_0528
dddd_hi_e_528
As you see, some names have and underscore and some digits at the end
indicating a date and some do not. The true name does not include the last
underscore or final digits.
Please help.
Anyhelp on this is VERY much appreciated.
