Filling fields in Access with a repeating character

  • Thread starter Thread starter Dan_S
  • Start date Start date
D

Dan_S

Dear all,

I am working with a particular dataset at the moment that requires any
unanswered fields to be filled with an 'X'. Is there a way by using an update
query that I can tell Access to put in the correct number of Xs required for
each field (e.g. if a field is 50 characters long, it fills it with 50 Xs, if
it's 2 characters long it fills it with 2 and so on)?

Thanks for your help in advance!

Dan
 
Hi Dan,

It is indeed possible, but it will require a separate query for each
field. Better to do it in code. Here is what I might do if it is an ongoing
need: Create a table that contains a number of select statements, one for
each table, selecting only the pertinent fields, probably filtering for only
those records that have blanks fields (i.e. select Field2, Field5, Field9
from tblAnswers where Field2 is null or Field5 is null or Field9 is null).
Then in code open up that table and read through it. For each row in it,
open up another recordset, using the stored select statement. Read through
those records. For each record in the second recordset go through each
field. For each field that is null, update it. If using ADODB, the inner
part would look something like this:

For Each fldCurrent In rstMyRecordSet.Fields
If IsNull(fldCurrent.Value) Then
fldCurrent.Value = String(fldCurrent.DefinedSize, "X")
End If
Next fldCurrent

Clifford Bass
 
If you know the field length you can to this

UPDATE SomeTable
SET Field1 = Left(Field1 & String(50,"X"),50)
Field2 = Left(Field2 & String(2,"X"),2)

That will fill the field with X up to the size of the field. If you
have 33 characters in the field you will end up with the original 33
characters and 17 "X" after it.

If that is not what you want then perhaps you want the following which
should only fill with "X" if there is nothing in the field.

Field1 = IIF(Len(Field1 & "") >0,Field1,String(50,"X"))
Field2 = IF(Len(Field2 & "")>0,Field2,String(2,"X"))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Dear all,

I am working with a particular dataset at the moment that requires any
unanswered fields to be filled with an 'X'. Is there a way by using an update
query that I can tell Access to put in the correct number of Xs required for
each field (e.g. if a field is 50 characters long, it fills it with 50 Xs, if
it's 2 characters long it fills it with 2 and so on)?

Thanks for your help in advance!

Dan
Click to expand...

Take a look at the VBA help for the String() function.
 
Back
Top