Can anyone help me, I need a procedure to validate input into an Access table field that will not allow for a space or a hyphen. The database was already set up and there are already spaces and hyphens in the field.
Printable View
Can anyone help me, I need a procedure to validate input into an Access table field that will not allow for a space or a hyphen. The database was already set up and there are already spaces and hyphens in the field.
if you are getting information from the user from a text box, for example, then you can not allow hyphens or spaces. there are several ways of doing this. i would recommend using the Replace function and take out those characters before yuo build your SQL statement to insert those values.
Sub ValidateData()
dim sName as string
sName = VBA.Replace(txtName.Text, "-")
sName = VBA.Replace(sName, " ")
sql = "INSERT INTO YourTable (Name) VALUES ('" & sName & "')"
End Sub
the variable sName now contains the text from the text box, but without any spaces or hyphens.
as for the data already in the table, you would have to write a little procedure to open the table and the go through the records one at a time and take out the spaces and hyphens. maybe something like this:
Sub FixData()
dim rs as adodb.recordset
dim cn as adodb.connection
dim x as long
set cn = new adodb.connection
cn.connecionstring = "Your connection string"
cn.open
sql = "SELECT * FROM theTable"
set rs = new adodb.recordset
rs.open sql, cn, adOpenDynamic
if not rs.eof then
while not rs.eof
for x = 0 to rs.fields.count
rs(x) = VBA.Replace(rs(x), " ")
rs(x) = VBA.Replace(rs(x), "-")
rs.update
next
rs.movenext
wend
end if
rs.close
set rs = nothing
cn.close
set cn = nothing
that should give you some ideas about how to clean up the data and keep it clean.
hope this helps,
John
John Pirkey
MCSD
http://www.ShallowWaterSystems.com
http://www.stlvbug.org