Results 1 to 8 of 8
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    =DString (A2000)

    Not sure if this can be done but here go's.

    Messing about yesterday with the DSum function and had another thought.
    I was trying to locate the current [Job] to tie in with Registration "CAPTION" "KM51KTA" of the button, so to see who has what vehicle at a particular date.
    I've attached a pic of form in design view to give you an Idea what I'm on about.

    The buttons on the left strangly get their text " CAPTION" value from some very complicated (to me anyway) code whic I think creates an array or something.
    These are called tglUnit01 , tglUnit02 and so-forth.
    I messed about with a DSum expression but changed table and field names respectively. I then changed the DSum to DString and was amazed as I didn't get any sysntax errors on exiting from the control source property field.

    If this DString is possible to use, can the String value of [Job] be taken from tblPeriod to fill the unbound text.

    The form itself redraws with the << & >> scroll buttons at the top of the calender, showing current job for the tglUnit relevent to the date.

    Is their a DString & DMax(date) statement mixed up here somewhere.

    ???????????????

    Dave
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: =DString (A2000)

    Dave,

    I've never heard of DString. If Access doesn't recognise the function, it'll assume that it is user-defined.

    I think you want a DLookup. Try

    =DLookup("Job", "tblPeriod", "UnitID=" & UnitID & " And Registration='" & tglUnit01.Caption & "'")

    Not the use of quotes (since Registration is a text field):

    <pre>" And Registration='" & tglUnit01.Caption & "'")</pre>


  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: =DString (A2000)

    None of the DLookup procedures work, according to the code attached with the db, the tglUnits(buttons) are numeric in values and only creates the caption itself.
    I don't think this can be done quite as easily as it looks.
    I can easily re-create the caption.

    Dim cap as String
    cap = tglUnit01.caption
    Text?? = cap

    But its what to do with it from here to pull the information I need.

    Dave


    Private Function FillUnits(vFirstUnitID As Variant) As Boolean
    ' set the captions on toggles with the current 30 displayed units

    ' loop counter variable
    Dim x As Integer
    ' counter for For Each loop
    Dim v As Variant
    ' current toggle being worked on
    Dim tgl As ToggleButton
    ' set to True if any units are selected
    Dim fSel As Boolean

    fSel = (mcolSelUnits.Count > 0)

    If cUnitPKDataType = "Numeric" Then
    mrsU.FindFirst cUnitPK & " = " & vFirstUnitID
    Else
    mrsU.FindFirst cUnitPK & " = '" & vFirstUnitID & "'"
    End If

    For x = 1 To 30
    Set tgl = Me("tglUnit" & Format(x, "00"))
    tgl = False
    With mrsU
    If Not .EOF Then
    tgl.Caption = .Fields(cUnitDisplayFld)
    tgl.Tag = .Fields(cUnitPK)
    Me("cmdUnit" & Format(x, "00")).Enabled = True
    If fSel Then
    For Each v In mcolSelUnits
    If CStr(v) = CStr(.Fields(cUnitPK)) Then
    tgl = True
    Exit For
    End If
    Next v
    End If
    .MoveNext
    Else
    tgl.Caption = ""
    tgl.Tag = ""
    Me("cmdUnit" & Format(x, "00")).Enabled = False
    End If
    End With
    Next x

    ' disable unit scroll buttons if needed
    Dim fPrev As Boolean, fNext As Boolean
    tglUnit01.SetFocus
    With mrsU
    .MoveFirst
    If (tglUnit01.Tag = .Fields(cUnitPK)) _
    Or tglUnit01.Caption = "" Then
    fPrev = False
    Else
    fPrev = True
    End If

    .MoveLast
    If (tglUnit15.Tag = .Fields(cUnitPK)) _
    Or (tglUnit15.Caption = "") Then
    fNext = False
    Else
    fNext = True
    End If
    End With

    cmdPrevUnit.Enabled = fPrev
    cmdPrevUnitGrp.Enabled = fPrev
    cmdNextUnit.Enabled = fNext
    cmdNextUnitGrp.Enabled = fNext


    End Function

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: =DString (A2000)

    Dave,

    According to the code you posted, the tglUnitnn are ToggleButtons. The value of a ToggleButton is a boolean (True/False). The caption is a string.

    The expression I proposed in my previous reply assumed that tblPeriod has fields UnitID (numeric), Registration (string) and Job (string), and that you wanted to retrieve the value of Job for a record with UnitID equal to the current value of UnitID in the form, and Registration equal to the caption of tglUnit01.

    If the text box with the expression as control source remains empty, the expression is syntactically correct, but there are no records satisfying the conditions. So perhaps the conditions should be different.
    If the text box displays an error (for instance Name#), there must be something wrong in the expression, perhaps incorrect field names or types.

    Without knowing more about the record source of the form and of the structure of tblPeriod, it's hard to give more pointed suggestions.

    Regards,
    Hans

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: =DString (A2000)

    This may help. I couldn't include tblUnit, the attach was too big.

    Thanks

    Dave
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: =DString (A2000)

    Dave,

    That makes things clearer. I'll have a look at it, but I'm busy with other things too. So if anybody else has the time right now...

    Regards,
    Hans

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: =DString (A2000)

    Dave,

    I think I have a solution that involves creating two queries. Maybe a SQL expert will come up with a one-step solution.

    Note: I have assumed that by "most current job", you mean the job with the latest FromDate. If you prefer ThruDate, you can use that instead.

    1. <LI>Create a new query. This query will select the most current FromDate for each UnitID.
      <UL><LI>Add tblPeriod.
      <LI>Make it into a Totals query.
      Add UnitID to the grid and set the Totals option to Group By.
      <LI>Also add FromDate to the query grid and set the Totals option to Max.
      <LI>Give this field an alias MostRecent by putting MostRecent: before the field name.
      <LI>The SQL is:

      SELECT tblPeriod.UnitID, Max(tblPeriod.FromDate) AS MostRecent
      FROM tblPeriod
      GROUP BY tblPeriod.UnitID;

      <LI>Save this query as qryRecentJobs.
    <LI>Create a second new query. This query will select the Job corresponding to the most current FromDate for each UnitID.
    <UL><LI>Add tblPeriod and qryRecentJobs.
    <LI>Add UnitID and Job to the query grid.
    <LI>Join tblPeriod and qryRecentJobs on UnitID<-->UnitID and on FromDate<-->MostRecent.
    <LI>The SQL is:

    SELECT tblPeriod.UnitID, tblPeriod.Job
    FROM qryRecentJobs INNER JOIN tblPeriod ON (qryRecentJobs.MostRecent = tblPeriod.FromDate)
    AND (qryRecentJobs.UnitID = tblPeriod.UnitID);

    <LI>Save this query as qryJobNrs.[/list]<LI>On your form, set the control source of the text box to
    <pre>=DLookUp("Job","qryJobNrs","UnitID='" & [tglUnit01].[Caption] & "'")</pre>
    [/list]HTH,
    Hans

  8. #8
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: =DString (A2000)

    Thanks Hans, it didn't work.

    Its got me thinking though, the tblPeriod has a field [PeriodID].
    When a new schedule is added, this increments by 1, so by including FromDate & ThruDate.

    It gives me something to try any-Way.

    I thought of using a combo with a SQL rowsource and then setting the control source to the caption.
    I'll let you know how I get on.

    Cheers

    Dave

Posting Permissions

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