Inserting 00 infront of field 1

[Dutt]

Hi All,

I'm trying to look at two fields using recordsets and if the number is less then 7 places put 00 in front of the number. ex. 12345 would then = 0012345. The datatype in the database is Text. It would iterate throught the table to see what records needed to be changed, update them and then exit.

rstImport.MoveFirst
Do Until rstImport.EOF
If Len(rstImport![Project ID]) And Len(rstImport![Customer ID]) < 7 Then

?????????????????
Loop

Thanks

Dutt

 

[ide]

something like this..

rstImport.MoveFirst
Do Until rstImport.EOF
If Len(rstImport![Project ID]) And Len(rstImport![Customer ID]) < 7 Then
with rstImport
.edit
![Project ID]=Format(Val(![Project ID]), &quot;0000000&quot
![Customer ID]=Format(Val(![Customer ID]), &quot;0000000&quot
.update
end with
end if
rstImport.movenext
Loop

 

[Dutt]

The code looks like what I need but I'm getting and Error.
The error is run-time error '3021. No Current Record.

Here's my code:

If Form_frmImport.lblLocation.Caption = &quot;&quot; Then
MsgBox &quot;Please choose a file before importing&quot;, vbInformation, &quot;Importing Error&quot;
Exit Sub
Else
strFileName = Form_frmImport.lblLocation.Caption
End If

ianswer = MsgBox(&quot;Is this the file you would like to import?&quot;, vbYesNo, &quot;Import File?&quot

If ianswer = vbYes Then
'Import data from file to temp table
DoCmd.TransferText acImportDelim, , &quot;ImportTable&quot;, strFileName, True
'open the database and set the two recordset
Set dbs = OpenDatabase(&quot;C:\My Documents\Access\TestDatabase.mdb&quot
'set record set for the Import Table and the Project Master Table
Set rstImport = dbs.OpenRecordset(&quot;SELECT * FROM [ImportTable]&quot;, dbOpenDynaset)
Set rstMaster = dbs.OpenRecordset(&quot;Select * FROM [Project Master File]&quot;, dbOpenDynaset)

'Move to first Record
rstImport.MoveFirst---><B><I>Getting Error Here</I></B>
Do Until rstImport.EOF
'The the the ID is less then 7 places insert 00 in front of number
If Len(rstImport![Project ID]) And Len(rstImport![Customer ID]) < 7 Then
With rstImport
.Edit
![Project ID] = Format(Val(![Project ID]), &quot;0000000&quot
![Customer ID] = Format(Val(![Customer ID]), &quot;0000000&quot
.Update
End With
End If
'Move to Next Record
rstImport.MoveNext
Loop ' Loop until end of File(EOF)

I think it has something to do with the import but not sure. I check to table after I imported it and there are records in the today..so I'm not sure, Can you see and errors on my part?

Dutt

 

[Dutt]

I figured it out, my Table had the wrong name

Thanks ide

Dutt