Len function in a query Results

Can I use the len function in a query's criteria field?

I've tried a few things like:

(len

I got help with the Public Function below that will loop through a column of PartNumbers values and it strips the left characters out until it runs into one that isn't a zero.

I don't know how to call this Public Function in a Query ?
Would somebody be nice please show me how to do this (may be with steps) and thanks a lot.



Public Function StripZeros(strNum as String)
Do Until Left(strNum,1) < "0"
strNum = Right(strNum,Len(strNum)-1)
Loop

StripZeros = strNum
End Function

Hi

Im trying to use Duane's concatenate function in a query. Basically I have two tables, one contains all the main records, the other table contains all the comments people have added relating to that table. The comment field in the comments table is a memo field.

In the query, im simply looking for it to combine all the comments into one field so it can be exported to Excel.

The function works ok by concatenating the Date, the area the comment relates to and the comment seperated by a comma - BUT, im hitting the problem that its truncating the text and I cant find a way around it. There is no Group By clause in the query.

The function script is as follows:

----
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ",") _
As String
' open the recordset using the current connection
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

' with the record set, go through each record until end of file and string the two fields together using
' the delimter specified above (pstrDelim)
Dim strConcat As String
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With

' close the recordset
Set rs = Nothing
' output string without initial seperator
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If

Concatenate = strConcat
End Function
-----

The Query in access is as follows:

SELECT T_RFC_Main_Data.Record_Number, T_RFC_Main_Data.RFC_Number, T_RFC_Main_Data.Description, Concatenate("SELECT Date_added & ' -(' & Relates_to & ') - ' & Comment FROM T_Comments WHERE link_to_rfc_rec_no =" & [t_rfc_main_data.record_number]) AS Commentis
FROM T_RFC_Main_Data;


Could anyone offer a work around for this?

Thanks
Gary

Hello all-

Having some trouble with some of my code below.

There are times where the strAccountPrefix_LOB variable is either blank or 2 characters. Because it is being dimensioned as 2 characters, when it is blank, it gives me spaces. The spaces return an asc() of 32, but the spaces will not go away using the replace command. Any help is appreciated.

FWIW, I set the string length for speed purposes because this is going to be a user defined function in a query. I have it as a string because sometimes it will be alphanumeric.


	Code:
	 
Function RetentionPeriod(strAccountPrefix As String, _
                            strCenterPrefix As String, _
                            Optional strDivision As String, _
                            Optional strLedger As String) As Double
'Standard dimensions
Dim strCenterSearch As String
Dim strSearchString As String
Dim strAccountSearch As String
Dim intAccountPrefix_Len As Double
Dim strRetention As String
Dim intAccountLevel As Integer
Dim intCenterLevel As Integer
'AccountPrefix dimensions
Dim strAccountPrefix_ClassCode As String * 1
Dim strAccountPrefix_GroupCode As String * 1
Dim strAccountPrefix_DetailCode As String * 3
Dim strAccountPrefix_LOB As String * 2
'CenterPrefix dimensions
Dim strCenterPrefix_Division As String * 2
Dim strCenterPrefix_LineofBusiness As String * 1
Dim strCenterPrefix_Marketing As String * 2
Dim strCenterPrefix_Other As String * 3
'Account Number Processing------------------------------------------------------------------------------
'Define Standard Variables
strAccountPrefix = Trim(Left(strAccountPrefix, 7))
strCenterPrefix = Trim(Left(strCenterPrefix, 8))
intAccountPrefix_Len = Len(strAccountPrefix)
'Break out account for ClassCode, GroupCode, DetailCode, and LOB
strAccountPrefix_ClassCode = Replace(Mid(strAccountPrefix, 1, 1), " ", "") '1 position
strAccountPrefix_GroupCode = Replace(Mid(strAccountPrefix, 2, 1), " ", "") '2 position
strAccountPrefix_DetailCode = Replace(Mid(strAccountPrefix, 3, 3), " ", "") '3-5 position
strAccountPrefix_LOB = Replace(Mid(strAccountPrefix, 6, 2), Chr(32), "") '6-7 position



Hello,

I have an Access 2010 VBA/Query problem I’d like to see if the forum can address. Basically, I have two problems:

1. My query runs very slowly (over 2.5 hours to pull 2.5M records) since I have 2 custom VBA functions to aid in calculating date ranges. Yes, it’s a lot of records and its growing!
2. The other being that I cannot filter a reports query when the criterion for the filter is applied to a custom VBA function. I get a “Data type mismatch in criteria expression” error.

I'll do my best to give all the relevant information now, apologies if it’s long, I want to be thorough – it’s probably overkill. The gist of the data: ~2.5M records which track line item detail of order shipments. So, multiple lines make an order while a single line is an item in an order. I need to analyze this data and part of that is determining if orders shipped on time, etc. The table has a number of fields but the relevant ones are:

· ORDER NUMBER
· REQUESTED SHIP DATE – (aka the date the order is received)
· DATE ORDER SHIPPED/RETURNED – RETURNED label is irrelevant
· NEW ITEM FLAG – determines if an item is a 90 day exempt item

From these I calculate (Field name – field values: description & formula):

· STATUS – 5, 25 or 90: 5 days if it shipped in 5 days or if 5 days have yet to elapse, 25 days (“backorder”) if it didn’t ship in 5 days or 5 days have already elapsed and 90 days (“exempt”) if the item has an “N” in NEW ITEM FLAG.
o Status: IIf([NEW ITEM FLAG]="N",90,IIf([DATE ORDER SHIPPED/RETURNED] Is Null,IIf((NETWORKDAYS([REQUESTED SHIP DATE],Now()))>5,25,5),IIf((NETWORKDAYS([REQUESTED SHIP DATE],[DATE ORDER SHIPPED/RETURNED]))>5,25,5)))
· ACTUAL SHIPPING TIME – Positive Integer: uses UDF NETWORKDAYS to calculate shipping time dependent on whether or not the item has shipped.
o Actual Shipping Time: IIf([DATE ORDER SHIPPED/RETURNED] Is Null,NETWORKDAYS([REQUESTED SHIP DATE],Now()),NETWORKDAYS([REQUESTED SHIP DATE],[DATE ORDER SHIPPED/RETURNED]))
· TARGET SHIP DATE – Date: uses UDF NETOWRKDAYS to calculate the day it should’ve shipped accounting for weekends and specific holidays.
o TargetShipDate: TargetShipDate([REQUESTED SHIP DATE],[Status])
· ON TIME – On Time, Late, Pending: analyzes the other calculated fields with DATE ORDER SHIPPED/RETURNED to determine if it was on time, late or pending.
o On Time: IIf(([DATE ORDER SHIPPED/RETURNED] Is Null And Now() dtEndDay. If so, then switch the dates If dtStartDay > dtEndDay Then dtNominalEndDay = dtStartDay dtStartDay = dtEndDay dtEndDay = dtNominalEndDay End If 'Here are how many weeks are between the two dates lngTotalWeeks = DateDiff("w", dtStartDay, dtEndDay) 'Here are the number of weekdays in that total week lngTotalDays = lngTotalWeeks * 5 'Here is the date that is at the end of that many weeks dtNominalEndDay = DateAdd("d", (lngTotalWeeks * 7), dtStartDay) 'Now add the number of weekdays between the nominal end day and the actual end day While dtNominalEndDay 0 Then If Left(strField, 1) "[" Then strField = "[" & strField & "]" End If Do strCriteria = strField & _ " = #" & Format(TSD, "mm/dd/yyyy") & "#" rs.FindFirst strCriteria If Not rs.NoMatch Then TSD = TSD + 1 End If Loop Until rs.NoMatch End If End If Loop Until Not IsWeekend(TSD) If Status > 1 Then Status = Status - 1 TSD = TSD + 1 GoTo TestNextDate End If ExitHere: TargetShipDate = TSD 'Debug.Print TSD Exit Function ErrHandler: ' No matter what the error, just ' return without complaining. ' The worst that could happen is that the code ' includes a holiday as a real day, even if ' it's in the table. Debug.Print "Error: " & Err & " - " & Error Resume ExitHere End Function

	Code:
	Private Function IsWeekend(dtStartDate As Date) As Boolean
'test for weekends
Select Case Weekday(dtStartDate)
   Case vbSaturday, vbSunday
       IsWeekend = True
End Select
End Function


Thank you,

~Chris

Afternoon,

Im trying to runt he following module on in a query so that it will remove spaces from a field that has 3 sets of values.

063, 683,091
spaces could be

063, 683,091
063,683 ,091
063, 683, 091


Public Function RemoveSpace(ByVal strData As String) As String
Dim intPosition As Integer
intPosition = InStr(1, strData, ", ")
If intPosition > 0 Then
RemoveSpace = Left(strData, intPosition - 1) & Mid(strData, intPosition + 2, Len(strData) - intPosition + 2)
Else
RemoveSpace = strData
End If
End Function

Nothing is getting amended if someone can help. id appreciate it.

Hi, I have an Access front end with a SQL Server backend and need to able to call a User define function in a query. To do this I need to create the function but I can't get the right syntax.

	Code:
	CREATE FUNCTION BCL_GetForeignKeyCode(@param1 as varchar(16))  
RETURNS Integer
AS  
BEGIN 
	declare @intPos Integer;
	declare @sTempValue varchar(16);
	declare @sNumber Integer;
	declare @iRet Integer;
	declare @iRett Integer;
	declare @sRet varchar(16);
	declare @sStr varchar(16);

	set  @iRet = 1;
	set @iRett = 1;

	While @sStr "O"
	Begin
		 @sStr=SubString(@param1,@iRet,1)
		 @sTempValue=@sTempValue +  @sStr
		 @iRet=@iRet + 1
	End
            'Convert from Hex to double ??? How
	Set @sNumber = "&H'" & @sTempValue;
       	 
	While @iRett

Hi,
I’m new to this forum (and to Access really), so please forgive anything that needs forgiving. J
I have got to be missing something really obvious here.
What I have:
I have a listbox on a form containing ‘key areas’ that a user can select. I have a public function in a module that returns a string of the key area IDs selected (separated by commas):
Public Function GetStrList() As String

Dim varItem As Variant
Dim strList As String
For Each varItem In Forms!frm_SearchForm!listbx_KeyArea.ItemsSelected
strList = strList & Forms!frm_SearchForm!listbx_KeyArea.Column(0, varItem) & ","
Next varItem

strList = Left(strList, Len(strList) - 1)

GetStrList = strList

End Function

I have a query, which has the following criteria for [KeyAreaID]: In (GetStrList())
I want this query to return records that contain any of the key areas the user selects.

The issue:
When I select more than one key area, the query returns no results, even tho the function GetStrList() appears to be returning an appropriate string.
To test this, I created a text box on a form, where Control Source = GetStrList(). If I view the form in form view, copy what’s displayed in the textbox, and paste to replace the actual function in the criteria (so the query criteria might now look like: In(5,6,7)), it works!
Does anyone know why my query doesn’t like my function? Why if I paste the results of the function into the criteria it works, but not if I call the function directly?
Any help would be much appreciated! I hope I was able to describe the problem adequately…
Thank you in advance!!

Hi All,

I've not tried to use a function when building an expression before so please bear with me if I am missing something obvious!

I have the following function which is placed in a query to return a concatenated string depending on which comboboxes are currently visible on the form (the idea being to fire a code on the next record button to check for a duplicate of the entire text string).

I seem to be having two issues with it:
1. The code seems to build twice the data that is needed ie [FIELD1]&[FIELD2]&FIELD3][FIELD1]&[FIELD2]&FIELD3] (no '&' in between the two sets) and I can't figure out why. It doesn't do this when I check it by stepping through (that I can see) but does when it is fired from the query. This I can kind of fix by dividing the text in two (as in the code) but I'm not sure if this may be the root of my issue.

2. The function does not return any data. Again if I step through and copy the information from the immediate window and place it in the query I can get the desired information, however if I allow it to work itself I get nothing!

Any ideas where I should be looking?


	Code:
	Public Function DuplicateStrChk()
Dim Ctrl As Control
Dim strDup As String
        strDup = ""
        For Each Ctrl In Forms![myForm].Controls
        If Ctrl.ControlType = acComboBox Then
        If Ctrl.Visible = True Then
        strDup = strDup & "[" & Ctrl.Name & "]&"
        End If
        End If
        Next
 
        If Right(strDup, 1) = "&" Then
        strDup = Left(strDup, Len(strDup) - 1)
        strDup = Left(strDup, Len(strDup) / 2)
        strDup = strDup
        End If
End Function



I created a form that has a list box and based on which items you select it will open another form for editing and filter so that only those items can be edited. It works really well. (The idea came from John Viescas who wrote a book for microsoft.)

I'd like to use this same form to print a certain report for all the items that one selects. I wrote the program in the OnClick to write the sql statement that creates a simple sub-query, and I put this string in the control source of the report. I thought it would just read the code and understand that it is a sub-query, but it said it couldn't find the record source. I've tried to insert the statement directly into the query. Any ideas???

This is the code:

1. I have a public module:

Public gstrAssets As String

Public Function GetAssetID() As String
GetAssetID = gstrAssets
End Function

2. I have this function in the OnClick()

Private Sub Asset_Maintenance_Report_Click()
On Error GoTo Err_Asset_Maintenance_Report_Click
Dim strWhere As String, varItem As Variant

' If no items selected, then nothing to do
If Me!lstAssets.ItemsSelected.Count = 0 Then Exit Sub
' Loop through the items selected collection
For Each varItem In Me!lstAssets.ItemsSelected
' Grab the AssetID column for each selected item
strWhere = strWhere & Me!lstAssets.Column(0, varItem) & ","
Next varItem
' Trash the extra comma on the "IN" string
strWhere = Left$(strWhere, Len(strWhere) - 1)
' Write a sub-query in sql
gstrAssets = "SELECT [Asset Maintenance Query].* FROM [Asset Maintenance Query] " & _
"WHERE ((Assets.AssetID) IN (" & strWhere & "));"
' Open the form to enter the two fields needed for the report
DoCmd.openform ("Asset Maintenance")
DoCmd.Close acForm, Me.Name


Exit_Asset_Maintenance_Report_Click:
Exit Sub

Err_Asset_Maintenance_Report_Click:
MsgBox Err.Description
Resume Exit_Asset_Maintenance_Report_Click

End Sub

Can anyone help me please.

I have been trying to create a report by using a query to include those records specified by the user. One field of a table can contain a number of letters eg
ABC, ADB, CDEB, or DEA etc. I want the user to be able to enter *their* selection of letters (in any order), and then match *any* of their chosen letters with those held in the field so if they entered AD then in my example above it would match on fields 2 and 3 as they both contain at least one of these letters. I don't believe that it can be done with a normal query string, and so a function has been suggested to me.

This is as far as I have got;

Public Function InChars(ByVal strLookFor As String, ByVal strInField As String) As Boolean
Dim i As Integer
InChars = False
For i = 1 To Len(strLookFor)
If InStr(strInField, Mid(strLookFor, i, 1)) = 1 Then
InChars = True
Exit For
End If
Next
End Function

Where in the field of my query I have the following;

Values: InChars([Enter letters:],[TableField])

This gives the user a parameter box into which they can enter their choice of letters. But the table that is created from this contains ALL records from the original table, and not those that were true -1. Athough the records that should match do contain a -1 whereas non matches contain a 0 or an #Error if the field was empty. What I want is for the table to contain *only* those records where they match ie that are currently marked as -1

I've got this far and am probably missing something obvious - can anyone help please.

Dave

Hi,

I am having trouble using these functions together in a query. I have a set of data that could be 3 char or could be 4 char. If it is only 3 char, I need to put a zero in front.

I am trying:

IIf(Len([MyField]=3,"0"&[MyField],[MyField]))

But am getting an error saying that my expression has the wrong number of arguments.

Any help much appreciated.

Cheers,
Kirsti

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)
Else
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
'case-insensitive
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
Else
InParam = 0
End If
Loop
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've used OOP before and some VBA lately, but I'm still not sure how to create a function from the raw ether.
I want to take a street address and return merely the city block, i.e. 2412 Main St. becomes 2400 Main St. Apartments are always delimited by commas, i.e. 12402 Maple Dr., Apt. 101 becomes 12400 Maple Dr.

I can do this in a query, however the two different cases perplex me. Here's what I have so far:

Public Function BlockLocator(OriginalAddress As String)
Dim varLocation, varL1, varL2 As String
Dim varS, varS1, varS2 As String

If IsNull(OriginalAddress) Then End Function

varS = Len([OriginalAddress])
varS1 = InStr(0, [OriginalAddress], " ")
varL1 = Left([OriginalAddress], varS1 - 3)
varL2 = Right([OriginalAddress], varS - varS1)
varS2 = InStr(0, varL2, ",", 1)

If (varS2 > 1) Then End Function

If (varS2 = 1) Then varL2 = Left(varL2, varS2 - 1)

varLocation = varL1 + "00 " + varL2

BlockLocator = varLocation

End Function

How do I debug test this? In theory it's going to become part of an SQL query so that I can interface with the police blotter.

Need help implementing way to obtain Median grouped by 2 columns and filtered by date in a Crosstab query.

We use Access as front end reporting tool with linked tables to SQL Server 2008 backend db. Management wants a time analysis report of each performer by median (in seconds) per PO lines of invoices with 1 to 10 lines only. We will later be doing a volume analysis to account for invoices with lines greater than 10. I kinda have the DMedian VBA function I got from MSDN site working, but I can’t figure out how to:

1. group by PERFORMER and then by NUM_LINES in the set strSQL = … line(s) of code in the VBA module(in seconds)
2. call DMedian function from within the Crosstab query.

I have attached (zipped per rules for new register users with less than 10 posts) a temporary db I threw together for sole purpose of trying to explain and use as test to get this Median issue worked out. The table, tblmedian_test_data, was dump of some test data from actual db. The crosstab query, qry_median_avg_test_1, is where I need to get this median thing worked out.
The db has a few VBA functions I started to play with trying to figure this out. The one I got from MSDN was commented better than any of the others I’d seen but at this point I don’t care which one we use. I just need to get this working. Lastly I am including the sql for the crosstab query and the VBA code for the DMedian function. Again there are other functions in db but this is one I’ve been trying to get to work.

I welcome any and all help!


	Code:
	TRANSFORM Nz(Avg(DMedian("SECONDS","tbl_median_test_data")),0) AS CrossTabAvgMedian
SELECT tbl_median_test_data.NUM_LINES
FROM tbl_median_test_data
WHERE (((tbl_median_test_data.APPROVE_DATE)>=#1/1/2013# And (tbl_median_test_data.APPROVE_DATE) 0 Then
        'strSQL = strSQL & " WHERE " & strCriteria
        strSQL = strSQL & " GROUP BY " & strCriteria
    End If
    
    strSQL = strSQL & " ORDER BY " & strField
    
    Set rstDomain = db.OpenRecordset(strSQL, dbOpenSnapshot)
    
    ' Check the data type of the median field
    intFieldType = rstDomain.Fields(strField).Type
    Select Case intFieldType
    Case dbByte, dbInteger, dbLong, dbCurrency, dbSingle, dbDouble, dbDate
        ' Numeric field
        If Not rstDomain.EOF Then
            rstDomain.MoveLast
            intRecords = rstDomain.RecordCount
            ' Start from the first record
            rstDomain.MoveFirst
    
            If (intRecords Mod 2) = 0 Then
                ' Even number of records
                ' No middle record, so move to the
                ' record right before the middle
                rstDomain.Move ((intRecords  2) - 1)
                varMedian = rstDomain.Fields(strField)
                ' Now move to the next record, the
                ' one right after the middle
                rstDomain.MoveNext
                ' And average the two values
                varMedian = (varMedian + rstDomain.Fields(strField)) / 2
                ' Make sure you return a date, even when
                ' averaging two dates
                If intFieldType = dbDate And Not IsNull(varMedian) Then
                    varMedian = CDate(varMedian)
                End If
            Else
                ' Odd number or records
                ' Move to the middle record and return its value
                rstDomain.Move ((intRecords  2))
                varMedian = rstDomain.Fields(strField)
            End If
        Else
            ' No records; return Null
            varMedian = Null
        End If
    Case Else
        ' Non-numeric field; so raise an app error
        Err.Raise errAppTypeError
    End Select
    DMedian = varMedian
    
ExitHere:
    On Error Resume Next
    rstDomain.Close
    Set rstDomain = Nothing
    Exit Function
HandleErr:
    ' Return an error value
    DMedian = CVErr(Err.Number)
    Resume ExitHere
End Function



Why am I receiving #Error when running my median query? Below are the code for 1) UserMedian query, 2) VBA MedianG code, and 3) visual of query results (once I end/sto debug "Run-time error '3061': Too few parameters. Expected 4." msg that appears. This median function and median query runs just fine in my local test db, but will not run in our live/prod db. I know I need to eventually add some error handling within VBA function code, but does anyone have any suggestions, ideas, thoughts why I'm getting the #Error results?


	Code:
	SELECT qry_MLMStatsLineCountTimeDurationDetail10Lines.PERFORMER,
MedianG("qry_MLMStatsLineCountTimeDurationDetail10Lines","SECONDS",[qry_MLMStatsLineCountTimeDurationDetail10Lines].[PERFORMER],"1")
AS Line1Median,
MedianG("qry_MLMStatsLineCountTimeDurationDetail10Lines","SECONDS",[qry_MLMStatsLineCountTimeDurationDetail10Lines].[PERFORMER],"2")
AS Line2Median,
MedianG("qry_MLMStatsLineCountTimeDurationDetail10Lines","SECONDS",[qry_MLMStatsLineCountTimeDurationDetail10Lines].[PERFORMER],"3")
AS Line3Median,
MedianG("qry_MLMStatsLineCountTimeDurationDetail10Lines","SECONDS",[qry_MLMStatsLineCountTimeDurationDetail10Lines].[PERFORMER],"4")
AS Line4Median,
MedianG("qry_MLMStatsLineCountTimeDurationDetail10Lines","SECONDS",[qry_MLMStatsLineCountTimeDurationDetail10Lines].[PERFORMER],"5")
AS Line5Median,
MedianG("qry_MLMStatsLineCountTimeDurationDetail10Lines","SECONDS",[qry_MLMStatsLineCountTimeDurationDetail10Lines].[PERFORMER],"6")
AS Line6Median,
MedianG("qry_MLMStatsLineCountTimeDurationDetail10Lines","SECONDS",[qry_MLMStatsLineCountTimeDurationDetail10Lines].[PERFORMER],"7")
AS Line7Median,
MedianG("qry_MLMStatsLineCountTimeDurationDetail10Lines","SECONDS",[qry_MLMStatsLineCountTimeDurationDetail10Lines].[PERFORMER],"8")
AS Line8Median,
MedianG("qry_MLMStatsLineCountTimeDurationDetail10Lines","SECONDS",[qry_MLMStatsLineCountTimeDurationDetail10Lines].[PERFORMER],"9")
AS Line9Median,
MedianG("qry_MLMStatsLineCountTimeDurationDetail10Lines","SECONDS",[qry_MLMStatsLineCountTimeDurationDetail10Lines].[PERFORMER],"10")
AS Line10Median
FROM qry_MLMStatsLineCountTimeDurationDetail10Lines
GROUP BY qry_MLMStatsLineCountTimeDurationDetail10Lines.PERFORMER
ORDER BY qry_MLMStatsLineCountTimeDurationDetail10Lines.PERFORMER;


	Code:
	Function MedianG(ptable As String, pfield As String, Optional pgroup1 As String, Optional pgroup2 As String) As Long
'*******************************************
'Purpose:   Return median value from a recordset
'Inputs:    MedianG("tableorqueryname", "numericfieldnametogetmedian", "performervalue", "linesvalue") 
'Output:    ideally the median value per performer/lines combination
'*******************************************
Dim Db As DAO.Database
Dim rs       As DAO.Recordset
Dim strSQL   As String
Dim n        As Double
Dim sglHold  As Long
Set Db = CurrentDb()
    'If pgroup1 provided then add it to strSQL
    If Len(pgroup1) > 0 Then
        strSQL = "SELECT " & pfield & " from " & ptable & " WHERE PERFORMER = '" & pgroup1 & "' Order by " & pfield & ";"
        'If pgroup2 provided then add it to strSQL
        If Len(pgroup2) > 0 Then
            strSQL = "SELECT " & pfield & " from " & ptable & " WHERE PERFORMER = '" & pgroup1 & "' AND NUM_LINES = " &
pgroup2 & "  Order by " & pfield & ";"
        End If
    Else
        strSQL = "SELECT " & pfield & " from " & ptable & " Order by " & pfield & ";"
    End If
    'Debug.Print strSQL
    Set rs = Db.OpenRecordset(strSQL)
    rs.MoveLast
    n = rs.RecordCount
    rs.Move -Int(n / 2)
    If n Mod 2 = 1 Then 'odd number of elements
       MedianG = rs(pfield)
    Else                'even number of elements
       sglHold = rs(pfield)
       rs.MoveNext
       sglHold = sglHold + rs(pfield)
       MedianG = sglHold / 2
    End If
    
    rs.Close
End Function



Hello.

I have this working seach function it's querying a invoice based on three tables. invoice, customer and invoice details. mainly this seach funtion is made to find a particualar invoice. BUT i would like to be able to get a value from invoice detals, the value i want is the serial nuber on the component sold. So if a product breaks down i'd be able to search for the S/N and get the invoice number and the RMA request would be alot more simple. but im a little to green on VB to get it to work as soon as i enter another table in the code. so i was thinking I'd base the seach function on a query based on the tables i want. but im not getting that to work either. pasting some of the code here. If someone knows how to make it work i'd be very happy =)

Private Sub cmdSearch_Click()

Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

strSQL = "SELECT BUTIKSERVICE.servicedetnr, BUTIKSERVICE.Nr , BUTIKSERVICE.serienummer , BUTIKSERVICE.Referens , BUTIKSERVICE.Märke " & _
"FROM BUTIKSERVICE"

strWhere = "WHERE"

strOrder = "ORDER BY BUTIKSERVICE.servicedetnr;"

If Not IsNull(Me.txtnamn) Then
strWhere = strWhere & " (BUTIKSERVICE.Nr) Like '*" & Me.txtnamn & "*' AND"

End If

If Not IsNull(Me.txtfaktnr) Then
strWhere = strWhere & " (BUTIKSERVICE.servicedetnr) Like '*" & Me.txtfaktnr & "*' AND"
End If

If Not IsNull(Me.txtsn) Then
strWhere = strWhere & " (BUTIKSERVICE.serienummer) Like '*" & Me.txtsn & "*' AND"
End If

strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

Me.lstfakt.RowSource = strSQL & " " & strWhere & "" & strOrder

End sub
_____________________

the best thing for me would be if someone could tell me how to make this work querying the info from a query instead of from a table as it is today.

I found a function to concatenate the results of a sql query. I want to use it as an aggregate string function in a sql statement. The function is:

module: 'modConcat' code:
------------------------------------------------


	Code:
	Option Compare Database
Option Explicit

Public Function Concat(pstrSQL As String, Optional pstrDelim As String = ",") As String
    'example
    'tblFamily with FamID as numeric primary key
    'tblFamMem with FamID, FirstName, DOB,...
    'return a comma separated list of FirstNames
    'for a FamID
    '    John, Mary, Susan
    'in a Query
    'SELECT FamID,
    'Concatenate("SELECT FirstName FROM tblFamMem
    '     WHERE FamID =" & [FamID]) as FirstNames
    'FROM tblFamily
    '

    '======For DAO uncomment next 4 lines=======
    '======     comment out ADO below    =======
    'Dim db As DAO.Database
    'Dim rs As DAO.Recordset
    'Set db = CurrentDb
    'Set rs = db.OpenRecordset(pstrSQL)

    '======For ADO uncomment next two lines=====
    '======     comment out DAO above     ======
    Dim rs As New ADODB.Recordset
    'MsgBox pstrSQL
    rs.Open pstrSQL, CurrentProject.Connection, _
            adOpenKeyset, adLockOptimistic
    Dim strConcat As String 'build return string
    With rs
        If Not .EOF Then
            .MoveFirst
            Do While Not .EOF
                strConcat = strConcat & _
                    .Fields(0) & pstrDelim
                .MoveNext
            Loop
        End If
        .Close
    End With
    Set rs = Nothing
    '====== uncomment next line for DAO ========
    'Set db = Nothing
    If Len(strConcat) > 0 Then
        strConcat = Left(strConcat, _
            Len(strConcat) - Len(pstrDelim))
    End If
    Concat = strConcat
End Function

-------------------------------------------------------------
The sample sql statement I am trying to execute is:

SELECT DISTINCT [Name], Concat("SELECT Position FROM BoardMembers WHERE Name='"+[Name]+"' ",'') AS Positions
FROM BoardMembers;

When I use that sql in a query in access, I get the error:
Method 'Open' of object '_Recordset' failed

And when I tuse the sql call on my asp webpage, I get the error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Undefined function 'Concat' in expression.

The database works fine, and the sql is tracing correctly. My ultimate goal is to use this Concat function on from a sql statement on the webpage, but I get the undefined function error. Do you know how to make the function call work?

Hello,

I am a beginner in using modules and functions in Access and I have tried to find a solution for this problem without success.

I have tried to use a Percentile Function and call it from a query but I always get this error:

Undefined function in expression. (Error 3085)

The Function that I took from the internet is as follows:

-----------------------------------

Public Function Percentile(fldName As String, _
tblName As String, p As Double, _
Optional strWhere As String = "") _
As Double

'Note tblName can also be the name of a query or view

'ADO
'Dim cnn As ADODB.Connection
'Dim rst As ADODB.Recordset
'DAO
Dim Cdb As Database
Dim rst As Recordset
'Other Vars
Dim break_pt As Double
Dim sqlSort As String
Dim low_obs As Long, high_obs As Long
Dim r1 As Double, r2 As Double, x As Double
Dim N As Long
Dim recno As Long


'VERIFY VALID PERCENTILE (0-100) WAS GIVEN
If (p = 100) Then
Percentile = -555555555 'Something to stick out!
Exit Function
End If

'ENSURE DESIRED DATA IS SORTED
If Len(strWhere) < 1 Then
sqlSort = "SELECT [" & fldName & "] " & _
"FROM [" & tblName & "] " & _
"ORDER BY [" & fldName & "]"
Else
sqlSort = "SELECT [" & fldName & "] " & _
"FROM [" & tblName & "] WHERE " & _
strWhere & _
" ORDER BY [" & fldName & "]"
End If

'ADO
'Set cnn = CurrentProject.Connection
'Set rst = New ADODB.Recordset
'rst.Open sqlSort, cnn, adOpenStatic, adLockReadOnly, 1
'DAO
Set Cdb = CurrentDb()
Set rst = Cdb.OpenRecordset(sqlSort, dbOpenSnapshot)
rst.MoveLast

'How many observatons? For example, N=12
N = rst.RecordCount

'Which observation would, theoretically, _
be the pTH "true" percentile.
'e.g., for 25th percentile would be _
the 0.25*(12+1)=3.25th observation
break_pt = (p / 100) * (N + 1) '3.25 = (25/100)*(12+1)

'There's 2 special extreme cases we need to worry about!

'small sample for small percentile
If break_pt < 1 Then break_pt = 1
'small sample for large percentile
If break_pt > N Then break_pt = N

'But since there's no such thing as a _
3.25th observation, we estimate it _
'somewhere between the 3rd and 4th observations. _
It'll be approximately: p = r1*low_obs + r2*high_obs

low_obs = Int(break_pt) '3 = int(3.25)
high_obs = low_obs + 1 '4 = 3 + 1

'Now, we have to interprolate between the "boundaries"
r1 = high_obs - break_pt '0.75 = 4 - 3.25
r2 = 1 - r1 '0.25 = 1 - 0.75

'Since we have determined the needed _
observations and their weights we can move to the _
projected low observation and loop _
through the record set until we reach the _
high observation to calc the resulting percentile

'DAO, where absolution position is 0 based
rst.AbsolutePosition = low_obs - 1: recno = low_obs - 1
'ADO where absolution position is 1 based
'rst.AbsolutePosition = low_obs: recno = low_obs - 1
DoEvents

Do Until rst.EOF
recno = recno + 1
If recno = low_obs Then x = r1 * rst(0)
If recno = high_obs Then
x = x + r2 * rst(0)
Exit Do
End If
rst.MoveNext
Loop
'We now have our percentile!
Percentile = x

rst.Close
Set rst = Nothing
'DAO
Set Cdb = Nothing
'ADO
'Set Conn = Nothing

End Function

Public Sub test()
Dim strMsg As String
strMsg = "25th = " & Percentile("YourField", "YourTable", 25) _
& vbNewLine & _
"Median = " & Percentile("YourField", "YourTable", 50) _
& vbNewLine & _
"75th = " & Percentile("YourField", "YourField", 75)
MsgBox strMsg
End Sub

-------------------------------


The module with the fucntion is correctly present in the database.

I used the Builder tool and the query SQL view shows the following:


-----------------------

SELECT Percentile([AlexaRank],[Table1],0.25,[«strWhere»]) AS Expr1
FROM Table1;

-----------------------


I tried with several other functions but I always get the same error. The only exception is when I use the built-in functions already available. They work with no problem.

Can anybody please direct me in the right track? Please consider that I am a beginner.

Cheers,

FF

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


	Code:
	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)
    Else
        strCriteria = "Tbl_Payment_YTD.[Rebate Period] Like '*'"
    End If
    
' Open the query
    DoCmd.OpenQuery "Query"

Set db = Nothing

End Sub

Module:

	Code:
	Option Compare Database
Option Explicit

Public strCriteria As String

Public Function ReturnStrCriteria() As String

ReturnStrCriteria = strCriteria

End Function




Not finding an answer? Try a Google search.