From: kejo41 on
The clinic I work for regularly gets data imported from the local lab. The
problem with the import is that the field that contains the "value" of the
test results is "memo" type, as there are many different types of information
in this field (ie numbers, Y/N, even some sentences.

E.g

Identifier Test-Type Test-Value
1 Platelet count 350
2 CD4 Count 0.235
3 cell Conc Complete
4 Platelet count 210
5 HEPATITIS C PCR/RNA Processed

You get the picture, apparentlly they have no intention of fixing this any
time soon. However, It is my job to do the queries to keep everyone happy.

The one saving grace is that I know that each test type will always have the
same type of test value (ie the test value for platelet count is always a
number while cell conc is always text). I want for example to query patients
who have a platelet count of less than 100. When I try and do this in a query
I obviously get data-type errors. I have tried to CInt(), CStr(), to no avail.

I hope this makes sense. Any help would be much appreciated.

Thanks.

From: Tom Wickerath AOS168b AT comcast DOT on
Hi Kejo,

Looks to me like you are going to need to write some custom VBA code to get
the data from the memo field into a properly normalized format for querying /
reporting. Can you always rely on some word, such as "Identifier" with the
test results starting on the following lines? Is it possible for sentences to
be in the middle of this data, or are the sentences either before or after
all of the data?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

"kejo41" wrote:

> The clinic I work for regularly gets data imported from the local lab. The
> problem with the import is that the field that contains the "value" of the
> test results is "memo" type, as there are many different types of information
> in this field (ie numbers, Y/N, even some sentences.
>
> E.g
>
> Identifier Test-Type Test-Value
> 1 Platelet count 350
> 2 CD4 Count 0.235
> 3 cell Conc Complete
> 4 Platelet count 210
> 5 HEPATITIS C PCR/RNA Processed
>
> You get the picture, apparentlly they have no intention of fixing this any
> time soon. However, It is my job to do the queries to keep everyone happy.
>
> The one saving grace is that I know that each test type will always have the
> same type of test value (ie the test value for platelet count is always a
> number while cell conc is always text). I want for example to query patients
> who have a platelet count of less than 100. When I try and do this in a query
> I obviously get data-type errors. I have tried to CInt(), CStr(), to no avail.
>
> I hope this makes sense. Any help would be much appreciated.
>
> Thanks.
>
From: kejo41 on
Hi Tom,

Thanks for the response, Unfortunatley it seems like in the Test-Value field
anything goes. The only real constant is what I described earlier, most test
types will always have a specific data-type in the test value field (there
are a couple of exceptions). In this case the identifier field is simply the
patient identifier.

"Tom Wickerath" wrote:

> Hi Kejo,
>
> Looks to me like you are going to need to write some custom VBA code to get
> the data from the memo field into a properly normalized format for querying /
> reporting. Can you always rely on some word, such as "Identifier" with the
> test results starting on the following lines? Is it possible for sentences to
> be in the middle of this data, or are the sentences either before or after
> all of the data?
>
>
> Tom Wickerath
> Microsoft Access MVP
> http://www.accessmvp.com/TWickerath/
> __________________________________________
>
> "kejo41" wrote:
>
> > The clinic I work for regularly gets data imported from the local lab. The
> > problem with the import is that the field that contains the "value" of the
> > test results is "memo" type, as there are many different types of information
> > in this field (ie numbers, Y/N, even some sentences.
> >
> > E.g
> >
> > Identifier Test-Type Test-Value
> > 1 Platelet count 350
> > 2 CD4 Count 0.235
> > 3 cell Conc Complete
> > 4 Platelet count 210
> > 5 HEPATITIS C PCR/RNA Processed
> >
> > You get the picture, apparentlly they have no intention of fixing this any
> > time soon. However, It is my job to do the queries to keep everyone happy.
> >
> > The one saving grace is that I know that each test type will always have the
> > same type of test value (ie the test value for platelet count is always a
> > number while cell conc is always text). I want for example to query patients
> > who have a platelet count of less than 100. When I try and do this in a query
> > I obviously get data-type errors. I have tried to CInt(), CStr(), to no avail.
> >
> > I hope this makes sense. Any help would be much appreciated.
> >
> > Thanks.
> >
From: KenSheridan via AccessMonster.com on
The Val function won't produce an error if applied to a non-numeric text
value; it will return a zero in fact. So for the example you've cited you
could query for:

WHERE Test-Type = "Platelet count"
AND Val([Test-Value]) < 100
AND IsNumeric([Test-Value])

Calling the IsNumeric function won't actually be necessary if, as you say,
the values for "Platelet count" are always numeric, but you might want to
leave it in just in case this isn't always so.

There can be exceptions which the IsNumeric function wouldn't catch, e.g.
"123E4" is a number; its 123 multiplied by 10 to the power of 4, but I don't
imagine that's very likely in your case.

Ken Sheridan
Stafford, England

kejo41 wrote:
>Hi Tom,
>
>Thanks for the response, Unfortunatley it seems like in the Test-Value field
>anything goes. The only real constant is what I described earlier, most test
>types will always have a specific data-type in the test value field (there
>are a couple of exceptions). In this case the identifier field is simply the
>patient identifier.
>
>> Hi Kejo,
>>
>[quoted text clipped - 36 lines]
>> >
>> > Thanks.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/201002/1

From: Tom Wickerath AOS168b AT comcast DOT on
Hi Ken,

> WHERE Test-Type = "Platelet count"
> AND Val([Test-Value]) < 100
> AND IsNumeric([Test-Value])

Doesn't this assume that the OP has a field named "Test-Type"? My
understanding is that ALL of his data is in one memo-type field.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

"KenSheridan via AccessMonster.com" wrote:

> The Val function won't produce an error if applied to a non-numeric text
> value; it will return a zero in fact. So for the example you've cited you
> could query for:
>
> WHERE Test-Type = "Platelet count"
> AND Val([Test-Value]) < 100
> AND IsNumeric([Test-Value])
>
> Calling the IsNumeric function won't actually be necessary if, as you say,
> the values for "Platelet count" are always numeric, but you might want to
> leave it in just in case this isn't always so.
>
> There can be exceptions which the IsNumeric function wouldn't catch, e.g.
> "123E4" is a number; its 123 multiplied by 10 to the power of 4, but I don't
> imagine that's very likely in your case.
>
> Ken Sheridan
> Stafford, England