Ed Robichaud said:Not sure why you'd want to do this at the query/recordsource level when you
can easily combine them in any form/report, but....
create a new field/column in your query, and in the first row of the design
grid put:
Combo Dates: [expensedate]&" "&[paymentdate]
This will give you a text/variant combining both dates, separated by a
space.
-Ed
Mark said:I have 2 date fields in a query [ExpenseDate] and [PaymentDate]. I want to
create an expression in the query that will display the values of these
fields in 1 single column.
Mark said:Thanks Ed! The Expression had the desired effect! Yes, you are right! It
is
in Reports I want the combination effect. I am able to do this through the
use of the control tool (text box) and then enter the expression you gave
me
in the control source property.
However, what I really want to do is:
Use the expression 'Between [Beginning Date] And [Ending Date]'. How can I
do this when I have 2 date fields in the Report?
Ed Robichaud said:Not sure why you'd want to do this at the query/recordsource level when
you
can easily combine them in any form/report, but....
create a new field/column in your query, and in the first row of the
design
grid put:
Combo Dates: [expensedate]&" "&[paymentdate]
This will give you a text/variant combining both dates, separated by a
space.
-Ed
Mark said:I have 2 date fields in a query [ExpenseDate] and [PaymentDate]. I want
to
create an expression in the query that will display the values of these
fields in 1 single column.
Ed Robichaud said:OK, I think that you're asking how to use a parameter query to filter
records based on a date range. Use the quoted expression (Between [Begin
Date] and [End Date]) as the criteria in the date column that you want to
filter on. If you want to use that same date range criteria on another
field, just use the same expression in the same row of the query grid design
to create an "AND" statement; or in the next row to create an "OR"
statement.
For more than the simplest query, this is usually done better by using a
form to collect the user criteria to filter on, then having the underlying
query refer to the values entered on the form (i.e. Between
Forms!MyForm!BeginDate and Forms!MyForm!EndDate)
-Ed
Mark said:Thanks Ed! The Expression had the desired effect! Yes, you are right! It
is
in Reports I want the combination effect. I am able to do this through the
use of the control tool (text box) and then enter the expression you gave
me
in the control source property.
However, what I really want to do is:
Use the expression 'Between [Beginning Date] And [Ending Date]'. How can I
do this when I have 2 date fields in the Report?
Ed Robichaud said:Not sure why you'd want to do this at the query/recordsource level when
you
can easily combine them in any form/report, but....
create a new field/column in your query, and in the first row of the
design
grid put:
Combo Dates: [expensedate]&" "&[paymentdate]
This will give you a text/variant combining both dates, separated by a
space.
-Ed
I have 2 date fields in a query [ExpenseDate] and [PaymentDate]. I want
to
create an expression in the query that will display the values of these
fields in 1 single column.
combo_ Dxs: [diagnosis1] & "| " & [diagnosis2] & "| " & [diagnosis3] & "|
" &
[diagnosis4] & "| " & [diagnosis5] & "| " & [diagnosis6] & "| " &
[diagnosis7] &
"| " & [diagnosis8]
jsullinger said:LOL...I think I just answered my question.
combo_ Dxs: [diagnosis1] & " " & [diagnosis2] & " " & [diagnosis3] & " " &
[diagnosis4] & " " & [diagnosis5] & " " & [diagnosis6] & " " &
[diagnosis7] &
" " & [diagnosis8]
Only problem, is not, it appears that all the text won't fit...gets
truncated. Where do I set character size? Or can I?
Here is the resultant text string....I want to find a way to just utilize
the clause which refers to my query criteria..."*carcinoma*"
A TISSUE, ASCENDING COLON POLYP: TUBULAR ADENOMA. B TISSUE, HEPATIC
FLEXURE: FRAGMENTS OF TUBULOVILLOUS ADENOMA. C TISSUE, TRANSVERSE COLON
POLYP: TUBULAR ADENOMA. D TISSUE, DESCENDING COLON POLYP: MODERATELY
WELL-DIFFERENTITATED ADENOCARCINOMA ARISIN
jsullinger said:I have a bit of an additional question regarding similar lines. I want to
combine 8 separate fields.
combo_ Dxs: [diagnosis1] & " " & [diagnosis2]
but for up to 8 total combinations. How do I do that?
Ed Warren said:It is not clear from the question what you are really trying to accomplish,
here are my best guesses.
From the code it appears you are trying to build a query to produce text for
use in a comboBox. However, I cannot see that this would be very useful as
a lookup so maybe you are trying to build a textfield to store in a
datatable.
If you have the table structure below you can get what you want with a query
and code to concatate the strings using a code module.
e.g.
Function returnDiagnosis (ClientID, strQuery as string) as string
dim strReturn as string
...----------code to do the following
lookup the client in the diagnosis table
get all the diagnoses that match the strQuery
loop through the returned records adding each text string to strReturn
when reached the end of the record set return strReturn
(e.g. strReturn = strReturn & " " & strDiagnosis)
------------------
returnDiagnosis = strReturn
end function
Your data structure depends on knowing in advance the number of 'diagnoses'
, e.g. 1-8 (what happens when you get the client with the 9th diagnosis?)
what you really want is:
Each client has many diagnoses (1:M relationship)
ClientTable (ClientID (Primary Key) + additional client specific
information.
DiagnosisTable (DiagnosisID (Primary Key) + Diagnosis text and other stuff
about the specific diagnosis
Client_DiagnosisTable (ClientID, DiagnosisID) ClientTable 1:Many &
Many :1 DiagnosisTable
You probably also want to 'code' the diagnoses (If US or UK you might want
to take a look a SNOMED CT, and pick a subset of interest, UMLS contains
the SNOMED CODES).
See:
UMLS: http://www.nlm.nih.gov/research/umls/
SNOMED CT: http://www.snomed.com/
Also of interest for a 'clinical system' is :
Health Level Seven (HL7: http://www.hl7.org/ )
Note all of these are far too complex for a simple application, but provide
insight into the basic data structure of health data.
Here are some examples from UMLS
[M]Tubular adenoma NOS [A3242587/SNOMEDCT/PT/189602000]
SNOMED CODE: 189602000 Tubular adenoma NOS
Tubular adenoma [A3079467/SNOMEDCT/PT/19665009]
SnomedCode Text
19665009 Tubular adenoma
This allows you to navigate the following SNOMED structure (I'm not a MD so
this is Greek to Me :> )
SNOMED CT Concept
Body structure
Morphologically altered structure
Morphologically abnormal structure
Mass
Neoplasm
Epithelial neoplasm
Adenoma AND/OR adenocarcinoma
Benign adenomatous neoplasm - category
Tubular adenoma
<[M]Tubular adenoma NOS>
Now to your basic question: You may want to use a "delimiter" between each
diagnosis,
combo_ Dxs: [diagnosis1] & "| " & [diagnosis2] & "| " & [diagnosis3] & "|
" &
[diagnosis4] & "| " & [diagnosis5] & "| " & [diagnosis6] & "| " &
[diagnosis7] &
"| " & [diagnosis8]
You can set the size of the text field in the table design (up to 255
characters for a 'text' field) and unlimited for a memo field -- note some
restrictions on indexing a memo field.
By the way you might want to make sure you meet HIPAA requirements.
Hope this helps
Ed Warren
jsullinger said:LOL...I think I just answered my question.
combo_ Dxs: [diagnosis1] & " " & [diagnosis2] & " " & [diagnosis3] & " " &
[diagnosis4] & " " & [diagnosis5] & " " & [diagnosis6] & " " &
[diagnosis7] &
" " & [diagnosis8]
Only problem, is not, it appears that all the text won't fit...gets
truncated. Where do I set character size? Or can I?
Here is the resultant text string....I want to find a way to just utilize
the clause which refers to my query criteria..."*carcinoma*"
A TISSUE, ASCENDING COLON POLYP: TUBULAR ADENOMA. B TISSUE, HEPATIC
FLEXURE: FRAGMENTS OF TUBULOVILLOUS ADENOMA. C TISSUE, TRANSVERSE COLON
POLYP: TUBULAR ADENOMA. D TISSUE, DESCENDING COLON POLYP: MODERATELY
WELL-DIFFERENTITATED ADENOCARCINOMA ARISIN
jsullinger said:I have a bit of an additional question regarding similar lines. I want to
combine 8 separate fields.
combo_ Dxs: [diagnosis1] & " " & [diagnosis2]
but for up to 8 total combinations. How do I do that?
jsullinger said:Actually, I am trying to produce a report that will list for each client
those patients for the month/year which had a specific cancer diagnosis,
ie.
adenocarcinoma. Our system does NOT use SNOMED, and each diagnosis field
is a
255 char text field. The cancer diagnosis will occur in any field. I want
to
be able to filter out the additional biopsies on the same patient that did
NOT have cancer, so I am only providing the part of the report diagnosis
with
the cancer in it. So, I am not really looking to build text for a combo
box,
I am trying to trim down the filtered criteria to exclude any diagnosis
field
that did NOT have "adenocarcinoma" in it that was part of the same record.
So, I want
A TISSUE, ASCENDING COLON POLYP: TUBULAR ADENOMA. B TISSUE, HEPATIC
FLEXURE: FRAGMENTS OF TUBULOVILLOUS ADENOMA. C TISSUE, TRANSVERSE COLON
POLYP: TUBULAR ADENOMA. D TISSUE, DESCENDING COLON POLYP: MODERATELY
WELL-DIFFERENTITATED ADENOCARCINOMA ARISIN (255 character limit cut this
off)
to become
D TISSUE, DESCENDING COLON POLYP: MODERATELY WELL-DIFFERENTITATED
ADENOCARCINOMA ARISIN
if using the combined field with all diagnosese for that record in it. Or,
just the diagnosis4 field which is D TISSUE, DESCENDING COLON POLYP:
MODERATELY WELL-DIFFERENTITATED ADENOCARCINOMA ARISIN, the only field
which
contains the item of interest.
Please forgive...I am a novice at this....everyone's assistance is very
much
appreciated.
Ed Warren said:It is not clear from the question what you are really trying to
accomplish,
here are my best guesses.
From the code it appears you are trying to build a query to produce text
for
use in a comboBox. However, I cannot see that this would be very useful
as
a lookup so maybe you are trying to build a textfield to store in a
datatable.
If you have the table structure below you can get what you want with a
query
and code to concatate the strings using a code module.
e.g.
Function returnDiagnosis (ClientID, strQuery as string) as string
dim strReturn as string
...----------code to do the following
lookup the client in the diagnosis table
get all the diagnoses that match the strQuery
loop through the returned records adding each text string to
strReturn
when reached the end of the record set return strReturn
(e.g. strReturn = strReturn & " " & strDiagnosis)
------------------
returnDiagnosis = strReturn
end function
Your data structure depends on knowing in advance the number of
'diagnoses'
, e.g. 1-8 (what happens when you get the client with the 9th diagnosis?)
what you really want is:
Each client has many diagnoses (1:M relationship)
ClientTable (ClientID (Primary Key) + additional client specific
information.
DiagnosisTable (DiagnosisID (Primary Key) + Diagnosis text and other
stuff
about the specific diagnosis
Client_DiagnosisTable (ClientID, DiagnosisID) ClientTable 1:Many
&
Many :1 DiagnosisTable
You probably also want to 'code' the diagnoses (If US or UK you might
want
to take a look a SNOMED CT, and pick a subset of interest, UMLS contains
the SNOMED CODES).
See:
UMLS: http://www.nlm.nih.gov/research/umls/
SNOMED CT: http://www.snomed.com/
Also of interest for a 'clinical system' is :
Health Level Seven (HL7: http://www.hl7.org/ )
Note all of these are far too complex for a simple application, but
provide
insight into the basic data structure of health data.
Here are some examples from UMLS
[M]Tubular adenoma NOS [A3242587/SNOMEDCT/PT/189602000]
SNOMED CODE: 189602000 Tubular adenoma NOS
Tubular adenoma [A3079467/SNOMEDCT/PT/19665009]
SnomedCode Text
19665009 Tubular adenoma
This allows you to navigate the following SNOMED structure (I'm not a MD
so
this is Greek to Me :> )
SNOMED CT Concept
Body structure
Morphologically altered structure
Morphologically abnormal structure
Mass
Neoplasm
Epithelial neoplasm
Adenoma AND/OR adenocarcinoma
Benign adenomatous neoplasm - category
Tubular adenoma
<[M]Tubular adenoma NOS>
Now to your basic question: You may want to use a "delimiter" between
each
diagnosis,
combo_ Dxs: [diagnosis1] & "| " & [diagnosis2] & "| " & [diagnosis3] &
"|
" &
[diagnosis4] & "| " & [diagnosis5] & "| " & [diagnosis6] & "| " &
[diagnosis7] &
"| " & [diagnosis8]
You can set the size of the text field in the table design (up to 255
characters for a 'text' field) and unlimited for a memo field -- note
some
restrictions on indexing a memo field.
By the way you might want to make sure you meet HIPAA requirements.
Hope this helps
Ed Warren
jsullinger said:LOL...I think I just answered my question.
combo_ Dxs: [diagnosis1] & " " & [diagnosis2] & " " & [diagnosis3] & "
" &
[diagnosis4] & " " & [diagnosis5] & " " & [diagnosis6] & " " &
[diagnosis7] &
" " & [diagnosis8]
Only problem, is not, it appears that all the text won't fit...gets
truncated. Where do I set character size? Or can I?
Here is the resultant text string....I want to find a way to just
utilize
the clause which refers to my query criteria..."*carcinoma*"
A TISSUE, ASCENDING COLON POLYP: TUBULAR ADENOMA. B TISSUE, HEPATIC
FLEXURE: FRAGMENTS OF TUBULOVILLOUS ADENOMA. C TISSUE, TRANSVERSE
COLON
POLYP: TUBULAR ADENOMA. D TISSUE, DESCENDING COLON POLYP: MODERATELY
WELL-DIFFERENTITATED ADENOCARCINOMA ARISIN
:
I have a bit of an additional question regarding similar lines. I want
to
combine 8 separate fields.
combo_ Dxs: [diagnosis1] & " " & [diagnosis2]
but for up to 8 total combinations. How do I do that?
Ed Warren said:John's response to your other post appears to be a good answer.
Ed Warren.
jsullinger said:Actually, I am trying to produce a report that will list for each client
those patients for the month/year which had a specific cancer diagnosis,
ie.
adenocarcinoma. Our system does NOT use SNOMED, and each diagnosis field
is a
255 char text field. The cancer diagnosis will occur in any field. I want
to
be able to filter out the additional biopsies on the same patient that did
NOT have cancer, so I am only providing the part of the report diagnosis
with
the cancer in it. So, I am not really looking to build text for a combo
box,
I am trying to trim down the filtered criteria to exclude any diagnosis
field
that did NOT have "adenocarcinoma" in it that was part of the same record.
So, I want
A TISSUE, ASCENDING COLON POLYP: TUBULAR ADENOMA. B TISSUE, HEPATIC
FLEXURE: FRAGMENTS OF TUBULOVILLOUS ADENOMA. C TISSUE, TRANSVERSE COLON
POLYP: TUBULAR ADENOMA. D TISSUE, DESCENDING COLON POLYP: MODERATELY
WELL-DIFFERENTITATED ADENOCARCINOMA ARISIN (255 character limit cut this
off)
to become
D TISSUE, DESCENDING COLON POLYP: MODERATELY WELL-DIFFERENTITATED
ADENOCARCINOMA ARISIN
if using the combined field with all diagnosese for that record in it. Or,
just the diagnosis4 field which is D TISSUE, DESCENDING COLON POLYP:
MODERATELY WELL-DIFFERENTITATED ADENOCARCINOMA ARISIN, the only field
which
contains the item of interest.
Please forgive...I am a novice at this....everyone's assistance is very
much
appreciated.
Ed Warren said:It is not clear from the question what you are really trying to
accomplish,
here are my best guesses.
From the code it appears you are trying to build a query to produce text
for
use in a comboBox. However, I cannot see that this would be very useful
as
a lookup so maybe you are trying to build a textfield to store in a
datatable.
If you have the table structure below you can get what you want with a
query
and code to concatate the strings using a code module.
e.g.
Function returnDiagnosis (ClientID, strQuery as string) as string
dim strReturn as string
...----------code to do the following
lookup the client in the diagnosis table
get all the diagnoses that match the strQuery
loop through the returned records adding each text string to
strReturn
when reached the end of the record set return strReturn
(e.g. strReturn = strReturn & " " & strDiagnosis)
------------------
returnDiagnosis = strReturn
end function
Your data structure depends on knowing in advance the number of
'diagnoses'
, e.g. 1-8 (what happens when you get the client with the 9th diagnosis?)
what you really want is:
Each client has many diagnoses (1:M relationship)
ClientTable (ClientID (Primary Key) + additional client specific
information.
DiagnosisTable (DiagnosisID (Primary Key) + Diagnosis text and other
stuff
about the specific diagnosis
Client_DiagnosisTable (ClientID, DiagnosisID) ClientTable 1:Many
&
Many :1 DiagnosisTable
You probably also want to 'code' the diagnoses (If US or UK you might
want
to take a look a SNOMED CT, and pick a subset of interest, UMLS contains
the SNOMED CODES).
See:
UMLS: http://www.nlm.nih.gov/research/umls/
SNOMED CT: http://www.snomed.com/
Also of interest for a 'clinical system' is :
Health Level Seven (HL7: http://www.hl7.org/ )
Note all of these are far too complex for a simple application, but
provide
insight into the basic data structure of health data.
Here are some examples from UMLS
[M]Tubular adenoma NOS [A3242587/SNOMEDCT/PT/189602000]
SNOMED CODE: 189602000 Tubular adenoma NOS
Tubular adenoma [A3079467/SNOMEDCT/PT/19665009]
SnomedCode Text
19665009 Tubular adenoma
This allows you to navigate the following SNOMED structure (I'm not a MD
so
this is Greek to Me :> )
SNOMED CT Concept
Body structure
Morphologically altered structure
Morphologically abnormal structure
Mass
Neoplasm
Epithelial neoplasm
Adenoma AND/OR adenocarcinoma
Benign adenomatous neoplasm - category
Tubular adenoma
<[M]Tubular adenoma NOS>
Now to your basic question: You may want to use a "delimiter" between
each
diagnosis,
combo_ Dxs: [diagnosis1] & "| " & [diagnosis2] & "| " & [diagnosis3] &
"|
" &
[diagnosis4] & "| " & [diagnosis5] & "| " & [diagnosis6] & "| " &
[diagnosis7] &
"| " & [diagnosis8]
You can set the size of the text field in the table design (up to 255
characters for a 'text' field) and unlimited for a memo field -- note
some
restrictions on indexing a memo field.
By the way you might want to make sure you meet HIPAA requirements.
Hope this helps
Ed Warren
LOL...I think I just answered my question.
combo_ Dxs: [diagnosis1] & " " & [diagnosis2] & " " & [diagnosis3] & "
" &
[diagnosis4] & " " & [diagnosis5] & " " & [diagnosis6] & " " &
[diagnosis7] &
" " & [diagnosis8]
Only problem, is not, it appears that all the text won't fit...gets
truncated. Where do I set character size? Or can I?
Here is the resultant text string....I want to find a way to just
utilize
the clause which refers to my query criteria..."*carcinoma*"
A TISSUE, ASCENDING COLON POLYP: TUBULAR ADENOMA. B TISSUE, HEPATIC
FLEXURE: FRAGMENTS OF TUBULOVILLOUS ADENOMA. C TISSUE, TRANSVERSE
COLON
POLYP: TUBULAR ADENOMA. D TISSUE, DESCENDING COLON POLYP: MODERATELY
WELL-DIFFERENTITATED ADENOCARCINOMA ARISIN
:
I have a bit of an additional question regarding similar lines. I want
to
combine 8 separate fields.
combo_ Dxs: [diagnosis1] & " " & [diagnosis2]
but for up to 8 total combinations. How do I do that?