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 might want to try the Round() function directly on the decimal numbers, as in Round([Dosage],3) to get three decimal places. I have used this in SQL in Access 2000 but have not used it to dump to a csv file. Good luck!

 

[uscitizen]

mind letting me have the expression you had in mind?

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

 

[BoulderRidge]

It would just be:

expr1: Round([Dosage],3) and expr2: Round([Height],1) and expr3: Round([Weight],1)

Which version of Access are you using?

 

[uscitizen]

hi,

i'm using ms access 2000 on a windows 2000 desktop....

let me show you the sql version of one of the query designs i'm using

SELECT COURSE_AGENTS.Table_Name, COURSE_AGENTS.Protocol_ID, COURSE_AGENTS.Patient_ID, COURSE_AGENTS.Course_ID, COURSE_AGENTS.Agent_ID, COURSE_AGENTS.Dose_Change, Round(Val(Format([Dose_Amount],"0.000")),3) AS Expr1, COURSE_AGENTS.Unit_Code INTO COURSE_AGENTS_Exporting
FROM COURSE_AGENTS;

and now the other's

SELECT TREATMENT_COURSES.Table_Name, TREATMENT_COURSES.Protocol_ID, TREATMENT_COURSES.Patient_ID, TREATMENT_COURSES.Course_ID, TREATMENT_COURSES.Course_Start_Date, TREATMENT_COURSES.TX_Asgnmnt_Code, TREATMENT_COURSES.Treating_Inst_ID, Round(Val(Format([Height],"0.0")),1) AS Expr1, Round(Val(Format([Weight],"0.0")),1) AS Expr2, TREATMENT_COURSES.AE_Experienced INTO TREATMENT_COURSES_Exporting
FROM TREATMENT_COURSES;


and i would add that in the instance of each exported table's, i find that the values of the three fields i wrote about have two significant digits beyond the decimal which are shown in the pasted outcome below:

"TREATMENT_COURSES","T95-0036","A5001",1,19961015,"A1","NY043",170.50,61.30,"2"
"TREATMENT_COURSES","T95-0036","A5001",2,19961021,"A1","NY043",170.50,61.30,"1"
"TREATMENT_COURSES","T95-0036","A5002",1,19961018,"A1","NY043",152.40,73.60,"2"
"TREATMENT_COURSES","T95-0036","A5003",1,19961018,"A1","NY043",180.30,95.40,"2"
"COURSE_AGENTS","T95-0036","A5001",1,673089,"2",258.12,"mg"
"COURSE_AGENTS","T95-0036","A5001",1,119875,"2",375.00,"mg"
"COURSE_AGENTS","T95-0036","A5001",2,673089,"2",375.00,"mg"
"COURSE_AGENTS","T95-0036","A5001",2,119875,"2",375.00,"mg"
"COURSE_AGENTS","T95-0036","A5002",1,673089,"2",245.00,"mg"
"COURSE_AGENTS","T95-0036","A5002",1,119875,"2",350.00,"mg"
"COURSE_AGENTS","T95-0036","A5003",1,673089,"2",278.00,"mg"
"COURSE_AGENTS","T95-0036","A5003",1,119875,"2",380.00,"mg"

i will reprise once again the suggestion you put forth, but i believe the results were identical even then?




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

 

[BoulderRidge]

I'll admit I didn't see why this was hard until I tried an example. Per Microsoft (

http://support.microsoft.com/default.aspx?scid=kb;en-us;208408&Product=acc2000)

"If you have a table that contains a Number field with more than two decimal places, and you export the table to a text file, the decimal places are truncated to two positions." Their "resolutions" both involved exporting the numbers as text which results in the quotations marks you tried to avoid. Weird.

A different approach could involve writing the file from code using the Write command and looping thru a recordset on the data you want to write. Some sample code I've used to do this is pasted below. It is not modified for your particular data and I don't have time to test it right now but I think if your recordset fields were numeric and rounded correctly they would write to the file without quotes. Let me know if you need more info:

intFileNum = FreeFile(0)
Open strCCPathFile For Output Access Write As #intFileNum
Write #intFileNum, strStamp
Write #intFileNum, gstrMbrFilters
Do Until rsMbrID.EOF
Write #intFileNum, rsMbrID!PatientID, rsMbrID!Name, rsMbrID!Phone
i = i + 1
rsMbrID.MoveNext
Loop
Close #intFileNum

 

[uscitizen]

will see if i can work through it

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

 

[uscitizen]

well, i guess i'm re-contacting you boulderridge for a slightly more detailed explanation regarding what the various commands do and where all this would fit into my database. what i have not gone into in very great detail (because it did not warrant going into) is the fact that the height and weight are gotten from one query. dosage is gotten by a query on a separate table than height and weights'. and that i have a macro which runs each of the queries in turn and then launches a RunCode command below

SHELL("cmd /c copy C:\COLL.csv+C:\CORR.csv+C:\PUBS.csv+C:\AUTH.csv+C:\PXS.csv+C:\PX_RACE.csv+C:\PRI_TXS.csv+C:\TX_CRS.csv+C:\CRSE_AGNTS.csv+C:\BSLNE_ABNR.csv+C:\AES.csv+C:\LTE_AE.csv+C:\BST_RESP.csv+C:\TRL_COM.csv+C:\PH1.csv+C:\PH1_DLT.csv C:\CDUS.csv")

which appends them in the same way they were hatched into CDUS.csv. the files CRSE_AGNTS.csv and TX_CRS.csv are the result of running the two queries which generate the height, weight and dosage informations.

lastly, you'd think the braintrust at ms would've come up with a resolution to their resolution by now?!

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

 

[BoulderRidge]

Hi,

I've taken the liberty of putting one of your queries into the code example as listed below. If you go to your Modules tab, click New, and paste this code in there you will have created a function procedure (save the module to a name of your choosing and add other functions to it as needed.)

You can call a function procedure from the RunCode command of a macro. This function should query the data from your source table and write the results directly to your target file. It doesn't append to the intermediate table that the original query used but if you still need to do that it would not be a hard modification.

Let me know if you have questions...

'****** paste this in the module *****
Public Function ExportCourseAgents()
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:\CRSE_AGNTS.csv"

'Put your query into a string variable as a SELECT query not an APPEND query
strSQL = "SELECT COURSE_AGENTS.Table_Name, COURSE_AGENTS.Protocol_ID, COURSE_AGENTS.Patient_ID, "
strSQL = strSQL & "COURSE_AGENTS.Course_ID, COURSE_AGENTS.Agent_ID, COURSE_AGENTS.Dose_Change, "
strSQL = strSQL & "Round([Dose_Amount],3) AS DoseAmt, COURSE_AGENTS.Unit_Code FROM COURSE_AGENTS;"

'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, rsMyData!Agent_ID, rsMyData!Dose_Change, _
rsMyData!DoseAmt, rsMyData!Unit_Code
'Move to the next row of query results
rsMbrID.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 Course Agents file"
Resume ExitMe

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

If this code works for you, you could make another copy of the function with a new name for your other query. All you need to edit is the strSQL contents, the strCSVPathFile target file name, the fields listed in the Write statement, and the error message.

 

[uscitizen]

this looks really cool i'll definitely give it a go!

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

 

[uscitizen]

hi boulderridge,

well....i replaced the query call in the macro with the RunCode invocation of ExportCourseAgents() which compiled successfully into the new module it's been pasted to. that's the good news, but the 'bad' are the fact i get a message titled 'Error writing Course Agents File' the body of which reads "Object required (424)". make any sense to you.


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

 

[uscitizen]

hi again; a postscript possibly relevant....there's one record written to the CRSE_AGNTS.csv file which can be viewed after exiting from MS A2K. Until then, you're told something about the file being unavailable owing to its being accessed by another user or other. there must be a way to trap when this error occurs.

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

 

[uscitizen]

darn! forgot to tell you that when i rename it to have a 'txt' extension and view the results, you get to see all elements are being exported as TEXT!

"COURSE_AGENTS","T95-0036","A5001","1","673089","2","258","mg"

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

 

[BoulderRidge]

Hi again,

The object error you got was a silly typo on my part (sorry.) I added a CDbl() function to the numeric field you are writing to force it to lose the quotes. Works on my end, hope it does on yours. The only drawback now is that I don't see a way to always force 3 decimal places and avoid the quotes on the same field; I think you have to live with one or the other. Maybe you can reformat the numbers to 3 decimals when you display them later...

Anyway, just replace the DO UNTIL...LOOP lines with the following and give it a try:

'*****
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, rsMyData!Agent_ID, rsMyData!Dose_Change, _
CDbl(rsMyData!DoseAmt), rsMyData!Unit_Code
'Move to the next row of query results
rsMyData.MoveNext
Loop
'*****

Good luck!
PS--I like your Lincoln quote.

 

[uscitizen]

thanks boulderridge, but unless i'm misunderstanding the endresult of using all this would be to bring me back to where i started, i.e. quotes around text including the value of dosage (which would be treated as text) albeit with 3 decimal places of precision.

in other words, if i ran the code w/ the modification now, would it result in a record like

"COURSE_AGENTS","T95-0036","A5001","1","673089","2","258.000","mg"

vs what i 'want', i.e.,

"COURSE_AGENTS","T95-0036","A5001",1,673089,"2",258.000,"mg"

a few people have favorably commented on the lincoln quote, btw. i'm sure 'honest abe' would be gladdened to know that.

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

 

[BoulderRidge]

Not quite. the new code will not write the quotes. it just will not force three digits when the base number does not have three digits. So in your example you will get:

"COURSE_AGENTS","T95-0036","A5001",1,673089,"2",258,"mg" if your source dosage was 258. If you source was 258.123456 then you would get:

"COURSE_AGENTS","T95-0036","A5001",1,673089,"2",258.123,"mg" because it would not allow more than three digits.

If I find a way to write it numeric and force empty digits I will definitely let you know.

 

[uscitizen]

i'm still awaiting reply to an outstanding email from client's reps but i hope/think this may fly, i.e. the idea that height/weight/dosage with nothing after the decimal point need not have a '0' (zero) or '000' (three zeroes).
certainly, md mdb is fixed so that height/weight can only be input with a single digit past the dec pt and dosage with no more than three digits of precision (258.123456 could never be entered using the input format).



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

 

[uscitizen]

i'm still waiting to hear back from my downstream 'clients'.

in the meanwhile, i've coded the module with the newest version of the do until loop.

on another thread, what about the data in the following lines (from my earlier queries):

"TREATMENT_COURSES","T95-0036","A5001",1,19961015,"A1","NY043",170.50,61.30 ,"2"
"TREATMENT_COURSES","T95-0036","A5001",2,19961021,"A1","NY043",170.50,61.30,"1"
"TREATMENT_COURSES","T95-0036","A5002",1,19961018,"A1","NY043",152.40,73.60,"2"
"TREATMENT_COURSES","T95-0036","A5003",1,19961018,"A1","NY043",180.30,95.40,"2"

the highlighted values (for height and weight, respectively, in cm and Kg units of measurement) were entered with single digits following the decimal point and should appear in this text file with just single digits?


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

 

[BoulderRidge]

Hi again,

I was not quite clear on whether you have created a second function procedure with modifications for the second query. If you have done that and your results are as you just posted, all you need is to modify the "Round" function on the SQL strings to round to 1 decimal place. Then when you write this file you will not get more than 1 decimal place. You could possibly still get 0 decimal places if the number entered did not have a fractional part.

If you haven't created the second function procedure yet, make a copy of all the code from the "Public Function.." to the "End Function" line. Then edit the name of the function, the reference to Coarse Agents in the Msgbox message at the end, and change the file, SQL and recordset lines to match your second query and output file. These are the file, SQL and recordset lines you would need to customize:

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

'Put your query into a string variable as a SELECT query not an APPEND query
strSQL = "SELECT COURSE_AGENTS.Table_Name, COURSE_AGENTS.Protocol_ID, COURSE_AGENTS.Patient_ID, "
strSQL = strSQL & "COURSE_AGENTS.Course_ID, COURSE_AGENTS.Agent_ID, COURSE_AGENTS.Dose_Change, "
strSQL = strSQL & "Round([Dose_Amount],3) AS DoseAmt, COURSE_AGENTS.Unit_Code FROM COURSE_AGENTS;"

Write #intFileNum, rsMyData!Table_Name, rsMyData!Protocol_ID, rsMyData!Patient_ID, _
rsMyData!Course_ID, rsMyData!Agent_ID, rsMyData!Dose_Change, _
CDbl(rsMyData!DoseAmt), rsMyData!Unit_Code


The new SQL would be from your second query and you would have two items with the Round function if you wanted to enforce no more than 1 decimal point each. The new Write statement could use the same recordset name (rsMyData) but each field name (after the !) would need to be changed to match your new query fields and you would need one matching recordset field for each query field. Use the "Cdbl" function on each numeric field that you want written without quotes.

Let me know if this doesn't answer your question...

 

[uscitizen]

following my hastily drafted request (above), i saw the proverbial light (as in, the fact i'd overlooked something you'd written into your responses), and created a code module for the TREATMENT_COURSES function:

------------------------------------------------------------

Option Compare Database

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

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

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

strSQL = "SELECT TREATMENT_COURSES.Table_Name, TREATMENT_COURSES.Protocol_ID, TREATMENT_COURSES.Patient_ID,"
strSQL = strSQL & " TREATMENT_COURSES.Course_ID, TREATMENT_COURSES.Course_Start_Date, TREATMENT_COURSES.TX_Asgnmnt_Code,"
strSQL = strSQL & " TREATMENT_COURSES.Treating_Inst_ID, Round([Height],1) AS PX_Height, Round([Weight],1) AS PX_Weight, TREATMENT_COURSES.AE_Experienced"
strSQL = strSQL & " FROM TREATMENT_COURSES;"


'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, rsMyData!Course, Start_Date, rsMyData!TX_Asgnmnt_Code, _
rsMyData!Treating_Inst_ID, CDbl(rsMyData!PX_Height), CDbl(rsMyData!PX_Weight), _
rsMyData!AE_Experienced

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

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

------------------------------------------------------------

as i haven't gotten the data enterer involved yet, i don't know 1st hand that it works or not, however some of the original debug errors (like a 3704) disappeared which is encouraging.




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