you are talking way over my head i will past the mod and if you would tell me what the best thing to do is
doug@papermoneyworld.net
Option Compare Database
Option Explicit
Public Sub EnterValueOfNotes100yen()
'=====> This requires a reference in TOOLS > References
'=====> to "Microsoft DAO 3.5 Object Library" or 3.6
'
'=====> To run it, press CTRL + G
'=====> In the bottom box, type "EnterValueOfNotes"
'=====> It takes about 2 minutes to run.
Dim db As Database
Dim rsNotes As DAO.Recordset
Dim rsLookup As DAO.Recordset
Dim SQL As String
Dim Lookup As Currency
Set db = CurrentDb
Set rsNotes = db.OpenRecordset("tb_notes100"
Debug.Print "start " & Time
Do Until rsNotes.EOF
If rsNotes!currency_value = 1000 Then
SQL = "Select * from Pricelist100yen" & _
" where series=" & "'" & Trim(rsNotes!Series) & "'" & _
" and denomnation = " & rsNotes!currency_value & _
" and Left(trim(start),1) ='" & Left(Trim(rsNotes!serialnumber), 1) & "'"
Else
'==== CHANGE ID NUMBER TO STARTING NUMBER ====
SQL = "Select * from Pricelist100yen" & _
" where series=" & "'" & Trim(rsNotes!Series) & "'" & _
" and denomnation = " & rsNotes!currency_value & _
" and Len(trim(start)) =" & Len(Trim(rsNotes!serialnumber)) & _
" and start <= " & " '" & Trim(rsNotes!serialnumber) & "'" & _
" and end >= " & " '" & Trim(rsNotes!serialnumber) & "'"
End If
Set rsLookup = db.OpenRecordset(SQL)
If rsLookup.RecordCount = 0 Then
Lookup = 0
Else
Select Case UCase(rsNotes!condition)
Case "Rag"
Lookup = rsLookup!rag
Case "VG"
Lookup = rsLookup!vgood
Case "F"
Lookup = rsLookup!fine
Case "VF"
Lookup = rsLookup!vfine
Case "XF"
Lookup = rsLookup!efine
Case "AU"
Lookup = rsLookup!au
Case "UNC"
Lookup = rsLookup!UNC
Case "CU"
Lookup = rsLookup!CU
Case "CCU"
Lookup = rsLookup!CCU
Case "GCU"
Lookup = rsLookup!GCU
Case Else
Lookup = 0
End Select
End If
rsNotes.Edit
rsNotes!Value = Lookup
rsNotes.Update
rsNotes.MoveNext
Loop
rsNotes.Close
rsLookup.Close
db.Close
Debug.Print "end " & Time
Set rsNotes = Nothing
Set rsLookup = Nothing
Set db = Nothing
End Sub
