By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,526 Members | 1,888 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,526 IT Pros & Developers. It's quick & easy.

Date changes and leading zeroes

P: 16
Access 2003 on XP pro machine...

i'm running a crosstab query and an export to a 3rd party company showing different benefit plans for employees; specifically dental, vision and medical pulling the effective dates (date which the plan when into effect). generally the plans all have the same effective date. however in the case they don't 2 lines are returned for the same employee; the first containing, for example, their medical and vision effective dated 10/1/2007 w/ dental blank and their dental effective dated 11/1/2007 because he added his wife to his dental plan, leaving the medical/vision columns blank . those who would be receiving the information get errors when loading it into their system because of seperate lines. they claim they need everything on one line with the greatest effective date value in that line, but i am unable to figure out how. any assistance would be greatly appreciated.

also, i feel the need to mention this as i have searched here and google and have yet to find a solution, same database;
the final product sent out is a fixed width flatfile (text) which includes SSNs. the way i keep everything formatted ensures the leading zeroes remain all the way through to the final step of the export through Access. however when viewing the flatfile all leading zeroes have been stripped and the SSN fields are left justified.

original: 454545454 xxxxxx454545454xxxx
orinigal: 056565656 xxxxxx56565656_xxxx
original: 006767676 xxxxxx6767676__xxxx

note: the underscore represents a space

i tried formatting the field as a Text exporting as text, tried as number exporting as text, as a number but running query converting it to a string, as a number changing the format to 000000000 but still no success

any assistance would be greatly appreciated.
Nov 15 '07 #1
Share this Question
Share on Google+
9 Replies


nico5038
Expert 2.5K+
P: 3,072
Did you try to use for the SSN part:
Expand|Select|Wrap|Line Numbers
  1. select right("00000000" & SSN,8) as SSNtext, ...
  2.  
Nic;o)
Nov 16 '07 #2

P: 16
i just did and it still returns the same results. i am using Access 03 but saved in Access 2000 file format, would that have anything to do with it?
Nov 20 '07 #3

nico5038
Expert 2.5K+
P: 3,072
Hmm, guess the crosstable queriy disturbes the datatype.
I would create a "Create table" query using the crosstable query as "input".
Next define a query with the "0000000" trick to save the result in a textfile.

Nic;o)
Nov 20 '07 #4

P: 16
just tried that and it doesn't work. are these tactics working for you when you try?
Nov 26 '07 #5

nico5038
Expert 2.5K+
P: 3,072
It does, however when you export the query, the export specification might cause the string to be transformed into a number.
Did you try a manual export and did you press the [Advanced] button ?

Nic;o)
Nov 26 '07 #6

P: 16
Yes, and i have specs but still drops the leading zeroes.
Nov 30 '07 #7

nico5038
Expert 2.5K+
P: 3,072
Did you verify that the "zero losing" field is defined as Text ?

Did you open the resulting file with notepad and not excel to verify?

Nic;o)
Nov 30 '07 #8

P: 16
yes, on both counts. i need to verify with notepad because it is to be sent/loaded as a fixed length flatfile.
i think i've about given up on trying to change import/export parameter and just load all SSNs with a symbol infront of them, such as "&" or "$", something which will never be found in the fields I'm querying, and fix the length to add 1 space on the export. when i get the flatfile, do a find/replace for the symbol and have it replace it with nothing
Dec 7 '07 #9

nico5038
Expert 2.5K+
P: 3,072
Hmm, running out of options.
The prefixing is indeed one of the last options, besides processing the recordset and using OPEN/Print #/CLOSE for writing to a textfile.

Nic;o)
Dec 7 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.