How to write an IF statement in VBA

  • Thread starter Thread starter Rose
  • Start date Start date
R

Rose

I've realized that adding a field that gives the type of
group a customer is in will immensely cut down on my query
headaches. I could write a regular IF statement in my query
to do this, except it would go way over 250 characters. So
this'll have to go into code.

The syntax is wrong, I'm sure. Please tell me the correct
way to word this.

If [Company]="ABC Company" Then "Group A"
Else If [Company]="ABD Company" Then "Group A"
Else If [Company]="ABE Company" Then "Group B"
Else If [Company]="ABF Company" Then "Group C"
Else "Group D"

There's about 15 companies in all to check for.

And please remind me: once I write the code, how I attach
it to my query? Normally an expression to define a new
field (in this case, GroupName) would go in the Field line
of my query view.
 
better use select case:
public function MyGroup(company)
select case company
case "abc", "adb"
MyGroup="A"
case "abe"
MyGroup="B"
...
end select
end function
 
I've realized that adding a field that gives the type of
group a customer is in will immensely cut down on my query
headaches. I could write a regular IF statement in my query
to do this, except it would go way over 250 characters. So
this'll have to go into code.
Click to expand...

Ummm... you could always use a database! A field of GroupType in the
Customer table would do it.

In VBA, you could use a DLookup() function.

Hope that helps


Tim F
 
I *am* creating the new field GroupName. It's populating it
that requires a If statement, or similar.
 
Rose
I would refer you to Alex Dybenko's proposed solution. You can either do a select case as a calculated field in a query, but that's very messy. Or you can do the select case, which works very well for exclusive sets of results.

If you want to populate the results of each recordfield after creating it, then (and I wish I had more skill in this area), you can get into recordsets. I barely remember SEQ data files in my Commodore 64 days, but each record is looked at individually and in order to read the next one, you must move the 'pointer'. Things like End of File and Beginning of File come into play as well. There are some fantastic resources within the newsgroup, as well as in the help file. And as you READ the recordset individually, you can also WRITE the values into the field individually. While it sounds like a long process, it's really up to your processor speed. I had 76000 records that I did a boatload of calculations to in less than 20 seconds, including setting up the data table using SQL CREATE statements

Sorry I don't have the specifics. I hope this at least helps somewhat

Dere
----- Rose wrote: ----

I *am* creating the new field GroupName. It's populating i
that requires a If statement, or similar
 
Rose said:
I've realized that adding a field that gives the type of
group a customer is in will immensely cut down on my query
headaches. I could write a regular IF statement in my query
to do this, except it would go way over 250 characters. So
this'll have to go into code.

The syntax is wrong, I'm sure. Please tell me the correct
way to word this.

If [Company]="ABC Company" Then "Group A"
Else If [Company]="ABD Company" Then "Group A"
Else If [Company]="ABE Company" Then "Group B"
Else If [Company]="ABF Company" Then "Group C"
Else "Group D"

There's about 15 companies in all to check for.

And please remind me: once I write the code, how I attach
it to my query? Normally an expression to define a new
field (in this case, GroupName) would go in the Field line
of my query view.
Click to expand...
Notwithstanding the other comment about using select instead of a bunch
of ifs, your code is basically correct. It does have a problem with the
then clauses, though. "Group X" is a null statement. I believe you are
looking for something like

dim sCompanyGroup as String
If [company] = "abc company" then sCompanyGroup = "A group"
else if [company] = "def company" then sCompanyGroup = "D group" ...
else sCompanyGroup = "Z group"
end if
<Do something with the sCompanyGroup variable>


Now, to raise a point on technique. From what you mentioned above, it
seems like your current queries are based on

Select (stuff) where ([company]="abc company" or [company]="bbb company"
or [company] = "lll company");

and you want to change it to

Select (stuff) where [companygroup] = "Group A";

If this is the case, proceed reading. Otherwise stop and ignore the rest.

Now, are you going to need to update a bunch of tables and databases, or
are you just writing this code to do it once? If you are just modifying
one table once, and then going to input that each time, you might want
to look into update queries, such as:
UPDATE <table> SET ClientGroup="Group A" WHERE ([company] = "abc
company" or [company]="def company");

Create and run this query for each group.

Hmmm...hopefully this won't get too long, but are you sure you want to
store both company group and company in the same table? What happens if
a company is moved from one group to another? A better idea may be to
make another table, field 1 being company name, field 2 being group, and
link to that table when you want to do group functions.
 
open (or create) a standard module in your database, and copy/paste the
following procedure, as

Public Function isGroup(ByVal strCompany As String) As String

Select Case strCompany
Case "ABC Company", "ABD Company"
isGroup = "Group A"
Case "ABE Company"
isGroup = "Group B"
Case "ABF Company"
isGroup = "Group C"
Case Else
isGroup = "Group D"
End Select

End Function
Normally an expression to define a new
field (in this case, GroupName) would go in the Field line
of my query view.
Click to expand...

this will work the same. put the following expression in the Field line, as

GroupName: isGroup([NameOfCompanyField])

in the function, substitute the correct names for the values *in quotes*,
only. in the query line, substitute the correct name of the company field.

hth


Rose said:
I've realized that adding a field that gives the type of
group a customer is in will immensely cut down on my query
headaches. I could write a regular IF statement in my query
to do this, except it would go way over 250 characters. So
this'll have to go into code.

The syntax is wrong, I'm sure. Please tell me the correct
way to word this.

If [Company]="ABC Company" Then "Group A"
Else If [Company]="ABD Company" Then "Group A"
Else If [Company]="ABE Company" Then "Group B"
Else If [Company]="ABF Company" Then "Group C"
Else "Group D"

There's about 15 companies in all to check for.

And please remind me: once I write the code, how I attach
it to my query? Normally an expression to define a new
field (in this case, GroupName) would go in the Field line
of my query view.
Click to expand...
 
Hi Rose,

I think that what Tim is referring to is that it appears
that you are creating this field in a table where each
company appears numerous times (otherwise why automate
it). Tim is suggesting that if this is the case it is
not the preferred way of handling data in a normalized
database (to add another field that is a constant based
on the company name).

Instead, you would normally have a company lookup table
that would list various data about the company, such as
the name and the group. Then, the customer table would
relate to the company just by storing the company ID.
Queries would then be used to relate the customer data
and any of the company data (including the group) from
the lookup table.

HTH, Ted Allen
 
Why don't you just create a new table and type in the 15 company names,a nd
the group they belong to?

You HAVE TO type in those 15 companies and the group they belong to
SOMEWHERE. Why do this in code?

Simply make a small table with the 15 companies..and the group.

The, for all your reports etc. simply join in the group via the company
name. This takes no code at all, and further y ou can add/change the company
list without resorting to mucking around with a bunch of code each time?

Any reason why you don't create a simply table..and type i the 15
companies..and then join tint your existing queries right now?
 
Tim is suggesting that if this is the case it is
not the preferred way of handling data in a normalized
database (to add another field that is a constant based
on the company name).
Click to expand...

Actually Tim must have been very tired at the time. What I think I was
stumbling toward was what Albert posted much more succintly downthread. The
GroupCode field belongs in the Companies table, not the Customers.

But yes, the whole point was to move this away from a programming problem
to getting the database design right.


B Wishes


Tim F
 
There's actually hundreds of companies, but 15 stand apart
in 4 different groups of their own. The list of companies
changes frequently, though the names of the 15 will not
change anytime soon. Sure, I could have a field in a table
that defaulted to Group 5 unless I said otherwise, but I'm
porting this company list from MAS weekly, not manually
adding new companies. I have to pull a query anyway for
what I need, rather than go straight with the table, so it
made sense to do this here.
 
I might have not been clear earlier. The companies *are*
the customers...I should have used the same term
throughout. They will only show up once in the Company table.

I 'port the company data from MAS (an accounting system)
about once a week. I have to run a query to prepare my data
for the rest of my queries, and it made sense to add
GroupName at this point, since I don't really need it in my
table and I don't add companies in manually. Furthermore, I
have hundreds of customers, but these 15 companies need to
be treated differently than the rest. Several people's
suggestion of a Select Case was what I needed.
 
Thank you for a succinct and very helpful post!
-----Original Message-----
open (or create) a standard module in your database, and copy/paste the
following procedure, as

Public Function isGroup(ByVal strCompany As String) As String

Select Case strCompany
Case "ABC Company", "ABD Company"
isGroup = "Group A"
Case "ABE Company"
isGroup = "Group B"
Case "ABF Company"
isGroup = "Group C"
Case Else
isGroup = "Group D"
End Select

End Function
Normally an expression to define a new
field (in this case, GroupName) would go in the Field line
of my query view.
Click to expand...

this will work the same. put the following expression in the Field line, as

GroupName: isGroup([NameOfCompanyField])

in the function, substitute the correct names for the values *in quotes*,
only. in the query line, substitute the correct name of the company field.

hth


Rose said:
I've realized that adding a field that gives the type of
group a customer is in will immensely cut down on my query
headaches. I could write a regular IF statement in my query
to do this, except it would go way over 250 characters. So
this'll have to go into code.

The syntax is wrong, I'm sure. Please tell me the correct
way to word this.

If [Company]="ABC Company" Then "Group A"
Else If [Company]="ABD Company" Then "Group A"
Else If [Company]="ABE Company" Then "Group B"
Else If [Company]="ABF Company" Then "Group C"
Else "Group D"

There's about 15 companies in all to check for.

And please remind me: once I write the code, how I attach
it to my query? Normally an expression to define a new
field (in this case, GroupName) would go in the Field line
of my query view.
Click to expand...


.
Click to expand...
 
Back
Top