selecting data by exact date or the next older one

[tone]

Hi,

need some help.

Got table A with two columns: type DATE, VALUE
Got table B with two columns: type DATE, VALUE

Now the thing is to update table A with the values from table B.
For each element in A I check if there is a corresponding element
(DATE as reference) in B.
If so, I update VALUE of A with VALUE from B. If not, I update VALUE
of A with the next older element of B.

How do we do this?
Thanks so long

[salad]

tone wrote:

Hi,

need some help.

Got table A with two columns: type DATE, VALUE
Got table B with two columns: type DATE, VALUE

Now the thing is to update table A with the values from table B.
For each element in A I check if there is a corresponding element
(DATE as reference) in B.
If so, I update VALUE of A with VALUE from B. If not, I update VALUE
of A with the next older element of B.

How do we do this?
Thanks so long

You could write a function similar to below and substituting the
table/field names with yours. In the value column, in the Update row,
you'd call it with
GetTheValue([Date])

The SQL would look similar to
UPDATE Table1 SET Table1.Value = GetTheValue([Year]);

Public Function GetTheValue(datF As Date) As Variant
Dim rst As Recordset
Dim var As Variant

Set rst = CurrentDb.OpenRecordset("Table2", dbOpenSnapshot)
rst.FindFirst "Year = #" & datF & "#"
If Not rst.NoMatch Then
'it found the date
GetTheValue = rst!Value
Else
'see if a lesser date exists
var = DMax("Year", "Table2", "Year < #" & datF & "#")
If Not IsNull(var) Then
'looks for next lesser date
rst.FindFirst "Year = #" & var & "#"
GetTheValue = rst!Value
End If
End If

rst.Close
Set rst = Nothing

End Function

[tone]

On 2 Apr., 22:36, salad <[e-mail]:

tonewrote:

Hi,

need some help.

Got table A with two columns: typeDATE, VALUE
Got table B with two columns: typeDATE, VALUE

Now the thing is to update table A with the values from table B.
For each element in A I check if there is a corresponding element
(DATEas reference) in B.
If so, I update VALUE of A with VALUE from B. If not, I update VALUE
of A with the nextolderelement of B.

How do we do this?
Thanks so long

You could write a function similar to below and substituting the
table/field names with yours. In the value column, in the Update row,
you'd call it with
GetTheValue([Date])

The SQL would look similar to
UPDATE Table1 SET Table1.Value = GetTheValue([Year]);

Public Function GetTheValue(datF AsDate) As Variant
Dim rst As Recordset
Dim var As Variant

Set rst = CurrentDb.OpenRecordset("Table2", dbOpenSnapshot)
rst.FindFirst "Year = #" & datF & "#"
If Not rst.NoMatch Then
'it found thedate
GetTheValue = rst!Value
Else
'see if a lesserdateexists
var = DMax("Year", "Table2", "Year < #" & datF & "#")
If Not IsNull(var) Then
'looks for next lesserdate
rst.FindFirst "Year = #" & var & "#"
GetTheValue = rst!Value
End If
End If

rst.Close
Set rst = Nothing

End Function- Zitierten Text ausblenden -

- Zitierten Text anzeigen -

salad, this kinda makes sence to me.
Anyway I have a hard time since the debugger bugs me at 'rst.FindFirst
"Year=#" & datF & "#" '.
It says syntax-error 3077.
Later on also where we concatenate the date.

Other question: "Value" and "Year" are supposed to be the two columns
of Table2?
Later

[salad]

tone wrote:

On 2 Apr., 22:36, salad <[e-mail]:

>>tonewrote:

>>>Hi,

>>>need some help.

>>>Got table A with two columns: typeDATE, VALUE
Got table B with two columns: typeDATE, VALUE

>>>Now the thing is to update table A with the values from table B.
For each element in A I check if there is a corresponding element
(DATEas reference) in B.
If so, I update VALUE of A with VALUE from B. If not, I update VALUE
of A with the nextolderelement of B.

>>>How do we do this?
Thanks so long

You could write a function similar to below and substituting the
table/field names with yours. In the value column, in the Update row,
you'd call it with
GetTheValue([Date])

The SQL would look similar to
UPDATE Table1 SET Table1.Value = GetTheValue([Year]);

Public Function GetTheValue(datF AsDate) As Variant
Dim rst As Recordset
Dim var As Variant

Set rst = CurrentDb.OpenRecordset("Table2", dbOpenSnapshot)
rst.FindFirst "Year = #" & datF & "#"
If Not rst.NoMatch Then
'it found thedate
GetTheValue = rst!Value
Else
'see if a lesserdateexists
var = DMax("Year", "Table2", "Year < #" & datF & "#")
If Not IsNull(var) Then
'looks for next lesserdate
rst.FindFirst "Year = #" & var & "#"
GetTheValue = rst!Value
End If
End If

rst.Close
Set rst = Nothing

End Function- Zitierten Text ausblenden -

- Zitierten Text anzeigen -

salad, this kinda makes sence to me.
Anyway I have a hard time since the debugger bugs me at 'rst.FindFirst
"Year=#" & datF & "#" '.
It says syntax-error 3077.
Later on also where we concatenate the date.

Other question: "Value" and "Year" are supposed to be the two columns
of Table2?
Later

K. Value and Year are the field names you supplied. I wouldn't use
them but perhaps they're the name you are using. You must change those
names to match your field name....hmmm I see you have typeDate and I
used Date.

Next,
Dim rst As Recordset
might need to be changed to
Dim rst As DAO.Recordset
as your reference might be pointing first to ADO.

Make sure you change the table names unless it really is Table1 and Table2.

The code above worked for me in the query I created.

[tone]

Thank you salad - so long.
I tried it with DAO but without success.
Might be I didn't investigate much enough to get right into it.
For now I am off. Then in like two months it will become serious
again.
Later