if then else depending on value in a db

[ZeitenWanderer]

Picking data from an access source works fine, now I need to add details.
One is to change a value to be exported into a csv file, depending on a value in that db.

Means I have a VB-line:

ts.Write "10" & vbTab

This works technically good in the export file, but does not always produce the desired result.
Means, if a value in column A is "> 950" and "< 4000" it must make it a "5" instead of "10".
If the value is more than 4000 and less than 8000, it must become a "3" instead of a "10".

Would anyone have an example?

[Marcus Tettmar]

Code: Select all

If>{(%A%>950) AND (%A%<4000)}
  Let>val_to_write=5
Else
  Let>val_to_write=10
Endif

Or maybe you're looking for VBScript syntax:

Code: Select all

If (A > 950) and (A < 4000) Then
  writeVal=5
Else
  WriteVal=10
Endif

"A" here is just a variable. Assuming you know how to get the value from the DB and assign it to a variable.

[ZeitenWanderer]

I am sorry, so many other thing needed attention. I have to return to this issue:

I can change it, so that I get this or that value out of the db, but I am having a hard time to figure out, how to change this value, when it is being written to the csv-file. The field to be asked is weight. Depending on it, I need to change the fee for shipping.

So I first would set a variable "DIM A"?
next a line
ts.Write rs.Fields("Weight") & vbTab
which I would have to enhance, but how would I place it?


This is my script (not mine actually else I would not find it difficult, I guess):


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:\darp2\Daten"

set MyDB = CreateObject("ADODB.Connection")
MyDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\darp2\Daten\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_amazon" & strSafeDate & "-" & strSafeTime & ".txt")


' Open the file for input.
Set MyFile = fs.OpenTextFile(ProjectFolder & "\export_amazon_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
ts.Write vbTab & vbTab
ts.Write rs.Fields("Titel") & vbTab
ts.Write rs.Fields("Verlag") & vbTab
ts.Write rs.Fields("item-note") & vbTab
ts.Write "update" & vbTab
ts.Write rs.Fields("price") & vbTab
ts.Write rs.Fields("quantity") & vbTab
ts.Write rs.Fields("item-condition") & vbTab
ts.Write rs.Fields("item-note") & vbTab
ts.Write vbTab &vbTab &vbTab &vbTab &vbTab
If rs.Fields("Bild") <> "" Then
ts.Write "http://www.zeitenwanderer.de/pics/" & rs.Fields("Bild") & vbTab
Else
ts.Write rs.Fields("Bild") & vbTab
End If
' ts.Write "http://www.zeitenwanderer.de/pics/" & rs.Fields("Bild") & vbTab
ts.Write vbTab & vbTab & vbTab & vbTab & vbTab & vbTab & vbTab
ts.Write rs.Fields("Nachname_Autor") & ", " & rs.Fields("Vorname_Autor") & vbTab
ts.Write rs.Fields("Einband")& vbTab
ts.Write rs.Fields("Druckjahr") & vbTab
ts.Write vbTab & vbTab
ts.Write "10" & vbTab
ts.Write rs.Fields("Rubrik") & vbTab
ts.Write rs.Fields("Sprache") & vbTab
ts.Write vbTab
ts.Write rs.Fields("illustrationsart") & vbCRLF


rs.MoveNext
Loop
Else
GetData = "Not Found"
End if
MyDB.Close
ts.Close
GetData="Die Daten sind jetzt im Amazon-Buchformat (mit dreizeiligem Header) exportiert."
End Function
VBEND

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

[Marcus Tettmar]

Something like this I guess:

Code: Select all

If (rs.Fields("Weight") > 950 AND rs.Fields("Weight") < 4000) Then
   NewVal = 5
Else
   NewVal = 10
Endif
ts.Write NewVal & vbTab

That's assuming I've understood correctly what you want to do. The above checks the value of the "Weight" field in the database record, and if it is between 950 and 4000 sets NewVal to 5, else sets NewVal to 10. It then writes NewVal to the csv file rather than writing the value of the "Weight" field in the record set.

[ZeitenWanderer]

Yes, you are right, indeed I did miss something ... In fact, I have to add this extra value on top of the existing "price" inside the csv, not the db.

This script is needed for anyone, who needs to export stock data to Amazon starting from our db. (Once we finished the very last English translation, we will throw it in the ring for the benefit of others, who may care - but no promises concerning quality or exspectations ... )

[ZeitenWanderer]

It is actually very simple to add a value to an existing field content. But next time, I will take more time to figure out, what exactly might be the best way to handle the problem.

[snippet=]
If (rs.Fields("weight") >= 0 AND rs.Fields("weight") <= 950) Then
ts.Write rs.Fields("price") & vbTab
Elseif (rs.Fields("weight") >= 950 AND rs.Fields("weight") <= 1700) Then
ts.Write rs.Fields("price") + 5 & vbTab
Elseif (rs.Fields("weight") >= 1700 AND rs.Fields("weight") <= 18000) Then
ts.Write rs.Fields("price") + 15 & vbTab
Elseif (rs.Fields("weight") >= 1700 AND rs.Fields("weight") <=1000000) Then
ts.Write rs.Fields("price") + 15 & vbTab
End if
[/snippet]

This example will help also check the size (or raise the price, if necessary) as well as the form, which is another issue.

[ZeitenWanderer]

Sigh ... This one still works for the price ...
[snippet=]
If rs.Fields("weight") >= 1 AND rs.Fields("weight") <= 950 Then
ts.Write rs.Fields("price") & vbTab
Else
ts.Write rs.Fields("price") + 5 & vbTab
End if
[/snippet]

But I can not get it to work properly, if I need to add more than one further condition. Means, it runs through, but instead of checking if the asked condition is given or not, it always moves to the Else-line, adding the 5 in just any case. What do I overlook?

[snippet=]
If rs.Fields("weight") >= 1 AND rs.Fields("weight") <= 950 AND rs.Fields("height") < 35 AND rs.Fields("width") < 30 AND rs.Fields("spine") < 14 Then
ts.Write rs.Fields("price") & vbTab
Else
ts.Write rs.Fields("price") + 5 & vbTab
End if[/snippet]

[Marcus Tettmar]

You need brackets:

Code: Select all

If (rs.Fields("weight") >= 1) AND (rs.Fields("weight") <= 950) AND (rs.Fields("height") < 35) AND (rs.Fields("width") < 30) AND (rs.Fields("spine") < 14) Then

So for that to resolve true and step INTO the If block ALL the following conditions must be true:

weight: between 1 and 950
height: <35
width: < 30
spine: < 14

[ZeitenWanderer]

Thank you very much! It works - but only if the fields height, width and spine are filled ...

Of course, I must have another beginners mistake - I overlooked how many fields are empty. Second, I mistook "empty" for "0" - as if one would have never had math in school ...

So I enhanced the line like this:

[snippet=]If (rs.Fields("weight") >= 1) AND (rs.Fields("weight") <= 950) AND ((rs.Fields("height") < 35) OR (rs.Fields("height") <> "")) AND ((rs.Fields("width") < 30) OR (rs.Fields("width") <> "")) AND ((rs.Fields("spine") < 14) OR (rs.Fields("spine") <> "")) Then[/snippet]

But the result again is, that all entries having at least one empty field (height, width or spine) are considered wrong and the program jumps to the "else"-condition. What am I missing here?