If...Then...Else Statement problem 2

[mikesh27]

I've used If...Then...Else Statements for a while to indicate a result from two choices without any problems in Access 2000.

I am now trying to do the same with 3 or 4 options and I'm trying to use the ElseIf clause but keep getting compile errors.

If Me!Sunroof = Sunroof Then
Forms!adverts!Sunroof = "sunroof, "
ElseIf Me!Sunroof = Electrice Sunroof Then
Forms!adverts!Sunroof = "electric sunroof, "
Else: Forms!adverts!Sunroof = Null
End If

It's failing on the 3rd line with "expected: Then".

I've gone through my Access 2000 and VB6 books and can't see why it fails. It's probably something really stupid I'm overlooking.

Any ideas would be gratefully received.


Thanks,


Mike

 

[RoyVidar]

Erm - what do you intend to test in the first if statement?

What you do test, is if the form control Sunroof equals the form control Sunroof - i e testing against itself. Are you intending to test for a text, a variable...

- guessing:

[tt]If Me!Sunroof = "Sunroof" Then
Forms!adverts!Sunroof = "sunroof, "
ElseIf Me!Sunroof = "Electrice Sunroof" Then
Forms!adverts!Sunroof = "electric sunroof, "
Else
Forms!adverts!Sunroof = Null
End If[/tt]

Roy-Vidar

 

[mikesh27]

Thanks Roy - that works fine.

Silly mistake.

As my original test worked using:

If Me!Sunroof = Sunroof Then
Forms!adverts!Sunroof = "sunroof, "
Else
Forms!adverts!Sunroof = Null
End If

Because of this I continued to omit the quotes.

Thanks again,

Mike

 

[willir]

Mike

Roy has given you the answer. However, something to consider, and this is a personal choice, is to use SELECT CASE...

Dim strSunRoofType as String, frmAdVerts as Form

strSunRoofType = Nz(Me.Sunroof, "")
frmAdVerts = Forms!adverts!

Select Case Sunroof
   Case "sunroof"
      frmAdVerts!Sunroof = "sunroof, "
   Case "Electrice Sunroof"
      frmAdVerts!Sunroof = "electrice sunroof, "
   Case Else
      frmAdVerts!Sunroof = Null
End Select

Of couse, another approach would be...

If Len(Nz(Me.Sunroof, "")) > 0 Then
   Forms!adverts!Sunroof = LCase(Me.Sunroof) & ", "
Else
   Forms!adverts!Sunroof = Null
End If

Richard

 

[mikesh27]

Thanks Richard,

I'm very rusty on Access/VB6 programming - so please excuse any stupid remarks and poor programming language.

I wrote a fairly lengthy program in Access 2 between 1990 and 1993 for two Chrysler Jeep dealers, which I adapted for three Suzuki and Daihatsu franchises and four independant mototr dealerships. A few years ago I rewrote the necessary code to upgrade to Access 2000.

I used SELECT CASE in a function to calculate profit margins on the sales of parts. The code was:

Public Function costprice(x, y)

Select Case (x)

Case "l", "L", "a", "A", "c", "C", "d", "D", "f", "F", "g", "G", "r", "R", "u", "U", "v", "V", "AA", "aa", "CC", "cc", "DD", "dd", "FF", "ff", "GG", "gg", "LL", "ll", "UU", "uu", "VV", "vv", "JA", "ja", "JB", "jb", "JH", "jh", "TT", "tt", "b", "B", "h", "H", "bb", "BB", "hh", "HH"
costprice = y * 0.74
Case "j", "J", "JJ", "jj", "t1", "T1"
costprice = y * 0.9
Case "h", "H", "jc", "JC"
costprice = y * 0.65
Case "m", "M", "MM", "mm"
costprice = y * 0.85
Case "n", "N", "JT", "jt"
costprice = y * 1
Case "t0", "T0"
costprice = y * 0
Case "s", "S", "SS", "ss", "t5", "T5"
costprice = y * 0.5
Case "t9", "T9"
costprice = y * 0.1
Case "t8", "T8"
costprice = y * 0.2
Case "t7", "T7"
costprice = y * 0.3
Case "t6", "T6"
costprice = y * 0.4
Case "t2", "T2"
costprice = y * 0.8
Case "WW", "ww", "t3", "T3"
costprice = y * 0.7
Case "RR", "rr", "t4", "T4"
costprice = y * 0.6
Case "B1", "b1"
costprice = y * 0.14
Case "B2", "b2"
costprice = y * 0.35
Case "B3", "b3"
costprice = y * 0.1283
Case Else
costprice = y * 1
End Select


End Function

As you can see my coding in very basic. This covered every discount code used by Chrysler including the those after the Mercedes merger.

I have tried your first sample, but it fails on a compile error on "frmAdVerts=" stating invalid use of property.

I have gone through the Select Case chapter on the Complete Idiots Guide to VB6 (very appropriate) and I can't see the problem after it's been declared.

I am attempting to produce a fairly simple database of two forms and tables for a motor dealer to produce stock lists and to download to the website I am writing for them by ASP.NET.

The first form called stock gives the end user a choice of 2000+ models via a combi box, year/plate, automatic - yes/no, leather - yes/no, sunroof - sunroof, electric sunroof, none, low profile tyres - yes/no, price, air conditioning - yes/no, entertainment - multi-CD player, CD player, tv - yes/no, satnav - yes/no, etc. So everything is either click or scroll down, maybe with 2000+ model a little typing. Hmmm, typing it out makes me think I should split it between make and model.

When they finish the record they click a button that create a new Adverts record, the code so far reads:

Private Sub Command19_Click()
On Error GoTo Err_Command19_Click


DocName = "Adverts"
DoCmd.Close A_FORM, "Adverts"
DoCmd.OpenForm DocName, , , , A_ADD
Forms!adverts!ref = Me!ref
Forms!adverts!model = Me!model
Forms!adverts!year = Me!year
Forms!adverts!price = Me!price



If Me!Sunroof = "Sunroof" Then
Forms!adverts!Sunroof = "sunroof, "
ElseIf Me!Sunroof = "electric sunroof" Then
Forms!adverts!Sunroof = "electric sunroof, "
Else: Forms!adverts!Sunroof = Null
End If


Exit_Command19_Click:
Exit Sub

Err_Command19_Click:
MsgBox Err.Description
Resume Exit_Command19_Click

End Sub


I know it's a declaration-free zone and I'm giving myself more work.

The idea is that the end result would be a text string something like this along with several photos taken at 640x480:

2001/51 Mercedes S430 Automatic, metallic black, satellite navigation, tv, grey leather interior, 19" alloy wheels, electric sunroof, parking aid.....£19,999

51 denotes it registered between September 1 and March 31 2001.

Sorry to go on but after your kind offer of ideas, you deserved an explanation.

Thanks,

Mike

PS - I'm trying to convert a yes/no field to a text string. I can't get it to work.

 

[willir]

My error...

Set frmAdVerts = Forms!adverts!

When ever you reference a specific object such as a database, table, query, or form, you need to use the SET command.

Richard