Update query problem

[rrh]

I am trying to update a field in one table with data from another
table. The problem I'm running into is I need to base the update on a
range of data in the 2nd table.

Table 1 has:
date field
new field

table 2 has:
key field (autonumber)
date field

I need to populate the "new field" in table 1 with the "key field"
from table 2 where the "date field" in table 2 is => the "date field"
in table 1 but < the "date field" in table one plus one month.

Based on the way the database is used, there should only be 1 record
that would match the criteria described above.

Any help would be greatly appreciated!

[Ruskin Hardie]

You could use the following code, which should work ok...

Sub UpdateDateField()
Dim myRec As DAO.Recordset
Dim fromDate As Date
Dim toDate As Date
Dim myKey As Long

Set myRec = CurrentDb().OpenRecordset("SELECT * FROM [Table 1];")
myRec.MoveFirst
Do Until myRec.EOF
DoEvents
fromDate = myRec![date field]
toDate = DateAdd("m", 1, fromDate)

If DCount("[key field]", "[Table 2]", _
"([date field]>=#" & fromDate & "#) " & _
"AND ([date field]<#" & toDate & "#)") = 1 Then
myKey = DLookup("[key field]", _
"[Table 2]", "([date field]>=#" _
& fromDate & "#) AND ([date field]<#" _
& toDate & "#)")

myRec.Edit
myRec![new field] = myKey
myRec.Update
Else
'Handle exceptions - key not found, or more than
'one record....
End If

myRec.MoveNext
Loop
myRec.Close
Set myRec = Nothing
End Sub

"rrh" <rr*@swbell.net> wrote in message
news:2e*************************@posting.google.co m...

I am trying to update a field in one table with data from another
table. The problem I'm running into is I need to base the update on a
range of data in the 2nd table.

Table 1 has:
date field
new field

table 2 has:
key field (autonumber)
date field

I need to populate the "new field" in table 1 with the "key field"
from table 2 where the "date field" in table 2 is => the "date field"
in table 1 but < the "date field" in table one plus one month.

Based on the way the database is used, there should only be 1 record
that would match the criteria described above.

Any help would be greatly appreciated!

[Ruskin Hardie]

Ooopsss... If I read your question properly, it seems you want to do this in
a query... In this case, you can create one query, with the fields of 'Table
1' (date field and new field) and the following extra user defined fields;

in one column;
to_date: DateAdd("m",1,[date field])

in another column;
key_field: DLookUp("[key field]","[Table 2]","[date field]>=#" & [date
field] & "# AND [date field]<#" & [to_date] & "#")

Then you could write an update query, to update Table 1, where the 'date
field' of this query matches the 'date field' of Table 1... You will then
update the 'new field' in Table 1, from the key_field in the query....

"Ruskin Hardie" <Ru******@xtra.NOSPAM.co.nz> wrote in message
news:X1**********************@news.xtra.co.nz...

You could use the following code, which should work ok...

Sub UpdateDateField()
Dim myRec As DAO.Recordset
Dim fromDate As Date
Dim toDate As Date
Dim myKey As Long

Set myRec = CurrentDb().OpenRecordset("SELECT * FROM [Table 1];")
myRec.MoveFirst
Do Until myRec.EOF
DoEvents
fromDate = myRec![date field]
toDate = DateAdd("m", 1, fromDate)

If DCount("[key field]", "[Table 2]", _
"([date field]>=#" & fromDate & "#) " & _
"AND ([date field]<#" & toDate & "#)") = 1 Then
myKey = DLookup("[key field]", _
"[Table 2]", "([date field]>=#" _
& fromDate & "#) AND ([date field]<#" _
& toDate & "#)")

myRec.Edit
myRec![new field] = myKey
myRec.Update
Else
'Handle exceptions - key not found, or more than
'one record....
End If

myRec.MoveNext
Loop
myRec.Close
Set myRec = Nothing
End Sub

"rrh" <rr*@swbell.net> wrote in message
news:2e*************************@posting.google.co m...

I am trying to update a field in one table with data from another
table. The problem I'm running into is I need to base the update on a
range of data in the 2nd table.

Table 1 has:
date field
new field

table 2 has:
key field (autonumber)
date field

I need to populate the "new field" in table 1 with the "key field"
from table 2 where the "date field" in table 2 is => the "date field"
in table 1 but < the "date field" in table one plus one month.

Based on the way the database is used, there should only be 1 record
that would match the criteria described above.

Any help would be greatly appreciated!

[rrh]

I think I can use either suggestion. Thanks so much for your help. I
was going in circles.

"Ruskin Hardie" <Ru******@xtra.NOSPAM.co.nz> wrote in message news:<g7**********************@news.xtra.co.nz>...

Ooopsss... If I read your question properly, it seems you want to do this in
a query... In this case, you can create one query, with the fields of 'Table
1' (date field and new field) and the following extra user defined fields;

in one column;
to_date: DateAdd("m",1,[date field])

in another column;
key_field: DLookUp("[key field]","[Table 2]","[date field]>=#" & [date
field] & "# AND [date field]<#" & [to_date] & "#")

Then you could write an update query, to update Table 1, where the 'date
field' of this query matches the 'date field' of Table 1... You will then
update the 'new field' in Table 1, from the key_field in the query....

"Ruskin Hardie" <Ru******@xtra.NOSPAM.co.nz> wrote in message
news:X1**********************@news.xtra.co.nz...

You could use the following code, which should work ok...

Sub UpdateDateField()
Dim myRec As DAO.Recordset
Dim fromDate As Date
Dim toDate As Date
Dim myKey As Long

Set myRec = CurrentDb().OpenRecordset("SELECT * FROM [Table 1];")
myRec.MoveFirst
Do Until myRec.EOF
DoEvents
fromDate = myRec![date field]
toDate = DateAdd("m", 1, fromDate)

If DCount("[key field]", "[Table 2]", _
"([date field]>=#" & fromDate & "#) " & _
"AND ([date field]<#" & toDate & "#)") = 1 Then
myKey = DLookup("[key field]", _
"[Table 2]", "([date field]>=#" _
& fromDate & "#) AND ([date field]<#" _
& toDate & "#)")

myRec.Edit
myRec![new field] = myKey
myRec.Update
Else
'Handle exceptions - key not found, or more than
'one record....
End If

myRec.MoveNext
Loop
myRec.Close
Set myRec = Nothing
End Sub

"rrh" <rr*@swbell.net> wrote in message
news:2e*************************@posting.google.co m...

I am trying to update a field in one table with data from another
table. The problem I'm running into is I need to base the update on a
range of data in the 2nd table.

Table 1 has:
date field
new field

table 2 has:
key field (autonumber)
date field

I need to populate the "new field" in table 1 with the "key field"
from table 2 where the "date field" in table 2 is => the "date field"
in table 1 but < the "date field" in table one plus one month.

Based on the way the database is used, there should only be 1 record
that would match the criteria described above.

Any help would be greatly appreciated!