Append records to create new record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

OK, this is apparently too newbie a question for the macros forum, so here it
is:
All one table. Column 1 = "BI3," and the values are numbers, e.g., "74389."
Column 2 = "Date" and the values are mm-dd-yyyy with a leading zero, e.g.,
03-20-1999. Column 3 = "Procedure," and the value is a text string, e.g.,
"Curetage." The new ID I want to create would look like:
"Curetage_03_20_1999_74389".

Simple, isn't it? But it's so simple I can't find an answer.
 
The new "ID"? What does that mean?

If you want to combine the items into one field, you can do so in a query or
report or form.

In a query, add a new column with something like the following in it...

NewFieldName: [Procedure] & "_" & Format([ProcDate],"MM") & "_" &
Format([ProcDate],"DD") & "_" & Format([ProcDate],"YYYY") & "_" & [B13]

Note that "Date" is a reserved word and should NOT be the name of one of
your fields. I changed it to "ProcDate" in the bove example.



In a form or report, add an unbound text box to your form or report and
paste the following into it...

=[Procedure] & "_" & Format([ProcDate],"MM") & "_" & Format([ProcDate],"DD")
& "_" & Format([ProcDate],"YYYY") & "_" & [B13]



In all cases, you do not store the result in your table. If one of the
values should change, then you'd have two fields to update. when you need
it, just use one of the procedures above to create it.

Hope that helps.
 
Rick B said:
The new "ID"? What does that mean?
Click to expand...
I changed it to "ProcDate" in the bove example.
Click to expand...

The "bove" example? What does that mean?

Actually, Date is shorthand for EffectiveDate of the MedicalProcedure. I do
know something about reserved names. I just have 500+ files that I am trying
to collect from various sources across three companies and six divisions,
assign consistent names, then use these to generate links to PDFs on an
intranet. Once I get consistent names, I can just populate - well, you know.

Thanks for your help.
Max
 
MaxWedge426 said:
OK, this is apparently too newbie a question for the macros forum,
Click to expand...

Did you reach that conclusion because you hadn't received a response in
less than 4 hours?
 
MaxWedge426 said:
Did you reach that conclusion because you hadn't received a response in
less than 4 hours?
Click to expand...
Not really. I looked at it and thought, "y'know, this might not even be a
macro question, It could be I'm missing something so obvious that it's not
even visible to someone who knows what they're doing." Like the guy who was
trying to do something complex when all he needed was to copy and paste a
column. It happens, and like I said, I know when I'm in unfamiliar
territory. I've never timed a response from a Microsoft forum, so I don't
know if 4 hours is "fast" or "slow" for a response. I take it that 4 hours
would be fast? Response times in other forums I've led (old WinHelp, then
HTML Help, next ... etc.) would be nearly instantaneous as consultants
jockeyed to see who could answer a newbie's question fastest - kind of a cult
thing. If I could have deleted my macro post, I would have. I don't see any
instructions in the Help section. But I do see a lot of incorrect
characters. Thanks again for your help.
 
Did you reach that conclusion because you hadn't received a response in
less than 4 hours?
Click to expand...
Steve, you're a heckuva guy. It's people like you who've made Microsoft
what it is today. In your honor, I have embedded a reference to you in our
code T4640 policy, which is honored (so to speak) in hospitals and doctors'
offices around the world. You are now immortal. Namaste
 
Back
Top