Date changes and leading zeroes

[jbaranski]

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.

[nico5038]

Did you try to use for the SSN part:

select right("00000000" & SSN,8) as SSNtext, ...
 

Nic;o)

[jbaranski]

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?

[nico5038]

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)

[jbaranski]

just tried that and it doesn't work. are these tactics working for you when you try?

[nico5038]

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)

[jbaranski]

Yes, and i have specs but still drops the leading zeroes.

[nico5038]

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)

[jbaranski]

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

[nico5038]

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)