Data-type issue
in [General]
|
From: kejo41 on 13 Feb 2010 23:16 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 13 Feb 2010 23:37 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 13 Feb 2010 23:57 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 14 Feb 2010 13:51 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 14 Feb 2010 15:15 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
|
Next
|
Last
Pages: 1 2 Prev: Time cell with colons Next: Does anyone help me to learn German languages ? |