On 8 jul, 22:04, XPS350 <xps...@gmail.com> wrote:
> On 8 jul, 21:49, TPK <tim.k...@comcast.net> wrote:
>
>
>
> > I haven’t used Microsoft Access in a number of years, and then it was
> > for use on an IIS server where I used ASP to pull data. This time I
> > need to create a stand alone database.
>
> > I want to create a simple database to catalog different classes of
> > publications (around 600 documents) so that editors can search for and
> > find specific documents that contain information to be changed.
>
> > I have about 5 different kinds of document. For example, one group is
> > Word documents that are called “job aids”, another group is largely
> > PowerPoint documents used for instruction and another group is
> > composed of Excel documents.
>
> > Here is the approach I thought of using:
> > Create a Table (called Table1)
> > Create a Query (called qryCPT4)
> > Create a user form to supply the variable (called Form1)
> > The Table contains these fields:
> > 1) * * *docID (key field)
> > 2) * * *fileName: File Name (with extension, text field 255 character
> > limit)
> > 3) * * *docSUBJECT: Subject (meta data, text field 255 character limit)
> > 4) * * *docAUTHOR: Author (meta data, comma separated phrases, text field
> > 255 character limit)
> > 5) * * *docKEYWORDS: Keywords (meta data, comma separated words, text field
> > 255 character limit)
> > 6) * * *docCPT4: CPT-4 Codes (numeric only, 3 digits) (comma separated
> > numbers, 5 digit numbers, memo field 64,000 character limit*)
> > 7) * * *docCPTSpecific: CPT-4 Specifier (2 character alpha numeric) (text
> > field 255 character limit)
> > 8) * * *docDATE: Last modification date (date/time field)
>
> > SAs an example, some documents mention (within their text) multiple
> > CPT codes. Each code is a 3 digit number. Each document might mention
> > dozens of codes. Some documents might have the same codes, some not.
>
> > Ideally, there would be a Form front end to the database where a user
> > can enter (for example) a 3 digit CPT code and see a list of all the
> > documents that mention that code.
>
> > My problem seems that when I use this query: (qryCPT4)
> > Like “*”&[Form1]&”*”
>
> > I only get one record returned. I should be getting several. The
> > problem might be the query or it might be the way the contents of the
> > field are structured. Right now the contents of the “docCPT4” field
> > are numbers separated by commas no spaces (example:
> > 999,997,998,621,423).
> > Any hints as to where I might have made an error?
>
> > Thank you,
>
> > TPK
>
> You have to refer to a field on the form in your query. So not Form1
> but:
> Forms!Form1!YourField
>
> Groeten,
>
> Peterhttp://access.xps350.com
By the way, it is not a good idea to put more than 1 bit of
information in one field.
For example Author. If there can be more than one other it is best to
create two extra tables, One with authors and one to store authors per
document. In that way there are no limits (what if the authors don't
fit in 255 positions) and you can make sure you always use the same
name for the same other. You can also make live easyer for the user.
Offer a combo here he can pick an author.
The same goes for keywords.
Groeten,
Peter
http://access.xps350.com
Similar Threads
