Vba to query with variable field names Results

First, I'm a bit of an Access newb and a first-time poster here so, bear with me, please...

I have a simple query that I need to run based on two tables:

Fields are: Directory Number, Zip Code, Inclusion
(Each directory number has several zip codes in it, with the Inclusion being the percentage of the zip that is included in the directory)

Fields are: Zip Code, and over 100 different demographics, e.g. population, number of Households, etc.

What I am trying to do is roll the demographic data up to the directory level. So, each demographic field from the ZipDemographic table needs to be multiplied by the inclusion rate of that Zip Code in the directory.

But, rather than having to type out the expression in each field in the query (which would take me hours), I'm thinking that I can probably run some code to run the query for me since I'm making the exact same calculation to each field in the query.

I'm not sure if you need more info or not... Like I said before I'm a bit of a newb, so try to be explicit, if possible. Thanks in advance for any and all help!


Firstly I've put this in here as it seems to cover most access areas and I'm sure it will involve VBA.

For some of the projects in my database (the once with a certain customer) we get back a rating on our performance. By boss would like us to be able to anaylse that feedback against multiple different criteria such as Year, Business Sector, Site etc

My vision on how this would work is that there would be a form on which you could select as may criteria as you wanted (out of a shortlist of sensible ones) to compare the data against and then click a button and a report would be generated showing all the anaysis grouped by different criteria.

My tables are set up as shown below. (the underscores are just spaces for the text)

Site ____________Res1
.. ______________Res4
... _____________Res5

All of the criteria are defined as text all of the results are numeric.

I am thinking that in order to produce this output i need a table/query with the following fields, where group relates on one of the comparison criteria (Site, Year etc), SubGroup is the possible options for that group (2008, 2007, 2009) etc and ProjectID is needed only to lookup futher project inforation as and when necessary:


My original thought was to use an append query with variable filed names to do this, however I found this link while searching which suggests creating an array in VBA and then using that to create the field.


So my questions are:

Does this sound like a sensible method to produce this output? I'm still fairly new to Access so this will tyake me a while to do and I'd rather not spend too long if I'm going down totally the wrong route.

If so, Is the option of producing an array containign all the data and then loading this into a table a better method than using append queries to produce this output?

If so, How do I fill a table from an array? I''m sure this is fairly simple and could find out but hey, worth asking?

Does anyone have any better idea?

Hope that all makes sense


PS, not sure if it makes any difference but I will want charts in my report

I have searched extensively for a solution but have not come across anything that explicitly refers to my issue and so I've not been able to resolve thus far.

I have a query that is filtered through a selection on a combo box and the result populates a report. This works perfectly.

I have a button that exports the resulting report to PDF format and saves it to a SharePoint document library. This also works perfectly.

My challenge is to:Set the PDF document name to the value contained in a field called "DocSetName" in my query and include the prefix "CRF_". The value of "DocSetName" is unique for each record. Set the save location to the correct folder in my document library, the name of which is also determined by the value of the "DocSetName" field in my query. So the saved result should look like this:

portalsitedoclibraryvariablefoldernameCRF_va riabledocname.pdf

where: variablefoldername = value of "DocSetName" and variabledocname = "CRF_" + value of "DocSetName".

I'm using Access 2010 and VBA.

Any ideas?

I would really appreciate it if anyone could help me with the below task.

I have some experience in VBA but very little with Access. The challenge I face right now is to use an index of names (in excel field) to repeatedly create reports and queries with each of these names. The reports and quires are in a set frame work so I thought this work can be completed with a loop modifying the SQL codes.

The process goes like this:
1. Create a report using person1 as the report name, modify field1 in this report.
2. Create a drop_query_person1 that drops table_person1 using person1's name.
3. Create a make_query_person1 that makes table_person1 using person1's name.
4. Execute make_query_person1 to make table_person1
5. Create append_query_person1 that would fill table_person1 from some other data source.
6. Execute append_query_person1 to fill table_person1 from an existing table called table_source.
7. Create query_person1 that sorts table_person1
8. And repeat the above steps using person2's name and on until the end of index.

Is it possible to use VBA to edit report?
What goes on the header in the VBA codes? (revise) what things do I need to declare in the header of the codes? Honestly I've forgotten a lot of things in VBA already..

If I want to dim a string that stores person1....100's name, how would I put it in the contest in terms of SQL code? For instance if I dim prs as a string, how would I use this variable, prs, to replace JohnDoe in the code below? (how does quotation work here?)

INSERT INTO [TBL_JohnDoe] ( JohnDoe_field, Personal_Info, Gender, Age)

Some sample code would really be helpful.
I know this sounds very noobie, but I'd really appreciate any inputs/help here.

Thanks a lot!!

Field names in my table A is built by a character followed by a number,
e.g. A1, where 'A' signies the variable and '1' an ID of a person.

My second table B have the same field name structure, like B1, B3 etc. This table is constructed as a result of a complex Pivot query, and I have not control of the fields (persons) it ends up with.

I would like to restructure table B in the following way:
1. If table B contains fields of persons that is not in table A, delete that field.
2. If table B misses some of the fields in table A, add that field.

The order of fields in B should be the same as in A.

Table A:

Key A1 A2
1 11 12
2 21 22

Table B:

Key B1 B3
1 .11 .12
2 .21 .22

So: Delete B3 since person 3 is not in A, and add B2 since person 2 is present in A but not in B. If successful, I shold end up with a table B_adjusted like

Key B1 B2
1 .11 null
2 .21 null

In reality the number of tables and fields are large, and I would like to program this in vba code. Tables will be exported to a matrix oriented language like Matlab for statistical analysis, and it is convenient to have tables with equal matrix dimensions, so that matrix index [i,j] refer to the same person and record, thus the need for introducing null fields.

The main problem is thus to compare the integer part of the string names, and based on the result, execute delete or add fields. I could rename field names by removing the character part if that makes the problem easier. Any hints are welcome.

Best regards thh

Version: Access 2002 SP3

I am attempting to employ the oft-recommended MS solution for creating a dynamic crosstab report. The solution involves an unbound parameter collector form, a crosstab query, and report based on that query which is full of unbound textboxes. If the code was working properly, the report’s textboxes would become populated with data from the parameterized crosstab upon opening.

The form and the query are working together beautifully. Both of the controls on my form are unbound comboboxes that display values from a lookup table. The parameters on my crosstab, which come from the form, are defined as integers. I don’t have any problems when I run the query while the form is open. But, when I try to open the report, I get this error message:

“The Microsoft Access database engine does not recognize “ as a valid field name or expression.”

Now, this isn’t the first time that I’ve danced with this lovely little error message. I believe I encountered it when I was first building the crosstab query. Thanks to advice found in this forum, I finally realized that unlike other types of queries, it is necessary to explicitly define the data type of any parameter used in a crosstab. Once I did that, the problem went away. This leads me to believe that the problem could lie with this part of my code:

	Private Sub Report_Open(Cancel As Integer)

   '  Create underlying recordset for report using criteria entered in
   '  parcolPrintAttendanceReport form.    
   Dim intX As Integer
   Dim qdf As QueryDef
   Dim frm As Form

   '  Set database variable to current database.
   Set dbsReport = CurrentDb
   Set frm = Forms!parcolPrintAttendanceReport

   '  Open QueryDef object.
   Set qdf = dbsReport.QueryDefs("AttendanceCrosstab")

   ' ***PROBABLE CULPRIT!***   
   ' Set parameters for query based on values entered
   ' in EmployeeSalesDialogBox form.  
   qdf.Parameters("Forms!parcolPrintAttendanceReport!Combo2") _
     = frm!Combo2
   qdf.Parameters("Forms!parcolPrintAttendanceReport!Combo4") _
     = frm!Combo4

   '  Open Recordset object.
   Set rstReport = qdf.OpenRecordset()

   '  Set a variable to hold number of columns in crosstab query.
   intColumnCount = rstReport.Fields.Count

 End Sub

The parameters in my crosstab are still defined. I have gone to great pains to make sure that there are no misspellings anywhere, and I’ve played around with the syntax in the code. Again, the problem seems to be with the code, since the form and the query work just fine. Any ideas?

I'm attempting to create a form with multiple combo boxes that allows a user to pick a field from a table (I'm wanting to limit the fields shown in the combo box, so I created a value list because I wasn't sure how to limit the options to just certain fields on a table or query). The user can then choose to AND or OR that field with another field. The fields that the filter looks at are formatted as Yes/No Boxes.

I've created a form that filters with just one combo box that picks out the field to filter using multiple ElseIf statements. Here is an example of my code:

If Not IsNull(Me.cboFilterProjType) Then
strCriteria = Me.cboFilterProjType
If strCriteria = "Field1" Then
strWhere = strWhere & "([Field1] = True) AND "
ElseIf strCriteria = "Field2" Then
strWhere = strWhere & "([Field2] = True) AND "
End If
End If

I came to the realization that I don't want to write ElseIf statements for every possible permutation of ANDing/ORing four different fields. Any suggestions on how I can call on the Access field in VBA when the Field name is variable and/or how to make the combo box a field list that's limited to only fields I specify (I am currently using a Value List)?

This problem involves reports, queries, and vba, so hopefully someone can help me with all three.

I have three reports that I run through a macro which saves all three to a specified place on my network. These all previously queried a table and were conditioned on a field which was binary. The reports previously only picked out those records which had a 1. I'm changing this field to an integer type field (for now it'll just have 0, 1, and 2, but eventually more). Now, I'm specifying in my query whether to run it for 1 or 2. My problem is that I need to somehow have the file name of the report show whether I ran it for a 1 or a 2 (so previously the report would simply be named 'MyReport' if I ran it, but now, depending on what parameter I specify, it'll now be named 'MyReport1' or 'MyReport2'). How do I pass the parameter in my query through to the macro so I can append it to the filename? Is it possible that, when I run the macro, I can somehow pass that through to the query instead? Thanks in advance.

Hi, I am using VBA to create a report and then write my data from my recordsource from. Does anyone know how I would write a line in the Page Header section? I'm using MS Access 2010. here is my code for my Page Header and I want to put a line at the bottom of the Page Header:

Private Sub RunReport_Click()
Dim db As Database ' database object
Dim rs As Recordset ' recordset object
Dim sSQL As String
Dim fld As Field ' recordset field
Dim txtNew As Access.TextBox ' textbox control
Dim lblNew As Access.Label ' label control
Dim lblSub As Access.Label ' label control
Dim rpt As Report ' hold report object
Dim lngTop As Long ' holds top value of control position
Dim lngLeft As Long ' holds left value of controls position
Dim title As String 'holds title of report
Dim lngBlack As Long
Dim rptData As String

lngBlack = RGB(0, 0, 0)

'set the title
title = "Activity Summary Report"

' initialise position variables
lngLeft = 0
lngTop = 0

'Create the report
Set rpt = CreateReport

' set properties of the Report
With rpt
.Width = 8500
.RecordSource = sSQL
.Caption = title
End With

sSQL = "SELECT Submission_Date, Nbr_Files_In_Set, Motion_filesize, Motion_DatePeriodFrom, Motion_DatePeriodTo " _
& "From dbo_Motion_Imagery;"

' Open SQL query as a recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(sSQL, dbOpenDynaset, dbSeeChanges)

rptData = "Motion Imagery"

' Create Report Header Title
Set lblNew = CreateReportControl(rpt.Name, acLabel, _
acPageHeader, , "Activity Summary Report", 0, 0)
With lblNew
.FontBold = True
.FontSize = 14
.FontName = "Arial"
.ForeColor = ingBlack
.FontUnderline = Yes
End With

' Create Report Header Sub Title
Set lblSub = CreateReportControl(rpt.Name, acLabel, _
acPageHeader, , rptData, 0, 600)
With lblSub
.FontBold = True
.FontSize = 12
.FontName = "Arial"
.ForeColor = ingBlack
.FontUnderline = Yes
End With

' Create Report Column Header Title
Set lblSub = CreateReportControl(rpt.Name, acLabel, _
acPageHeader, , "Submission", 200, 1000)
With lblSub
.FontBold = True
.FontSize = 12
.FontName = "Arial"
.ForeColor = ingBlack
.FontUnderline = Yes
End With

' Create Report Column Header Title
Set lblSub = CreateReportControl(rpt.Name, acLabel, _
acPageHeader, , "Date", 600, 1300)
With lblSub
.FontBold = True
.FontSize = 12
.FontName = "Arial"
.ForeColor = ingBlack
.FontUnderline = Yes
End With

' Create Report Column Header Title
Set lblSub = CreateReportControl(rpt.Name, acLabel, _
acPageHeader, , "Classification", 1700, 1000)
With lblSub
.FontBold = True
.FontSize = 12
.FontName = "Arial"
.ForeColor = ingBlack
.FontUnderline = Yes
End With

' Create Report Column Header Title
Set lblSub = CreateReportControl(rpt.Name, acLabel, _
acPageHeader, , "Nbr of Files", 3400, 1000)
With lblSub
.FontBold = True
.FontSize = 12
.FontName = "Arial"
.ForeColor = ingBlack
.FontUnderline = Yes
End With

' Create Report Column Header Title
Set lblSub = CreateReportControl(rpt.Name, acLabel, _
acPageHeader, , "Filesize", 4900, 1000)
With lblSub
.FontBold = True
.FontSize = 12
.FontName = "Arial"
.ForeColor = ingBlack
.FontUnderline = Yes
End With

' Create Report Column Header Title
Set lblSub = CreateReportControl(rpt.Name, acLabel, _
acPageHeader, , "Date Period", 5950, 1000)
With lblSub
.FontBold = True
.FontSize = 12
.FontName = "Arial"
.ForeColor = ingBlack
.FontUnderline = Yes
End With

' Create Report Column Header Title
Set lblSub = CreateReportControl(rpt.Name, acLabel, _
acPageHeader, , "Date Period", 7500, 1000)
With lblSub
.FontBold = True
.FontSize = 12
.FontName = "Arial"
.ForeColor = ingBlack
.FontUnderline = Yes
End With

' Create Report Column Header Title
Set lblSub = CreateReportControl(rpt.Name, acLabel, _
acPageHeader, , "From", 6300, 1300)
With lblSub
.FontBold = True
.FontSize = 12
.FontName = "Arial"
.ForeColor = ingBlack
.FontUnderline = Yes
End With

' Create Report Column Header Title
Set lblSub = CreateReportControl(rpt.Name, acLabel, _
acPageHeader, , "To", 8000, 1300)
With lblSub
.FontBold = True
.FontSize = 12
.FontName = "Arial"
.ForeColor = ingBlack
.FontUnderline = Yes
End With

' Create Report Column Header Title
'Set lblSub = CreateReportControl(rpt.Name, acLabel, _
'acPageHeader, , "-", 0, 1500, 1500)
'With lblSub
' .BorderStyle = Solid
' .BorderWidth = 1
' .BorderColor = ingBlack
'End With

' Create corresponding label and text box controls for each field.
For Each fld In rs.Fields

'Create new text box control and size to fit data.
Set txtNew = CreateReportControl(rpt.Name, acTextBox, _
acDetail, , fld.Name, lngLeft + 1500, lngTop)

' Create new label control and size to fit data.
Set lblNew = CreateReportControl(rpt.Name, acLabel, acDetail, _
txtNew.Name, fld.Name, lngLeft, lngTop, 1500, txtNew.Height)

' Increment top value for next control
lngTop = lngTop + txtNew.Height + 25

' Create datestamp in Footer
Set lblNew = CreateReportControl(rpt.Name, acLabel, _
acPageFooter, , Now(), 0, 0)

' Create page numbering on footer
Set txtNew = CreateReportControl(rpt.Name, acTextBox, _
acPageFooter, , "='Page ' & [Page] & ' of ' & [Pages]", rpt.Width - 1000, 0)

' Open new report.
DoCmd.OpenReport rpt.Name, acViewPreview

' Cleanup all objects - close and exit form/Report
'resume next on errors
On Error Resume Next

Set rs = Nothing
Set rpt = Nothing
Exit Sub

'Error Handler Routine
Select Case Err
Case Else
MsgBox Err & ": " & Err.Description
End Select

GoTo Cleanup

End Sub

i have run into yet another problem that im sure you guys can help me with. i have a form composed of combo-boxes and a few checkboxes. the table that its linked to has the "yes/no" fields, which the checkboxes are linked to. i could not get it to work with checkboxes so i changed them to comboboxes with yes/no - value list. what the code does is checks to see if the combobox is empty - if it is then it puts a "Like *" into the criteria, if it has a Yes, i simply assign a "Yes" to the criteria, no...etc. Its not the most efficient way but i cant get it to work!

i have a feeling that i should be using boolean variables since the table is set using yes/no field, but im not quite sure. ive tried using boolean but it sitll doesnt work. my sql errors out saying "Error Number: 3075....Syntax error (missing operator).

heres a snippet of my code (with combo-boxes):

	Dim strxxxEnabled as String
If IsNull(Me.cboxxxEnabled.Value) Then
        strxxxEnabled = " Like '*' "
ElseIf Me.cboxxxEnabled.Value = "Yes" Then
        strxxxEnabled = " Yes "
ElseIf Me.cboxxxEnabled.Value = "No" Then
        strxxxEnabled = " No "
End If

strSQL = "SELECT Systems.* " & _
             "FROM Systems " & _
             "WHERE Systems.Name" & strName & _
             "AND Systems.xxxEnabled" & strxxxEnabled & _
             "AND Systems.Building" & strBuilding & ";"

i have many comboboxes using the same code (for the if/then check) and it works just fine, however it will not work with these yes/no fields. please tell me im just stupid and missing something easy. ive been trying to figure this out since friday!

quoting and posting here

Quote: I have this code that stops the user from manually deleting the table objects. that it needs vba to delete the table object. Knowing that, I thought that maybe there is also this code that will stop manual open of database tables.

I have attached in here the code that stop manual delete of table. maybe someone know how to modify or know how to stop manual open of table.


I have seen a number of post where members have asked how to regain a table after they have accidentally deleted it. I am sure each of us in time past might have deleted a table accidentally ourselves to..... well i know at least I have.

So here is a solution that will stop an access table from being deleted manually by pressing the delete button on the keyboard by accident or otherwise (sabotage). The only way to delete the table is by using DDL with VBA as example 1 shows or using DDL only as a query object.


currentproject.connection.execute "DROP TABLE YourtableName"

This code only works if you have an autonumber field in your table and the autonumber field does not necessarily have to be a primary key as well. For those of us who use the Surrogate Key approach (Autonumber's as primary keys) will have no problem adopting this into their applications.

What will make this code stop working?

* If you are developing an database and you run the code to stop the manual deleting of tables everything will work find. But when you split the database (FE & BE) the code stops working and you can go back to manually delete any table. However, this is not a problem but more of a point to note. Because all you have to do is run the code again but this time in the backend (BE) and everything is find again i.e the user cannot delete the tables manually.

* If you run the code in your current database but thereafter you import your tables into a new database the code becomes void in the new database and will not work. Therefore you will have to re-run the code in the new database with the old tables for it to work again.

How to use this Code!

* 1. Copy and paste the code into a new module in your database and save it.
* 2. Press (CTL + G) to open the immediate window.
* 3. In the immediate window you must enter the following ?StopManualTableDelete("Yes") and press enter on your keyboard to DISALLOW a user from manually deleting a table:
* 4. If you want to RE-ENABLE a user to delete the tables again run the following ?StopManualTableDelete("No") in the immediate window:

There is all the code to go into the module.



' Procedure : StopManualTableDelete

' DateTime : 5/26/2008 08:05

' Author : Dane Miller - Dallr

' Purpose : Created to stop a person from Manually deleting a table

' Arguments : "Yes" OR "No"

' References: Microsoft ActiveX Data Objects x.x Library

' : Microsoft DAO x.x Library

' Returns : N/A



Public Function StopManualTableDelete(YesOrNo As String)

Dim fld As DAO.Field

Dim db As DAO.Database

Dim tbl As DAO.TableDef

Dim SQL_CreateConstraint As String, SQL_DropConstraint As String

Dim strConstraint As String ' this variable holds the name of the constraint

Dim i As Integer

Dim tblNames As String, DeleteInfo As String

Set db = CurrentDb()

i = 0

For Each tbl In db.tableDefs

' Bypass system tables with autonumbers

' Also any hidden table that starts with "~"

If Mid(tbl.Name, 1, 4) "MSys" Then

If Left(tbl.Name, 1) "~" Then

For Each fld In db.TableDefs(tbl.Name).Fields

If dbAutoIncrField = (fld.Attributes And dbAutoIncrField) Then 'Find autonumber

DoCmd.Hourglass True

strConstraint = "con_" & fld.Name & "_" & tbl.Name 'Build constraint name

If YesOrNo = "YES" Then

i = i + 1

'Drop any existing autonumber field constraints if there is one.

If FindCheckConstraint(strConstraint) = True Then

SQL_DropConstraint = "ALTER TABLE " & tbl.Name & _

" DROP CONSTRAINT " & strConstraint

CurrentProject.Connection.Execute SQL_DropConstraint

End If

DoEvents ' await a while just in case

'create the new constraint to disallow the table from being deleted.

SQL_CreateConstraint = " ALTER TABLE " & tbl.Name & " ADD " & _

" CONSTRAINT " & strConstraint & _

" CHECK (" & fld.Name & " IS NOT NULL))"

'Debug.Print SQL_CreateConstraint

CurrentProject.Connection.Execute SQL_CreateConstraint

DeleteInfo = "CANNOT"

End If

If YesOrNo = "NO" Then

'Drop any existing autonumber field constraints.

If FindCheckConstraint(strConstraint) = True Then

i = i + 1

SQL_DropConstraint = "ALTER TABLE " & tbl.Name & _

" DROP CONSTRAINT " & strConstraint

CurrentProject.Connection.Execute SQL_DropConstraint

DeleteInfo = "CAN"

End If

End If

tblNames = tblNames & tbl.Name & vbNewLine

Exit For

End If

Next fld

End If

End If

Next tbl


Set db = Nothing

DoCmd.Hourglass False

If i > 0 Then

MsgBox i & " tables have been set so they " & DeleteInfo & " be deleted manually. " _

& vbNewLine & "These tables are:" & vbNewLine & vbNewLine & tblNames


MsgBox "There are no tables with Autonumber fields present in this database." _

& vbNewLine & "Therefore this code did not have any effect on this database."

End If

End Function


Public Function FindCheckConstraint(MyConstraint As String) As Boolean

'this function checks to see if a check constraint already exist on the autonumber field.

Dim fld As ADODB.Field

Dim rst As ADODB.Recordset

Set rst = CurrentProject.Connection.OpenSchema(adSchemaCheck Constraints)

Do Until rst.EOF

For Each fld In rst.Fields

If fld.Name = "CONSTRAINT_NAME" Then

If fld.Value = MyConstraint Then

'Debug.Print fld.Value

FindCheckConstraint = True

Exit For

End If

End If

Next fld



End Function

I was wondering what syntax is used to refer to a variable value chosen in a combo box of a form?

I am using Allen Browns concatrelated function to list results from a query but I am trying to limit results to a specific date. I cannot use Forms]![frmViewAgenda]![Choosedate] in the query I refer to as the function falls over so I need to adapt the function so it works dynamically in VBA to check what date was chosen in the form

this is the code from Allen:

Public Function ConcatRelated(strField As String, _
    strTable As String, _
    Optional strWhere As String, _
    Optional strOrderBy As String, _
    Optional strSeparator = ", ") As Variant
On Error GoTo Err_Handler
    'Purpose:   Generate a concatenated string of related records.
    'Return:    String variant, or Null if no matches.
    'Arguments: strField = name of field to get results from and concatenate.
    '           strTable = name of a table or query.
    '           strWhere = WHERE clause to choose the right values.
    '           strOrderBy = ORDER BY clause, for sorting the values.
    '           strSeparator = characters to use between the concatenated values.
    'Notes:     1. Use square brackets around field/table names with spaces or odd characters.
    '           2. strField can be a Multi-valued field (A2007 and later), but strOrderBy cannot.
    '           3. Nulls are omitted, zero-length strings (ZLSs) are returned as ZLSs.
    '           4. Returning more than 255 characters to a recordset triggers this Access bug:
    '               http://allenbrowne.com/bug-16.html
    Dim rs As DAO.Recordset         'Related records
    Dim rsMV As DAO.Recordset       'Multi-valued field recordset
    Dim strSql As String            'SQL statement
    Dim strOut As String            'Output string to concatenate to.
    Dim lngLen As Long              'Length of string.
    Dim bIsMultiValue As Boolean    'Flag if strField is a multi-valued field.
    'Initialize to Null
    ConcatRelated = Null
    'Build SQL string, and get the records.
    strSql = "SELECT " & strField & " FROM " & strTable
    If strWhere  vbNullString Then
        strSql = strSql & " WHERE " & strWhere
    End If
    If strOrderBy  vbNullString Then
        strSql = strSql & " ORDER BY " & strOrderBy
    End If
    Set rs = DBEngine(0)(0).OpenRecordset(strSql, dbOpenDynaset)
    'Determine if the requested field is multi-valued (Type is above 100.)
    bIsMultiValue = (rs(0).Type > 100)
    'Loop through the matching records
    Do While Not rs.EOF
        If bIsMultiValue Then
            'For multi-valued field, loop through the values
            Set rsMV = rs(0).Value
            Do While Not rsMV.EOF
                If Not IsNull(rsMV(0)) Then
                    strOut = strOut & rsMV(0) & strSeparator
                End If
            Set rsMV = Nothing
        ElseIf Not IsNull(rs(0)) Then
            strOut = strOut & rs(0) & strSeparator
        End If
    'Return the string without the trailing separator.
    lngLen = Len(strOut) - Len(strSeparator)
    If lngLen > 0 Then
        ConcatRelated = Left(strOut, lngLen)
    End If
    'Clean up
    Set rsMV = Nothing
    Set rs = Nothing
    Exit Function
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "ConcatRelated()"
    Resume Exit_Handler
End Function

I'm trying to remove underlines that might exist in a table field (type MEMO, set to RTF, so the user's formatting is preserved). My plan of attack is to use an update query (pointed at the table) whose "update to" calls a VBA function, passing the name of memo field as "InputMemo". Where I hit a dead end is how to implement the UNDERLINE property (i.e., someobject.underline = FALSE). Here's the code that doesn't compile:

Public Function funRemoveUnderlines(InputMemo As Variant) As Variant
Dim FixedMemo As Variant
FixedMemo = InputMemo
FixedMemo.Underline = False
funRemoveUnderlines = FixedMemo
End Function

FixedMemo, which is intended to be a container with the "fixed" (no underlines) version of the InputMemo field, is a variable, not an object, which is why FixedMemo.Underline is an invalid statement (this is my interpretation, anyway).

Any fixes or suggestions for an alternative approach? The fact that I need to modify a table field, rather than a form or report control, makes things more complicated, I think.


I am attemtping to add additional column headings to create a crosstab query. I have worked out the scenario to do this through query design and then based a report on same query. I am now trying to assign variables through a form. I feel as though I have tried every option and still get errors. I fear it may not be possible, however if you can create the query (Check) and you can create the report (check) and you can assign variables you have to be able to do the same with in crosstab report/query, right?

I am basically using the following query as the basis for my report and sql variable design.

	TRANSFORM CCur(Nz(Sum(IIf([FieldName]="Sales",Nz([Sales],0),Nz([Commission],0))))) AS TheValue
SELECT [Sales Analysis].Principal, [Sales Analysis].[customer name]
FROM tblXtabColumns, [Sales Analysis] INNER JOIN Orders ON [Sales Analysis].[Order ID] = Orders.[Order ID]
WHERE (((Orders.[Order Date]) Between #1/1/2011# And #12/31/2011#))
GROUP BY [Sales Analysis].Principal, [Sales Analysis].[customer name]
PIVOT [FieldName] & DatePart("q",([Orders].[Order Date]));

To get the extra columns for the crosstab I created a table = tblXtabColumns which has two fields = Sales & Commission. This aligns with the queries true source and equals the same field names for the values I want.

I believe this is what is causing the error. This is the SQL from my VBA

	strSQL = "TRANSFORM CCur(Nz(Sum(IIf([FieldName]=[Sales],Nz([Sales],0),Nz([Commission],0))))) As TheValue"
strSQL = strSQL & " SELECT [" & TempVars![Display1] & "] As SalesGroupingField1, [" & TempVars![Display] & "] As
strSQL = strSQL & " FROM tblXtabColumns, [Sales Analysis] INNER JOIN Orders ON [Sales Analysis].[Order ID] = Orders.[Order
strSQL = strSQL & " Where [Quarter]=" & TempVars![Quarter] & " AND [Year]=" & TempVars![Year]
strSQL = strSQL & " GROUP BY SalesGroupingField1, SalesGroupingField"
strSQL = strSQL & " Pivot [tblXtabColumns].[FieldName] & [Sales Analysis].[MonthOfQuarter] In (1,2,3)"

TempVars!Display & 1 are values set from my "selection" form.
Quarter, Year, MonthofQuarter are all fields in Query Sales Analysis.

Does anyone see the issue here? Is this even possible? I feel it must be.......

I had been seeing various posts of people building "search forms" in Access, needing to build the correct query string based on the populated search form values. So, I thought to myself... "I already know how to read the controls of the form (my Validation classes do that), and I have working example code of how to use ADODB.Command / .Parameters objects to run SELECT queries. So why not harness ADODB.Command / .Parameters objects to build a dynamic query with a variable number of .Parameters objects?!"

Sample code performing a SQL SELECT with ADODB.Command / .Parameters objects:

	Public Function LocateByPartNumber() As Boolean
On Error GoTo Err_LocateByPartNumber

  Dim adoCMD As ADODB.Command
  Dim adoRS As ADODB.Recordset
  Dim strSQL As String

  'Define a query to look for the KeyValue based on the KeyName and Run-Time Environment
  strSQL = "SELECT [piw].[aid],[piw].[title],[piw].[qtyper],[oldqtyper],[piw].[addpartrecordflg],[piw].[doneflg] " & _
           "FROM [" & Me.FETempTableName & "] AS [piw] " & _
           "WHERE [piw].[partnumber] = ?;"

  'Define attachment to database table specifics
  Set adoCMD = New ADODB.Command
  With adoCMD
    .ActiveConnection = CurrentProject.Connection
    .CommandType = adCmdText
    .CommandText = strSQL
    .Parameters.Append .CreateParameter("p1", adVarChar, adParamInput, 25, Me.partnumber)
    Set adoRS = .Execute()
  End With

  With adoRS
    'Was no record found?
    If .BOF Or .EOF Then
      LocateByPartNumber = False
      'Fetch the value found
      Me.aid = Nz(adoRS!aid, 0)
      Me.title = Nz(adoRS!title, vbNullString)
      Me.qtyper = Nz(adoRS!qtyper, 0)
      Me.oldqtyper = Nz(adoRS!oldqtyper, 0)
      Me.addpartrecordflg = Nz(adoRS!addpartrecordflg, False)
      Me.doneflg = Nz(adoRS!doneflg, False)
      LocateByPartNumber = True
    End If

    'Close the database table
  End With

  'Clean up the connection to the database
  Set adoCMD = Nothing
  Set adoRS = Nothing

  Exit Function

  Call errorhandler_MsgBox("Class: clsObjPartsImportWizardTbl, Function: LocateByPartNumber()")
  LocateByPartNumber = False
  Resume Exit_LocateByPartNumber

End Function

All that should need to happen is:

1) Template the SQL query, base part + dynamic WHERE CLAUSE... loop through the fields populated and hard code AND / OR binary operations
2) For the .Parameters objects, I already am calling the .CreateParameter method already, so just need to:
2a) Keep a step counter to dynamically / sequentially name the parameters objects. Ex: p1, p2, p3, p4... While Access does not require such, I still do it for good house keeping. Much easier to see what is what in the watches window if you follow such a standard
2b) Need some sort of static reference table to keep track of the field's property values required when creating the .Parameters objects. Simple in-memory collection class, keyed by table/column and then you receive back the rest of the details required in the .CreateParameter call.

The appeal of performing queries with ADODB.Comamnd / .Parameters objects is the greater ease of dealing with building the string, NOT having to fight with quote characters or needing to escape characters. Once a variable piece of data goes inside its respective .Parameters objects... it is encapsulated, ready to be handed off to the query engine. That is a SO COOL feature of queries via ADODB.Comamnd / .Parameters objects!!

Oh..... I see why I have not tried coding this up yet... I have not found a way to totally variablize the call to .CreateParameter so that it may be put into a loop. All right, digging into my Object Rexx bag of tricks... INTERPRET to the rescue! I recall VBA had something roughly equivalent... but I seem to recall it can not work with objects... gggrrr..... Aaahhh yes, Eval() is what is in VBA.

Has anyone ever tried such with ADODB.Comamnd / .Parameters objects?

Hello, I have a basic question but I do not see how to make.

In a code VBA, I would like to calculate the number of day between two dates in a variable like this

hello = DCount(?)

I have a query with a field dateStart and a DateEnd. I would like to count the number of day between these two dates with DCount but I do not see how to make!!. Normally, It's like that

DCount(Name of the field, name of the query or the table, the SQL )

but I cannot integrate two fields. I looked in the forum but I did not find my answer.

Could you help me.

Thank you Nilses

I have a join query I need to run that takes in two parameters (startDate and endDate of Type Date) and it pulls two of its fields from two other parameter queries that use the same parameters. If I execute this as a saved query from the database window it works flawlessly. I'm assuming it passes those parameters on to its component queries since they use the same names.

The problem is I need to run this query programatically (so I can insert the parameters programatically. The problem is that by using the method of inserting the parameters with variables (ie "select * from tblTable where " & x & " = " & y) replaces those prarameters with static values and they're no longer really parameters and so they don't get inhereited by the other queries in the join. I figure I could use DAO's querydef object instead, but I'm trying to avoid DAO as ADO is set to supersede it. Also the other recordsets involved are ADO. Everything is contained in the same .mdb file (or linked tables within it).

I guess the concise question is: How can I build this join parameter query programatically so that the parameter queries in the join can inherit the parameters passed to the join query in ADO? Or do I have to use DAO querydefs?

90% of what I know about VBA/Access is self taught while working on the job, so I might be missing something that would make it easier.

Hi people,

I'm using the following code to generate a list of the reports I want for a combo box:

	For Each accObjectQry In CurrentData.AllQueries

   Me.cboSelectReport.AddItem accObjectQry.name 


This grabs the queries for the reports I want to generate on my form through the combo box.

However, the name of the report, is taken from the name of the query I use to generate the report, e.g.

Report: rptSale has query name = qryRptSales.

I want to keep the names as they are for consistency in my naming conventions for the database and to avoid programming problems with spaces in db object names.

I thought I could use the Description field of the query. So I right mouse clicked on my query, then went to Properties, and under Description, wrote a nicely formatted name for the query ("My Sales Report").

I was hoping to call that Description field through VBA. But it doesn't seem to be an option when I try to reference the

	Dim accObjectQry As Access.AccessObject

variable I setup.

Is it possible to call the Description of a Database Object (in this case a query) through VBA?

Thanks in advance.

I'm attempting to append a table using only a few fields from another table.

Unfortunately, I have to take a generalized approach with the "master" table (the one being appended) since it needs to hold a listing of information from several tables. So my thought process was to add a field, ProjAndIrun, into the "master" table which isn't found in the other tables to differentiate between the several different "projects."

Well, I'm having a hard time adding the information to the master table through an SQL command line. Using a query it'll open up an input box, but when replacing the "expr1" with a variable previously defined in the coding I receive an "Incomplete Query Cause" error (Error 3450).

The line works when I remove the project variable and associated field name, but I need for that information to be added to the "master" each time.

Any ideas?

	SQL = "INSERT INTO Master_Test_Table ( Product_Number, Serial_Number, Asset_Number, PO_Number, ProjAndIrun )" _
& " FROM '" & strTestTable & "';"

CurrentDb.Execute SQ

Thanks in advance!

Hi there!

I am new to VBA and trying to query an Excel spreadsheet to enter all the data into a temporary Access table. I am taking one step at a time though, and after reading this microsoft article on how to query a spreadsheet from Access (http://www.microsoft.com/technet/scr.../tips0607.mspx) I thought I was well on my way. I liked how they kind of broke it all down for you.

Well, I actually can't get the code from the article working! The only things I've changed are the file's directory path and names (file, worksheet, and Field names (headers)). I get a "compile error: variable not defined" on

	Set objConnection

and I'm pretty sure it'll be the same with the following

	Set objRecordset

What I take from that error is that I'd need to predefine those objects before being able to use them... but isn't that what those statements are doing?! Maybe I'm just using it wrong... I have the script in the code for a Form I made as the Click event for a cmdButton. I'm using Access and Excel 2003. Here's the code in case it's easier than loading the article:

	On Error Resume Next

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:ScriptsTest.xls;" & _
        "Extended Properties=""Excel 8.0;HDR=Yes;"";" 

objRecordset.Open "Select * FROM [Sheet1$]", _
    objConnection, adOpenStatic, adLockOptimistic, adCmdText

Do Until objRecordset.EOF
    Wscript.Echo objRecordset.Fields.Item("Name"), _

Also, I've been looking to try to find a good tutorial for learning ADO commands, but if anyone has any suggestions that would be great to hear too! Thank you for any advice!

Not finding an answer? Try a Google search.