Removing Expr (1 Viewer)

gblack · May 15, 2012

I am wondering if anyone out there has some nifty widget, or knows how to quickly remove the Alias that Access throws into a field when it doesn't understand the field name.

For Example. I open a query but the underlying table has been removed... now Access changes all the field names with an alias of: Expr1, Expr2, Expr3, etc...

It's a pain in the @$$ to go back and delete these... and if I have any functions or calcs, it's not as simple as deleting all the fields and pulling them back down from the table again...

I could go into the SQL and replace all the "as Expr" with no value... but then there's the numbers to deal with...

So does anyone know of a quick way to remove these? Maybe a custom string function, or some nifty replace method I can use?

I'll do it myself and post it, if no else has anything out there... but if so, I don't want to reinvent the wheel.

Thanks,
Gary

Alansidman · May 15, 2012

If you have deleted a table, why would you open a query that wass linked to that table. If you are replacing it with a new table, then give the new table the same name and then Access won't be driven to change the field names. As it stands, Access doesn't know what to do when you open the Query that was formerly linked to a table that no longer exists.

gblack · May 15, 2012

Yeah you kind of miss my point. The example was just a simple way to explain what is going on without telling you my life story about why I need this. Whatever the case is where MS Access auto-generates a field name alias as Expr1... That's my issue atm.

Basically I just want to remove "AS Expr1" "AS Expr2"... "AS Expr66" From the SQL Statement, because access added those in when it didn't understand the field names, but once I reset the underlying table/query back the values are fine...

unfortunately all field names now have an auto-generated alias starting with Expr... So I need to get rid of these. This kind of thing happens to me enough where I'd like to have a tool for it.

Short of painstakingly going in and deleting each instance, I'd like to have a quick function that I can take the entire SQL string and get rid of all the aforementioned alias for each and every field... the issue I see is the number applied to the end Expr1, Expr2... Expr107

I was kind of wishing one of the super geniuses on this forum might have a brilliant way of doing this... through a few simple string functions... so I could say WOW! And not have to waste my very short supply of pea brain power attempting it myself

Thanks,
Gary

gblack · May 15, 2012

OK Just did it.

I am sure someone can come up with a MUCH faster way by a clever use of teh string functions, but I am not so smart... so I did it the long way... Here's the long way:

First I opened Excel. Wrote: "AS Expr1" in a cell then dragged down to "AS Expr127" I figured I wouldn't have more than 127 fields

Then I made another column from 1 to 127 and used the filter/sort to sort in reverse order (so that "As Expr127" was at the top). Then I post a 1 in the thrid column and dragged it all the way down so as to have a PK field to sort by once I pulled it into access where 1 corresponds with "As Expr127" and 2 would correspond to "AS Expr126" etc...

You must reverse the order so that "AS Expr1" Doesn't go first and remove all the stuff that starts with "AS Expr1" Like "AS Expr10"..."AS Expr11".... Also you can order this using the field itself because it will order it alphabetically...

Then I pull this into access as a table... with the order, I stated, in tact.

Create a Function like this:

Code:

Public Function Remove_Expr_from_SQL(strQRY As String) As String
Dim Db As Database
Dim strSQL As String

    
Set Db = CurrentDb
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

strSQL = "Select * From RemoveExpr;"
With rs
    .ActiveConnection = CurrentProject.Connection
    .Open (strSQL)
    .MoveFirst
End With

Do While Not rs.EOF
    strExpr = rs!String_2B_Removed
    strQRY = Replace(strQRY, strExpr, "")
    rs.MoveNext
Loop

Remove_Expr_from_SQL = strQRY


End Function

I create a Form with a text box to paste your SQL String into and a button with this event:

Code:

Me.txtStrQry = Remove_Expr_From_SQL(Me.txtStrQRY)

It's clunky but it works... and when you have something like this to deal with:

SELECT [00150_BASIS_PLANNED_BUYS_ALL].BuyPlanID AS Expr1, [00150_BASIS_PLANNED_BUYS_ALL].TYCOM AS Expr2, [00150_BASIS_PLANNED_BUYS_ALL].Community AS Expr3, [00150_BASIS_PLANNED_BUYS_ALL].OBLOC AS Expr4, [00150_BASIS_PLANNED_BUYS_ALL].TOA AS Expr5, [00150_BASIS_PLANNED_BUYS_ALL].TOA_SECTION AS Expr6, [00150_BASIS_PLANNED_BUYS_ALL].FACILITY AS Expr7, [00150_BASIS_PLANNED_BUYS_ALL].ASSEMBLY_EC [00150_BASIS_PLANNED_BUYS_ALL].ASSEMBLY_EC_DESC, [00150_BASIS_PLANNED_BUYS_ALL].BuyType AS Expr10, [00150_BASIS_PLANNED_BUYS_ALL].FundingLine AS Expr11, [00150_BASIS_PLANNED_BUYS_ALL].Control_Type, [00150_BASIS_PLANNED_BUYS_ALL].FundCtrlID, [00150_BASIS_PLANNED_BUYS_ALL].[Exists_Control_$] AS Expr14, [00150_BASIS_PLANNED_BUYS_ALL].ILS_Factor AS Expr15, [00150_BASIS_PLANNED_BUYS_ALL].BuyListID AS Expr16, [00150_BASIS_PLANNED_BUYS_ALL].[AssyEC/NSN/Item] AS Expr17, [00150_BASIS_PLANNED_BUYS_ALL].ItemDescr AS Expr18, [00150_BASIS_PLANNED_BUYS_ALL].[UNIT_COST$] AS Expr19, [00150_BASIS_PLANNED_BUYS_ALL].AAO AS Expr20, [00150_BASIS_PLANNED_BUYS_ALL].[On-Hand(Prime)] AS Expr21, [00150_BASIS_PLANNED_BUYS_ALL].[On-Hand(Sub)] AS Expr22, [00150_BASIS_PLANNED_BUYS_ALL].OA_FY13 AS Expr23, [00150_BASIS_PLANNED_BUYS_ALL].OA_FY14 AS Expr24, [00150_BASIS_PLANNED_BUYS_ALL].OA_FY15 AS Expr25, [00150_BASIS_PLANNED_BUYS_ALL].OA_FY16 AS Expr26, [00150_BASIS_PLANNED_BUYS_ALL].OA_FY17 AS Expr27, [00150_BASIS_PLANNED_BUYS_ALL].OA_FY18 AS Expr28, [00150_BASIS_PLANNED_BUYS_ALL].FundedNotOnOrder AS Expr29, [00150_BASIS_PLANNED_BUYS_ALL].DueIn AS Expr30, [00150_BASIS_PLANNED_BUYS_ALL].Deferred AS Expr31, [00150_BASIS_PLANNED_BUYS_ALL].[IOShorts(TOAARS)] AS Expr32, [00150_BASIS_PLANNED_BUYS_ALL].FY13_Buys_Shorts AS Expr33, [00150_BASIS_PLANNED_BUYS_ALL].FY13_Buys_OA AS Expr34, [00150_BASIS_PLANNED_BUYS_ALL].FY14_Buys_Shorts AS Expr35, [00150_BASIS_PLANNED_BUYS_ALL].FY14_Buys_OA AS Expr36, [00150_BASIS_PLANNED_BUYS_ALL].FY15_Buys_Shorts AS Expr37, [00150_BASIS_PLANNED_BUYS_ALL].FY15_Buys_OA AS Expr38, [00150_BASIS_PLANNED_BUYS_ALL].FY16_Buys_Shorts AS Expr39, [00150_BASIS_PLANNED_BUYS_ALL].FY16_Buys_OA AS Expr40, [00150_BASIS_PLANNED_BUYS_ALL].FY17_Buys_Shorts AS Expr41, [00150_BASIS_PLANNED_BUYS_ALL].FY17_Buys_OA AS Expr42, [00150_BASIS_PLANNED_BUYS_ALL].FY18_Buys_Shorts AS Expr43, [00150_BASIS_PLANNED_BUYS_ALL].FY18_Buys_OA AS Expr44, [00150_BASIS_PLANNED_BUYS_ALL].Remarks AS Expr45, [00150_BASIS_PLANNED_BUYS_ALL].TOTAL_Buys_Shorts AS Expr46, [00150_BASIS_PLANNED_BUYS_ALL].TOTAL_Buys_OA AS Expr47, [00150_BASIS_PLANNED_BUYS_ALL].TOTAL_BUYS AS Expr48, ([FY13_Buys_Shorts]*[UNIT_COST$]*ToThePowerOf(GetSYSVar("INFLT"),1)) AS [FY13_Buy$_Shorts], ([FY13_Buys_OA]*[UNIT_COST$]*ToThePowerOf(GetSYSVar("INFLT"),1)) AS [FY13_Buy$_OA], CCur([ILS_Factor]*(nz([FY13_Buy$_Shorts],0)+nz([FY13_Buy$_OA],0))) AS [FY13_ILS_$], ([FY14_Buys_Shorts]*[UNIT_COST$]*ToThePowerOf(GetSYSVar("INFLT"),2)) AS [FY14_Buy$_Shorts], ([FY14_Buys_OA]*[UNIT_COST$]*ToThePowerOf(GetSYSVar("INFLT"),2)) AS [FY14_Buy$_OA], CCur([ILS_Factor]*(nz([FY14_Buy$_Shorts],0)+nz([FY14_Buy$_OA],0))) AS [FY14_ILS_$], ([FY15_Buys_Shorts]*[UNIT_COST$]*ToThePowerOf(GetSYSVar("INFLT"),3)) AS [FY15_Buy$_Shorts], ([FY15_Buys_OA]*[UNIT_COST$]*ToThePowerOf(GetSYSVar("INFLT"),3)) AS [FY15_Buy$_OA], CCur([ILS_Factor]*(nz([FY15_Buy$_Shorts],0)+nz([FY15_Buy$_OA],0))) AS [FY15_ILS_$], ([FY16_Buys_Shorts]*[UNIT_COST$]*ToThePowerOf(GetSYSVar("INFLT"),4)) AS [FY16_Buy$_Shorts], ([FY16_Buys_OA]*[UNIT_COST$]*ToThePowerOf(GetSYSVar("INFLT"),4)) AS [FY16_Buy$_OA], CCur([ILS_Factor]*(nz([FY16_Buy$_Shorts],0)+nz([FY16_Buy$_OA],0))) AS [FY16_ILS_$], ([FY17_Buys_Shorts]*[UNIT_COST$]*ToThePowerOf(GetSYSVar("INFLT"),5)) AS [FY17_Buy$_Shorts], ([FY17_Buys_OA]*[UNIT_COST$]*ToThePowerOf(GetSYSVar("INFLT"),5)) AS [FY17_Buy$_OA], CCur([ILS_Factor]*(nz([FY17_Buy$_Shorts],0)+nz([FY17_Buy$_OA],0))) AS [FY17_ILS_$], ([FY18_Buys_Shorts]*[UNIT_COST$]) AS [FY18_Buy$_Shorts], ([FY18_Buys_OA]*[UNIT_COST$]) AS [FY18_Buy$_OA], CCur([ILS_Factor]*(nz([FY18_Buy$_Shorts],0)+nz([FY18_Buy$_OA],0))) AS [FY18_ILS_$], [FY18_Buy$_Shorts]+[FY13_Buy$_Shorts]+[FY14_Buy$_Shorts]+[FY15_Buy$_Shorts]+[FY16_Buy$_Shorts]+[FY17_Buy$_Shorts] AS [Total_Buy$_Shorts], [FY18_Buy$_OA]+[FY13_Buy$_OA]+[FY14_Buy$_OA]+[FY15_Buy$_OA]+[FY16_Buy$_OA]+[FY17_Buy$_OA] AS [Total_Buy$_OA], [FY18_ILS_$]+[FY13_ILS_$]+[FY14_ILS_$]+[FY15_ILS_$]+[FY16_ILS_$]+[FY17_ILS_$] AS [Total_Buy$_ILS], [Total_Buy$_Shorts]+[Total_Buy$_OA]+[Total_Buy$_ILS] AS [TOTAL_BUY$]
FROM 00150_BASIS_PLANNED_BUYS_ALL;

You just don't want to delete these things manually... BTW: above example is not my SQL... so no comments on it please...

If anyone has a better way to complete this task... please post it.

Thanks,
Gary

Alansidman · May 15, 2012

I understand now. A simple way would be to cut and paste your SQL statement into Word and do a Find and Replace. You would have to do three iterations as you would need to put in the wildcard (Special Characters) three times. Once for 1-9, once for 10-99 and once for anything greater than 99. I took your SQL statement provided and it took approximately 20 seconds to get a new SQL statement to put back into Access.

gblack · May 16, 2012

OK so how do you get rid of the appropriate numbers but not the actual numbers such as in the snippet:

([FY18_Buys_Shorts]*[UNIT_COST$]) AS [FY18_Buy$_Shorts], ([FY18_Buys_OA]*[UNIT_COST$]) AS [FY18_Buy$_OA], CCur([ILS_Factor]*(nz([FY18_Buy$_Shorts],0)+nz([FY18_Buy$_OA],0))) AS [FY18_ILS_$], [FY18_Buy$_Shorts]+[FY13_Buy$_Shorts]+[FY14_Buy$_Shorts]+[FY15_Buy$_Shorts]+[FY16_Buy$_Shorts]+[FY17_Buy$_Shorts] AS [Total_Buy$_Shorts], [FY18_Buy$_OA]+[FY13_Buy$_OA]+[FY14_Buy$_OA]+[FY15_Buy$_OA]+[FY16_Buy$_OA]+[FY17_Buy$_OA] AS [Total_Buy$_OA], [FY18_ILS_$]+[FY13_ILS_$]+[FY14_ILS_$]+[FY15_ILS_$]+[FY16_ILS_$]+[FY17_ILS_$] AS [Total_Buy$_ILS], [Total_Buy$_Shorts]+[Total_Buy$_OA]+[Total_Buy$_ILS] AS [TOTAL_BUY$]

How do you NOT get rid of all the numbers above (such as FY13 through FY18) with a find and replace?

In any event I made the thing and it works fast. The form opens to a texbox that you can past the SQL string into... You click the button and it's done... I even set it so that once you paste the SQL String in and run it, Access dumps the cleaned up SQL string back onto your clipboard (if you want it to)... so it's much faster than three itterations of find and replace, in word, or notpad, or whatever.... Nevertheless I am still currious as to how you managed to only delete only the numbers at the end of Expr and not all the rest of the necessary numbers within the SQL string... that was kind of my main issue with find and replace.

spikepl · May 16, 2012

I use Word for this type of things many times. Word also allows to search for wildcard characters etc.

I record a macro, searching for the first instance of what I want to replace and moving the cursor to the next character (beyond what I operate on). Then I open the macro code, and wrap a loop around the code and then tie the macor to a keyboard key. Done. Normally it takes a few minutes to customize the loop.

JimK · Mar 22, 2019

I've seen this happen when tables are dropped and re-linked when a DB is split between a front-end and back-end. I solved for address the auto-aliases being assigned expr1...n by using the querydef to cycle through the query objects and adjusting the corresponding sql script which seems to be the only available solution.

Sub FixQueryFieldNameAliases()
ReplaceFieldNameAlias "expr"
End Sub

Function ReplaceFieldNameAlias(pstrFieldName As String)

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim sQry As String
Dim sSrcFld As String
Dim sFldName As String
Dim sSQL As String
Dim i As Integer

Set db = CurrentDb

For Each qdf In db.QueryDefs
sQry = Nz(qdf.Name, "")
sSQL = qdf.SQL
For i = 1 To qdf.Fields.Count - 1
sFldName = qdf.Fields(i).Name
sSrcFld = qdf.Fields(i).SourceField
If InStr(1, sFldName, pstrFieldName) > 0 Then
sSQL = Replace(sSQL, sFldName, sSrcFld, 1, , vbTextCompare)
qdf.SQL = sSQL
End If
Next i
DoEvents
Next qdf

On Error Resume Next
Set db = Nothing
Set qdf = Nothing

End Function

The_Doc_Man · Mar 22, 2019

There is always the chance that you could open the .SQL of the query and edit that with a little bit of parsing. For a short query, this is rarely useful because it would take longer to code up the parse than it would to just hand-edit.

HOWEVER, it should be possible to do a little bit of VBA to loop through an SQL string looking for the next occurrence of "AS Expr" and then look for the first comma following that string as the delimiter. Find the next comma or (for the last such field, the next space) and then take everything from the "AS" to just before the comma or space and replace it with nothing using the MID$ function on the left-hand side of the expression.

gblack · Mar 22, 2019

Honestly,
The function that Jimk provided looks great. My accdb file (attached above) works really well for me.

I simply copy the SQL open the db file... paste the SQL in and hit the button... Access throws the new SQL onto my clipboard and I paste it over the prior code...

It's extremely fast and I don't have to have the code, in the Db I'm using.

Removing Expr (1 Viewer)

gblack

Registered User.

Alansidman

AWF VIP

gblack

Registered User.

gblack

Registered User.

Alansidman

AWF VIP

gblack

Registered User.

Attachments

spikepl

Eledittingent Beliped

JimK

New member

The_Doc_Man

Immoderate Moderator

gblack

Registered User.

Similar threads

Users who are viewing this thread