Val() function 4

[uscitizen]

i have a query on a table. the table has three fields (to keep it simple), dosage amount, height and weight. all three are real numbers and can have decimal values on entry into the table. i want to export the three values as numbers (not text) into a csv text file. my query uses

expr1: Format([Dosage],"0.000") and expr2:Format([Height],"0.0") and expr3: Format([Weight],"0.0")

which yield output like "3435.320", "125.4", "65.0" (yes the quotations are surrounding the output).

what i need is to have the values exported w/o the surrounding quotations, so i reasoned i'd need to convert them to numeric again.

i unearthed a 'Val()' function which i placed outside the format functions which has sort of done what i wanted, but the output is getting exported with two significant digits in all instances, e.g. as

3435.32 125.40 65.00

when what i'm trying to get is

3435.320 125.4 65.0

to use the same specimen values.

i'm not really sure that the Val() function lets me control the precision. is there any other?

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[uscitizen]

this is one thread that i definitely think has helped shape the manner the way in which i view vba into the future, boulderridge.

cheers!

“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[BoulderRidge]

Thanks guys--it's been my pleasure, and your kind words are much appreciated! B-) --BoulderRidge

 

[uscitizen]

hi, i gave my user the file to complete and since that point, i have begun to get two Errors.

from writing Patients file comes a "Type mismatch (13)" and
from writing Late Adverse Events file comes: "Invalid use of null(94)".

the Patients vba looks like this and (assuming that hovering over a field in the write area is the same as your suggested carving/pasting to the immediate window), can't believe that i can't locate the offending value


'****** paste this in the module ***** thread701-811127

Public Function ExportPatients()
On Error GoTo ErrMe
Dim strSQL As String
Dim cn As ADODB.Connection
Dim rsMyData As ADODB.Recordset
Dim intFileNum As Integer
Dim strCSVPathFile As String

'Set the path variable to your target file path and name
strCSVPathFile = "C:\PXS.csv"

'Put your query into a string variable as a SELECT query not an APPEND query

strSQL = "SELECT PATIENTS.Table_Name, PATIENTS.Protocol_ID, PATIENTS.Patient_ID, PATIENTS.Zip_Code,"
strSQL = strSQL & " PATIENTS.Country_Code, PATIENTS.Birth_Date, PATIENTS.Gender_Code, PATIENTS.Ethnicity_Flag,"
strSQL = strSQL & " PATIENTS.Method_Of_Payment, PATIENTS.Date_Of_Entry, PATIENTS.Reg_Group_ID, PATIENTS.Reg_Inst_ID,"
strSQL = strSQL & " PATIENTS.TX_On_Study, PATIENTS.Off_TX_Reason, PATIENTS.Last_TX_Date, PATIENTS.Off_Study_Reason,"
strSQL = strSQL & " PATIENTS.Off_Study_Date, PATIENTS.Subgroup_Code, PATIENTS.Ineligibility_Status, PATIENTS.Baseline_PS_Code,"
strSQL = strSQL & " PATIENTS.Prior_Chemo_Regs, PATIENTS.Disease_Code, PATIENTS.Resp_Eval_Status, PATIENTS.Baseline_Abnormalities_Flag"
strSQL = strSQL & " FROM PATIENTS;"



'Open a connection (required) and a recordset (rsMyData) on your query
'The recordset allows you to "walk" through the query results one row at a time
Set cn = CurrentProject.Connection
Set rsMyData = New ADODB.Recordset
rsMyData.Open strSQL, cn, adOpenStatic, adLockOptimistic

'FreeFile returns an Integer representing the next file number available for use by the Open statement.
intFileNum = FreeFile(0)

'This line opens your target file so that you can write to it
'It creates the file if it doesn't exist yet
'I DON'T KNOW whether it overwrites an existing file or appends to it--please test!

Open strCSVPathFile For Output Access Write As #intFileNum

'*****
Do Until rsMyData.EOF 'Until you reach the end of the query result set...
'Write the next line of results to your file
'(The underscore "_" is a line continuation character, making a long line more readable
Write #intFileNum, rsMyData!Table_Name, rsMyData!Protocol_ID, rsMyData!Patient_ID, rsMyData!Zip_Code, _
rsMyData!Country_Code, CLng(Format(rsMyData!Birth_Date, "yyyymm")), rsMyData!Gender_Code, rsMyData!Ethnicity_Flag, _
rsMyData!Method_Of_Payment, CLng(Format(rsMyData!Date_Of_Entry, "yyyymmdd")), rsMyData!Reg_Group_ID, rsMyData!Reg_Inst_ID, _
rsMyData!TX_On_Study, rsMyData!Off_TX_Reason, CLng(Format(rsMyData!Last_TX_Date, "yyyymmdd")), rsMyData!Off_Study_Reason, _
CLng(Format(rsMyData!Off_Study_Date, "yyyymmdd")), rsMyData!Subgroup_Code, rsMyData!Ineligibility_Status, rsMyData!Baseline_PS_Code, _
CInt(rsMyData!Prior_Chemo_Regs), CLng(rsMyData!Disease_Code), rsMyData!Resp_Eval_Status, rsMyData!Baseline_Abnormalities_Flag

'Move to the next row of query results
rsMyData.MoveNext
Loop
'*****

Close #intFileNum 'When you are done, close the file

ExitMe:
'Put away your toys when you are done...
Set rsMyData = Nothing
Set cn = Nothing
Exit Function

ErrMe:
MsgBox Err.description & " (" & Err.Number & ")", vbOKOnly, "Error writing Patients file"
Resume ExitMe

End Function
'****** end of paste *****

the late adverse events code that it's choking on is:

Option Compare Database

'****** paste this in the module ***** thread701-811127

Public Function ExportLate_Adverse_Events()
On Error GoTo ErrMe
Dim strSQL As String
Dim cn As ADODB.Connection
Dim rsMyData As ADODB.Recordset
Dim intFileNum As Integer
Dim strCSVPathFile As String

'Set the path variable to your target file path and name
strCSVPathFile = "C:\LTE_AE.csv"

'Put your query into a string variable as a SELECT query not an APPEND query

strSQL = "SELECT LATE_ADVERSE_EVENTS.Table_Name, LATE_ADVERSE_EVENTS.Protocol_ID, LATE_ADVERSE_EVENTS.Patient_ID,"
strSQL = strSQL & " LATE_ADVERSE_EVENTS.AE_Type_Code, LATE_ADVERSE_EVENTS.AE_Grade_Code,"
strSQL = strSQL & " IIf([AE_Other_Specify]='COMPLETE AS APPROPRIATE','',[AE_Other_Specify]) AS AE_OTHER,"
strSQL = strSQL & " LATE_ADVERSE_EVENTS.AE_Attribution_Code, LATE_ADVERSE_EVENTS.AE_Start_Date FROM LATE_ADVERSE_EVENTS;"



'Open a connection (required) and a recordset (rsMyData) on your query
'The recordset allows you to "walk" through the query results one row at a time
Set cn = CurrentProject.Connection
Set rsMyData = New ADODB.Recordset
rsMyData.Open strSQL, cn, adOpenStatic, adLockOptimistic

'FreeFile returns an Integer representing the next file number available for use by the Open statement.
intFileNum = FreeFile(0)

'This line opens your target file so that you can write to it
'It creates the file if it doesn't exist yet
'I DON'T KNOW whether it overwrites an existing file or appends to it--please test!

Open strCSVPathFile For Output Access Write As #intFileNum

'*****
Do Until rsMyData.EOF 'Until you reach the end of the query result set...
'Write the next line of results to your file
'(The underscore "_" is a line continuation character, making a long line more readable
Write #intFileNum, rsMyData!Table_Name, rsMyData!Protocol_ID, rsMyData!Patient_ID, _
IIf(rsMyData!AE_Type_Code = 0, "", CLng(rsMyData!AE_Type_Code)), IIf(rsMyData!AE_Grade_Code = 0, "", CInt(rsMyData!AE_Grade_Code)), _
rsMyData!AE_OTHER, CInt(rsMyData!AE_Attribution_Code), CLng(Format(rsMyData!AE_Start_Date, "yyyymmdd"))


'Move to the next row of query results
rsMyData.MoveNext
Loop
'*****

Close #intFileNum 'When you are done, close the file

ExitMe:
'Put away your toys when you are done...
Set rsMyData = Nothing
Set cn = Nothing
Exit Function

ErrMe:
MsgBox Err.description & " (" & Err.Number & ")", vbOKOnly, "Error writing Late Adverse Events file"
Resume ExitMe

End Function
'****** end of paste *****




and the only place in the write command that is = Null is

CInt(rsMyData!AE_Attribution_Code)

ps: i tried using CLng but still got the 94 error?!!!





“The philosophy of the school room in one generation will be the philosophy of government in the next." --- Abraham Lincoln

 

[BoulderRidge]

On the second one, try replacing CInt(rsMyData!AE_Attribution_Code)
with IIf(IsNull(rsMyData!AE_Attribution_Code), "", CInt(rsMyData!AE_Attribution_Code)) so you are not applying the CInt function to a potential Null value.

On the first one, you might have to copy and paste each expression from the Write statement (is that for sure the line that errors?) in the immediate window in order to see the impact of the nested function calls on the initial value. Skip the items without functions and when you find the error, peel off layers of functions from the outside in until it goes away. Then you know what you are dealing with.

Good luck!!!

 

[uscitizen]

hi boulderridge,

in the first instance the '13' does not seem to refer to the vba line, however using your investigative methodology,
i uncovered the dynamic duo that is offending the interpreter:

?CLng(Format(rsMyData!Last_TX_Date, "yyyymmdd")) <----
?CLng(Format(rsMyData!Off_Study_Date, "yyyymmdd")) <---

also, i oberved that the first record in the patients table has no data for either of these variables; is it that we have forgotten to handle nullity or something?

in the 2nd instance, i replaced the code w/ your suggested and so doing recompiled and tested it after pasting it into the immediate window:

?IIf(IsNull(rsMyData!AE_Attribution_Code), "", CInt(rsMyData!AE_Attribution_Code))

but still no cigar! the message we see is the one about the invalid use of null(94)!?





“The philosophy of the school room in one generation will be the philosophy of government in the next.&quot; --- Abraham Lincoln

 

[BoulderRidge]

Yes, for your first instance you will want to handle for "nullity" either in the data entry process or in the export code.

Believe it or not, it is really good that you are catching these things now because often we fail to test the "extremes" of the data and then it is the customer who runs into the error after some time of using the software successfully. Much better now while you are still officially in a testing or beta mode. It would be good for you to test all of these extremes of data possibilities if time and resources allow, ie. try leaving every field null or blank, either all at once or randomly, and also try entering illogical codes, number values, and dates. One can spend an inordinate amount of time on data validation, and that is not always the best approach, but it would be good for you to know and perhaps document what data validation rules you think are critical to enforce.

As for the Null error, have you determined for sure that this line is causing the error:
?IIf(IsNull(rsMyData!AE_Attribution_Code), "", CInt(rsMyData!AE_Attribution_Code))

If so, you could back up and handle for null in the data entry process, although I admit I can't see why it shouldn't work as listed above. Do you have other fields in any of your export procedures using this IIF(IsNull... syntax?

Just another thought: when you are testing code and you start getting errors that seem unwarranted, it may help to just exit the whole database file (save any changes you meant to save) and even exit Access. A Repair and Compact on your database is also worth a try. Sometimes it seems an error "hangs over" in debugging and doesn't property clear out until you exit.

Good luck and let me know what you find about the Null error. --BoulderRidge

 

[uscitizen]

hi boulderridge,

there are fields which can legitimately be null and i decided i'd try a small experiment and deliberately removed the datum from one (which was otherwise completed with the number 4) to see what would happen in a module where we are using:

IIf(IsNull(rsMyData!AE_Attribution_Code), "", CInt(rsMyData!AE_Attribution_Code))

w/o incident. the result was the appearance of the "Invalid use of null (94)" message. and i found that when i hovered over the components of the above statement that a2k told me it/they were equal to Null. replacing the empty cell w/ the 4 did not produce an error.

so what's going on here. why is it an invalid use of null. could it be this error is a non-issue which my code can selectively choose to bypass; i know i've read something about this in some vba documentation i found?

on the same topic of testing/dealing with nullity, there's some function named 'Nz' that the documentation treats; the docs say it's more efficient than the Iif IsNull approach; are we not using it because it would not be appropriate?



“The philosophy of the school room in one generation will be the philosophy of government in the next.&quot; --- Abraham Lincoln

 

[BoulderRidge]

Hi,

Good test. Looks like our IIf...IsNull was never working correctly. I can't explain why since I've seen Knowledge Base articles using exactly that combination of functions...but if it is broke, you have to fix it somehow, right?

Nz is a great function that I had not thought of in this situation. Unfortunately I don't think we can replace all the logic with just that function because if the value is NOT Null, we don't want to write just the original value (which is what Nz would return), we want to apply the numeric conversion function to the original value so that it writes without quotes. What probably would work, and would be the next thing I'd try, is to put the Nz function on all the suspect fields in your SQL statement, like

Nz(LATE_ADVERSE_EVENTS.AE_Attribution_Code,0).

Then in your Write statement you could use

IIf(rsMyData!AE_Attribution_Code=0, "", CInt(rsMyData!AE_Attribution_Code)).

Of course the 0 is arbitrary in the Nz function; you can specify any value when null and then test for the same value in the IIF.

This assumes that in case of a null value you do not want to write a zero or some other number but you want to write an empty string. If that is not the case, and you could write a numeric result that your customer could interpret as the null value, then you could eliminate one step by using your substitue numeric value in the Nz function call for the "value if null" argument and let the CInt (or CLng, etc) function work on both valid data values and your substitute numeric value alike without needing the IIF part. The only reason we needed the IIF test in the Write statement was because the CInt, etc. functions blow up if the value being converted is Null.

Good luck! ;-) --BoulderRidge

 

[uscitizen]

hi boulderridge,

the plot thickens as they say.

i'm kinda chompin' at the bit to try this angle out on our unsuspecting compiler.

talk 2ya later.


“The philosophy of the school room in one generation will be the philosophy of government in the next.&quot; --- Abraham Lincoln

 

[uscitizen]

hi boulderridge,

the compiler's spoken. the code as it reads currently is as under:

Option Compare Database

'****** paste this in the module ***** thread701-811127

Public Function ExportLate_Adverse_Events()
On Error GoTo ErrMe
Dim strSQL As String
Dim cn As ADODB.Connection
Dim rsMyData As ADODB.Recordset
Dim intFileNum As Integer
Dim strCSVPathFile As String

'Set the path variable to your target file path and name
strCSVPathFile = "C:\LTE_AE.csv"

'Put your query into a string variable as a SELECT query not an APPEND query

strSQL = "SELECT LATE_ADVERSE_EVENTS.Table_Name, LATE_ADVERSE_EVENTS.Protocol_ID, LATE_ADVERSE_EVENTS.Patient_ID,"
strSQL = strSQL & " LATE_ADVERSE_EVENTS.AE_Type_Code, LATE_ADVERSE_EVENTS.AE_Grade_Code,"
strSQL = strSQL & " IIf([AE_Other_Specify]='COMPLETE AS APPROPRIATE','',[AE_Other_Specify]) AS AE_OTHER,"
strSQL = strSQL & " Nz(LATE_ADVERSE_EVENTS.AE_Attribution_Code,0), LATE_ADVERSE_EVENTS.AE_Start_Date FROM LATE_ADVERSE_EVENTS;"



'Open a connection (required) and a recordset (rsMyData) on your query
'The recordset allows you to "walk" through the query results one row at a time
Set cn = CurrentProject.Connection
Set rsMyData = New ADODB.Recordset
rsMyData.Open strSQL, cn, adOpenStatic, adLockOptimistic

'FreeFile returns an Integer representing the next file number available for use by the Open statement.
intFileNum = FreeFile(0)

'This line opens your target file so that you can write to it
'It creates the file if it doesn't exist yet
'I DON'T KNOW whether it overwrites an existing file or appends to it--please test!

Open strCSVPathFile For Output Access Write As #intFileNum

'*****
Do Until rsMyData.EOF 'Until you reach the end of the query result set...
'Write the next line of results to your file
'(The underscore "_" is a line continuation character, making a long line more readable

Write #intFileNum, rsMyData!Table_Name, rsMyData!Protocol_ID, rsMyData!Patient_ID, _
IIf(rsMyData!AE_Type_Code = 0, "", CLng(rsMyData!AE_Type_Code)), IIf(rsMyData!AE_Grade_Code = 0, "", CInt(rsMyData!AE_Grade_Code)), _
rsMyData!AE_OTHER, IIf(rsMyData!AE_Attribution_Code = 0, "", CInt(rsMyData!AE_Attribution_Code)), CLng(Format(rsMyData!AE_Start_Date, "yyyymmdd"))


'Move to the next row of query results
rsMyData.MoveNext
Loop
'*****

Close #intFileNum 'When you are done, close the file

ExitMe:
'Put away your toys when you are done...
Set rsMyData = Nothing
Set cn = Nothing
Exit Function

ErrMe:
MsgBox Err.description & " (" & Err.Number & ")", vbOKOnly, "Error writing Late Adverse Events file"
Resume ExitMe

End Function
'****** end of paste *****

and what we're seeing is a runtime error 3265 ("Item cannot be found in the collection corresponding to the requested name or ordinal"). i think i've seen and dealt with this earlier on in our thread, there was a typo. is there one here or am i going blind?

“The philosophy of the school room in one generation will be the philosophy of government in the next.&quot; --- Abraham Lincoln

 

[BoulderRidge]

When you added

Nz(LATE_ADVERSE_EVENTS.AE_Attribution_Code,0)

you lost the SQL reference to the field AE_Attribution_Code; that is the object it cannot find. The solution is something like

Nz(LATE_ADVERSE_EVENTS.AE_Attribution_Code,0) as AE_Attribution

and then your Write statement should reference rsMyData!AE_Attribution instead of rsMyData!AE_Attribution_Code.

You're not going blind; it is always easier to catch things when a second pair of eyes look at it fresh.
Happy coding! --BoulderRidge

 

[uscitizen]

hi boulderridge,

did you ever see that 'mcgiver' episode where after returning from reconnoitering the strength of the bad guys, he tells his tiny group of stalwarts that there's good news and that there's bad news and which do they want to hear first? 'the good news' the fair heroine chimes in, to which he replies, 'well, the good news is we're all gonna die!' one of the group then shouts, 'if that's the good news, what's the bad news?!!?'. mcgiver: 'we just don't know when!'

here's the good news, the code ran w/o our friendly compiler's needling us about error 94 again. the bad news is that the data's not getting exported per our friendly downstream folks' requirement...


"LATE_ADVERSE_EVENTS","T95-0036","A5002",10018876,4,"","",19981007

is what we get using the code below; i hope i'm not going to embarrass myself by not seeing something that there's to be seen by another pair of eyes

Option Compare Database


'****** paste this in the module ***** Thread701-811127

Public Function ExportLate_Adverse_Events()
On Error GoTo ErrMe
Dim strSQL As String
Dim cn As ADODB.Connection
Dim rsMyData As ADODB.Recordset
Dim intFileNum As Integer
Dim strCSVPathFile As String

'Set the path variable to your target file path and name
strCSVPathFile = "C:\LTE_AE.csv"

'Put your query into a string variable as a SELECT query not an APPEND query

strSQL = "SELECT LATE_ADVERSE_EVENTS.Table_Name, LATE_ADVERSE_EVENTS.Protocol_ID, LATE_ADVERSE_EVENTS.Patient_ID,"
strSQL = strSQL & " LATE_ADVERSE_EVENTS.AE_Type_Code, LATE_ADVERSE_EVENTS.AE_Grade_Code,"
strSQL = strSQL & " IIf([AE_Other_Specify]='COMPLETE AS APPROPRIATE','',[AE_Other_Specify]) AS AE_OTHER,"
strSQL = strSQL & " Nz(LATE_ADVERSE_EVENTS.AE_Attribution_Code,0) AS AE_Attribution , LATE_ADVERSE_EVENTS.AE_Start_Date FROM LATE_ADVERSE_EVENTS;"



'Open a connection (required) and a recordset (rsMyData) on your query
'The recordset allows you to "walk" through the query results one row at a time
Set cn = CurrentProject.Connection
Set rsMyData = New ADODB.Recordset
rsMyData.Open strSQL, cn, adOpenStatic, adLockOptimistic

'FreeFile returns an Integer representing the next file number available for use by the Open statement.
intFileNum = FreeFile(0)

'This line opens your target file so that you can write to it
'It creates the file if it doesn't exist yet
'I DON'T KNOW whether it overwrites an existing file or appends to it--please test!

Open strCSVPathFile For Output Access Write As #intFileNum

'*****
Do Until rsMyData.EOF 'Until you reach the end of the query result set...
'Write the next line of results to your file
'(The underscore "_" is a line continuation character, making a long line more readable

Write #intFileNum, rsMyData!Table_Name, rsMyData!Protocol_ID, rsMyData!Patient_ID, _
IIf(rsMyData!AE_Type_Code = 0, "", CLng(rsMyData!AE_Type_Code)), IIf(rsMyData!AE_Grade_Code = 0, "", CInt(rsMyData!AE_Grade_Code)), _
rsMyData!AE_OTHER, IIf(rsMyData!AE_Attribution = 0, "", CInt(rsMyData!AE_Attribution)), CLng(Format(rsMyData!AE_Start_Date, "yyyymmdd"))

'Move to the next row of query results
rsMyData.MoveNext
Loop
'*****

Close #intFileNum 'When you are done, close the file

ExitMe:
'Put away your toys when you are done...
Set rsMyData = Nothing
Set cn = Nothing
Exit Function

ErrMe:
MsgBox Err.description & " (" & Err.Number & ")", vbOKOnly, "Error writing Late Adverse Events file"
Resume ExitMe

End Function
'****** end of paste *****





“The philosophy of the school room in one generation will be the philosophy of government in the next.&quot; --- Abraham Lincoln

 

[uscitizen]

hi, i just want to quickly supplement what i just finished writing with the following , i n the unlikely event i've successfully, albeit unintentionally, confused you and/or the issue;

if AE_Attribution_Code is entered as the number 4, our code will reply with:

"LATE_ADVERSE_EVENTS","T95-0036","A5002",10018876,4,"",4,19981007

and this is okay, since it's a numeric field.

we already saw that when it's allowed to go un entered, that the code returns with:

"LATE_ADVERSE_EVENTS","T95-0036","A5002",10018876,4,"","",19981007

which is not okay, since our client/downstream want

"LATE_ADVERSE_EVENTS","T95-0036","A5002",10018876,4,"",,19981007

when a number field is empty.

“The philosophy of the school room in one generation will be the philosophy of government in the next.&quot; --- Abraham Lincoln

 

[BoulderRidge]

Didn't realize the blank string was not sufficient--thanks for the clarification. Well, I've nosed around a bit and haven't found any nice solutions to writing the kind of line in your example. I did find that if your Write statement specifies nothing at all for a field, just the comma, you get exactly what you want in your text file. However, the nothing is in your case conditional, and therein lies the rub. The only thing I can think of at the moment to write that would be to create multiple Write statements, one for each combination of possible Null vs populated fields, and then use either Case or nested If..Else statements to get to the one Write statement that works for the current line of data. Not pretty, but could work.

If this sounded like gobbledygook I can try and rough it out for you. Can you tell me which fields in your "Late Adverse Events" example must be written totally empty (",,") when null and which ones should be written as blank strings (","",") when null? Then I can show you what I'm thinking...

Unless your client's requirements might be flexible...are you sure this is a firm requirement as opposed to an arbitrary one?

 

[uscitizen]

hi boulderridge,

this seems like an awful amount of seemingly inescapable work -- i went into this thinking the export part would be quite straightforward: fields are either text or number and everything'd follow from there. but 'nothing' (nullity) has gotten in the way it'd seem.

at any rate, since i'm home and therefore not able to glance through the instructions and documentation that are driving this effort, i can roughly recollect/guess that the fields that are either number or date in type would be expressed as being absent by the appearance of two contiguous commas (to use your terminology, ",,"). the others would be written as "two blank strings when null" (","",") when null.

from the example above:

"LATE_ADVERSE_EVENTS","T95-0036","A5002",10018876,4,"",,19981007

i have bolded the fields that belong to the first group,
AE_Type_Code,AE_Grade_Code,AE_Attribution,AE_Start_Date

my feeling is quite strong that i have recollected them correctly but will double check the source tomorrow and comment further if warranted.



“The philosophy of the school room in one generation will be the philosophy of government in the next.&quot; --- Abraham Lincoln

 

[BoulderRidge]

Thanks for the info. I will take a stab at this after my noon conference call today with my "real job." I agree it shouldn't be this hard or this messy...but barring a new discovery I don't know what else to do but keep plowing forward.

Can you for the code example above clarify that all four of the fields you listed could legitimately be null in the export? THanks! ...BoulderRidge

 

[uscitizen]

hi boulderridge,

as usual it's good to see your reply...

i have done a bit of add'l snooping 'round and this' resulted in the following structure to the code for Adverse Events...

(just the really 'relevant' parts are excerpted below)


strSQL = "SELECT ADVERSE_EVENTS.Table_Name, ADVERSE_EVENTS.Protocol_ID, ADVERSE_EVENTS.Patient_ID, ADVERSE_EVENTS.Course_ID,"
strSQL = strSQL & " ADVERSE_EVENTS.AE_Type_Code, ADVERSE_EVENTS.AE_Grade_Code, IIf([AE_Other_Specify]='COMPLETE AS APPROPRIATE','',[AE_Other_Specify]) AS AE_OTHER,"
strSQL = strSQL & " ADVERSE_EVENTS.AE_Attribution_Code, ADVERSE_EVENTS.AER_Filed FROM ADVERSE_EVENTS;"



'Open a connection (required) and a recordset (rsMyData) on your query
'The recordset allows you to "walk" through the query results one row at a time
Set cn = CurrentProject.Connection
Set rsMyData = New ADODB.Recordset
rsMyData.Open strSQL, cn, adOpenStatic, adLockOptimistic

'FreeFile returns an Integer representing the next file number available for use by the Open statement.
intFileNum = FreeFile(0)

'This line opens your target file so that you can write to it
'It creates the file if it doesn't exist yet
'I DON'T KNOW whether it overwrites an existing file or appends to it--please test!

Open strCSVPathFile For Output Access Write As #intFileNum

'*****
Do Until rsMyData.EOF 'Until you reach the end of the query result set...
'Write the next line of results to your file
'(The underscore "_" is a line continuation character, making a long line more readable
Write #intFileNum, rsMyData!Table_Name, rsMyData!Protocol_ID, rsMyData!Patient_ID, rsMyData!Course_ID, _
IIf(rsMyData!AE_Type_Code = 0, "", CLng(rsMyData!AE_Type_Code)), IIf(rsMyData!AE_Grade_Code = 0, "", CInt(rsMyData!AE_Grade_Code)), _
rsMyData!AE_OTHER, IIf(IsNull(rsMyData!AE_Attribution_Code), vbNullChr, CInt(Nz(rsMyData!AE_Attribution_Code))), rsMyData!AER_Filed

when i don't enter anything into the AE_attribution_Code field in the underlying table, the above layout generated

"ADVERSE_EVENTS","T95-0036","A5001","2",10018876,4,"",,"1"

which is good, 'cause the troublesome appearance of the pair of double quotation marks demarking the missingness of the number is no longer an issue.

obversely (is this good english?) when i enter a plausible value in the field, the code generates aes.csv having this structure:
"ADVERSE_EVENTS","T95-0036","A5001","2",10018876,4,"",4,"1"

which is good, since it's a number field and client is requesting it not be enclosed with pairs of double quotations.

the results look encouraging and i'm planning on broadening the scope of its usage to the other fields in this module that would warrant it and to other modules.

i'll get back with more on this after a little more testing.

stay tuned...







“The philosophy of the school room in one generation will be the philosophy of government in the next.&quot; --- Abraham Lincoln

 

[BoulderRidge]

Morning,

Looks like an excellent find! I'll have to remember that one. Seems like you could even drop the Nz function from the Write statement line since you are already testing for Null in the IIF and it won't get to your CInt part if it is Null.

Let me know if this seals the deal! B-) --BoulderRidge

 

[uscitizen]

good morning,

here's the rub...


i was modifying the Best Responses module and find that when there's no value entered in the "Observed Date" field of the underlying table's, we see this "Type mismatch(13)" error

the code's below

Option Compare Database

Public Function ExportBest_Responses()
On Error GoTo ErrMe
Dim strSQL As String
Dim cn As ADODB.Connection
Dim rsMyData As ADODB.Recordset
Dim intFileNum As Integer
Dim strCSVPathFile As String


strCSVPathFile = "C:\BST_RESP.csv"

strSQL = "SELECT BEST_RESPONSES.Table_Name, BEST_RESPONSES.Protocol_ID, BEST_RESPONSES.Patient_ID,"
strSQL = strSQL & " BEST_RESPONSES.Category, BEST_RESPONSES.Observed_Date FROM BEST_RESPONSES;"

Set cn = CurrentProject.Connection
Set rsMyData = New ADODB.Recordset
rsMyData.Open strSQL, cn, adOpenStatic, adLockOptimistic

intFileNum = FreeFile(0)

Open strCSVPathFile For Output Access Write As #intFileNum

Do Until rsMyData.EOF
Write #intFileNum, rsMyData!Table_Name, rsMyData!Protocol_ID, rsMyData!Patient_ID, _
rsMyData!CATEGORY, IIf(IsNull(rsMyData!Observed_Date), vbNullChr, CLng(Nz(Format(rsMyData!Observed_Date, "yyyymmdd"))))


rsMyData.MoveNext
Loop


Close #intFileNum

ExitMe:

Set rsMyData = Nothing
Set cn = Nothing
Exit Function

ErrMe:
MsgBox Err.description & " (" & Err.Number & ")", vbOKOnly, "Error writing Best Responses file"
Resume ExitMe

End Function


IFthere's no slick way to get 'round this unwelcome fact, i was wondering about having a default value each 'Observe_Date' field would get, like July 4, 1776 or whatever that'd be something out of range that would get replaced with vbNullChr?


“The philosophy of the school room in one generation will be the philosophy of government in the next.&quot; --- Abraham Lincoln

 

[BoulderRidge]

Hmmm...

1. Can you isolated the error to one function call in your bold section of code by pasting one layer at a time from the inside out into the immediate window?

2. What happens if you remove the Nz function call there?

...waiting for the next installment....--BoulderRidge