Combining different data type fields into one?

[kinktao]

Hi,

I have two columns that I want to combine. However, there data fields are different (one is a "number" and the other is a "short text").

For example:

Column A: 27622


Column B: Washington Street
Combined Column: 27622 Washington Street

I tried using the expression: "Column A" + "Column B" but I get "#Error"

Thank you for your help.

[Micron]

I tried using the expression: "Column A" + "Column B" but I get "#Error"

Because you can't add (sum) text and numbers. You can concatenate them, but only if where you're going to keep them allows it, such as in a control or table field whose data type is set to text.

First thought is, since data entry and display should be handled by reports or forms, then why bother? What is in the table is OK as it is - you'd assemble (concatenate) them in a form or report control. If you insist on putting them together, numbers can be converted to text but not the other way around (unless of course, all the text looks like numerals, which yours do not). So you'd have to convert the number field to text. Say goodbye to being able to do any math on a text field with numbers, if you ever want to. Numbers as text data brings a sorting issue: 11, 12, 115 will sort ascending as 11, 115, 12, which makes perfect sense if you understand why.

[ssanfu]

I agree with micron. Leave the tables alone.

In a query, you would use something like
StreetAdd: ColumnA & " " & ColumnB

("&" is the concatenation operator NOT "+")

In a control on a form/report, you would have "= ColumnA & " " & ColumnB" in the control source

[kinktao]

Hey guys, thanks for the help.

I don't want to change the number field into a text field for the reasons mentioned above.
I can't use the operator & instead of + because sometimes there is nothing in one of the columns but there is something in the other columns. (the example of the address above is not what I'm trying to do, it was just the easiest way to explain it).

The one way I figured out how to do it is to make another column in my table and have that column equal the column of my number column. The copied column can then be turned into a short text column. Hence:

Column A: 27622
Column B: Washington Street
Column C: =Column A
- I would then set Column C to short text
- I could then do: Column C + Column B

The only reason I was hesitant about doing this is because I feel it would be unnecessary stored data in my table.

Is it possible to copy a column in a query, and then be able to change the data type to short text? I've tried and couldn't figure it out.

[Micron]

Is it possible to copy a column in a query, and then be able to change the data type to short text?

Yes it is possible but I feel as though I would be doing a dis-service to you or anyone else who follows this thread to tell you how after telling you to concatenate the fields in a form or report and leave the table alone. The fact that you're saying there is not always something in one of the fields only strengthens my opinion, not to mention that it's far more work than it deserves. If it's not attractive to you to do this in a control because you sometimes have partial data, it makes even less sense to do it in a table when you have partial data.

The reason you cannot use the + has nothing to do with whether or not one of the fields has no data, as ssanfu has pointed out.

Not only do I not see a posted reason as to why you don't want to change the field type, I don't see where it was suggested. In fact, we're saying leave the table alone.

[ssanfu]

....(the example of the address above is not what I'm trying to do,.....

Really makes it hard to give a correct response when you don't explain what you are actually trying to do.

I can't use the operator & instead of + because sometimes there is nothing in one of the columns

You can use the correct operator ("&"), even if one field is NULL. Just a matter of you explaining correctly what you are trying to do.


You could use the NZ() function if one column (or both) has a NULL/empty string.

StreetAdd: Trim(ColumnA & " " & NZ(ColumnB,""))

The NZ() function changes a NULL to an empty string. The TRIM() function will remove the training space if "ColumnB" is NULL/empty string.

Can you explain what you are actually trying to do?

[orange]

kinktao,

Further to the advice offered by micron and ssanfu, I think it would be helpful for you and readers if you would describe the real issue you are facing and trying to solve. Too often posters will "make it simpler/easier to understand" by making a quasi-thought-out analogy.

Focus on WHAT the issue is, describe it in simple English; provide a few examples to clarify. Before you can get focused responses/advice, readers have to understand your situation.

At this point we're still not sure of the issue, the environment, any attempts to solution you have made.....

Good luck with your project.

[kinktao]

Hey you guys,

Sorry about the confusion.

So what I'm actually trying to do is make a PRINTABLE Table that includes a lot of information such as Pt name, age, pt's room, date of service, assessment, pt's primary doc, diagnosis, pt's medications.

The columns that I'm asking for help is the patient medication list column. I'm using this build for the column:

Pt Meds: ("Codeine "+[Codeine (mg)]+"mg Q"+[Codeine q_hrs] + "hrs, ") & ("Morphine " + [Morphine (mg)] + "mg Q" + [Morphine q_hrs] + "hrs, "] & ("Dilaudid" + [Dilaudid(mg)] + "mg Q" + [Dialudid q_hrs)] + "hrs, ")
Column Output: Codeine 10mg Q4hrs, Morphine 5mg Q6hrs, Dilaudid 8mg Q4hrs

Not everyone's going to be on every medication and hence I use the + operator so that the output can also show: Codeine 10mg Q4hrs, Dilaudid 8mg Q4hrs
However, when I use the + operator, I will get error since columns like [Codeine (mg)] is a Number data field instead of a short text.

Thanks for any help.

[orange]

In overview, you are dealing with

Patients who receive a quantity of one or more Medications at various Daily frequencies; and you want to track these.


Patient ---->DailyDoseAndFrequency<---Medication

[kinktao]

In overview, you are dealing with

Patients who receive a quantity of one or more Medications at various Daily frequencies; and you want to track these.


Patient ---->DailyDoseAndFrequency<---Medication

Yes, that's what I'm trying to do.

I also have to make sure I can print it on a very easy table for the doctors to see. I prefer to use the query table to print it because the report forums are difficult for them to see what's actually printing. Furthermore, I'm not that good at making the report forums and it ends up printing on multiple pages.

[orange]

Here is another model with a little more detail based on re-reading the thread.
Again these are my thoughts/interpretation, and may not be in sync with your requirements.
Feel free to challenge any of this.

Business Rules:

A Doctor may have 1 or Many Patients
A Patient may have 0,1 or Many Assessments
A Room may contain 1 or Many Patients
A Medication(Drug) may be involved in 0,1 or Many Prescriptions
A Medication is divided into Doses of various quantities
A Dose (of Medication) is prescribed by A Doctor ( for a Patient)
A Dose (of Medication) is prescribed at a specific Quantity (of Medication)
A Dose (of Medication) is prescribed to be given at specific Frequency

Included in the model for consideration

A Dose of Medication will be for an Quantity of Medication and will have a Lo and Hi value. This allows you to do a quick check that Doses are within accepted norms. This is more for validation of values (not a business rule as such).
Similarly Doses are prescribed to be given at a specific frequency. Frequency is likely to have a range of Lo and Hi and this is intended to allow for validation of the frequency prescribed. Again, this is more for validation of values (not a business rule as such).

Attached is the updated model to reflect these rules.

[kinktao]

That looks like a similar structure to what I have.

I'm still curious as to how I'm going to fit all the medications + med freq + dose freq into a single column. Any suggestions?

[orange]

Don't confuse the table and relationship (structure) with HOW you will display the data.
Get the structure correct by design and testing.