Rocky Smolin - Beach Access Software
bchacc at san.rr.com
Thu May 18 10:26:42 CDT 2006
Martin:
Here's a snip where I add a field based on the field count of a table.
I suppose instead of checking the field count you could loop through the
fields collection looking at the field names.
HTH
Rocky
Dim wrk As DAO.Workspace
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Set wrk = DBEngine.Workspaces(0)
Set Db = wrk.OpenDatabase(gstrDatabaseName)
Set tdf = Db.TableDefs("tblPartMaster")
If tdf.Fields.Count = 24 Then
Set fld = tdf.CreateField("Picture", DB_TEXT, 255)
fld.Properties("AllowZeroLength") = True
tdf.Fields.Append fld
End If
Set fld = Nothing
Set tdf = Nothing
Set Db = Nothing
Set wrk = Nothing
Martin Reid wrote:
> Is there an easy way to check if a field exists in a particular table vai vba?
>
> Maritn
>
> Martin WP Reid
> Training and Assessment Unit
> Riddle Hall
> Belfast
>
> tel: 02890 974477
>
>
> ________________________________
>
> From: accessd-bounces at databaseadvisors.com on behalf of John Clark
> Sent: Thu 18/05/2006 15:59
> To: accessd at databaseadvisors.com
> Subject: Re: [AccessD] Splitting up names
>
>
>
> Sorry that it has taken so long to get back to y'all...been extremely
> busy, and every time I sit down to do something, something else comes
> up.
>
> As usual I've gotten some good ideas from you all. As I suspected,
> there doesn't seem to be a clear-cut solution. I picked up a little from
> many of these posts, and I have actually begun some code that is
> progressing nicely toward a solution.
>
> I haven't tried this code here yet...I'll try to get to it in a little
> bit...but I'm sure it too will help me out.
>
> This project isn't exactly high priority...it is basically helping a
> former co-worker that has moved on...so it has been difficult to put
> much time into it, with everything else going on.
>
> Thanks again though!
>
> John W. Clark
>
>
>
>>>> adtp at hotmail.com 5/12/2006 1:01 am >>>
>>>>
> John,
>
> Functions Fn_FirstName() & Fn_LastName() as given below, should
> get you the desired results as per pattern indicated by you..
>
> Just make sure that the string variable named SuffixList in
> Fn_FirstName() includes all the likely suffixes.
>
> Could you please try it out and let me know whether any further
> fine tuning is needed.
>
> Best wishes,
> A.D.Tejpal
> ----------------
>
> ===========================================
> Function Fn_FirstName(ByVal FullName As _
> String) As String
> ' Returns First Name
> Dim Txt As String, Cnt As Long
> Dim SuffixList As String, Rtv As Variant
>
> SuffixList = "Sr-Jr-Dr-Esq-Rev-Hon-Sir-Lord"
>
> Txt = FullName
>
> Rtv = Split(SuffixList, "-")
> For Cnt = 0 To UBound(Rtv)
> Txt = Trim(Replace(Txt, " " & Rtv(Cnt), ""))
> Next
>
> Fn_FirstName = Trim(Left(Txt, InStrRev(Txt, " ") - 1))
>
> End Function
> ------------------------------------------------------------------------------
>
> Function Fn_LastName(ByVal FullName As _
> String) As String
> ' Returns Last Name
> Dim Txt As String
>
> Txt = Fn_FirstName(FullName)
>
> Fn_LastName = Trim(Mid(FullName, Len(Txt) + 1))
>
> End Function
> ===========================================
>
> ----- Original Message -----
> From: John Clark
> To: accessd at databaseadvisors.com
> Sent: Thursday, May 11, 2006 23:51
> Subject: [AccessD] Splitting up names
>
>
> If got a user running a db in Access. She has a list of names and
> they
> were all input into a single "name" field, rather than "last name" &
> "first name." She would now like to split these names up, but there
> are
> some double first names, as well as "jr" and "sr" suffixes.
>
> Is there a way to do this?
>
> Basically names might be like this:
>
> JOHN SMITH JR
> MARY ELLEN SMITH
> BARBARA SMITH
>
> And I'd need to get them like this into a first name field like:
>
> JOHN
> MARY ELLEN
> BARBARA
>
> and a last name field like:
>
> SMITH JR
> SMITH
> SMITH
>
> John W. Clark
> Computer Programmer
> Niagara County
> Central Data Processing
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com <http://www.databaseadvisors.com/>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com <http://www.databaseadvisors.com/>
>
>
>
--
Rocky Smolin
Beach Access Software
858-259-4334
www.e-z-mrp.com