Defining an external name field

[Gary_Mc_Mahon]

Hi,

I want to put a function together that will call a particular field in a query if an arguement is passed in.

Eg If I pass in the arguement "01" I want my function to pull out the field "JanRev" from the query "qry_Key_Rev".

Can ayone help me with it. I'm having real trouble. I've tried many approaches but to no avail.

Here's my latest effort.

Function KeyRev(Period As String) As Field

Dim Aug03Rev As Field
Jan03Rev = [qry_Key_Rev].[JanRev]

If Period = "01" Then
KeyRev = Jan03Rev
Else
End If


End Function

This function will only be used in the query qry_Key_Rev which is a select query.

Gary

 

[Gary_Mc_Mahon]

**TYPE ERROR IN LAST POST!!


Hi,

I want to put a function together that will call a particular field in a query if an arguement is passed in.

Eg If I pass in the arguement "01" I want my function to pull out the field "JanRev" from the query "qry_Key_Rev".

Can ayone help me with it. I'm having real trouble. I've tried many approaches but to no avail.

Here's my latest effort.

Function KeyRev(Period As String) As Field

Dim Jan03Rev As Field
Jan03Rev = [qry_Key_Rev].[JanRev]

If Period = "01" Then
KeyRev = Jan03Rev
Else
End If


End Function

This function will only be used in the query qry_Key_Rev which is a select query.

Gary

 

[mdmilner]

You're heading in the right direction, but, while I honestly do not know if what you ask is possible or not (feels like it might be) there is another easier way to manage this.

Why not build the entire query from code?
You can build it then assign it as a querydef thus creating it 'permanently' and available for things like use by reports or other queries.

Method works like this:

Function KeyRev(Period As String)
Dim dbs as DAO.Database
Dim qdf as QueryDef
Dim strSQL, strQRY as String

Set dbs = CurrentDB()
strQRY = nameofquery  'set this

Select Case Period
  Case "01":
    strSQL = ", fldname2"
  Case Else
End Select

strSQL = "SELECT fldname1" & strSQL & " FROM tblName"
If ObjectExists("Query", strQRY) Then 
  DoCmd.DeleteObject acQuery, strQRY
End if
Set qdf = dbs.CreateQueryDef(strQRY, strSQL) 

Set dbs = Nothing
End Function

Function ObjectExists(strObjectType As String, strObjectName As String) As Boolean

     Dim db As Database
     Dim tbl As TableDef
     Dim qry As QueryDef
     Dim i As Integer
     
     Set db = CurrentDb()
     ObjectExists = False
     
     If strObjectType = "Table" Then
          For Each tbl In db.TableDefs
               If tbl.Name = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next tbl
     ElseIf strObjectType = "Query" Then
          For Each qry In db.QueryDefs
               If qry.Name = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next qry
     ElseIf strObjectType = "Form" Or strObjectType = "Report" Or strObjectType = "Module" Then
          For i = 0 To db.Containers(strObjectType & "s").Documents.Count - 1
               If db.Containers(strObjectType & "s").Documents(i).Name = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next i
     ElseIf strObjectType = "Macro" Then
          For i = 0 To db.Containers("Scripts").Documents.Count - 1
               If db.Containers("Scripts").Documents(i).Name = strObjectName Then
                    ObjectExists = True
                    Exit Function
               End If
          Next i
     Else
          MsgBox "Invalid Object Type passed, must be Table, Query, Form, Report, Macro, or Module"
     End If
     
End Function

I've added a borrowed universal function that checks to see if the query exists in the database and deletes it first -- otherwise it just assigns the query concatenated together into one and it'll show up under your query tab in Access.

You can create any type of query in this fashion.
If you're going to do any action queries, it's better to NOT create the qdf, but instead to execute them directly from VBA.

DoCmd.RunSQL strSQL

Mike

 

[mdmilner]

While I'm talking here - I'll add.
I threw your prompt value into a Select Case only because I wanted to show you that you can allow for a variety of variables to be passed in. Adding a Case "02" would allow you to specify a completely different field.

What's even slicker is to use a portion of the incoming value (the "01") as a portion of your fieldname. That way, should you have 10 fields called something like: part01, part02, part03 -- you could just use the word 'part' and add the number you're passing in to it.

I'm hoping this at least pushes you in a direction that works.

 

[Gary_Mc_Mahon]

Thank you very much for the help Mike, you've given me loads to think about. I'll have a good crack at it today and see how I get on.......

Gary