how to Delete query fields (1 Viewer)

brp

Registered User
Joined
Mar 26, 2012
Messages
13
I have created a query programmatically with a field names like; "Name", "Address", "City", "Field3", "Field4", "Field5". The field names are programmatically changed. At every time, all the fields name "Field" have no data. Then the fields, name like "Field" should be deleted and query should export to a excel worksheet. The excel worksheet should display the column and the data except the column name like "Field"(with no data).


I search many site throughout the day and couldn't find how to delete query fields relevant to above. I could able to create vba code to export the query to excel format.
 

vba_php

Custom Title Here
Joined
Oct 6, 2019
Messages
2,081
are you asking for a way in VBA to DROP query fields? those statements are not heavily used in access, but none the less I think are still available. those are statements used more in systems like Oracle and MYSQL. but if that's what you want, I think you can do it like this:
Code:
ALTER TABLE tableName DROP fieldName
I'm not sure if you can do it for a querydef object, but surely you can for a tabledef object.
 

brp

Registered User
Joined
Mar 26, 2012
Messages
13
Thanks for your quick reply.


That's not work for me. It's only for the specific field name(s). Really I want to define field name like "Field" instead of Field1, Field2, Field3 like that.
 

Gasman

Enthusiastic Amateur
Joined
Sep 21, 2011
Messages
5,017
I would have thought that it might be easier not to create the fields in the first place?
 

vba_php

Custom Title Here
Joined
Oct 6, 2019
Messages
2,081
That's not work for me. It's only for the specific field name(s). Really I want to define field name like "Field" instead of Field1, Field2, Field3 like that.
I'm not sure I'm following you my friend. do you want to RENAME the query fields to other names? if that's what you want, access might have that statement too, but you'll have to test it. for instance, in MYSQL you would do this:
Code:
ALTER TABLE tableName RENAME COLUMN OldFieldName TO NewFieldName
note also that I believe the word "field" is a reserved word in access, and if it is, you should never used it to name any object, method, property, attribute, type, enum, etc, etc... when you're developing.
I would have thought that it might be easier not to create the fields in the first place?
obviously that would be a better way, however the question is still being asked....
 

The_Doc_Man

Happy Retired Curmudgeon
Joined
Feb 28, 2001
Messages
15,699
There IS a way but you have to like (or at least be familiar with) text parsing. It is possible (when considering a query) to edit the SQL to include or exclude whatever you want, including the possibility of applying aliases to field names through the AS keyword.

In the following link, the third example shows you how to replace the SQL in a saved query by supplying a different SQL string. The example changes a WHERE clause but there is no reason that you could not replace the SELECT clause instead.

https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/querydef-object-dao
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,846
Hi brp. Pardon me for jumping in, but how many fields are we talking about, max? If not too many, you might be able to do something like this:

Code:
Replace(Replace(Replace(strSQL,",Field3",""),",Field4",""),",Field5","")
Hope that helps...
 

brp

Registered User
Joined
Mar 26, 2012
Messages
13
Thanks for all your ideas. I'll explain briefly what I did so far. I wanted to create a search form and needed to take excel output with filtered results and selected fields. Then,
First I created a search form like Allen Browne's with about 25 search criteria. As the high number of search criteria, it is needed to select limited required field to satisfy to take a print in single sheet. Therefore I added a Roger Carlson's "ChooseReportsFiedls.mdb" to select the fields that what I want. In this db, fields are selected only for the report and define the fields as "Field0", "Field1" like that. So I could able to create vb code to select the required field with filtered results. But rest of non selected fields remained "Field3", "Field4" so on. This is problem I encountered so for. Any help would be greatly appreciated.
 

Gasman

Enthusiastic Amateur
Joined
Sep 21, 2011
Messages
5,017
But that code only selects fields that have been selected in the listbox?
 

brp

Registered User
Joined
Mar 26, 2012
Messages
13
UG added Code Tags

Code:
Private Sub Form_Current()


    Dim dbs As Database, qdf As QueryDef
    Dim fld As Field, rst As Recordset
    Dim tbl As String

   
        ' Return reference to current database.
    Set dbs = CurrentDb
        ' Return reference to Employees table.
    Set qdf = dbs.QueryDefs("qryEquipment")
   
    
    DoCmd.SetWarnings False
    DoCmd.RunSQL ("Delete * from TableFields")
    DoCmd.SetWarnings True
    Set rst = dbs.OpenRecordset("TableFields", dbOpenDynaset)
    ' Enumerate all fields in Fields collection of TableDef object.
    For Each fld In qdf.Fields
        If fld.Type >= 1 And fld.Type <= 8 Or fld.Type = 10 Then
            rst.AddNew
            rst!FieldName = fld.Name
            rst!FieldType = fld.Type
            rst.Update
        End If
    Next fld
    Set dbs = Nothing
   
   lstLocalAuthority.Requery
    'reset

Exit_cboQuery_AfterUpdate:
    Exit Sub

Err_cboQuery_AfterUpdate:
    MsgBox Err.Description
    Resume Exit_cboQuery_AfterUpdate

 End Sub



Private Sub cmdRunReport_Click()

On Error GoTo Err_cmdRunReport_Click
    
    Dim MyDB As Database
    Dim qdf As DAO.QueryDef
    Dim rst As Recordset, rst2 As Recordset
    Dim i As Integer, j As Integer, k As Integer, strSQL As String
    Dim strFieldList As String, strIN As String
    Dim flgAll As Boolean
  

   '----------------------
Dim strWhere As String
Dim strFile As String
Const strcStub = "SELECT qryEquipment.* FROM qryEquipment" & vbCrLf
Const strcTail = "ORDER BY SomeField;"
Const strcExportQuery = "qryLocalAuthority"



If Me.FilterOn Then
strWhere = "WHERE " & Me.Filter & vbCrLf
End If

    Set MyDB = CurrentDb()
    Set rst = MyDB.OpenRecordset("tablefields")
    
    strSQL = "SELECT "
    
    j = 0
    k = 0
    rst.MoveFirst
    'create the IN string by looping thru the listbox
    For i = 0 To lstLocalAuthority.ListCount - 1
        If lstLocalAuthority.Selected(i) Then
            strIN = strIN & "[" & lstLocalAuthority.Column(0, i) & "] as Field" & k & ","
            rst.Edit
            rst!indexx = k
            rst.Update
            rst.MoveNext
            k = k + 1
        Else
            rst.Edit
            rst!indexx = Null
            rst.Update
            rst.MoveNext
        End If
         j = j + 1
     Next i
     For i = k To lstLocalAuthority.ListCount - 1
            strIN = strIN & "null as Field" & i & ","
     Next i
    ' stripoff the last comma of the IN string
    strFieldList = Left(strIN, Len(strIN) - 1)
   
    
    
    
    strSQL = strSQL & strFieldList & " FROM qryEquipment "
    'MsgBox strSQL
  
    
   MyDB.QueryDefs.Delete "qryLocalAuthority"
    Set qdf = MyDB.CreateQueryDef("qryLocalAuthority", strSQL & strWhere)
    
    strFile = "D:\DB\REPORT\MyExports.xls"
    
DoCmd.OutputTo acOutputQuery, "qryLocalAuthority", acFormatXLS, strFile
 
Last edited by a moderator:

vba_php

Custom Title Here
Joined
Oct 6, 2019
Messages
2,081
brp,

that's WAY too much for anyone here to digest, and I doubt anyone has time to read through it. but here's a thought for you....this portion:
Code:
' Return reference to current database.
    Set dbs = CurrentDb
        ' Return reference to Employees table.
    Set qdf = dbs.QueryDefs("qryEquipment")
   
    DoCmd.SetWarnings False
    DoCmd.RunSQL ("Delete * from TableFields")
    DoCmd.SetWarnings True
    Set rst = dbs.OpenRecordset("TableFields", dbOpenDynaset)
    ' Enumerate all fields in Fields collection of TableDef object.
    For Each fld In qdf.Fields
        If fld.Type >= 1 And fld.Type <= 8 Or fld.Type = 10 Then
            rst.AddNew
            rst!FieldName = fld.Name
            rst!FieldType = fld.Type
            rst.Update
        End If
    Next fld
    Set dbs = Nothing
is doing something that is totally unrelated to what you originally asked about doing, or at least that's what it sounded like. You are *not* dropping actual fields or renaming them. what you *are* doing is storing the actual field *names* and field *types* in table records. the first thing that comes to my mind is: what in the world for!? Pardon me, but it really seems like you have something here that is extremely complex in nature, and you could make things easier on yourself by re-thinking the set up you have and what the goal here is. you said:
Therefore I added a Roger Carlson's "ChooseReportsFiedls.mdb" to select the fields that what I want. In this db, fields are selected only for the report and define the fields as "Field0", "Field1" like that.
when I read the words "to select the fields that what I want. fields are selected only for the report", it makes me think of many recent threads where people were trying to do the same thing. and there *is* a much easier way to do it using the front-end interface GUI of access. are you just trying to open a simple report and put fields on it that are specified by the user? if that's what you want, there surely is a much easier way....
 

brp

Registered User
Joined
Mar 26, 2012
Messages
13
Thanks for your reply. I'll try to rethink of it. If you could please send me the correct vb code or sample for the above requirement.
 

theDBguy

I’m here to help
Joined
Oct 29, 2018
Messages
7,846
Thanks for your reply. I'll try to rethink of it. If you could please send me the correct vb code or sample for the above requirement.
Hi. Are you able to post a demo version of your db?
 

vba_php

Custom Title Here
Joined
Oct 6, 2019
Messages
2,081
Thanks for your reply. I'll try to rethink of it. If you could please send me the correct vb code or sample for the above requirement.
we'll I'm not sure where to look brp. for the life of me I can't find any thread for which I claimed to have seen the process that you want to implement! but just out of curiosity, is this current requirement of yours in this thread related to your previous issue?

https://www.access-programmers.co.uk/forums/showthread.php?t=308730
Hi. Are you able to post a demo version of your db?
I totally agree with that one!
 

sxschech

Registered User
Joined
Mar 2, 2010
Messages
636
Addressing the specific aspect of the question

how to Delete query fields with no data
Here is a function that will create a string that can be incorporated into an SQL statement to exclude fields that have no data. There is an optional Alias which will add a prefix to the included field names. For example if you wanted your fields to begin with EQP the field would show as EQP_Address, EQP_City.

Put in a standard module.
Code:
Function ColHasData(Table_OR_Query As String, Optional addALIAS As String, Optional Exclude1 As String)
'Create CSV string only of cols in the table
'that have data, so that export excludes
'empty fields
'20150813
'modified to handle spaces by surrounding
'with brackets to be used for non universal
'tables
'20160122
'Modified to work with either queries or
'tables.  Added optional ALIAS so that
'if exporting to excel can identify fields
'from their respective table/query
'20160225
'Added Exclude one field option
'20160328
    Dim db As DAO.Database
    Dim qt As Variant
    'Dim qd As DAO.QueryDef
    'Dim td As DAO.TableDef
    Dim fld As DAO.Field
    Dim stFields As String
    Dim stFieldName As String
    Dim ColCount As Long
    Dim objType As Integer
    
    Set db = CurrentDb
    
    objType = DLookup("Type", "MSysObjects", "NAME = '" & Table_OR_Query & "'")
    If objType = 5 Then
        Set qt = db.QueryDefs(Table_OR_Query)
    Else
        Set qt = db.TableDefs(Table_OR_Query)
    End If
    
    For Each fld In qt.Fields
        ColCount = DCount("[" & fld.Name & "]", Table_OR_Query)
        If ColCount > 0 Then
            If fld.Name <> Exclude1 Then
                If InStr(fld.Name, " ") Then
                    stFieldName = "[" & fld.Name & "]"
                Else
                    stFieldName = fld.Name
                End If
                If Len(addALIAS) > 0 Then
                    stFields = stFields & ", " & Table_OR_Query & "." & stFieldName & " AS " & addALIAS & "_" & stFieldName
                Else
                    stFields = stFields & ", " & Table_OR_Query & "." & stFieldName
                End If
            End If
        End If
    Next fld
    ColHasData = Mid(stFields, 3)
End Function
For your calling code.
In its simplest form, to use:
Code:
Dim stsql As String
Dim sthasdata As String

sthasdata = ColHasData("YourTableORQuery")

stsql = "SELECT " & sthasdata & " " & _
        "FROM SameOrDifferentQueryTable;"
Set qd = EditQryDef("QueryNameToSave")
qd.SQL = stsql
You can use a variable as well such as pulling the query or table name from a combobox. The field names can be pulled from one table/query and used in building an sql string that gets data from another tablequery. You can save the sql string as a saved query (that can be used to export to excel) or use the sql string as is.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom