Union Query results – format problem -number shows (97)

Topic

New Reply

I have a Union query based upon 2 queries.
The 2 queries work fine and the format of the results works fine.
When I run the union query: the renewal month field which is prompted for is displayed as currency.
I have all tables and all forms set to general number with no decimals.
What surprises me is the 2 base queries do not display it with $.

Code:
SELECT tblPeople.FirstName, tblPeople.LastName, tblPeople.Title, tblPeople.Salutation, tblPeople.Address1, tblPeople.[Apt#1], tblPeople.City1, tblPeople.State1, tblPeople.Zip1, tblPeople.ReceiveMail1, tblPeople.Company, tblPeople.RenewalMonth, tblPeople.MembershipLevel, tblPeople.RenewalYear
FROM tblPeople
WHERE (((tblPeople.ReceiveMail1)=”YES”) AND ((tblPeople.RenewalMonth)=[Enter the Renewal Month number 3 to 9]) AND ((tblPeople.MembershipLevel)>0));

UNION SELECT tblPeople.FirstName, tblPeople.LastName, tblPeople.Title, tblPeople.Salutation, tblPeople.Address2, tblPeople.[Apt#2], tblPeople.City2, tblPeople.State2, tblPeople.Zip2, tblPeople.ReceiveMail2, tblPeople.Company, tblPeople.MembershipLevel, tblPeople.RenewalMonth, tblPeople.RenewalYear
FROM tblPeople
WHERE (((tblPeople.ReceiveMail2)=”YES”) AND ((tblPeople.MembershipLevel)>0) AND ((tblPeople.RenewalMonth)=[Enter the Renewal Month number 3 to 9]))
ORDER BY tblPeople.Zip1;
When I enter a 9 the results come back as $9.00

What’s up?
Soc

Reply | Quote

Replies

What’s the underlying datatype for the field in the tables? If its and Integer or Long, you shouldn’t be coming up with a Currency type. In any case, you can use the CInt function to coerce the result into an integer.

Reply | Quote

The data type is Long.

I changed the position of the field MembershipLevel(which is currency data type) and that solved it.
That is very odd. I moved it in the following way. Compare it to my original code:

SELECT tblPeople.MembershipLevel, tblPeople.FirstName, tblPeople.LastName, tblPeople.Title, tblPeople.Salutation, tblPeople.Address1, tblPeople.[Apt#1], tblPeople.City1, tblPeople.State1, tblPeople.Zip1, tblPeople.ReceiveMail1, tblPeople.Company, tblPeople.RenewalMonth, tblPeople.RenewalYear
FROM tblPeople
WHERE (((tblPeople.ReceiveMail1)=”YES”) AND ((tblPeople.RenewalMonth)>8));

UNION SELECT tblPeople.MembershipLevel, tblPeople.FirstName, tblPeople.LastName, tblPeople.Title, tblPeople.Salutation, tblPeople.Address2, tblPeople.[Apt#2], tblPeople.City2, tblPeople.State2, tblPeople.Zip2, tblPeople.ReceiveMail2, tblPeople.Company, tblPeople.RenewalMonth, tblPeople.RenewalYear
FROM tblPeople
WHERE (((tblPeople.ReceiveMail2)=”YES”) AND ((tblPeople.RenewalMonth)>8))
ORDER BY tblPeople.Zip1;

All I did was move this field to the beginning of the code, away from the RenewalMonth, and it solved the problem.
(There are a few other changes in the code but they had no effect. Once I moved this field in the code, the results did not have the $ in front.)
(Another way I solved it with the original code was to change the data type of RenewalMonth from Long to Text. That also solved it. These mysteries keep me awake. uuggghhhh)

Soc

Reply | Quote