RE: Adding info to query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Can you post the SQL and the function you have created?

"mjj4golf" wrote:

> Hey Thanks, it sort of makes sense now.
> One more ?? though. In the function statement itself. My date field in the
> table is called "Date Signed up" I tried to enclose it in [] but got a
> compile error. Is there another way to declare it w/ou changing the name w/o
> spaces?
>
> "Ofer" wrote:
>
> > Sorry, the RecordSet should be
> >
> > Set MyRec = MyDb.OpenRecordSet("Select DateFieldName From TableName
> > Where [IdFieldName] < " & IdFieldName & " And DateFieldName Is Not Null
> > Order By IdFieldName Desc")
> >
> > --
> > \\// Live Long and Prosper \\//
> >
> >
> > "Ofer" wrote:
> >
> > > I'll try and give you an example to what I mean, it could be that the prev
> > > record is also null, so you need to look for the previous and not empty date,
> > > also, what if the first record is empty, there will be no previous record.
> > > So, try this example
> > > ==========================================
> > > Query:
> > > Select IdFieldName , DatFieldName, DateDiff("d",LookForLastDate(IdFieldName,
> > > DateFieldName) ,Date()) As DifferentInDays From TableName
> > > ==========================================
> > > Function:
> > > Function LookForLastDate(IdFieldName As Double, DateFieldName As Variant)
> > > As Date
> > > On Error Goto LookForLastDate_Err
> > > Dim MyDb as Dao.DataBase, MyRec As Dao.RecordSet
> > > ' If there is date, it will return
> > > If Not IsNull(DateFieldName) Then
> > > LookForLastDate = DateFieldName
> > > Else
> > > 'If the date is empty, it will return the prev one that is not empty
> > > Set MyDb = CurrentDb
> > > Set MyRec = MyDb.OpenRecordSet("Select DateFieldName From TableName
> > > Where [IdFieldName] < " & IdFieldName & " Order By IdFieldName Desc")
> > > If MyRec.Eof then
> > > LookForLastDate = Date()
> > > Else
> > > LookForLastDate = MyRec!DateFieldName
> > > End If
> > > End If
> > > Exit Function
> > > LookForLastDate_Err:
> > > MsgBox Error
> > > LookForLastDate = Date()
> > > End Function
> > >
> > > --
> > > \\// Live Long and Prosper \\//
> > >
> > >
> > > "mjj4golf" wrote:
> > >
> > > > Yes, all the id's are unique to identify the person. But, I just want to go
> > > > to the previous record where the date is and enter that date in the record
> > > > with the empty date.. I would then go down till I come to the next empty
> > > > date field and go back one record, get the date, and plug that date in the
> > > > empty date..etc.
> > > >
> > > > Mike J
> > > >
> > > > "Ofer" wrote:
> > > >
> > > > > You can by using a function to return the prev date, do you have a unique id
> > > > > for each record, so you can find all the prev records?
> > > > >
> > > > > --
> > > > > \\// Live Long and Prosper \\//
> > > > >
> > > > >
> > > > > "mjj4golf" wrote:
> > > > >
> > > > > > I have a table with a date field that the user left blank sometimes. Now I
> > > > > > need to use it to find out how many monts between todays date and the date in
> > > > > > the table. Is there a way to use the date in the previous record in the
> > > > > > query and put it into the next record with the blank date?
> > > > > >
> > > > > > Mike J
> > > > > >
> > > > > >
.

Relevant Pages

  • Re: Limit on number of life peers?
    ... > Is there any sort of limit on the number of life peers? ... empty. ... Prev by Date: ...
    (alt.talk.royalty)
  • RE: Adding info to query
    ... I'll try and give you an example to what I mean, it could be that the prev ... also, what if the first record is empty, there will be no previous record. ... Function LookForLastDate(IdFieldName As Double, DateFieldName As Variant) ... >>> Mike J ...
    (microsoft.public.access.queries)
  • RE: Adding info to query
    ... >> I'll try and give you an example to what I mean, it could be that the prev ... >> also, what if the first record is empty, there will be no previous record. ... >> DateFieldName),Date) As DifferentInDays From TableName ... >> LookForLastDate = DateFieldName ...
    (microsoft.public.access.queries)
  • Create empty list by type name
    ... I need to create empty Listobject for this entity by class name ... I tried the following code but got compile error shown in comment. ...
    (microsoft.public.dotnet.languages.csharp)
  • delete rows from datagrid
    ... My problem is that whatever rows I delete, dgDeleted is always empty ... What should I do to store the deleted rows in a dataset so i can ... Thx ... Prev by Date: ...
    (microsoft.public.dotnet.languages.vb)