Results 1 to 13 of 13
  1. #1
    Join Date
    Jun 2008
    Posts
    163

    Unanswered: Access 2003, basic questions about tables, autonumbers, expressions and others

    I'm an Access newbie. I started this thread with one question, got a reply, then posted another question in this same thread. In the event that other newbies like me stumble here, I edited this first post as a summary of the questions I asked and the answers I got. Most of what is here concerns the generation of sequenced numbers and formatting them using tables and queries. Chances are, I still have some silly newbie questions at the bottom of this thread; I never run out of them), and as always, any help is greatly appreciated.

    How do I output the content of a row of fields into a single cell?

    fieldname1 & fieldname2 & fieldname3 & fieldname4 (from pbaldy)

    He also noted that adding an: & "-" ....will add a dash
    I was able to create a query that placed the contents of 4 fields from a table into a single cell in the query. I've yet to figure out how to use the expression in the tables.

    How do I put leading zeroes in an autonumber?

    In the design view, place 0000 in the format option after choosing autonumber as a data type

    How do I put leading zeroes in a query?

    (from StarTrekker) In the field, type: DesiredColumnName: Format([fieldname],"0000")

    ...where DesiredColumnName and fieldname are user-chosen

    Is there an easy way to create multiple sequenced numbers?

    (from izyrider)
    No, it is not a basic function available in the GUI and will require a little scripting. A program in the 9th post of the stickied Codebank thread (see the Ms Access forums) shows a program that creates multiple sequential numbers. It uses a module and some VBA know-how.



    --------------------------------------------------------------------------------------
    below is my original 1st post:



    I cant draw a row, I'll flip it into a column:

    1: ab
    2: 11
    3: cd
    4: 22

    I'm hoping to get:

    5: ab11cd22

    actually, is it possible for the computer to format is as:

    5: ab11cd-22 ?

    how do I get access to automate column 5 to contain the values of columns 1 to 4 if 1 to 5 will contain a combination of numbers and letters (list boxes, manual input and autonumbers). Running on Access 2003.

    Thanks!
    Last edited by coffeecat; 06-25-08 at 23:25. Reason: previously: output the content of a row into a single cell

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    If those are 4 fields:

    Field1 & Field2 & Field3 & "-" & Field4
    Paul

  3. #3
    Join Date
    Jun 2008
    Posts
    163
    Thanks! It works now. =)

    btw, one of my fields uses a 4 digit number with zeroes before the integer (it uses a 0000;#;_ in the format). but the output gives me a value without the zeroes.

    a: 11
    b: bb
    c: 0004

    d: 11bb-4

    how do I make it look like 11bb-0004?

    ------------------

    I feel like it might flood the forums so I'll ask a question unrelated to the above in this same thread as well...

    How do I make the auto number to count specific entries? the first column is a two-letter code from a list box, and the second column is the auto-number. what I get is this:

    a: AA, 0001
    b: AA, 0002
    c: AA, 0003
    d: BB, 0004
    e: BB, 0005
    f: AA, 0006
    g: BB, 0007

    what I hope to get is this -the autonumber restarts counting based on the content of the first column:

    a: AA, 0001
    b: AA, 0002
    c: AA, 0003
    d: BB, 0001
    e: BB, 0002
    f: AA, 0004
    g: BB, 0003

    I could choose to create separate tables for my AA's and BB's. But I was hoping it would be possible to make it in one. Oh, and if anyone wants to know, all this is for ID-generation. Thanks again. =D
    Last edited by coffeecat; 06-25-08 at 02:18.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    right$("0000" & yourNumber, 4)

    #9 in the codebank illustrates one way to generate separate unbroken sequences of numbers at data-entry time.

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Jun 2008
    Posts
    163
    its embarrassing to admit but I'm really new at this. I use the design view, where can I use the expressions?

    Thanks for the tip izy, I'll try to figure it out.

    As for #9, that's an awesome program. I'm not familiar with the scripts used (i don't know what DAO is), hopefully there's a simpler way of doing this.
    Last edited by coffeecat; 06-25-08 at 05:47.

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    somewhere you are doing
    blah blah blah yourNumber blah blah blah

    change to
    blah blah blah right$("0000" & yourNumber, 4) blah blah blah.

    for the other part - sorry, but no. sure you can remove the opportunistic concurrency lock if you are in one-user world, sure you can use DLookup() which might look simpler, but the bottom line is that you cannot maintain multiple sequential numbers without getting your feet dirty.

    izy
    currently using SS 2008R2

  7. #7
    Join Date
    Jun 2008
    Posts
    163
    About what I'm doing, its more of just clicking design view, putting in a field name then choosing a data type.

    I got the hang of using some of the features in the general tab, but I don't know where in the general tab I can input those expressions. I've done a couple of list/combo boxes that use queries and have been able to use expressions there. Can you point me in the right direction?

    ---

    O_o; uh. I don't know what a concurrency lock is, let alone using vbasic scripts. I'll get to learn those eventually since I need to create multiple sequential numbers.

    ---

    In the tables, autonumber is the only automated data type I've seen. Are there other ways of making MS Access process and input data automatically in the tables? (not counting forms, reports, modules, etc)

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    concurrency lock is where two or more users are trying to do soemthing to the same table / page / row at the same time and one or more get locked out because someone else is there already. if you KNOW the application will never ever be used by more than one person at one time, you can ignore concurrency/lock issues.. however that's a very very dangerous proposition.

    An autonumber column exists to make certain that each row has a unique value. an autonumber column should only have meaning within the system ie it should not have any significance outside the system itself.. A lot of newbie developers try to coerce the autonumber value to have a meaning outside (eg as a Snvoice number, GRN, a Sales order number. Autonumbers are not appropriate for that.

    If you must have an automatic numbering system then you have to dip your toes into some VBA behind the scenes to do that.. soemthing you've already said you don't want to do.

    So at present the choice is
    use autonumber
    don't bother with an autonumber
    write your own autonumber scheme.. there are plenty of examples in this forum
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    healdem is totally right.

    if you are looking for a number that has no meaning, autonumber is the perfect choice.
    if you are looking for a number that has meaning - autonumber is not a candidate. you must inevitably use code to manage your meaningful sequential number(s).

    time to get your feet dirty!

    there are many many many ways to manage sequential numbers. some good. some risky. some hopelessly doomed. i receive $1000 cash** for each download of my demo from this site so i obviously want you to use my method, but there are simpler methods that can work.

    going from specific to general: you will not have a happy Access life until you dip your toes into VBA code: macros and form-clicking are so horribly limited.

    izy

    ** i wish !
    currently using SS 2008R2

  10. #10
    Join Date
    Jun 2008
    Posts
    163
    About the concurrency issues. Am I right to assume that it will not matter if:

    1. Only two users will use the database at the same time at any given time.
    2. Only one of the two users (lets call it user A) will have the permission to create new data to which the numbers will be assigned to.
    3. The other user (user B) may only access data that has already been created by user A. User B's functions will consist of appending information to the data created by A.

    Yeah, I too wanted to use the autonumber function to create IDs.
    ----

    Indeed, VBA intimidates me to no end. But as I have realized from what you folks have advised me, there's no going around that. As Izy has said, time to get my feet dirty. =D

    I've been reading the code on Izy's demo. I was expecting to see some case-if-then-else scripts, my bad. Can you orient me on which tags tell the computer which radio button is highlighted?

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You have two users editing data... you will still have to consider the record locking situation.

    VBA isn't that hard once you get going

    Normally you determine which radio button is "on" by examining the group control that the radio buttons are in.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  12. #12
    Join Date
    Jun 2008
    Posts
    163
    You're right. And the current setup may eventually expand to add more users at the same time. I'll learn about handling that later on (probably when I try to see how to set up a server for the database).

    For now, I'll look into how to properly plan the tables and how to use VBA. =D

  13. #13
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Great move
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •