472,986 Members | 2,788 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,986 software developers and data experts.

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!
Nov 12 '05 #1
3 6263
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!

Nov 12 '05 #2
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!


Nov 12 '05 #3
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!


Nov 12 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: David Shorthouse | last post by:
Hello folks, I have a problem with an update query on an asp not updating the table in an Access db. The code runs, I have no errors, but when I examine the table, nothing was updated. The query...
17
by: kalamos | last post by:
This statement fails update ded_temp a set a.balance = (select sum(b.ln_amt) from ded_temp b where a.cust_no = b.cust_no and a.ded_type_cd = b.ded_type_cd and a.chk_no = b.chk_no group by...
10
by: Randy Harris | last post by:
I imported records into a table, later found out that many of them had trailing spaces in one of the fields. If I'd caught it sooner, I could have trimmed the spaces before the import. This...
9
by: James Butler | last post by:
Our setup: Online db: MySQL Inhouse db: MS Access 97 with MySQL tables linked via ODBC Our issue: Almost every field updates successfully, except one. A scenario: Information is written to...
8
by: Maxi | last post by:
There is a lotto system which picks 21 numbers every day out of 80 numbers. I have a table (name:Lotto) with 22 fields (name:Date,P1,P2....P21) Here is the structure and sample data: ...
2
by: joeyrhyulz | last post by:
Hi, I'm trying to make a very simple update statement (in Oracle) in jet sql that seems much more difficult than it should be. The root of my problem is that I'm trying to update a field on a...
16
by: ARC | last post by:
Hello all, So I'm knee deep in this import utility program, and am coming up with all sorts of "gotcha's!". 1st off. On a "Find Duplicates Query", does anyone have a good solution for...
3
by: RAG2007 | last post by:
I'm using the QueryDef and Execute method to update a record in my MySQL backend. Problem: When the Passthrough update query is defined using QueryDef, it becomes a select query, and I cannot use...
1
by: giovannino | last post by:
Dear all, I did a query which update a sequence number (column NR_SEQUENZA) in a table using a nice code (from Trevor !). 1) Given that I'm not a programmer I can't understand why...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
0
tracyyun
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
0
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...
0
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.