[AccessD] Cheecking field exists

Martin Reid mwp.reid at qub.ac.uk
Thu May 18 10:47:07 CDT 2006


Thanks folks
 
One of our medical people wants a highly interactive search screen built for doctors to use. Sets of check boxes etc. Was stuck there for a bit.
 
Martin
 
Martin WP Reid
Training and Assessment Unit
Riddle Hall
Belfast
 
tel: 02890 974477
 

________________________________

From: accessd-bounces at databaseadvisors.com on behalf of Rocky Smolin - Beach Access Software
Sent: Thu 18/05/2006 16:26
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Cheecking field exists



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/>  <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/>  <http://www.databaseadvisors.com/>
>
>
>  

--
Rocky Smolin
Beach Access Software
858-259-4334
www.e-z-mrp.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com <http://www.databaseadvisors.com/> 




More information about the AccessD mailing list