Number formatting not displaying correctly in union query

[megsull]

Hello all. This is my first time posting in any forum ever, and I'm certainly no Access whiz, I'm just "making it work" as I go along; so forgive me if my etiquette and/or terminology are a bit off.



I have created a union query that combines member data from several different tables (or, rather, queries based on those tables), and for each of the source queries/tables, the number format of the member ID is unique. The numbers should appear in the formats AR00000, LA00000, MS00000, SC00000, NA00000, and 0000000 (no special format needed for this one). In the union query, I inserted formatting code for the member id field in each of the source query's codes; however, for 3 of the 5 requiring special formatting, the numbers don't display as they should. The "MS" group displays like this, "5000000" (with the first number varying from 1-10 and repeating multiple times), the "SC" group like this, "010/24/191200000" (going in order, so the next record says "010/25/191200000"), and the "NA" group barely missing the mark, appearing like this, "0A00000" (each one appears exactly like that, no variations in the numbers at all).

It may help to know that I checked, and in each of the source tables and queries, the formats are exactly the same, with the exception of the two-letter prefix.

Here is the code for the union query. My apologies for the lengthy code; thankfully, the fields in question are the first ones appearing in each of the "chunks" of code.

SELECT Format(AR.Member_Id, "AR00000"), AR.First_Name, AR.Middle_Name, AR.Last_Name, AR.MailingBusinessName1, AR.MailingBusinessName2, AR.Mailingadd1, AR.Mailingadd2, AR.Mailingcity, AR.MailingState, AR.MailingZip, AR.[Mailing Country], AR.Member_Level, AR.Joined_Date, AR.Expiration_Date, AR.Date_Entered, AR.Renew, AR.HomePhone, AR.Workphone, AR.Work_Extension, AR.Fax, AR.Email, AR.[Bad _Address], AR.Time_In_Field, AR.Student_Id, AR.University, AR.Membership_Status, AR.Source_Code, AR.OnMailingList, AR.[Local Affiliate(s)], AR.District_Number, AR.Amount_Paid, AR.Check_Number, AR.Referred_By, AR.Areas_of_Interest, AR.Comments, AR.Password, AR.Status, AR.Trans_Id, AR.Trans_Key
FROM AR
WHERE (((AR.Expiration_Date)>DateAdd("m",-2,Date())));
UNION ALL
SELECT Format(LA.[member_id], "LA00000"), LA.[first_name], LA.[middle_name], LA.[last_name], LA.[business_name1], LA.[business_name2], LA.[address1], LA.[address2], LA.[city], LA.[state], LA.[zip], LA.[country], LA.[membership_level], LA.[join_date], LA.[exp_date], LA.[date_entered], LA.[renew], LA.[home_phone], LA.[work_phone], LA.[work_ext], LA.[fax], LA.[email], LA.[bad_address], LA.[time_in_field], LA.[student_id], LA.[university], LA.[membership_status], LA.[source_code], LA.[On Mailing List], LA.[local_affiliate], LA.[district_number], LA.[amount_due], LA.[check_number], LA.[referred_by], LA.[areas_of_interest], LA.[Comments], LA.[password], LA.[status], LA.[trans_id], LA.[trans_key]
FROM LA
WHERE (((LA.[exp_date])>DateAdd("m",-2,Date())));
UNION ALL
SELECT Format(MS.[member_id], "MS00000"), MS.first_name, MS.middle_name, MS.last_name, MS.business_name1, MS.business_name2, MS.address1, MS.address2, MS.city, MS.state, MS.zip, MS.country, MS.membership_level, MS.join_date, MS.exp_date, MS.date_entered, MS.renew, MS.home_phone, MS.work_phone, MS.work_ext, MS.fax, MS.email, MS.bad_address, MS.time_in_field, MS.student_id, MS.university, MS.membership_status, MS.source_code, MS.[On Mailing List], MS.local_affiliate, MS.district_number, MS.amount_due, MS.check_number, MS.referred_by, MS.areas_of_interest, MS.Comments, MS.password, MS.status, MS.trans_id, MS.trans_key
FROM MS
WHERE (((MS.exp_date)>DateAdd("m",-2,Date())))
UNION ALL
SELECT Format(SC.Member_ID, "SC00000"), SC.First_Name, SC.Middle_Name, SC.Last_Name, SC.MailingBusinessName1, SC.MailingBusinessName2, SC.Mailingadd1, SC.Mailingadd2, SC.Mailingcity, SC.MailingState, SC.MailingZip, SC.[Mailing Country], SC.Member_Level, SC.Joined_Date, SC.Expiration_Date, SC.Date_Entered, SC.Renew, SC.Homephone, SC.Workphone, SC.Work_Extension, SC.Fax, SC.Email, SC.Bad_Address, SC.Time_In_Field, SC.Student_Id, SC.University, SC.Membership_Status, SC.Source_Code, SC.OnMailingList, SC.[LocalAffiliate(s)], SC.District_Number, SC.Amount_Paid, SC.Check_Number, SC.Referred_By, SC.Areas_of_Interest, SC.Comments, SC.password, SC.status, SC.trans_id, SC.trans_key
FROM SC
WHERE (((SC.Expiration_Date)>DateAdd("m",-2,Date())));
UNION ALL
SELECT Format([Non-Affiliate].[MemID], "NA00000"), [Non-Affiliate].[First Name], [Non-Affiliate].[Middle Name], [Non-Affiliate].[Last Name], [Non-Affiliate].[Mailing Business1], [Non-Affiliate].[Mailing Business2], [Non-Affiliate].[Address 1], [Non-Affiliate].[Address 2], [Non-Affiliate].[City], [Non-Affiliate].[State], [Non-Affiliate].[Zip], [Non-Affiliate].[Country], [Non-Affiliate].[Memtyp], [Non-Affiliate].[Join Date], [Non-Affiliate].[Expired Date], [Non-Affiliate].[Date Entered], [Non-Affiliate].[Renew], [Non-Affiliate].[Home phone], [Non-Affiliate].[Work phone], [Non-Affiliate].[Work phone ext], [Non-Affiliate].[Fax], [Non-Affiliate].[Email], [Non-Affiliate].[Bad_Address], [Non-Affiliate].[Time_In_Field], [Non-Affiliate].[student_id], [Non-Affiliate].[University], [Non-Affiliate].[Membership Status], [Non-Affiliate].[Source Code], [Non-Affiliate].[MailingList], [Non-Affiliate].[Local Affiliate], [Non-Affiliate].[District Number], [Non-Affiliate].[Amount Due], [Non-Affiliate].[Check Number], [Non-Affiliate].[Referred by], [Non-Affiliate].[areas of interest], [Non-Affiliate].[Comments], [Non-Affiliate].[password], [Non-Affiliate].[status], [Non-Affiliate].[trans_id], [Non-Affiliate].[trans_key]
FROM [Non-Affiliate]
WHERE ((([Non-Affiliate].[Expired Date])>DateAdd("m",-2,Date())));
UNION ALL SELECT NAEYC.[Member_Id], NAEYC.[First_Name], NAEYC.[Middle_Name], NAEYC.[Last_Name], NAEYC.[MailingBusinessName1], NAEYC.[MailingBusinessName2], NAEYC.[Mailingadd1], NAEYC.[Mailingadd2], NAEYC.[Mailingcity], NAEYC.[MailingState], NAEYC.[MailingZip], NAEYC.[MailingCountry], NAEYC.[Member_Level], NAEYC.[Joined_Date], NAEYC.[Expiration_Date], NAEYC.[Field16], NAEYC.[Field17], NAEYC.[Home_Area_Code HomePhone], NAEYC.[Work_Area_Code Workphone], NAEYC.[Work_Extension], NAEYC.[Fax_Area_Code Fax], NAEYC.[Email], NAEYC.[Bad_Address], NAEYC.[Time_In_Field], NAEYC.[Student_Id], NAEYC.[University], NAEYC.[Membership_Status], NAEYC.[Source_Code], NAEYC.[OnMailingList], NAEYC.[Local Affiliate(s)], NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
FROM NAEYC
WHERE (((NAEYC.[Expiration_Date])>DateAdd("m",-2,Date())))

My sincerest thanks to anyone who might be able to shed some light on this one for me.

[pbaldy]

I would try:


"AR" & Format(AR.Member_Id, "00000")

[megsull]

That worked like a charm. You are officially my new hero. Thank you VERY much!

[pbaldy]

No problem, and welcome to the site by the way!