New Access question

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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?
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,749
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.
 

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!
 

wedor

Thread Starter
Joined
Nov 7, 1999
Messages
4,504
Hmmm... tried that, no go. I get an #error# when I try those changes.
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,749
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]
 
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.)
 
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
 
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...
 

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?
 
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.
 

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?
 
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.
 

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!
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online

Top