1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

New Access question

Discussion in 'Business Applications' started by wedor, Jan 20, 2002.

Thread Status:
Not open for further replies.
Advertisement
  1. wedor

    wedor Thread Starter

    Joined:
    Nov 7, 1999
    Messages:
    4,504
    I have a field on my form that needs a zero added after the number ( SSN on Medicaid form ), is it possible to add the number in the report ( I print the claim forms using a report )? I have changed other data to fit the required format but nothing of this sort. Is this possible?
     
  2. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Okay, wedor, maybe I'm being dumb, but can't you change the control from, for instance:

    [SSN]

    to:

    [SSN]&"0"

    ?

    Or are you trying to add LEADING zeroes?

    format:=([SSN],"000-000-0000"

    I'm not positive. But this could keep you busy until someone better gets here.
     
  3. wedor

    wedor Thread Starter

    Joined:
    Nov 7, 1999
    Messages:
    4,504
    I suppose that would work, I really don't know. I have only been working in Access for about 6 months now so I have learned quite a bit about some parts and know nothing about others. I will give this a try and let you know if it functions correctly. And yes, you understand correctly, instead of the "000-00-000" format they will only accept "000-00-0000", as always thanks for the quick and useful assistance!
     
  4. wedor

    wedor Thread Starter

    Joined:
    Nov 7, 1999
    Messages:
    4,504
    Hmmm... tried that, no go. I get an #error# when I try those changes.
     
  5. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    wedor:

    If you can send me a little sample table of your data, and any query you're using, maybe I can help you yet tonight. Otherwise, have you tried changing the query to have those properties?

    Like "unshow" the SSN field, but add another "show" column with that Format:=([SSN],"000-00-0000")

    ?

    [email protected]
     
  6. wedor

    wedor Thread Starter

    Joined:
    Nov 7, 1999
    Messages:
    4,504
    Hmmm, little typo there, it needs to be 000-00-00000.
     
  7. downwitchyobadself

    downwitchyobadself

    Joined:
    Oct 13, 2000
    Messages:
    941
    Do NOT store the zero in the table. Then, on your report (or wherever else you're not entering data, but you want to see the zero), set the appropritate control's Control Source to
    Code:
    =[SSN] & "0"
    where [SSN] is to be replaced with the name of your field. (The equal-sign is essential, or Access thinks it's a field name.)
     
  8. theMusicMan

    theMusicMan

    Joined:
    Oct 7, 2001
    Messages:
    747
    Ok Ok Ok so I am not anywhere as experienced as you guys but.....

    If you wish to add a zero to a field - can you not multiply the number entered by 10!!! I suppose I am assuming that the field is numeric - so I guess I'd better shut up eh!!

    If not, can you not concatenate a "0"???

    Sorry to pester!

    John
     
  9. downwitchyobadself

    downwitchyobadself

    Joined:
    Oct 13, 2000
    Messages:
    941
    Yes, if it's a number. (SSNs are generally stored as text, since they contain dashes ###-##-####, and since no field in any db should be stored as a number unless it is to be used as a number. Which is almost never the case with an ID number like that.)

    And as for concatenating, well, that's what the replies here suggest to do. (Note the ampersand above. I don't know how else you might define concatenation?) Even the Unfailing Dreamboat was suggesting that, she just left out the equal sign. Perhaps this will put your pestering to restering...
     
  10. wedor

    wedor Thread Starter

    Joined:
    Nov 7, 1999
    Messages:
    4,504
    You are correct DWYBS it is stored as text but there are no dashes. The formula doesn't seem to work though. The field is called RECIP_SSN. When I try to use your formula I get, #Error. Any suggestions?
     
  11. downwitchyobadself

    downwitchyobadself

    Joined:
    Oct 13, 2000
    Messages:
    941
    Umm... Where are you using the formula? Can you describe to me exactly what's typed in there? If it's on a report, you want the control source to be
    Code:
    =[RECIP_SSN] & "0"
    I promise. Try putting it into the underlying query by removing the equal sign, see if you still get an error.

    EDIT: And clear the input-mask and format properties of the control, too. That might help.
     
  12. wedor

    wedor Thread Starter

    Joined:
    Nov 7, 1999
    Messages:
    4,504
    OK, in this particular report the source is a query, when I try the formula you gave it always gives me the #error in the space where the SSN should appear. Is the fact that the SSN is related to other tables relevant?
     
  13. downwitchyobadself

    downwitchyobadself

    Joined:
    Oct 13, 2000
    Messages:
    941
    No, something else is wonky. Have you named the field something other than SSN?

    Well, whatever. Can you post the query SQL here? (from query design view, View > SQL, cut and paste the whole text here.) It's probably something very small, but without seeing it, I can't tell.
     
  14. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
  15. wedor

    wedor Thread Starter

    Joined:
    Nov 7, 1999
    Messages:
    4,504
    Here you go,

    SELECT [ELG Elec Professional].ICN, [ELG Elec Professional].RECIP_SSN, [ELG Elec Professional].DIAG_PRIM, [ELG Elec Professional].DIAG_SEC, [ELG Elec Professional].DIAG_3, [ELG Elec Professional].DIAG_4, [ELG Elec Professional].BPROV_NUM, [ELG Elec Professional].CLNT_SEX, Recipients.FirstName, Recipients.Addr1, Recipients.Addr2, Recipients.City AS Recipients_City, Recipients.State, Recipients.Zip AS Recipients_Zip, Recipients.Sex, Recipients.DOB, Providers.ProvName, Providers.Address, Providers.City AS Providers_City, Providers.ST, Providers.Zip AS Providers_Zip, P.*, DIAG.*, [Field 31].*, [Processed date].*, Doctors.Name, Doctors.UPIN, [ELG Elec Professional].[24K], [ELG Elec Professional].Attending, [ELG Elec Professional].Referring, [ELG Elec Professional].Printed, Recipients.LastName, [ELG Elec Professional].FROM_DOS
    FROM P, DIAG, [Field 31], [Processed date], (([ELG Elec Professional] INNER JOIN Recipients ON [ELG Elec Professional].RECIP_SSN = Recipients.SSN) INNER JOIN Providers ON [ELG Elec Professional].BPROV_NUM = Providers.ProvNum) INNER JOIN Doctors ON [ELG Elec Professional].BPROV_NUM = Doctors.ProvNum
    WHERE ((([ELG Elec Professional].Printed)=True))
    ORDER BY Providers.ProvName, Recipients.LastName, [ELG Elec Professional].FROM_DOS;


    Thanks again for taking the time to look at this!
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/65639

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice