Adding info to query

  • Thread starter Thread starter Guest
  • Start date Start date
OK, Guys. GOT IT. Thanx a whole bunch.

Mike J

mjj4golf said:
OK, Forget that. I found the problem but now it is not putting a date for
the blank date but it is putting a 2 in the diiferentinmths column beside the
blank date.
Progress!

Mike J

Tom Ellison said:
Dear MJ:

What your code produces looks like this:

Select DateSignedup From RC_MainWhere [Id] < 12345 And Date_Signed_up Is Not
NullOrderBy Id

Notice there is no space where you have concatenated Where or OrderBy. Add
a space at those two places, and between "Order" and "By".

I strongly recommend you do this:

Function LookForLastDate(id As Long, Date_Signed_up As Variant) As Date
On Error GoTo LookForLastDate_Err
Dim MyDb As DAO.Database, MyRec As DAO.Recordset
Dim StrSQL As String

' If there is date, it will return
If Not IsNull(Date_Signed_up) Then
LookForLastDate = Date_Signed_up
Else
'If the date is empty, it will return the prev one that is not empty
Set MyDb = CurrentDb
StrSQL = "Select DateSignedup From RC_Main" _
& "Where [Id] < " & id & " And Date_Signed_up Is Not Null" _
& "OrderBy Id"
Set MyRec = MyDb.OpenRecordset(StrSQL)
If MyRec.EOF Then
LookForLastDate = Date
Else
LookForLastDate = MyRec!DateSignedup
End If
End If
Exit Function

For testing, put a breakpoint on the OpenRecordset line. In the immediate
pane, look at StrSQL to see what you have generated. If you can see this
kind of error, you will probably easily avoid it.

Tom Ellison


mjj4golf said:
I am getting an invalid From clause and it also looks like MyDb and MyRec
is
not getting set when I do some brkpts in the Function, Here is the func
and
the query:

Function LookForLastDate(id As Long, Date_Signed_up 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(Date_Signed_up) Then
LookForLastDate = Date_Signed_up
Else
'If the date is empty, it will return the prev one that is not empty
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordset("Select DateSignedup From RC_Main" _
& "Where [Id] < " & id & " And Date_Signed_up Is Not Null" _
& "OrderBy Id")
If MyRec.EOF Then
LookForLastDate = Date
Else
LookForLastDate = MyRec!DateSignedup
End If
End If
Exit Function

LookForLastDate_Err:
MsgBox Error
LookForLastDate = Date

End Function

Query:
SELECT Id, DateSignedup,
DateDiff("m",LookForLastDate(Id,DateSignedup),Date()) AS differentinmonths
FROM RC_Main;

Thx

:

Can you post again the SQL and function?
==================================
Also, I just noticed in the function , that you didn't use square
brackets
for the date field
And, Is the Id field name [Id Desc] Or [Id], it look like you used both


Function LookForLastDate(Id As Long, [Date Signed up] 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 = [Date Signed up]
Else
'If the date is empty, it will return the prev one that is not empty
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordSet("Select [Date Signed up] From
RC_Main "
& _
" Where [Id] < " & Id & " And [Date Signed up] Is Not Null " & _
" Order By [Id Desc]")
If MyRec.EOF Then
LookForLastDate = Date
Else
LookForLastDate = MyRec![Date Signed up]
End If
End If
Exit Function

--
\\// Live Long and Prosper \\//


:

Also, can you explain the 'AS DifferentinMonths'
Mike J

:

Never Mind!
Thx

:

The parameter doesn't need to have the same name as the field
Try this

Function LookForLastDate(Id As Long, Date_Signed_up 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 = Date_Signed_up
Else
'If the date is empty, it will return the prev one that is not
empty
Set MyDb = CurrentDb
Set MyRec = MyDb.OpenRecordSet("Select Date Signed up From
RC_Main _
Where [Id] < " & Id & " And [Date Signed up] Is Not Null _
Order By Id Desc")
If MyRec.EOF Then
LookForLastDate = Date()
Else
LookForLastDate = MyRec![Date Signed up]
End If
End If
Exit Function

LookForLastDate_Err:
MsgBox Error
LookForLastDate = Date()

End Function



--
\\// Live Long and Prosper \\//


:



:

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

:

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?

:

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 \\//


:

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 \\//


:

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

:

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 \\//


:

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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top