If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
Adding info to query
OK, Guys. GOT IT. Thanx a whole bunch.
Mike J "mjj4golf" wrote: 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" wrote: 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" wrote in message ... 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 "Ofer" wrote: 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 \\// "mjj4golf" wrote: Also, can you explain the 'AS DifferentinMonths' Mike J "mjj4golf" wrote: Never Mind! Thx "Ofer" wrote: 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 \\// "mjj4golf" wrote: "Ofer" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Toolbars, Drop-Down Menus | Rick | New Users | 1 | September 21st, 2005 11:17 AM |
adding numbering column in a query | Giz | New Users | 8 | February 8th, 2005 01:03 AM |
Moving from xBase/Clipper | [email protected] | New Users | 1 | February 3rd, 2005 07:25 PM |
Union Query Not Returning A Value | Jeff G | Running & Setting Up Queries | 2 | October 19th, 2004 05:47 PM |
Big number gives error! | Sara Mellen | Running & Setting Up Queries | 8 | October 11th, 2004 02:48 AM |