Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    string within a string (A2k2, AP2)

    Hi All,
    My problem:
    <pre>strTMName = "SELECT [MyField] "
    strTMName = strTMName & "FROM qryMyQuery "
    strTMName = strTMName & "WHERE INumberPKID = " & Me!lngNumberPKID

    strCHName = "SELECT [MyOtherField] "
    strCHName = strCHName & "FROM qryMyOtherQuery "
    strCHName = strCHName & "WHERE OtherNumberPKID = " & Me!lngOtherNumberPKID

    strExMessage = "What I want to be able to do is to " & vbCrLf & _
    "use the 2 strings above, such as '" & strTMName & "'" & " or " & vbCrLf & _
    "this " & """" & strCHName & """" & " for their values."
    </pre>

    But what I end up with is the the entire SQL statement like so:
    <pre>"SELECT [MyField] FROM qryMyQuery WHERE NumberPKID = " & Me!lngNumberPKID
    </pre>

    in place of strCHName or strTMName.

    I've tried all the different quotation patterns that I know of, Chr(34) as well. When using Debug.Print on the first 2 strings and paste that into a query, it returns the correct 'answer' (Text, btw). I am using this for an invisible text box that will be used on a report as a type of "updateable caption", depending on certain entries into the form.
    I really thought I had done this before but I cannot find where I might have done it. It's also quite late here <img src=/S/yawn.gif border=0 alt=yawn width=15 height=15> so I just may be dreaming by now.
    Thanks in advance for info on if and how this can be done.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: string within a string (A2k2, AP2)

    Are you trying to enclose entire SQL string in double-quotes (including actual value of lngNumberPKID in WHERE clause)? If that is case, you could use something like this example:

    Public Function GetSQLString(strFld As String, strQry As String, _
    lngRecordID As Long) As String

    Dim strSQL As String
    Dim strMsg As String

    strSQL = "SELECT <!t>[" & strFld & "]<!/t> " & _
    "FROM <!t>[" & strQry & "]<!/t> " & _
    "WHERE <!t>[RecordID]<!/t> =" & lngRecordID & ";"

    strSQL = Chr(34) & strSQL & Chr(34)
    GetSQLString = strSQL

    ' For test purposes:
    strMsg = "This is the SQL String: " & strSQL & " generated by this function."
    MsgBox strMsg, vbInformation, "SQL STRING"
    Debug.Print strMsg

    End Function

    Example of use (called from Immediate window):

    ? GetSQLString("Field1", "Query1", 1000)
    This is the SQL String: "SELECT [Field1] FROM [Query1] WHERE [RecordID] =1000;" generated by this function.
    "SELECT [Field1] FROM [Query1] WHERE [RecordID] =1000;"

    Note that you can simplify things by constructing SQL string with one statement, using line continuation characters, as shown above. If called from form, the function's arguments can be provided by form control values or properties where applicable.

    HTH

  3. #3
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: string within a string (A2k2, AP2)

    Hi Mark,
    Thanks for replying.
    That's my problem. I am returning the SQL string when I want to just have the value returned.
    Here is the actual coding I am using.
    '---------------------------------------------------------------------------------------------------------------------------------
    Set rstAssay = db.OpenRecordset("tblTempAssay")

    strClientRace = "SELECT [ClientRace] "
    strClientRace = strClientRace & "FROM qryIGICaseNumberClientAssayReportDetails "
    strClientRace = strClientRace & "WHERE IGICaseNumberPKID = " & Me!lngIGICaseNumberPKID
    strClientRace = strClientRace & " AND PersonDesignator Like 'TM*'"

    strTMName = "SELECT [ClientFirstLastName] "
    strTMName = strTMName & "FROM qryIGICaseNumberClientAssayReportDetails "
    strTMName = strTMName & "WHERE IGICaseNumberPKID = " & Me!lngIGICaseNumberPKID
    strTMName = strTMName & " AND PersonDesignator Like 'TM*'"

    strCHName = "SELECT [ClientFirstLastName] "
    strCHName = strCHName & "FROM qryIGICaseNumberClientAssayReportDetails "
    strCHName = strCHName & "WHERE IGICaseNumberPKID = " & Me!lngIGICaseNumberPKID
    strCHName = strCHName & " AND PersonDesignator Like 'CH*'"

    dblProbPat = FlexRound((((PIProbabilityProduct2("tblTempAssay", rstAssay![LabDataPKID])) * 0.5) / & _
    (((PIProbabilityProduct2("tblTempAssay", rstAssay![LabDataPKID])) * 0.5) + 0.5)), 10) * 100

    strExclusionMessage = "Alleles are displayed in the child's DNA sample that are not detected in the tested man's " & vbCrLf & _
    "DNA sample. This data supports the conclusion that " & strTMName & " is excluded as the biological " & vbCrLf & _
    "father of " & strCHName & "."

    strInclusionMessage = "The probability of paternity, using the " & strClientRace & "-American database " & vbCrLf & _
    "(assuming a prior probability or 0.5), is " & dblProbPat & "." & vbCrLf & _
    "These data support the conclusion that " & strTMName & " cannot be excluded as the biological father " & vbCrLf & _
    "of " & strCHName & "."

    MsgBox strInclusionMessage, vbOKOnly, "Test"
    '---------------------------------------------------------------------------------------------------------------------------------------------
    Wherever strTMName, strCHName, strClientRace are in strExclusionMessage or strInclusionMessage they show as the SQL string, i.e. "SELECT xxx FROM xxx " etc. When I need the value returned by the SQL string, i.e. strTMName should return a Client's name, strClientRace should return the Client's race, etc. dblProbPat, however, returns the proper value, a percentage (double).
    I've tried numerous variations of quotes, single and double, Chr(34) and the syntax shown in the above code. Each results in returning the SQL string and not the value.
    Have I missed something in your reply or am I still playing with my mental blocks? <img src=/S/newbrain.gif border=0 alt=newbrain width=21 height=22>
    Thank you again.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: string within a string (A2k2, AP2)

    I think I understand this a little better now.... if you assign SQL string to variable then that's what the variable will return. I think what you want is something like this:

    Public Sub TestSub()
    On Error GoTo Err_Handler

    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strMsg As String
    Dim strSQL As String
    Dim strClientRace As String
    Dim strTMName As String
    Dim strCHName As String
    Dim dblProbPat As Double

    Set db = CurrentDb

    strSQL = "SELECT <!t>[ClientRace]<!/t>, <!t>[ClientFirstLastName]<!/t> " & _
    "FROM qryIGICaseNumberClientAssayReportDetails " & _
    "WHERE IGICaseNumberPKID = " & Me!lngIGICaseNumberPKID & " " & _
    "AND PersonDesignator Like 'TM*';"

    Set rst = db.OpenRecordset(strSQL)

    strClientRace = rst!<!t>[ClientRace]<!/t>
    strTMName = rst!<!t>[ClientFirstLastName]<!/t>

    rst.Close

    strSQL = "SELECT <!t>[ClientRace]<!/t>, <!t>[ClientFirstLastName]<!/t> " & _
    "FROM qryIGICaseNumberClientAssayReportDetails " & _
    "WHERE IGICaseNumberPKID = " & Me!lngIGICaseNumberPKID & " " & _
    "AND PersonDesignator Like 'CH*';"

    strCHName = rst!<!t>[ClientFirstLastName]<!/t>

    rst.Close

    Set rst = db.OpenRecordset("tblTempAssay")

    ' You lost me here....
    dblProbPat = FlexRound((((PIProbabilityProduct2("tblTempAssay", rstAssay!<!t>[LabDataPKID]<!/t>)) * 0.5) / & _
    (((PIProbabilityProduct2("tblTempAssay", rstAssay!<!t>[LabDataPKID]<!/t>)) * 0.5) + 0.5)), 10) * 100

    Exit_Sub:
    If Not rst Is Nothing Then rst.Close
    Set db = Nothing
    Set rst = Nothing
    Exit Sub
    Err_Handler:
    strMsg = "Error No " & Err.Number & ": " & Err.Description
    Beep
    MsgBox strMsg, vbExclamation, "ERROR MESSAGE"
    Resume Exit_Sub

    End Sub

    As illustrated above, you need to open recordset based on SQL and assign value of field, not SQL string, to the variable, then close & reopen recordset as necessary with modified SQL statement to assign value to other variables. Since your code uses db.openrecordset syntax I assume you're using DAO. Also assume that the WHERE criteria will select only a single record from the source query. The first 2 SQL statements used same criteria so there was no need for separate statements; you can "recycle" the strSQL and rst variables as shown, just be sure to close recordset object before re-opening with new SQL string.

    As noted, you lost me with the "dblProbPat " variable assignment statement - it this using user-defined functions? The statement references both tblTempAssay and rstAssay which is based on same table so w/o knowing how the UDF's are defined am not sure what is going on there....

    HTH

  5. #5
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: string within a string (A2k2, AP2)

    Hi Mark,
    Thanks. That's what I forgot, setting the SQL string = rst![the chosen field]. I knew I had done that before. Funny (or not) how one (i.e. me) can forget little things like that. <img src=/S/nope.gif border=0 alt=nope width=15 height=15>
    Yes the dblProbPat is a user-defined function which does a convoluted calculation of probability of paternity. It works, however.
    Again yes, I've learned to take great care if I open it I close it. Too many inconsistencies happen if I don't.
    Thank you so much again.

    gdr
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

Posting Permissions

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