Fox Pro like running programs in Access (VB based?)

  • Thread starter Thread starter EmOXon
  • Start date Start date
E

EmOXon

Hi everyone,

I have a table with a field that I need to update based on the certain
conditions. For example, if Serial number are in range of 1000 to 2000
it would filled field with value "Gold", except for for specific values
1113 and 1713 I need it to be marked as "Iron" and range of 1200 to
1300 marked as "Copper". I would like to do it in the single run -
rather than - in this case - run 3 update queries. I suppose I should
be able to do in VB module?

Thanks in advance
EM
 
You could write a function and call it from the query, or, a nested IIf()
function could do it.

IIf([SerialNumber] between 1000 And 2000, IIf([SerialNumber] In(1113, 1713),
"Iron", IIf([SerialNumber] Between 1200 And 1300, "Copper","Gold")), NUll)

In the Query Builder, you would put this in the Update To Column of the
field you want to put these values in.

Be aware the above is untested "air code" and the syntax may or may not be
correct, but it should give you the basic idea (or a bad headache)
 
Currently I am using nested IIf method, trouble is - it gets too
complicated, if I have many areas (like a large box and many small
boxes inside). Also

Can I hear more about writing a function, I think that is what I am
looking for. A simple example would be most appreciated.

Thanks
You could write a function and call it from the query, or, a nested IIf()
function could do it.

IIf([SerialNumber] between 1000 And 2000, IIf([SerialNumber] In(1113, 1713),
"Iron", IIf([SerialNumber] Between 1200 And 1300, "Copper","Gold")), NUll)

In the Query Builder, you would put this in the Update To Column of the
field you want to put these values in.

Be aware the above is untested "air code" and the syntax may or may not be
correct, but it should give you the basic idea (or a bad headache)

EmOXon said:
Hi everyone,

I have a table with a field that I need to update based on the certain
conditions. For example, if Serial number are in range of 1000 to 2000
it would filled field with value "Gold", except for for specific values
1113 and 1713 I need it to be marked as "Iron" and range of 1200 to
1300 marked as "Copper". I would like to do it in the single run -
rather than - in this case - run 3 update queries. I suppose I should
be able to do in VB module?

Thanks in advance
EM
Click to expand...
Click to expand...
 
What you do is write a funtion to determine what value needs to be returned
based on the value or values you pass to it. It needs to be a Public
Function in a standard module.

Then you put the function in your query where you need to return the value.

For example, lets say you want to update the field in your table with the
values based on your original rules, you would put it in the Update To row of
your query in the column for that field.

The function would look something like this:

Public Function GetLevel(lngSerialNmber as Long) As String

If lngSerialNumber = 1113 Or lngSerialNumber = 1713 Then
GetLevel = "Iron"
ElseIf lngSerialNumber >= 1200 And lngSerialNumber <= 1300 Then
GetLevel = "Copper"
ElseIf lngSerialNumber >= 1000 And lngSerialNumber <= 2000 Then
GetLevel = "Gold
Else
GetLevel = Null 'Or whatever you want to return if it is
anything else
End If
End Function

Then in the query, you pass it the value of the field in the record to
evaluate

GetLevel([SerialNumber])

A couple of notes
To get a query to execute for every row in a query, you have to pass it a
value. It does not have to be a field name or have any meaning. I have had
a case where I needed the function to process for each record, but not use
anything from the record, so I did DoMyFunction(""). The function doesn't
even have to look at what is passed.

Next, and this may be important. I just assumed that SerialNumber is a Long
Integer. You may have to type it differently, depending on the data type of
the field. If it is possible there could be records where the SerialNumbder
is Null, then you would need to change the data type in the function to
Variant or it will cause an error.

EmOXon said:
Currently I am using nested IIf method, trouble is - it gets too
complicated, if I have many areas (like a large box and many small
boxes inside). Also

Can I hear more about writing a function, I think that is what I am
looking for. A simple example would be most appreciated.

Thanks
You could write a function and call it from the query, or, a nested IIf()
function could do it.

IIf([SerialNumber] between 1000 And 2000, IIf([SerialNumber] In(1113, 1713),
"Iron", IIf([SerialNumber] Between 1200 And 1300, "Copper","Gold")), NUll)

In the Query Builder, you would put this in the Update To Column of the
field you want to put these values in.

Be aware the above is untested "air code" and the syntax may or may not be
correct, but it should give you the basic idea (or a bad headache)

EmOXon said:
Hi everyone,

I have a table with a field that I need to update based on the certain
conditions. For example, if Serial number are in range of 1000 to 2000
it would filled field with value "Gold", except for for specific values
1113 and 1713 I need it to be marked as "Iron" and range of 1200 to
1300 marked as "Copper". I would like to do it in the single run -
rather than - in this case - run 3 update queries. I suppose I should
be able to do in VB module?

Thanks in advance
EM
Click to expand...
Click to expand...
Click to expand...
 
Hi Klatuu

I wrote a function and it seems OK, but when I try to use in query it
gives me a Compile Error message. I rtied to change type of variables
to Variant as you suggested, but it didn't solve the problem.

I am defining 6 arguments (which are actually name of the fields I am
using to find the variable) and using some moderate functions such as
Left and Right but nothing too fancy. Please see below:

Public Function updatemesto(CI As String, pot As String, RO As String,
RRS As String, FC As String, CC As String) As String

If Left(RO, 1) = "Q" Then mesto = "O"
ElseIf Left(RO, 1) = "I" Then mesto = "T"
ElseIf Left(RO, 1) = "C" And Right(RRS, 4) = "6144" Then mesto =
"T"
ElseIf Left(RO, 1) = "Q" And pot = "10116" Then mesto = "T"
ElseIf pot = ("48267" Or "58941" ) Then mesto = "T"
ElseIf CI = ("553810") Then mesto = "T"
ElseIf Left(FC, 2) = "AR" Or Left(FC, 2) < "SQ" Then mesto = "W"
ElseIf pot = ("42685" Or "79135" ) Then mesto = "W"
ElseIf pot = "13645" And Left(RO, 1) = "Q" Then mesto = "W"
ElseIf CC = "AR001S" Or CC = "WQ002D" Then mesto = "W"
Else: mesto = Left(RO, 1) 'Or whatever you want to return
if it is anything else
End If
End Function

What you do is write a funtion to determine what value needs to be returned
based on the value or values you pass to it. It needs to be a Public
Function in a standard module.

Then you put the function in your query where you need to return the value.

For example, lets say you want to update the field in your table with the
values based on your original rules, you would put it in the Update To row of
your query in the column for that field.

The function would look something like this:

Public Function GetLevel(lngSerialNmber as Long) As String

If lngSerialNumber = 1113 Or lngSerialNumber = 1713 Then
GetLevel = "Iron"
ElseIf lngSerialNumber >= 1200 And lngSerialNumber <= 1300 Then
GetLevel = "Copper"
ElseIf lngSerialNumber >= 1000 And lngSerialNumber <= 2000 Then
GetLevel = "Gold
Else
GetLevel = Null 'Or whatever you want to return if it is
anything else
End If
End Function

Then in the query, you pass it the value of the field in the record to
evaluate

GetLevel([SerialNumber])

A couple of notes
To get a query to execute for every row in a query, you have to pass it a
value. It does not have to be a field name or have any meaning. I have had
a case where I needed the function to process for each record, but not use
anything from the record, so I did DoMyFunction(""). The function doesn't
even have to look at what is passed.

Next, and this may be important. I just assumed that SerialNumber is a Long
Integer. You may have to type it differently, depending on the data type of
the field. If it is possible there could be records where the SerialNumbder
is Null, then you would need to change the data type in the function to
Variant or it will cause an error.

EmOXon said:
Currently I am using nested IIf method, trouble is - it gets too
complicated, if I have many areas (like a large box and many small
boxes inside). Also

Can I hear more about writing a function, I think that is what I am
looking for. A simple example would be most appreciated.

Thanks
You could write a function and call it from the query, or, a nested IIf()
function could do it.

IIf([SerialNumber] between 1000 And 2000, IIf([SerialNumber] In(1113, 1713),
"Iron", IIf([SerialNumber] Between 1200 And 1300, "Copper","Gold")), NUll)

In the Query Builder, you would put this in the Update To Column of the
field you want to put these values in.

Be aware the above is untested "air code" and the syntax may or may not be
correct, but it should give you the basic idea (or a bad headache)

:

Hi everyone,

I have a table with a field that I need to update based on the certain
conditions. For example, if Serial number are in range of 1000 to 2000
it would filled field with value "Gold", except for for specific values
1113 and 1713 I need it to be marked as "Iron" and range of 1200 to
1300 marked as "Copper". I would like to do it in the single run -
rather than - in this case - run 3 update queries. I suppose I should
be able to do in VB module?

Thanks in advance
EM
Click to expand...
Click to expand...
Click to expand...
 
EmOXon said:
I wrote a function and it seems OK, but when I try to use in query it
gives me a Compile Error message. I rtied to change type of variables
to Variant as you suggested, but it didn't solve the problem.

I am defining 6 arguments (which are actually name of the fields I am
using to find the variable) and using some moderate functions such as
Left and Right but nothing too fancy. Please see below:
Click to expand...

You need a carriage return after the first THEN in your IF block:

If Left(RO, 1) = "Q" Then
mesto = "O"
ElseIf Left(RO, 1) = "I" Then mesto = "T"

In a block of IF...ELSEIF statement, the first condition that returns
true will trigger the THEN clause and no other conditions will be
tested, therefore this line will never be executed:

ElseIf Left(RO, 1) = "Q" And pot = "10116" Then mesto = "T"

To get round this, you could try using 'Select Case' instead of your
IF blocks:

Public Function updatemesto(CI As String, pot As String, RO As String,
RRS As String, FC As String, CC As String) As String

mesto = Left(RO, 1)

Select Case Left(RO, 1)
Case "Q"
Select Case pot
Case "10116"
mesto = "T"
Case "13645"
mesto = "W"
Case Else
mesto = "O"
End Select
Case "C"
If Right(RRS, 4) = "6144" Then mesto = "T"
Case "I"
mesto = "T"
End Select

Select Case pot
Case "48267", "58941"
mesto = "T"
Case "42685", "79135"
mesto = "W"
End Select

If CI = ("553810") Then mesto = "T"
If Left(FC, 2) = "AR" Or Left(FC, 2) < "SQ" Then mesto = "W"
If CC = "AR001S" Or CC = "WQ002D" Then mesto = "W"

End Function
 
Another hint - when I changed type of variables in the header from
Variant to String - an empty line in query got filled with #Error.
Hope that works
Thanks
EM
 
The carriage return helped, thanks. The Select Case give the same
result though. I also tried to simplify function but limiting number of
arguments to 1, and then even replacing it with a fixed value - to no
avail....

Another hint - when I changed type of all variables in the header from
Variant to String - an empty line in query got filled with #Error. So I
guess it's reading the variables (or at least their types) but
condition is not being satisfied. All variables have type TEXT in the
table, so I assume String or Variant type in the module would be
appropriate.

Hope that helps you to help me :)
Thanks
EM
 
Back
Top