Query using len Results

Access 2003 SP3, Windows XP

I have a sub report which is returning two different fields depending on if a checkbox is checked. I needed to exlcude any records where both of the notes were blank and so I came up with a field to return BothNull if both had no data. If the checkbox is checked it is supposed to check one set of two fields and if it is not checked it is supposed to check the other. The result that I came up with was confusing a bit, because when I tried using Len([Field]) = 0 it wouldn't recognize it for the two fields which come from a linked SQL Server table, but it worked fine on the Access linked table. So, by using Len([Field]) < 1 it actually works. I'm trying to figure out how the length of a field can be greater than 0 but have no data, including spaces.

Anyway, this is what I wound up with:

	BothNull: IIf(([Forms]![frmAdmin]![chkUseBarkleyNotes]=0) And (Len([ProfileNote] & "")=0 And Len([WatchlistNote] & "")=0) Or
([Forms]![frmAdmin]![chkUseBarkleyNotes]0) And (Len([BarkleyQtrNotes])

I would like to query a records to get the the records where number of character in a field is less than a certain number. For example

If i used this criteria: Len([firstname]) = "8", then it will produce all the records where all firstname have 8 character.

But if i used this criteria: Len([firstname]) < "9" , I don't get the result that I need where the number of character in the firstname is less than 9.

I would really appreciate your help.

Hi Everyone
I am very new to Access so please go gentle on me. I have searched to forum but can not find the answer I am looking for........

I have created a database to record all County darts matches with their stats. What I am trying to do, and I feel I'm almost there, is create a form with a list box to select a player and that generates a report on that players stats.

So far I have got to the stage where I have created the report, created the form with the list box and inserted the code.

My problem is that the list box is replicating the names of the players in the list box instead of just one instance of their name. So for example if a player has played 6 times his name is appearing 6 times in the list box. I just need it to appear once.

I have listed the code below

	Private Function GetCriteria() As String
   Dim stDocCriteria As String
   Dim VarItm As Variant
   For Each VarItm In PlayerListFilter.ItemsSelected
  stDocCriteria = stDocCriteria & "[PlayerID] = " & PlayerListFilter.Column(0, VarItm) & " OR "
   If stDocCriteria  "" Then
  stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 4)
  stDocCriteria = "True"
   End If
   GetCriteria = stDocCriteria
End Function

Private Sub ButtonOpen_Click()
   DoCmd.OpenReport "rpt_Players", acPreview, , GetCriteria()

End Sub

Everything seems to work fine apart from this. Any help would be greatly appreciated.


This procedure works properly except that it skips the SQL statement that selects the criteria for my query named 'strQry'. This results in running the query wide open with the DoCmd at the end.

I have tested the SQL statement in a separate query and it works properly. Am I missing a command in VBA that runs this piece of code?

Any help would be appreciated....cy

Private Sub cmdSubmit_Click()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
Set db = CurrentDb()
Set qdf = db.QueryDefs("strQry")

For Each varItem In cmbCATGDRUG.ItemsSelected
strCriteria = strCriteria & "," & cmbCATGDRUG.ItemData(varItem) & " "
Next varItem

If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If
strCriteria = Right(strCriteria, Len(strCriteria) - 1)


strSQL = "SELECT * " & _
"FROM T2_Drugstore " & _
"WHERE T2_Drugstore.[DVSN_NBR]=" & Me.cmbDIVDRUG.Value & " " & _
"AND T2_Drugstore.[DEPT_NBR]=" & Me.cmbDEPTDRUG.Value & " " & _
"AND T2_Drugstore.[CATG_NBR] IN (" & strCriteria & ")" & _
"ORDER BY T2_Drugstore.[LOCN_NBR];"

DoCmd.OpenQuery "strQry"
Set qdf = Nothing
Set db = Nothing

I have a form where I have fields that can be filled in to create a filter. Then the results are returned in a subform. Works Great! Now I need to Append the "filtered" records to a table. I am not sure how to create a query using the filter. I set the recordsource of the subform using the buildfilter but I dont know how to Append the records.
Here is the BuildFilter code:

	Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    Dim varColor As Variant
    Dim varItem As Variant
    Dim intIndex As Integer
    varWhere = Null  ' Main filter
    'varColor = Null  ' Subfilter used for colors
    ' Check for LIKE First Name
    If Me.txtQuoteNumber > "" Then
        varWhere = varWhere & "[QuoteNo] LIKE  ""*" & Me.txtQuoteNumber & "*"" And "
    End If
    ' Check for LIKE Last Name
    If Me.txtAccount > "" Then
        varWhere = varWhere & "[Account] LIKE ""*" & Me.txtAccount & "*"" AND "
    End If
    ' Check for CityID
    If Me.txtProductType > "" Then
       varWhere = varWhere & "[Product Type / Scope] LIKE ""*" & Me.txtProductType & "*"" AND "
    End If
    ' Check for LIKE Address
    If Me.cmbAssigned > "" Then
        varWhere = varWhere & "[PSR Contact] LIKE ""*" & Me.cmbAssigned & "*"" AND "
    End If
    ' Check for LIKE Zip
    'If Me.txtZip > "" Then
    '    varWhere = varWhere & "[clzip] LIKE """ & Me.txtZip & "*"" AND "
    'End If
    ' Check for min Age
    If Me.txtQuoteTotal > "" Then
        varWhere = varWhere & "[Quote Total] < " & Me.txtQuoteTotal & " AND "
    End If
    ' Check for max Age
    If Me.txtQuoteTotalMore > "" Then
        varWhere = varWhere & "[Quote Total] > " & Me.txtQuoteTotalMore & " AND "
    End If
    ' Check for DateModified
    If Me.CmbDateModified > "" Then
       'varWhere = varWhere & "[Month] Between """ & Me.CmbSTARTMonth.Value & """ And """ & Me.CmbENDMonth.Value & """"
        varWhere = varWhere & "[DateModified]> #" & Me.CmbDateModified.Value & "# AND "
        'varWhere = varWhere & "[Date] Between #" & Me.CmbSTARTMonth.Value & "# AND #" & Me.CmbENDMonth.Value & "#"
    End If
     'Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
        varWhere = "WHERE " & varWhere
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
    End If
    BuildFilter = varWhere
  Debug.Print varWhere
End Function

Here is the subform filter code:

	Me.frmsubClients.Form.RecordSource = "SELECT * FROM MASTER " & BuildFilter

Hi Everyone,

I am trying to diagnose possible loss of data or incorrect calculations in one of my database's tables. This first database (Import_25) is populated by the sum of two fields from another table (Tab F1). The field name which is populated in Import_25 is based on an Index number from Tab F1. So I have developed a Custom Function which returns the conditional statement to be used in the SQL Query. Unfortunetly I am not getting the results I expect from the Query.

Below is the relevant portion of my code:

    'Build SQL query string
    strSQL = "SELECT"
    'Data Field(s)
    strSQL = strSQL + " Entity_List.[Entity Number], [Tab F1 - Deferred Rollfwd - Balances (LC)].Index, [Tab F1 - Deferred
Rollfwd - Balances (LC)].Year, [Tab F1 - Deferred Rollfwd - Balances (LC)].Month, [Tab F1 - Deferred Rollfwd - Balances
(LC)].[G TU YTD-PM], [Tab F1 - Deferred Rollfwd - Balances (LC)].[G TU], [Import_25].[CASH]"
    'Source Table(s)
    strSQL = strSQL + " FROM Entity_List INNER JOIN ([Tab F1 - Deferred Rollfwd - Balances (LC)] INNER JOIN [Import_25] ON
[Import_25].[Entity Number] = [Tab F1 - Deferred Rollfwd - Balances (LC)].[Entity Number]) ON Entity_List.[Entity Number] =
[Tab F1 - Deferred Rollfwd - Balances (LC)].[Entity Number]"
    'Conditional Statement(s)
    strSQL = strSQL + " WHERE ([Tab F1 - Deferred Rollfwd - Balances (LC)].Year = " & intYear & " AND [Tab F1 - Deferred
Rollfwd - Balances (LC)].Month = " & intMonth & " AND BuildImportTableCondition('Import_25', '  [G TU YTD-PM] + [G TU]', [Tab
F1 - Deferred Rollfwd - Balances (LC)].Index))"
    'Order Statement(s)
    strSQL = strSQL + " ORDER BY Entity_List.[Entity Number], [Index];"

    Debug.Print strSQL
    rs.Open strSQL, cnn, adOpenDynamic, adLockOptimistic
    MsgBox (rs.GetString())
End Function

'Function to return the SQL string which will be used as the conditional statement
'in the SQL query
Public Function BuildImportTableCondition(strImportTableName As String, strCondition As String, intMapIndex As Integer) As
    Dim rs As New ADODB.Recordset
    Dim cnn As New ADODB.Connection
    Dim strSQL As String
    Dim varReturnCond As Variant
    Dim strRecord As String

    'Build Database connection
    Set cnn = CurrentProject.Connection
    'Build SQL statement to determine the Column Heading for the supplied Index
    strSQL = "SELECT TOP 1"
    'Data Field(s)
    strSQL = strSQL + " [Column Heading]"
    'Source Table(s)
    strSQL = strSQL + " FROM [Import Index Map]"
    'Conditional Statement(s)
    strSQL = strSQL + " WHERE [Index] = " & intMapIndex & ";"
    'Run SQL statement
    rs.Open strSQL, cnn, adOpenDynamic, adLockOptimistic
    'Build SQL Condition
    If rs.EOF Then
        varReturnCond = Null
        strRecord = rs.GetString()
        varReturnCond = "[" + strImportTableName + "].[" + Left(strRecord, Len(strRecord) - 1) + "]" + strCondition
    End If
    BuildImportTableCondition = varReturnCond
End Function

I am trying to pass a global variable to criteria in a query using the below code, but the query is returning no results. I have the function ReturnStrCriteria() included in the query's criteria. When I manually put the criteria into the query (Like "*2007"), it returns results. Not sure what I'm doing wrong. Any suggestions would be appreciated. Thanks

	Option Compare Database
Option Explicit

  Public Sub cmdOK_Click()

' Declare variables
    Dim db As DAO.Database
    Dim varItem As Variant
    Dim strSQL As String

' Get the database and stored query
    Set db = CurrentDb()
    DoCmd.RunSQL "Delete * From Table;"
    ' Loop through the selected items in the list box and build a text string
    If Me!lstRebate_Period.ItemsSelected.Count > 0 Then
        For Each varItem In Me!lstRebate_Period.ItemsSelected
            strCriteria = strCriteria & "Like " & Chr(34) _
                          & "*" & Me!lstRebate_Period.ItemData(varItem) & Chr(34) & "OR "
        Next varItem
        strCriteria = Left(strCriteria, Len(strCriteria) - 3)
        strCriteria = "Tbl_Payment_YTD.[Rebate Period] Like '*'"
    End If
' Open the query
    DoCmd.OpenQuery "Query"

Set db = Nothing

End Sub


	Option Compare Database
Option Explicit

Public strCriteria As String

Public Function ReturnStrCriteria() As String

ReturnStrCriteria = strCriteria

End Function

Here's a current IIF query i'm trying to change/modify for Access 2007.

X: Sum(IIf(IsNumeric(Left([Prod],1)),1,0)

I'm trying to get it to add up only if [Prod] is 11 characters instead of the above in this format: "1234-A-56.7" (numbers, dashes, letters and decimal). I've tried using "Len" instead of "IsNumeric" but since it contains different characters, it gives me a syntax error. Any point in the right direction would be greatly appreciated.

Kind Regards

so i have two listboxes that have the values i want for the query parameters. I slightly modified one code i found so that i can query using one of them, however i cannot figure out how to get the second listbox to put criteria into another field. The working code i'm using is:

Private Sub Command_Click()
On Error GoTo Err_Command_Click

On Error GoTo Err_Handler
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String

For Each varItem In Me.PartyBox.ItemsSelected
strCriteria = strCriteria & "counterparties.counterparty =" & Chr(34) & Me.PartyBox.ItemData(varItem) & Chr(34) & " Or "
Next varItem

strCriteria = Left(strCriteria, Len(strCriteria) - 4)

strSQL = "SELECT counterparties.[Counterparty Entity], Fund.[Fund Name], products.Product, combine.[Available?] " & _
"FROM products INNER JOIN (Fund INNER JOIN (counterparties INNER JOIN combine ON counterparties.[Counterparty ID] = combine.[company id]) ON Fund.[Fund ID] = combine.[fund id]) ON products.[Product ID] = combine.[product id] " & _
"WHERE " & strCriteria

CurrentDb.QueryDefs("1").SQL = strSQL

DoCmd.OpenQuery "1"

Exit Sub

If Err.Number = 5 Then
MsgBox "Must Make A Selection First", , "Make A Selection First"
Exit Sub
MsgBox Err.Number & " " & Err.Description
Resume Exit_Handler
End If

Dim stDocName As String
stDocName = "combqry"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit Sub

MsgBox Err.Description
Resume Exit_Command_Click

End Sub

I am trying to be able to search by Product and counterparty.
Any help is greatly appreciated, thanks!

I have a query used to drive a subform showing part numbers. It works fine, but I need the ability to enter a partial part number and have it return anything starting with that partial part number.
So I enter "ABCDE" and the query returns "ABCDE123", "ABCDE345", "ABCDE456", etc.
I imagine that I need to do a match on LEFT(PartNumber,LEN(MyEntry)), but I would like to do this without the criteria being a control on a form, as I can't get that to work. So can I do this using the query as the data source of the subform, then a Master / Child relationship from my main form to the subform?


I have a database which uses for different IT systems, the data is combined into 1 table and then uses another system to link Policy_References together, however 1 system holds the format of the policy reference in a different format compared to the others, so I have created 2 different queries to define the format that is required, but when I create a temp table to hold both queries I end up with around 1,500 duplicate records, what I would like to do is use just one query to combine both. This is what I have so far:

First Query
Length: Len([policy_ref]) - This counts the characters in the policy_ref field so I can then perform this expression based on 19 and 13 characters.

MyValue: IIf([System_Details]="QIE Iris",IIf([Length]=19,Left([Policy_Ref],10) & Right([Policy_Ref],4),IIf([Length]=13,Left([Policy_Ref],10) & Right([Policy_Ref],3),[Policy_Ref])),[Policy_Ref])

This is the SQL View 19 Characters

SELECT [Underwriting Support].MI_Reference, [Underwriting Support].Policy_Ref, Len([policy_ref]) AS Length, IIf([System_Details]="QIE Iris",IIf([Length]=19,Left([Policy_Ref],10) & Right([Policy_Ref],4),IIf([Length]=13,Left([Policy_Ref],10) & Right([Policy_Ref],3),[Policy_Ref])),[Policy_Ref]) AS MyValue, Right([Policy_Ref],9) AS Right1, Left([Right1],4) AS [UW Year]
FROM [Underwriting Support];

Second Query
Length: Len([policy_ref]) - This counts the characters in the policy_ref field so I can then perform this expression based on 18 and 13 characters.

MyValue: IIf([System_Details]="QIE Iris",IIf([Length]=18,Left([Policy_Ref],10) & "0" & Right([Policy_Ref],3),IIf([Length]=13,Left([Policy_Ref],10) & Right([Policy_Ref],3),[Policy_Ref])),[Policy_Ref])

This is the SQL View 18 Characters

SELECT [Underwriting Support].MI_Reference, [Underwriting Support].Policy_Ref, Len([policy_ref]) AS Length, IIf([System_Details]="QIE Iris",IIf([Length]=18,Left([Policy_Ref],10) & "0" & Right([Policy_Ref],3),IIf([Length]=13,Left([Policy_Ref],10) & Right([Policy_Ref],3),[Policy_Ref])),[Policy_Ref]) AS MyValue, Right([Policy_Ref],8) AS Right1, Left([Right1],4) AS [UW Year]
FROM [Underwriting Support];

What I would like to do is use one expression for both of the above to elimante duplicate records!

Any help would be great

I am at a loss... I think what I am trying to do is simple but every attempt and example that I come across does not seem to work.

All I want to do is use a selection in a listbox on a form and run a make table query using the listbox selection as criteria for one field in the make table query... I am using a button to run this query. All I have found online is mutliselect listboxes which seems much more complicated??

I used this to set the criteria for a report within access and it works perfect, I just want to use the same criteria in a make table query to export the same report data to excel. I don't know how I can set the "GetCriteria" below to an openquery function??

Private Function GetCriteria() As String
Dim stDocCriteria As String
Dim VarItm As Variant
For Each VarItm In List0.ItemsSelected
stDocCriteria = stDocCriteria & "[SEMINAR_ID] = " & List0.Column(0, VarItm) & " OR "
If stDocCriteria "" Then
stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 4)
stDocCriteria = "True"
End If
GetCriteria = stDocCriteria
End Function

Private Sub Command10_Click()

DoCmd.OpenReport "SEMINAR_ATTENDANCE_TBL", acPreview, , GetCriteria()

End Sub

Please let me know of your thoughts.

Thanks in advance

If i have a field where all entries should be 10 characters in length (a phone number, no formatting), and the in the table, i set the length of the Phone field to be 10 characters, max, AND I make a query and in the Phone field of the query i say


That should return all records who's phone number is 8, 9 or 10 digits in length, correct?

Problem is, it is only returning records whose phone number is 8 or 9 digits in length. Not those with 10.

Using Len([Phone])>9 does not return the records with 10 digit length. The only way I can get those records to show up in the query is to state it:


FYI, I created a field in the query Expr1:Len([Phone]) and it lists all those records as having phone numbers with a length of 10.

Last time I checked 10 was greater than 7. Does anyone have any idea why it wouldn't return those entries using >7?

Access 2003 FE
Sql Server BE

i Can loop a listbox to create a pass thru query using strCriteria as the parameter as thus

' Loop through the selected items in the list box and build a text string
For Each varItem In Me!lstActual.ItemsSelected
strCriteria = strCriteria & "," & Me!lstActual.ItemData(varItem) & ""
Next varItem
' Remove the leading comma from the string
strCriteria = Right(strCriteria, Len(strCriteria) - 1)

' Build the new SQL statement incorporating the string

strSQL = "SELECT DISTINCT TOP 100 PERCENT dbo.aaaaaaaaaa......

can i loop the values in a access table, to create the strCriteria that i can use in the pass thru query

Many Thanks

Hi all,
I have very little progamming experience with Access. I have field "User" which is filled with "firstname" and "lastname" (format looks like: firstname.username). Next I have second field "Nickname". I would like to fill "Nickname" automatically when I go to next record. Nickname contains 6 characters (all uppercase). Format of Nickname is: first character from "firstname" and first five characters from "lastname" (behind dot).For example:

For example:
1st user
Field User: Peter.Smith
Field Nickname: PSMITH

2nd user
Field User: Thomas.Sikoooo
Field Nickname: TSIKOO

3rd user

I have this query, which work correct for me, but I do not want to run an update query.
Code: UPDATE Table1 SET Table1.NickName = UCase(Left(Left([User],InStr(1,[User],".")-1),1) & Left(Right([User],Len([User])-InStr(1,[User],".")),5)); How I can convert this query to VBA code. I want, than update was automatically executing, when I'm going to next record in form. Necessary condition to run code: Field "user" must be filled!

Many thanks! Attached Files QueryUsingVBA.mdb (448.0 KB, 11 views) Reply With Quote 01-29-2013, 02:16 PM #2 ssanfu VIP Windows XP Access 2000 Join Date Sep 2010 Location Anchorage, Alaska, USA Posts 1,932 Having the first name and last name in the same field is not good practice. It violates normalization rules.
How do you do you handle names like:
O'Brian (apostrophe)
Van Horn (space in name)
Del La Cruz (spaces in name)

I would use the form before update:
Code: Private Sub Form_BeforeUpdate() Dim TestArray() As String TestArray = Split(Me.User, ".") Me.NickName = Left(TestArray(0), 1) & Left(TestArray(1), 5) End Sub

I have a form with a listbox that gets categories from a table. based on which categories were selected (multiselect), a recordset is built from another table. But now that i have a recordset floating around in RAM, how the heck do i spit the data out?

I want to use that recordset to populate a Report I have made. how!?

heres what i have:

Listbox (multiselect)
button to click and fire off this code piece

	' Set query string
strSQL = "SELECT categories.category, [Public programs contacts].[Last Name], " & _
"[Public programs contacts].[First name], [Public programs contacts].Cat_ID " & _
"FROM categories INNER JOIN [Public programs contacts] ON categories.ID=[Public programs contacts].Cat_ID " & _
"WHERE cat_id IN "
' init working criteria
strSQL2 = ""

'Loop to build ID list
If Me!box_category_list.ItemsSelected.Count > 0 Then
For Each varItem In Me!box_category_list.ItemsSelected
strSQL2 = strSQL2 & Me!box_category_list.ItemData(varItem) & ","
Next varItem

strSQL2 = strSQL & "(" & Left(strSQL2, Len(strSQL2) - 1) & ");"

Dim db As DAO.Database, rs As DAO.Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL2)
    While Not rs.EOF

        Debug.Print rs.Fields(0)
    Set rs = Nothing
    'somehow call this report and use data from above recordset
    DoCmd.OpenReport "My_Report"

My original problem was i tried building a query, but I couldnt get the criteria to work correctly.
I needed to put inside the CAT_ID Criteria field, a IN (Forms![Report - Category]![Text18])
In this case text18 = "7,3,8" (or some combination of IDs)
for some reason the query would pull nothing unless text18 had only 1 item in it....

leading me to the above VB attempts to do the same thing


I'm trying to get the InParam() function provided by Microsoft to work, but without luck, so far. I have searched the forum, but this problem doesn't show up in the searches I have found. I suspect the problem is my implementation.

I copied and pasted the function to a Module named InParam as is, removing the Option Compare Database line (since it is not included in the Microsoft example) :

'************************************************* ***********
'Declarations section of the module.
'************************************************* ***********

Option Explicit

'================================================= ===========
' The GetToken() function defines the delimiter character.
'================================================= ===========

Function GetToken(stLn, stDelim)
Dim iDelim As Integer, stToken As String
iDelim = InStr(1, stLn, stDelim)
If (iDelim 0) Then
stToken = LTrim$(RTrim$(Mid$(stLn, 1, iDelim - 1)))
stLn = Mid$(stLn, iDelim + 1)
stToken = LTrim$(RTrim$(Mid$(stLn, 1)))
stLn = ""
End If
GetToken = stToken
End Function

'================================================= ===========
' The InParam() function is the heart of this article. When
' the query runs, this function causes a query parameter
' dialog box to appear so you can enter a list of values.
' The values you enter are interpreted as if you
' had entered them within the parentheses of the In() operator.
'================================================= ===========
Function InParam(Fld, Param)
Dim stToken As String
'The following two lines are optional, making queries
Fld = UCase(Fld)
Param = UCase(Param)
If IsNull(Fld) Then Fld = ""
Do While (Len(Param) > 0)
stToken = GetToken(Param, ",")
If stToken = LTrim$(RTrim$(Fld)) Then
InParam = -1
Exit Function
InParam = 0
End If
End Function

The query I am using has two fields (to be expanded once this works): FileNumber and TransactionsID.

I want the function to work on the FileNumber field (datatype long integer), thus

InParam([FileNumber],[Enter list using commas, no spaces:])

I get a syntax error in the query: The expression you entered contains invalid syntax.

I also tried changing the dataype of FileNumber to text, and get this message: Undefined function in 'InParam' in expression.

What have I done wrong?


I am working on a simple database where i would like the user to select records from a multiple selection list box and then based on what the user had chosen a report displayed for just those records.

I have some code from a sample database but i cant get it to work.

I have 2 fields in a table called ID NO and Name
I have a form called Sounds

Everytime i press the button i get the following error message

Runtime error "3075"

Syntax error (Missing operator ) In query expression '(ID n)'

I have copied and pasted the code below:

'Run a report displaying only the records chosen
'by the user in the form's listbox.
Dim v As Variant
Dim Frm As Form
Dim ctl As Control
Dim theId As Long
Dim WhereCrit As String

'If nothing is selected, notify user...
If Me.LstFindings.ItemsSelected.Count = 0 Then
MsgBox "Please select a supplier or two.", vbExclamation, "No Supplier Selected"
'and then scram.
Exit Sub
End If

'Assign form and control to object variables.
Set Frm = Forms!Sounds
Set ctl = Frm!LstFindings

'Begin building Where string.
WhereCrit = "ID no = "

'Add each selected item to the WHERE string.
For Each v In ctl.ItemsSelected
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~
'The first column in the list, holding SupplierID, is hidden.
'See the list's "Column Widths" and "Column Count" properties in
'its property dialog and look up the terms in Help for more info.
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~
'Coordinates: 1st column (0); row v --
'where v changes for each round of the loop.
theId = ctl.Column(0, v)
WhereCrit = WhereCrit & theId & " OR ID no = "
Next v
'Loop ends; selected items are now accounted for...
'Test: Uncomment the next line to print the Where string to the Immediate window.
'Debug.Print WhereCrit
'Clean-up the Where string by removing the trailing text.
WhereCrit = Left(WhereCrit, Len(WhereCrit) - 17)

'Test: Uncomment the next line to print the Where string to the Immediate window.
'Debug.Print WhereCrit
'Open the suppliers report using the Where clause to filter it.
DoCmd.OpenReport "report", acViewPreview, , WhereCrit
End Sub

Any help would be much appreciated.
Thanks in Advance

I have a report which details the cost and amount of chemicals being applied to a farm to combat target pests. This report is grouped by [Target Pest].Value which ends up displaying the report as a groups of records grouped on individual values from [Target Pest]. This is the expected and desired result.

However, sometimes we record applications of chemicals which target multiple pests. When this occurs, grouping remains the same, but records containing multiple target pests will be present in the groups for each of the pests targeted. This is also expected and desired, but with an unwanted consequence. This duplication is a good, realistic and truthful representation of chemical applications, but in terms of representing the cost to spray for each pest, the duplication of costs is false and misleading representation.

My attempt to solve this problem was to divide the cost associated with each record by the amount of pests each record targets (on a per record basis in a text box control). I.e. a record of an application which targets 3 pests would show 1/3 of the total cost of that application as it's cost in this report. The record would show up three times (once in each pest's group), and the total sum of costs would remain accurate.

One problem I run into when performing this calculation is that I cannot make a direct reference to the number of target pests of a specific chemical application because they are entered in a multivalued field. One way of extracting this count is to find the difference in Length of the multivalued field string with commas, and without commas - to find the number of commas, and add 1 to find the number of target pests in the multivalued field. After this is done, I can divide the cost of the application by the number of pests to provide an accurate cost for the application based on target pest.

With that said, this length subtraction creates #Type! errors in report views and keeps that specific text box control from being summed, averaged, or undergoing any other header/footer type operations other than "count."

Is there a way to count individual values in a multivalued field without using len(), or is there a specific level (such as query or table level) where I can calculate or find this count explicitly/directly and have it properly referenced from the report in such a manner that I can perform sums, averages, etc. to the data?

If you are even close to grasping this, or need clarification, please feel free to ask any other questions or request a stripped down version of the DB in question. I'll be back to work on this in 16 hours.

Thanks in advance!

Good afternoon all,

I hope everyone is well?

I was wondering If anyone could help with the following problem I am having. I need to export the results of a query in the following format, each row having a line number...

1,61545001,,21/04/2009 08:00,21/04/2009 17:00,16/04/2009 17:00
2,61545001,,28/04/2009 08:00,28/04/2009 17:00,23/04/2009 17:00
3,61545001,,05/05/2009 08:00,05/05/2009 17:00,30/04/2009 17:00
4,61545001,,12/05/2009 08:00,12/05/2009 17:00,07/05/2009 17:00
5,61545001,,19/05/2009 08:00,19/05/2009 17:00,14/05/2009 17:00
6,61545001,,26/05/2009 08:00,26/05/2009 17:00,21/05/2009 17:00
7,61545001,,02/06/2009 08:00,02/06/2009 17:00,28/05/2009 17:00
8,61545001,,09/06/2009 08:00,09/06/2009 17:00,04/06/2009 17:00
9,61545001,,16/06/2009 08:00,16/06/2009 17:00,11/06/2009 17:00

The fields involved are:txtUnitCode e.g 61545001 txtStartDate e.g. 21/04/2009 txtStartTime e.g. 08:00 txtEndDate e.g. 11/06/2009 txtEndTime e.g. 17:00 I have managed to export a whole table with the following code, and I am sure I can change it so it points to a query. Its just the formatting I am stuggling with.

''''''''''''''''''My Code'''''''''''''''''''''

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim fld As DAO.Field
Dim strDelimeter As String
Dim strText As String
Dim blnColumnHeaders As Boolean
Dim FilePartOne As String
Dim mdbpath As String

Set db = CurrentDb
blnColumnHeaders = True
strDelimeter = Chr(9)
Set rst = db.OpenRecordset("select * from tblData")

Open "S:TelemarkPROJECTSACTIVE_PROJECTSexportfile.t xt" For Output As #1If blnColumnHeaders = True Then
For Each fld In rst.Fields
strText = strText & fld.Name & strDelimeter
Next fld

'get rid of the last delimeter
strText = Left(strText, Len(strText) - Len(strDelimeter))

strText = strText & vbNewLine
End If

Do While Not rst.EOF
For Each fld In rst.Fields
strText = strText & fld.Value & strDelimeter

'get rid of the extra delimeter
strText = Left(strText, Len(strText) - Len(strDelimeter))
strText = strText & vbNewLine

'removes the extra line
strText = Left(strText, Len(strText) - Len(vbNewLine))
Print #1, strText
Close #1
End Sub
I have tried to create a specification also but it doesnt look like I want it to (the data is used by a web program and must be exact) can I do this in code only? I know there is some formatting above but with a loop required for the incrementing of the line number and an unfamilarity with formatting Im stuggling a little.

Many thanks for any help / ideas in advance


Not finding an answer? Try a Google search.