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

 

[BoulderRidge]

You're going to hit a little snag with the comma where there should be an underscore in this line:
rsMyData!Course_ID, rsMyData!Course, Start_Date, rsMyData!TX_Asgnmnt_Code, _

But the rest looks great. FYI, you can put multiple function procedures in the same module if you want to. Just leave a couple of blank lines between the End Function and the next Public Function line to make it easy to find them.

Good luck!

 

[uscitizen]

darn right i would.
corrected it.
still don't know what you meant about the appending unless it's to tell me that the "...Exporing" tables i was using for the two csvs aren't needed now that we're handling it via vba because the the job of exporting these two doesn't depend on their existence.

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

 

[uscitizen]

i guess i'm writing you again boulderridgeman....

curiousity got the best of me and i had an overpowering urge to enter enough data to let me review the csv file's appearance once copied to be a 'txt' file.


"PUBLICATIONS","999999_+++",1,"fgsdf","adfadf","","",,"",""
"AUTHORS","999999_+++",1,1,"ddsfasda"
"PATIENTS","999999_+++","6666","","",,"","","",,"","","","",,"",,"","","",,,"",""
"TREATMENT_COURSES","999999_+++","6666","1",#NULL#,"","",10.1000003814697,20.6000003814697,""
"COURSE_AGENTS","999999_+++","6666","1","723227","1",301,"billion pfu"
"COURSE_AGENTS","999999_+++","6666","1","722277","1",323.654,"cm"


the result above suggests something's slightly awry with the height and weight values which were entered as 10.1 and 20.6 into their respective fields. their table properties which i just recorded are as follows: field size = single, format = fixed, decimal = 1, input mask = 9999.9. so, i guess one of my questions are fairly predictable, what happened to prompt their display as per the above? my suspicion's that it might have something to do with the use of the Dbl function, but...? the 2nd one's over the fact that a number field in its TREATMENT_COURSE table is showing up as a text field in the txt file above, as "1" vs 1 in both the COURSE and TREATMENT outputs. hmmm?

on the other hand, i entered a pair of records, each with a slightly different value of the Dosage parameter (one was integer and the other was entered with three significant dts past the decimal) and the text file's got them right.

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

 

[BoulderRidge]

Yes that's exactly what I meant about the appending thing.

I love it when code does not behave consistently (not!) The first thing I'd try is switching your CDbl functions to CSng instead; lower precision might eliminate the bleeding decimals. As for the other number field that is showing up as "1," it seems that no matter what the field type is in your table, it defaults to writing it as text in the output file unless you use a numeric function in the write statement. So I'd try adding "CInt" to those integer fields that you want to lose the quotes on, the same way you used the CDbl or now hopefully CSng functions on the decimal fields.

Another thought is that IF you get the above issue working satisfactorily, you could then try removing the "Round" function from the SQL since your input parameters really should be already taking care of that. No point in doing more processing than necessary. Just don't change two things at the same time or it becomes difficult to determine which change caused which effect.

Sorry this has not been more straight-forward; glad you've been patient and persistent with the problem. And FYI, it's actually boulderridgewoman....

 

[uscitizen]

right you are boulderridgewoman...
she works just fine now and i'm mighty grateful for all the attention. patience you say....this'd taken forever w/o your good offices.

took your ideas & implemented 'em linearly (meaning in sequence) and humpty dumpty's back together again.

i'd be willing to bet this is the subject of (yet) another thread but did you notice how when i left the "Course_Start_Date" empty, the vba wrote it out as #NULL#?. this is bound to a table field with long integer property (as YYYYMMDD)and when the user's cursor gets to the appropriate entry field on the data entry form, a got-focus event property is actuated with the following code

good_date = Format(CDate([Course_Start_Date].Text), "yyyy/mm/dd")

my users may have to fill this in so the above behavior may be a moot point.

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

 

[BoulderRidge]

Very glad to hear it is behaving better! As for your date, there are lots of options. First if you are going to require the user to fill it in, then all you might want to do is test that they have filled in a reasonable date, usually with some comparison to Now() depending on whether reasonable is in the past or future, etc. On the other hand, if you don't require the date, you could test for the user leaving it blank and then fill in a predetermined value that your recipient would recognize like 9999/99/99 or similar, either on the After Update event of your control or data entry form or else right in your export module's Write statement. I'm a little curious as to why you chose to store your dates as long integer rather than either Date or Text data types, which tend to be easier to parse and validate...but there's usually a reason...

 

[uscitizen]

hi,

on the subject of motivation behind choosing long integer which is (hopefully still) valid...

my downstream clients want date values to appear in the ensuing txt file as follows

YYYYMMDD

but not as

"YYYYMMDD"

which in the instance of using the datetime data type would ensue....unless there's some nifty way of circumventing that tendency of ms access 2000's.

just by way of introduction, in the case of "Course_Start_Date" the vba code behind the form's control for it i'm using is as under:

Option Compare Database

Private Sub Course_Start_Date_BeforeUpdate(Cancel As Integer)
If IsDate([Course_Start_Date].Text) Then
good_date = Format(CDate([Course_Start_Date].Text), "yyyy/mm/dd")
x = MsgBox("If you did not mean to enter " & _
Format(good_date, "mmmm d, yyyy") & "," & Chr(13) & "then please correct it now.", 321)
Else
x = MsgBox("The date you entered is not a valid date." & Chr(13) & "Please re-enter it immediately!", 273, "Error!")
End If
End Sub

Private Sub Course_Start_Date_GotFocus()
On Error GoTo Err_Form_GotFocus

good_date = Format(CDate([Course_Start_Date].Text), "yyyy/mm/dd")

Err_Form_GotFocus:
If Err.Number = 13 Then 'Type mismatch
x = MsgBox("Either the date you entered is not a valid date or the field is empty." & Chr(13) & "Please (re-)enter it immediately!", 273, "Error!")
ElseIf Err.Number <> 0 And Err.Number <> 13 Then
x = MsgBox("An error number " & Err.Number & " with description " & Err.description & "occurred. See Admin", vbCritical, "Un-Expected Error")
End If
End Sub

perhaps there's an approach that strikes you as less convoluted you might share?

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

 

[BoulderRidge]

You know, there is something to be said for "if it's not broken, don't fix it." So these are just suggestions if you feel like tinkering...

If the only entity who wants to see your date in YYYYMMDD format is downstream from the text file, you could make your table field a Date/Time data type. Then you could use either your "reversed" input mask of 9999/99/99;;_ or the more commonly seen 99/99/9999;;_ for your data entry users. You can also specify a matching Format property of yyyy/mm/dd for display since the default display will be mm/dd/yyyy regardless of your input mask, and you can specify it either at the table field level or the form control level. The advantage to using a Date/Time data type is that Access will automatically force you to have a valid date and you wouldn't need to use CDate or IsDate in your validations. Having it already a date also makes it easy to compare with other dates. You could even write your validation rule into the table field or form control Validation Rule and Validation Text properties. Be sure if you do this that you include "or Is Null" in the Validation Rule property if you don't want to absolutely require a date; else your user can be stuck forever on the control.

If you try that approach, then to get the saved dates into your text file format all you need to do in the export module Write statement is something like: CLng(Format(rsMyData!Course_Start_Date,"yyyymmdd")). The Format part converts the date value into the appropriate string and then the CLng part converts that text string into long integer.

By the way, I really liked how you showed the user their newly entered date in the "mmmm d, yyyy" format since that is very easy to recognize. However, they might get annoyed if you popped it up every time they entered a date--maybe only if they entered a date that was questionable, like too far in the past or the future to fit the circumstances.

 

[uscitizen]

i was talked into thinking that the only way a datetime field was going to get into a text file was as a text field ("YYYYMMDD")-- not as a number field (YYYYMMDD) hence all the workarounds conditioned on the fact we're talking about entering an integer valued number which is entered to look like the desired value, YYYYMMDD. i'm not sure time permits to code what i feel to be your interesting approach into the vba, however when it does, i will and get back in touch.

lastly, my only regret which has arisen pursuant to our interaction is that this website seems to have an upper limit of one star per submitter -- otherwise you'd have a bigger dipper to go with your time and efforts

“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 kind remarks B-) and best of luck with your project! --BoulderRidge

 

[uscitizen]

hi boulderridge!

i guess i'm writing 'cause i took the bait and decided to make a copy of my mdb file and perform some enhancements/improvements following some reflection on the time available for it.

there are a small number of tables in the db where dates are entered (even YYYYMM) when the data type was configured to be integer valued and vba code similar to the one above was used to test/convert for export purposes.

suffice it to say, i agree that having genuine datetime entered is the ideal (especially if down the road the chronological (vs 'integer') meaning of the information is needed w/in the database itself), so i sort of took on the task of beginning with my PATIENTS table which, among other things, collected Birth_Date (as YYYYMM), Date_Of_Entry (YYYYMMDD), Last_TX_Date (YYYYMMYY), Off_Study_Date (YYYYMMDD). after having converted the fields to datetime, changed the input mask to MM/YYYY or MM/DD/YYYY, set the format to mm/yyyy or Medium Date, removed the event property vba code and generally ransacked the place, i modified the modules as follows:

Option Compare Database

'****** 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 *****

which brings me to the reason for the gory details......

"Error writing Patients file: Invalid Use of Null(94)" is showing up; can I test/trap for it somehow?

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

 

[BoulderRidge]

Very nice, and ambitious too! Sure, that is the advantage of code over macros, you can test and trap to your heart's content.

You actually are trapping the error already, with the On Error Goto ErrMe and corresponding code that gives you a controlled & customized error message instead the Access default one. The next step would be to determine exactly why this error occurs so that you can prevent it or handle for it better. In this case, I would first determine where in the procedure the error is occurring. You may know how to step thru code but in case you don't i'll explain briefly...

Go to your module in the Visual Basic window. Locate the first executable line in your function after the Dim statements, probably this one: strCSVPathFile = "C:\PXS.csv"
Click once with your mouse in the gray margin just left of the code line. It should put a maroon dot in the margin and highlight the line with the same color. This sets a breakpoint so that when the code runs, it stops on this line and lets you watch. Then you can probably test run your function by itself directly from the immediate window (unless it requires previous steps to be run right before it, if so just run them as usual but leave the VB window open and the breakpoint set.)
To run from the immediate window, from the VB View menu pick "Immediate Window." It opens a narrow window below your code. In your code, highlight the name of the function you want to test (like "ExportPatients") and copy. In the immediate window, type a question mark (?) and then paste your function name. When you hit ENTER it runs the function. It will stop on your breakpoint line, highlighted in yellow. From this point, the F8 key will walk you one line at a time thru your code. When you are on a line, you can mouse-over variables and see their current values (or lack of them). If you step this way thru the module, you will find that suddenly it jumps to your Error routine. The line that makes it jump is the line with the error. I'm guessing it will be the Write statement but not sure...
Once you know which line, you can check for which variable or value in the line looks suspicious. Let's say it is your Write line. While the code is still active (even if it is poised on the ErrMe part), you can copy an individual variable (like "CLng(Format(rsMyData!Birth_Date, "yyyymm"))") and in your immediate window, on a new line, type a question mark and paste the variable and hit enter. It should respond with the current value of that variable, or an error message.
FYI, as I am writing this and browsing your code I see two other things you might want to check first. 1) on the Write statement Birth_Date item mentioned above, you have a semicolon instead of a comma between that and the next field. 2) sometimes in code strings the use of double quotes within the string gets confused with quotes that define the string itself. Your Write statement is not exactly a string variable assignment, so that might not be an issue, but it wouldn't hurt to try changing all the double quotes in your Format function calls to single quotes if nothing else seems to work.
IF you discover the problem is with trying to Format or CSng a null value in your Write statement, the preferable solution is to test for Null upstream and fill in a recognizable default value (like 99/99/9999) that won't cause the error but won't mess up your downstream client's data integrity. Alternatively, you could use an IIF statement on each potentially null value right in the Write statement and write something directly to the text file alone. Like IIF(IsNull(rsMyData!Birth_Date), 999999, CLng(Format(rsMyData!Birth_Date, "yyyymm"))).
Enough said, good luck and let me know how it turns out! B-)

 

[uscitizen]

hi boulderridge,

i think anyone reading through your mini-tutorial on vba debugging will not but fail to find it invaluable! kudos, as usual (and try though i do, this website is fixated on a one star policy).

the breakpoint method you painstakingly described worked (i think)!! it uncovered the fact that 'NULL' had been stuffed into two of the number fields towards the end of the write string. one of them, disease code must be filled in by the enterer and the other, number of previous regimens almost certainly will be, so upon having myself entered valid data into them and re-run things, we seem to be experiencing the illuminative sunshine of razor sharp minds once again.
[removing the typo semi-colon probably did not hurt, but trying to replace the double quotations (") with single quotations (') in the write statement got the compiler/interperter to blush and turn my code beet red in the write statement]

on another note, while awaiting your (hoped for) response, i walked a bit further out on the proverbial plank and drafted yet another bit of code to handle the exporting of data from a "COLLECTIONS" table which i've gone ahead and pasted below:

Option Compare Database

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

Public Function ExportCollections()
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:\COLLS.csv"

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

strSQL = "SELECT COLLECTIONS.Table_Name, COLLECTIONS.Protocol_Id, COLLECTIONS.Subm_Date, COLLECTIONS.CutOff_Date,"
strSQL = strSQL & " COLLECTIONS.Current_Trial_Status_Code, COLLECTIONS.Current_Trial_Status_Date,"
strSQL = strSQL & " COLLECTIONS.Completer_Name, COLLECTIONS.Completer_Phone, COLLECTIONS.Completer_Fax,"
strSQL = strSQL & " COLLECTIONS.Completer_Email, COLLECTIONS.Change_Code FROM COLLECTIONS;"

'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, CLng(Format(rsMyData!Subm_Date, "yyyymmdd")), _
CLng(Format(rsMyData!CutOff_Date, "yyyymmdd")), rsMyData!Current_Trial_Status_Code, _
CLng(Format(rsMyData!Current_Trial_Status_Date, "yyyymmdd")), rsMyData!Completer_Name, rsMyData!Completer_Phone, rsMyData!Completer_Fax, _
rsMyData!Completer_Email, rsMyData!Change_Code

'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 Collections file"
Resume ExitMe

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

having been confused at a higher level than beforehand, i will attempt to apply the principles you described and debug the meaning of the current error that's manifesting,
i.e. "Item cannot be found in the collection corresponding to the requested name or ordinal. (3265)"

catch ya later!



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

 

[uscitizen]

hold those presses! i think i've uncovered the demon in the program:

there's a reference to a variable that is never collected (probably a holdover from the previous version of the same module from which it was copied)...


Write #intFileNum, rsMyData!Table_Name, rsMyData!Protocol_ID,rsMyData!Patient_ID , CLng(Format(rsMyData!Subm_Date, "yyyymmdd")), _

which when highlighted, reveals that it's value is equal to the error message!

i took a course in vba last december, but the teacher ignored my pleading and spent way too much time going over relational operators and the elements of boolean logic and other irrelevant (to me, given that appearances to the contrary i have done a fair amount of programming in 'other' languages) so called 'essentials' and too little on the really important stuff which you succinctly managed to encapsulate. i will definitely keep your outline handy for next time



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

 

[uscitizen]

it seems that almost every time i look in on a data entry form i discover yet another with something a little quirky feature that begs to go into your vba code template. as an example, here's the sql code from my query

strSQL = "SELECT LATE_ADVERSE_EVENTS.Table_Name, LATE_ADVERSE_EVENTS.Protocol_ID, LATE_ADVERSE_EVENTS.Patient_ID,"
strSQL = strSQL & " IIf([AE_Type_Code]=0,"",[AE_Type_Code]) AS AE_Type, IIf([AE_Grade_Code]=0,"",[AE_Grade_Code]) AS AE_Grade,"
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;"

AE_Type_Code is a number field (long integer), as is AE_Grade_Code (byte). AE_Other_Specify (as you'd expect) is a text field.

when the user enters this form, the default values for the three fields are zero, zero, and 'COMPLETE AS APPROPRIATE', the reason being that it allows me to conditionally format the three fields in obnoxious colors to remind the user to resolve (as in complete) the first 2. the third, if allowed to default, must be exported as a blank text field, i.e. "".

so, i guess my question is how to write the "Write" statement - is there a right way? my hunch sort of tells me that the following's got a better'n even chance:

Write #intFileNum, rsMyData!Table_Name, rsMyData!Protocol_ID, rsMyData!Patient_ID, _
CLng(rsMyData!AE_Type), CInt(rsMyData!AE_Grade), rsMyData!AE_OTHER, _
rsMyData!AE_Attribution_Code, CLng(Format(rsMyData!AE_Start_Date, "yyyymmdd"))




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

 

[BoulderRidge]

Looks like a good start to try, with maybe one exception. If you use the numeric conversion functions like CLng or CInt on an empty string ("") like you've created conditionally in your SQL, you'll get a nasty error. I'd suggest that you move your IIF statements for those two items from the SQL section right into the Write statement. Then when the result is a valid numeric, use the function and if not, export the empty string.
Example: leave the original field alone in the SQL and use this in the Write: IIf(rsMyData!AE_Grade_Code=0,"",CInt(rsMyData!AE_Grade_Code))

Now I didn't test these ideas so no guarantee here...also I'm not sure what you'll get in your text file by writing an empty string but should be easy enough to find out.

 

[uscitizen]

y'know, this nasty error messenger you're expecting might be responsible for the one i'm looking at when i runs this; words to the effect that there's a

Syntax error in string query expression 'IIf([AE_Other_Specify]='COMPLETE AS APPROPRIATE',",[AE_Other_Specify]) AS AE_OTHER, LATE_ADVERSE_EVENTS.AE_Attribution_Code, LATE_ADVERSE_EVENTS.AE_Start_Date FROM LATE_ADVERSE_EVENTS;'.(-2147217900)

then again, my brain's turning to spaghetti at this time of day; i'll try your workarounds and see whether it disappears or not.

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

 

[uscitizen]

yet another update of possible interest....

i took this app'n to my home desktop which runs windows xp (as opposed to 2000 on my office machine) and also has ms a2002 (vs a2k on my desktop); nothing was different about the error i received at that point either when running the macro with the offensive module; however, i came up with what seems to have been a productive idea; i had already replaced the pair of double quotations surrounding the default value of the field with single quotations which had a salutatory effect on the interpreter. so, i reasoned i'd try the same substitution on the value of the string conditioned on 'COMPLETE AS APPROPRIATE'. in simple english, i replaced "" with '' and 'lo the complaint has ceased to re-appear (even on my a2k machine). this is promising. hopefully my user/guinea pig will show up today for a test-drive slash field-test and the export file'll match the test sample i'm using for a 'validation'.



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

 

[BoulderRidge]

Sounds like you are making great progress in your project and also picking up some little tips that you can use for many projects to come. Have a blessed Easter weekend, or if you don't celebrate Easter have a great spring holiday!
--BoulderRidge

 

[TheAceMan1]

How are ya BoulderRidge . . . . .

Your carry through on this post was excellent. I can only give ya one star . . . . but its worth one for each post.

Cheers!

See Ya! . . . . . .