Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2016
    Posts
    4

    Question Unanswered: Text/Date time conversion in queries

    I'm working with a SQL back end/ Access front end database.

    I'm trying to create a query that returns everything between two dates, user specified. The fields are text fields (not my idea - I inherited it this way; and I'm going to be given all kinds of grief changing it).

    In the query, for
    Field D1: IIf(IsDate([Distribution]),DateValue([Distribution]),Null)
    grouped by
    ascending
    Criteria Between [begin date?] And [end date?]

    So I put in 1/1/2015 for begin date 3/1/15 for end date... my result is pretty much anything that has months that start with 1, 2, 3 dating back to 2004 to present time. In perfect ascending order.

    Ideas... I'm beating my head on the desk on this one.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Is the query executed in Access or is it a pass-through query (executed by the server)?
    If you can use a pass-through query, nothing prevents you from convertings the value there:
    Code:
    SELECT..., CAST([Distribution] AS SMALLDATETIME) AS Distribution, ...
    Have a nice day!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Equally nothing prevents you from using cdate inside a standard Access query.
    But the real solution is which ever muppet designed this needs taking out of their place of working and go for a long walk in say a game reserve in Africa wearing nothing but, say raw meat, holding nothing more than a happy grin.

    Likewise the DBA (s) who permitted this design and / or allowed it to continue need naming and shaming so they dont work again unless its making MuckBurgers.

    But apart from that. Assuming you are using passs through queries Sinndho's solution is the right way out of this mess, assuming your employers arent prepared to fix rather than patch over the problem
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Feb 2016
    Posts
    4
    The reason they did this was so they could have more than one date in one field. It doesn't happen often, but it does rarely. Alot of my issues have been trying to take a fairly complex database, and correct it, when I don't have access to the actual SQL fields/datatypes. And this is more of my homework outside of regular work if you will.

    I'll do some reading on pass through. Honestly, my SQL is shaky, at best, and typically use the query design to work in instead of SQL view. I'm not in IT, and have never had "formal training" in SQL/Access.

    Just out of curiosity, it sounds like pass-throughs could speed up our database reports (which will take around 30 minutes to run a lot of times). Is this the case? If so, it might be something for me to seriously look into.

    I did finally get something to work as a "work-around" but not really ideal.

    D1: IIf(IsDate([Distribution]),Format([Distribution],"yyyy/mm/dd"),"Null") ... my to/from date have to be entered in that format. (I had also tried Cdate as was suggested, and it was still giving me a whole bunch of different dates outside of the range)

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Ok so you arent using pass through queries....
    ....almost certainly you should. Especially if its taking too long.
    Using a server backend and Access front end is fine, assuming you pkay to the strengths of both. A pass through query means run tbe query on the server not the local machine. The server then sends only the required data to the Access front end meaning less local processing, less network traffic.. win win.

    Using a server backend with Access front end means running queries on the local machine more network traffic, not playing to the strengths of using a server db. (the server has to send the indexes AND data to the local machine for local processing. essentially you end up using the serve as nothing more than a file server.

    The excuse proffered by the server dba raises serious issues in my mind of the calibre (and or competence) of your DBAs. Its a crappy design. If there can be two dates, then there should be two date columns. whats been doen sounds alike a classic fudge. heck it may even date back to the previous generation of computer system as a quick and dirty work around to a pressign problem, the issue that a quick and dirty fix is often very expensive in the medium tolong term

    So for now use the CDATE function to do the equivalent of the CAST suggested by Sinndho.
    But because you may have more than one date you need to be a bit more creative.
    You need to limit the text to a valid date. But the date length varies depending on the month and or day number.
    But the problem is that its tough to write a means of handling one or two dates (which could be variable length, again the authors of this mess have compounded the scope for probloems by using 1..12 instead of 01 to 12 for month, the same for days, its trickier if that also means you have to handle NULLS. Offhand i cant think of an easy way to handle this. Frankly its a totally uneccesary nightmare.

    if you had just the one date then its trivial
    cdate(mycolumn)
    if you want the first date then its a bit more fiddly
    cdate(left(mycolumn,instr(mycolumn," ")) 'searches for the first occurance of a space in the specified column, but that will fail with single date values UNLESS you embrace the spirit of creating monumental fudges and patches to cover up poor design and say add a trailing space to every date
    alternatively you could write a function that handles it for you, but that means using local (JET) queries, for which there is little point in using a server backend. that function could handle any case, but at a cost. it means running the function every time you need the date value extracting. whether you run the function in a local query or form or report is your call.

    the 'correct' solution is to develop a workaround for the current problem but in the medium to long term fix the problem by using proper design techniques... get rid of the notion that dates and or times can or should be stored in char/text/string columns. that also applies to numeric values. pay the pain of converting the data once when the data is first captured, not each and every time you need to use or manipulate the data
    Last edited by healdem; 02-18-16 at 06:22.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Feb 2016
    Posts
    4
    It's taking so long because of the CONCAT we have in several queries that all feed in to a report (there are 5 of these). Take those out, and everything runs in a jiffy. Put those in, and we are w...a...i...t...i...n...g.

    I'm looking in to the pass through queries, and have been able to convert some of our smaller queries over with no effort on my part. Now to get my brain back into the coding mode and experiment with some of the more complex ones to see what kind of time savings we get from it.

    The people working with the database here have about the same level of database expertise as me - zilch. We are all self taught, and not in the IT field. Our IT people have been little help (and typically specialize in Oracle), so honestly I don't necessarily fault the people before me. I'm just trying to use the database for reporting purposes, where in the past it wasn't necessarily used primarily as that task.

    I have converted over several sets of fields (hence the reason we have five CONCAT queries, where it used to be all as one field). Just have to pace myself I suppose...

    So can I run pass through queries on a connected Oracle database also? Would it be the same t-sql or different coding (I forgot since we just recently tapped into this, our front end is Access, back end that we keep all our data is SQL, with a read only connection to an Oracle database). Basically several of my expressions are IIF statements... if the field in the Oracle database is empty, look in the SQL database. Is that possible to do as a pass through?

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by purpleangelbear View Post
    So can I run pass through queries on a connected Oracle database also? Would it be the same t-sql or different coding (I forgot since we just recently tapped into this, our front end is Access, back end that we keep all our data is SQL, with a read only connection to an Oracle database). Basically several of my expressions are IIF statements... if the field in the Oracle database is empty, look in the SQL database. Is that possible to do as a pass through?
    Sure it is ! The syntax wil be slightly different (Oracle and SQL Server "speak" different SQL "dialects") and the connection string (the Connect property of the QueryDef object) will be different too. In both cases, the IT people you mentioned (those specialized in Oracle) should be able to help you. If not, do not hesitate to come back here for more help.
    Have a nice day!

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    taking so long because of the CONCAT?

    ..what strings are you concatenating, why should a concat consume 'so much' more time

    ...there is something very very odd about the queries in my books.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Feb 2016
    Posts
    4
    You mean it isn't supposed to? If this could be fixed, this would be a HUGE help to us! We have four tables that we concat into five different fields in query/report. Without the five concat fields it takes about 3 minutes to run. With them in there it takes about 30-45 minutes to run.

    The first table has 6373 rows in it; the second one has 4903; the third one 12930; and the last one has 3345.


    Looks like the VBA is adapted from allenbrowne.com (I'm guessing):

    Code:
    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
                    rsMV.MoveNext
                Loop
                Set rsMV = Nothing
            ElseIf Not IsNull(rs(0)) Then
                strOut = strOut & rs(0) & strSeparator
            End If
            rs.MoveNext
        Loop
        rs.Close
        
        'Return the string without the trailing separator.
        lngLen = Len(strOut) - Len(strSeparator)
        If lngLen > 0 Then
            ConcatRelated = Left(strOut, lngLen)
        End If
    
    Exit_Handler:
        'Clean up
        Set rsMV = Nothing
        Set rs = Nothing
        Exit Function
    
    Err_Handler:
        MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "ConcatRelated()"
        Resume Exit_Handler
    End Function


    One of the queries (though they all differ based on what we are looking for):
    New Application Date: IIf(Nz([QRY_APP_MAXDATE]![MaxOfApplication Review and Signed],0)<[QRY_ASMT_NOPERM_MAXDATE]![MaxOfASMT Sent to Design] And [QRY_ASMT_NOPERM_MAXDATE]![ASMT COMMENTS]="NO PERMITS REQUIRED","N/A",ConcatRelated("[Application Review and Signed]","Applications","PIN = """ & [Applications]![PIN] & """","[Application Review and Signed] DESC"))

    This query alone takes about ten minutes to run...



    Quote Originally Posted by healdem View Post
    taking so long because of the CONCAT?

    ..what strings are you concatenating, why should a concat consume 'so much' more time

    ...there is something very very odd about the queries in my books.

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Ok take a step out of the trenches. In English what are you trying to do here?
    Where are you calling this function
    Can yoy give a couple of examples of what you are trying to do and what the result should be.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •