Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    May 2003
    Location
    London, Gtr London, England
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Union Query results - format problem -number shows (97)

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

  3. #3
    Lounger
    Join Date
    May 2003
    Location
    London, Gtr London, England
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Union Query results - format problem -number shows (97)

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •