Convert Excel formula to Access Query formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SJW_OST - 29 May 2008 03:08 GMT

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.

Reply to this Message

S.Clark - 29 May 2008 04:43 GMT

Try this in the Immediate Window (Ctrl+G)

x = "dddd_hi_e_528"
? left$(x,instrrev(x, "_")-1)

Signature

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.
>
[quoted text clipped - 13 lines]
> Please help.
> Anyhelp on this is VERY much appreciated.

Reply to this Message

SJW_OST - 29 May 2008 05:20 GMT

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)
>
[quoted text clipped - 18 lines]
> > Please help.
> > Anyhelp on this is VERY much appreciated.

Reply to this Message

S.Clark - 29 May 2008 13:43 GMT

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]

Signature

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
[quoted text clipped - 25 lines]
> > > Please help.
> > > Anyhelp on this is VERY much appreciated.

Reply to this Message

SJW_OST - 29 May 2008 18:27 GMT

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.

> 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.
[quoted text clipped - 30 lines]
> > > > Please help.
> > > > Anyhelp on this is VERY much appreciated.

Reply to this Message

S.Clark - 29 May 2008 19:36 GMT

So, to recap... You receive a daily Excel file with field names that may be
different on a daily basis.

If this is the case, then you may want to create a Macro in Excel that tacks
on a new field name... one that matches your desired destination table in
Access.

Signature

Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting

> 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
[quoted text clipped - 69 lines]
> > > > > Please help.
> > > > > Anyhelp on this is VERY much appreciated.

Reply to this Message

SJW_OST - 29 May 2008 19:55 GMT

I appologize. I have not asked my question correctly. I am talking about
Access. I created a formula in Excel only to get an idea of how to do the
same thing in Access. I am a beginner with Access formulas. You have helped
greatly however please refer to the posting "#Error" for a simplified
question.

Thank you very much.

> So, to recap... You receive a daily Excel file with field names that may be
> different on a daily basis.
[quoted text clipped - 76 lines]
> > > > > > Please help.
> > > > > > Anyhelp on this is VERY much appreciated.

Reply to this Message