External Name Not Defined (1 Viewer)

pastelrain

Registered User.
Local time
Today, 04:15
Joined
Jul 12, 2016
Messages
23
Hi,
I am trying to create a new column within an existing table in my db. I know I need to define the external field [PHARM_NAME], but I am new to VBA and am not sure of the syntax for defining it.

I was trying to accomplish this in a query expression, but there are several other criteria and it was saying the expression was too complex.

Public Sub CreateField()
Dim DB As DAO.Database
Dim TableDef As DAO.TableDef
Dim Fld As DAO.Field2
Set DB = CurrentDb()
Set TableDef = DB.TableDefs("NABP_StarValues_w_County")
Set Fld = TableDef.CreateField("GROUPER_NAME", dbDouble)

Fld.Expression = IIf(Left([PHARM_NAME], 4) = "WALG", "WALGREENS", "")
TableDef.Fields.Append Fld
MsgBox "Added"
End Sub
 

plog

Banishment Pending
Local time
Today, 06:15
Joined
May 11, 2011
Messages
11,611
I am trying to create a new column within an existing table in my db

Usually that is done manually in the design view of the table itself. Why are you trying to accomplish this programmatically?
 

pastelrain

Registered User.
Local time
Today, 04:15
Joined
Jul 12, 2016
Messages
23
So, I removed the brackets and am no longer getting the name not defined error, but am now receiving a 'Cannot define field more than once' for the TableDef.Fields.Append fld line of code.
 

pastelrain

Registered User.
Local time
Today, 04:15
Joined
Jul 12, 2016
Messages
23
I get the error that it is too complex. Any other ideas a greatly appreciated.

IIf(Left([PHARM_NAME],4)="WALG" And [COUNTY]="DADE","WALGREENS SOUTH FL",IIf(Left([PHARM_NAME],4)="WALG" And [COUNTY]="BROWARD","WALGREENS SOUTH FL",IIf(Left([PHARM_NAME],4)="WALG" And [COUNTY]="PALM BEACH","WALGREENS SOUTH FL",IIf(Left([PHARM_NAME],4)="WALG" And [COUNTY]<>"DADE","WALGREENS - OTHER",IIf(Left([PHARM_NAME],4)="WALG" And [COUNTY]<>"BROWARD","WALGREENS - OTHER",IIf(Left([PHARM_NAME],4)="WALG" And [COUNTY]<>"PALM BEACH","WALGREENS - OTHER",IIf(Left([PHARM_NAME],5)="WAL M" Or Left([PHARM_NAME],5)="WALMA" Or Left([PHARM_NAME],5)="WAL-M","WAL-MART",IIF(LEFT([PHARM_NAME],4)="COST","COSTCO",IIF(LEFT([PHARM_NAME],4)="TARG","TARGET",IIF(LEFT([PHARM_NAME],3)="CVS","CVS",IIF(LEFT([PHARM_NAME],3)="PUB","PUBLIX",IIF(LEFT([PHARM_NAME],4)="WINN","WINN-DIXIE",IIF(LEFT([PHARM_NAME],3)="NAV","NAVARRO",IIf(Left([PHARM_NAME],3)="K M" Or Left([PHARM_NAME],3)="KMA" Or Left([PHARM_NAME],3)="K-M","K-MART")))))))))
 

plog

Banishment Pending
Local time
Today, 06:15
Joined
May 11, 2011
Messages
11,611
How does the above code accomplish your stated aim of adding a new field to a table? Hint: it doesn't.

What exaclty are you trying to do? No code, no error messages, no contextless examples of what you've tried--Step back 10 feet and tell us in a simple paragraph what it is you want to accomplish.
 

pastelrain

Registered User.
Local time
Today, 04:15
Joined
Jul 12, 2016
Messages
23
Sorry for not giving any background, I thought it was an easy fix.

I have a table of pharmacy names that I need to create a new column to group them into more simple names. The [PHARM_NAME] field is the entire pharmacy name which could be Walgreens 1, 2, 3, etc., so I am trying to create a calculated field in which to group the pharmacy names into the GROUPER_NAME field.
 

plog

Banishment Pending
Local time
Today, 06:15
Joined
May 11, 2011
Messages
11,611
Your best bet is to make a grouping table or a custom function. Looks like the Grouping
Name is based upon the county and the name. So you would have those 2 fields in your new table along with the Grouping you want to assign:

GroupingTable
COUNTY, PHARM_NAME, PharmGrouping
DADE, WALG, WALGREENS SOUTH FL
BROWARD, WALG, WALGREENS SOUTH FL
...

Of course that's going to take one entry for every PHARM_NAME/COUNTY permutation you have. If you did it programmatically through a function, you are really going to have to think about your logic. In that last code snippet you posted, you are doing some unnecessary things for example:

...IIf(Left([PHARM_NAME],4)="WALG" And [COUNTY]<>"DADE","WALGREENS - OTHER",IIf(Left([PHARM_NAME],4)="WALG" And [COUNTY]<>"BROWARD","WALGREENS - OTHER"...

You will never hit that second test in the above code. If you do decide to do it through a function, I suggest you write a sentence in English for every piece of logic you have. Then read over every sentence, make sure it makes sense by itself and in its position with all the other criteria sentences. Then convert eahc sentence to code.
 

pastelrain

Registered User.
Local time
Today, 04:15
Joined
Jul 12, 2016
Messages
23
Thank you for your suggestions. I did initially try to reference a grouping table similar to what you described. For some reason when I added the logic of if the pharmacy name was like *pharm_name* in the grouping table, it wasn't giving me the correct results and creating duplicate rows with null values. I will keep trying other scenarios and hopefully get it one way or another.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:15
Joined
Feb 28, 2001
Messages
26,996
I don't know about any other pharmacy chains but all Walgreen's Pharmacy stores have a five-digit store number. The one I use is "02468" but I used a couple of their stores in Texas for a few months after Hurricane Katrina and that's how I found out about store numbers. The way I would treat this is to find the store number or whatever they use for store ID and include that as a separate field, then if you need uniqueness make a compound key of out chain name and store number.
 

Users who are viewing this thread

Top Bottom