How to raise a value up to a limit in VBS

[ZeitenWanderer]

Exporting from Access via VBS works well, now I need to modify a value. A currency field "price" is to exported not as is, but risen to a minimum. So, if usually I would have to export 0,01, I want this to become at least "3", if it is "3" or higher already, leave it untouched. I came up with this:

Code: Select all

   
            function max(a,b)
            max = a
            If b > a Then max = b
            End Function
            
            ts.Write max(rs.Fields("price"), 3) & vbTab

But running the code in MS it stops in the first line, saying this is a syntax error. What is wrong?

[JRL]

Maybe I'm missing something but this works for me.

Code: Select all

VBSTART
  function max(a,b)
  max = a
  If b > a Then max = b
  End Function
              
  ' ts.Write max(rs.Fields("price"), 3) & vbTab
VBEND

VBEval>max("3","0,01"),res1
VBEval>max("3","5,01"),res2

MDL>%res1%%crlf%%res2%

[ZeitenWanderer]

Tried to figure it out myself, but it shows the same behaviour.

Once I run it in the MS editor it complains about the line

function max(a,b)

Calling it a syntax error ...

Next, I do not get, why the line is commented, which should transport the data from db to csv?
Hints appreciated ...

[JRL]

Starting with the commented line.

The write line failed for me so I figured there was something missing from the posted code and commented that line out. It wasn't needed to demonstrate that the "max" function was working. The max function seems to work as long as the values being passed to the function are text values, that is, enclosed in quotes.



For the syntax error.

Are you passing the values for a and b as quoted text?

Are you running this in Macro Scheduler using VBEval> or VBRun>? Are you simply placing the VB code in the script and letting it evaluate on its own? Is this a small portion of a larger Visual Basic script?

Does the script sample I posted run without error if you run the sample alone?

What version of Macro Scheduler are you using?

[ZeitenWanderer]

Ok, these questions already help a bit.

Well, I am using the latest MS version.
And yes, I am running it inside a greater VBS script, using VBRUN.

[snippet=]
VBSTART
Function GetData(Quantity,Sonderangebot)
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Dim SQLString
Dim Value
Dim TextLine
Dim strSafeTime
Dim strSafeDate
Dim ProjectFolder

ProjectFolder="C:\test"

set MyDB = CreateObject("ADODB.Connection")
MyDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test\DARP_DB.mdb"

strSafeTime = Right("0" & Hour(Now), 2) & Right("0" & Minute(Now), 2) & Right("0" & Second(Now), 2)
strSafeDate = replace(FormatDateTime(now,2),"/","")

SQLString = "select * from tblBuchdaten where quantity >=1"

Set fs = CreateObject("Scripting.FileSystemObject")

Set ts = fs.CreateTextFile(ProjectFolder & "\export_booklooker" & ".txt")

' Open the file for input.
Set MyFile = fs.OpenTextFile(ProjectFolder & "\BLexport_csv_header.txt", ForReading)
' Read from the file and display the results.
Do While MyFile.AtEndOfStream <> True
TextLine = MyFile.ReadLine
ts.WriteLine(TextLine)
Loop
MyFile.Close

set rs = MyDB.Execute(SQLString)
If Not (rs.EOF And rs.BOF) Then
rs.MoveFirst
Do Until rs.EOF = True
ts.Write rs.Fields("ArticleID") & vbTab

function max(a,b)
max = a
If b > a Then max = b
End Function

ts.Write max(rs.Fields("price"), 3) & vbTab

VBEval>max("3","0,01"),res1
VBEval>max("3","5,01"),res2

MDL>%res1%%crlf%%res2%

rs.MoveNext
Loop
Else
GetData = "Not Found"
End if
MyDB.Close
ts.Close
GetData="Daten sind exportiert."
End Function
VBEND

VBEval>GetData("%Quantity%","%Sonderangebot%"),Message
MessageModal>%Message%

[/snippet]

I have not checked, if it would work all by itself, since I assumed it would do that, once it is being handled properly.

[JRL]

I see that you have included Macro Scheduler functions inside the VBScript block. I don't think you should do that. Might even throw an error message.

VBSTART
...
...
ts.Write rs.Fields("ArticleID") & vbTab
function max(a,b)
max = a
If b > a Then max = b
End Function
ts.Write max(rs.Fields("price"), 3) & vbTab
VBEval>max("3","0,01"),res1
VBEval>max("3","5,01"),res2
MDL>%res1%%crlf%%res2%
rs.MoveNext
Loop
...
...
VBEND

I can tell for certain this doesn't work. It gives a "Microsoft VBScript compilation error :1002r Syntax error" message.

Code: Select all

VBSTART
  MDL>test
VBEND