Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Working with text and numbers in a union query (2000(SP3))

    I have a Union Query that combines CurrentAge from query A and Description from query B into a new field in the Union Query called Criteria. Current Age is calculated from a date field in query A.

    The Union Query needs to use parameters based on the field Criteria. The problem is the ages are being read as text strings so when I have a parameter that reads Between '3' and '15' , I get every (text)/number sorted alphabetically from one-five through three (so all of the 20s show up). The problem is perfectly logical and I understand why it is happening. What I would like to know is: Is there a way I can treat the info as a variant data type in a union query like I could in VBA?

    I tried inserting leading zeros into query A (and therefore into the union query) which works to a point, but the ages are compared to a reference table which records the various age parameters as numbers. I suppose I could record the info in the table with leading zeros as well, but then I am left with some rather ugly display issues. Any ideas?

    Peter N <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Working with text and numbers in a union query (2000(SP3))

    I don't understand your description. How do you combine two fields in a union query into a new field? Can you be more specific, for example post the SQL of the query?

  3. #3
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working with text and numbers in a union query (2000(SP3))

    As usual, too many words, not enough clarity! I'm not combining two fields in a union query, I'm creating a union query that has a single field Criteria that contains data of different types from the underlying queries. Hopefully that is a little clearer. The problem is getting the numbers to be interpreted as numbers rather than text and the text as text when I set the parameters.

    The Union Query is very garden variety. Here is a stripped down version of it.

    SELECT IndividualID, IndName, EnvelopeNumber, AgeFinal AS Criteria, Address1, Address2
    FROM qryBasicIndInformation
    UNION SELECT IndividualID, IndName, EnvelopeNumber, CommitteeName as Criteria, Address1, Address2
    FROM [qryCommittee Lists]
    ORDER BY IndName;

    Peter

  4. #4
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working with text and numbers in a union query (2000(SP3))

    PS Interpret "CommitteeName" in my SQL as "Description" from my original post. They are the same thing in terms of my question.

    Peter

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Working with text and numbers in a union query (2000(SP3))

    It seems very strange to put different data types into one field. Why not use two separate fields:

    SELECT IndividualID, IndName, EnvelopeNumber, AgeFinal, Null, Address1, Address2
    FROM qryBasicIndInformation
    UNION SELECT IndividualID, IndName, EnvelopeNumber, Null, CommitteeName, Address1, Address2
    FROM [qryCommittee Lists]
    ORDER BY IndName;

  6. #6
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Working with text and numbers in a union query (2000(SP3))

    I didn't actually know you could do that with a Union Query. This is all a followup on the Multi-select SQL I was working on for the church database a few weeks ago. The implications are interesting although it would mean some rewriting of my hard-won code for producing the reports. On the other hand, it may actually simplify some things in the long run as all that would really have to happen is some rewriting of the definitions of committees and possibly not even there.

    I will cogitate for a while <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15> and post back to this thread if I get stuck.

    Thanks for the info.

    Peter

Posting Permissions

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