I have an If, Then, Else problem

[Guest]

Below is the code I have written to load data into various parts of a
database, It falls over at the first "IF" statement, What am I doing wrong?
The error message is as follows "Error No: 2465, Description: Microsoft
Office Access can't find the field '|' referred to in your expression.

Private Sub cmdSLGLInput_Click()
On Error GoTo ErrorHandler

DoCmd.SetWarnings False
DoCmd.OpenQuery "qappSLInvValueGross"
If [tblSalesInvoice]![AccountCodeVAT] = 2205 Then
DoCmd.OpenQuery "qappSLInvValueVAT"
ElseIf [tblSalesInvoice]![AccountCode1] Is Not Null Then
DoCmd.OpenQuery "qappSLInvValue1"
ElseIf [tblSalesInvoice]![AccountCode2] Is Not Null Then
DoCmd.OpenQuery "qappSLInvValue2"
ElseIf [tblSalesInvoice]![AccountCode3] Is Not Null Then
DoCmd.OpenQuery "qappSLInvValue3"
ElseIf [tblSalesInvoice]![AccountCode4] Is Not Null Then
DoCmd.OpenQuery "qappSLInvValue4"
ElseIf [tblSalesInvoice]![AccountCode5] Is Not Null Then
DoCmd.OpenQuery "qappSLInvValue5"
ElseIf [tblSalesInvoice]![AccountCode6] Is Not Null Then
DoCmd.OpenQuery "qappSLInvValue6"
End If
DoCmd.SetWarnings True

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & _
Err.Description
Resume ErrorHandlerExit

End Sub

 

[Keith W]

If [tblSalesInvoice]![AccountCodeVAT] = 2205 Then

How does Access know what these objects are? Might be an idea to post what
it is you're trying to achieve.

Keith.
www.keithwilby.com

 

[Guest]

It is a syntax problem:
Incorrect:
ElseIf [tblSalesInvoice]![AccountCode1] Is Not Null Then
Correct:
ElseIf Not IsNull([tblSalesInvoice]![AccountCode1]) Then

I also note that if none of the conditions is true, nothing happens. Is
that what you want?

 

[Guest]

I have added the additional code and I now get a differen Error, No:13 "Type
MisMatch"
What I am trying to do is run a given append query base on the contents of a
field in the tblSalesInvoice table.

Private Sub cmdSLGLInput_Click()
Dim dbs As Database
Dim rst As Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblSalesInvoice", dbOpenDynaset)
DoCmd.OpenQuery "qappSLInvValueGross"
If ([tblSalesInvoice]![AccountCodeVAT]) = 2205 Then
DoCmd.OpenQuery "qappSLInvValueVAT"
ElseIf Not IsNull([tblSalesInvoice]![AccountCode1]) Then
DoCmd.OpenQuery "qappSLInvValue1"

If [tblSalesInvoice]![AccountCodeVAT] = 2205 Then

How does Access know what these objects are? Might be an idea to post what
it is you're trying to achieve.

Keith.
www.keithwilby.com

 

[Guest]

I have changed the code based on your suggestion and added Object recognition
as follows:
Private Sub cmdSLGLInput_Click()
Dim dbs As Database
Dim rst As Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblSalesInvoice", dbOpenDynaset)
DoCmd.OpenQuery "qappSLInvValueGross"
If ([tblSalesInvoice]![AccountCodeVAT]) = 2205 Then
DoCmd.OpenQuery "qappSLInvValueVAT"
ElseIf Not IsNull([tblSalesInvoice]![AccountCode1]) Then
DoCmd.OpenQuery "qappSLInvValue1"

The error I now get is No: 13 Type MisMatch

It is a syntax problem:
Incorrect:
ElseIf [tblSalesInvoice]![AccountCode1] Is Not Null Then
Correct:
ElseIf Not IsNull([tblSalesInvoice]![AccountCode1]) Then

I also note that if none of the conditions is true, nothing happens. Is
that what you want?

Below is the code I have written to load data into various parts of a
database, It falls over at the first "IF" statement, What am I doing wrong?
The error message is as follows "Error No: 2465, Description: Microsoft
Office Access can't find the field '|' referred to in your expression.

Private Sub cmdSLGLInput_Click()
On Error GoTo ErrorHandler

DoCmd.SetWarnings False
DoCmd.OpenQuery "qappSLInvValueGross"
If [tblSalesInvoice]![AccountCodeVAT] = 2205 Then
DoCmd.OpenQuery "qappSLInvValueVAT"
ElseIf [tblSalesInvoice]![AccountCode1] Is Not Null Then
DoCmd.OpenQuery "qappSLInvValue1"

 

[Guest]

I don't know what the parentheses do for this line, but I doubt it is the
problem. In any case, you don't really need them:

If ([tblSalesInvoice]![AccountCodeVAT]) = 2205 Then

Is [tblSalesInvoice]![AccountCodeVAT] a numeric data type?

What line do you get the error on?

I have changed the code based on your suggestion and added Object recognition
as follows:
Private Sub cmdSLGLInput_Click()
Dim dbs As Database
Dim rst As Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblSalesInvoice", dbOpenDynaset)
DoCmd.OpenQuery "qappSLInvValueGross"
If ([tblSalesInvoice]![AccountCodeVAT]) = 2205 Then
DoCmd.OpenQuery "qappSLInvValueVAT"
ElseIf Not IsNull([tblSalesInvoice]![AccountCode1]) Then
DoCmd.OpenQuery "qappSLInvValue1"

The error I now get is No: 13 Type MisMatch

It is a syntax problem:
Incorrect:
ElseIf [tblSalesInvoice]![AccountCode1] Is Not Null Then
Correct:
ElseIf Not IsNull([tblSalesInvoice]![AccountCode1]) Then

I also note that if none of the conditions is true, nothing happens. Is
that what you want?

Below is the code I have written to load data into various parts of a
database, It falls over at the first "IF" statement, What am I doing wrong?
The error message is as follows "Error No: 2465, Description: Microsoft
Office Access can't find the field '|' referred to in your expression.

Private Sub cmdSLGLInput_Click()
On Error GoTo ErrorHandler

DoCmd.SetWarnings False
DoCmd.OpenQuery "qappSLInvValueGross"
If [tblSalesInvoice]![AccountCodeVAT] = 2205 Then
DoCmd.OpenQuery "qappSLInvValueVAT"
ElseIf [tblSalesInvoice]![AccountCode1] Is Not Null Then
DoCmd.OpenQuery "qappSLInvValue1"

 

[Guest]

Yes [tblSalesInvoice]![AccountCodeVAT] is numeric with the following format;
Double, Fixed, 0 Decimal places.

I don't know what the parentheses do for this line, but I doubt it is the
problem. In any case, you don't really need them:

If ([tblSalesInvoice]![AccountCodeVAT]) = 2205 Then

Is [tblSalesInvoice]![AccountCodeVAT] a numeric data type?

What line do you get the error on?

I have changed the code based on your suggestion and added Object recognition
as follows:
Private Sub cmdSLGLInput_Click()
Dim dbs As Database
Dim rst As Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblSalesInvoice", dbOpenDynaset)
DoCmd.OpenQuery "qappSLInvValueGross"
If ([tblSalesInvoice]![AccountCodeVAT]) = 2205 Then
DoCmd.OpenQuery "qappSLInvValueVAT"
ElseIf Not IsNull([tblSalesInvoice]![AccountCode1]) Then
DoCmd.OpenQuery "qappSLInvValue1"

The error I now get is No: 13 Type MisMatch

It is a syntax problem:
Incorrect:
ElseIf [tblSalesInvoice]![AccountCode1] Is Not Null Then
Correct:
ElseIf Not IsNull([tblSalesInvoice]![AccountCode1]) Then

I also note that if none of the conditions is true, nothing happens. Is
that what you want?

:

Below is the code I have written to load data into various parts of a
database, It falls over at the first "IF" statement, What am I doing wrong?
The error message is as follows "Error No: 2465, Description: Microsoft
Office Access can't find the field '|' referred to in your expression.

Private Sub cmdSLGLInput_Click()
On Error GoTo ErrorHandler

DoCmd.SetWarnings False
DoCmd.OpenQuery "qappSLInvValueGross"
If [tblSalesInvoice]![AccountCodeVAT] = 2205 Then
DoCmd.OpenQuery "qappSLInvValueVAT"
ElseIf [tblSalesInvoice]![AccountCode1] Is Not Null Then
DoCmd.OpenQuery "qappSLInvValue1"

 

[Guest]

It would be very helpful If I knew what line you are getting the error on.

Yes [tblSalesInvoice]![AccountCodeVAT] is numeric with the following format;
Double, Fixed, 0 Decimal places.

I don't know what the parentheses do for this line, but I doubt it is the
problem. In any case, you don't really need them:

If ([tblSalesInvoice]![AccountCodeVAT]) = 2205 Then

Is [tblSalesInvoice]![AccountCodeVAT] a numeric data type?

What line do you get the error on?

I have changed the code based on your suggestion and added Object recognition
as follows:
Private Sub cmdSLGLInput_Click()
Dim dbs As Database
Dim rst As Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblSalesInvoice", dbOpenDynaset)
DoCmd.OpenQuery "qappSLInvValueGross"
If ([tblSalesInvoice]![AccountCodeVAT]) = 2205 Then
DoCmd.OpenQuery "qappSLInvValueVAT"
ElseIf Not IsNull([tblSalesInvoice]![AccountCode1]) Then
DoCmd.OpenQuery "qappSLInvValue1"

The error I now get is No: 13 Type MisMatch

:

It is a syntax problem:
Incorrect:
ElseIf [tblSalesInvoice]![AccountCode1] Is Not Null Then
Correct:
ElseIf Not IsNull([tblSalesInvoice]![AccountCode1]) Then

I also note that if none of the conditions is true, nothing happens. Is
that what you want?

:

Below is the code I have written to load data into various parts of a
database, It falls over at the first "IF" statement, What am I doing wrong?
The error message is as follows "Error No: 2465, Description: Microsoft
Office Access can't find the field '|' referred to in your expression.

Private Sub cmdSLGLInput_Click()
On Error GoTo ErrorHandler

DoCmd.SetWarnings False
DoCmd.OpenQuery "qappSLInvValueGross"
If [tblSalesInvoice]![AccountCodeVAT] = 2205 Then
DoCmd.OpenQuery "qappSLInvValueVAT"
ElseIf [tblSalesInvoice]![AccountCode1] Is Not Null Then
DoCmd.OpenQuery "qappSLInvValue1"

 

[Guest]

The error occurs at the first "If" statement, The append query that follows
the statement works fine in isolation.

It would be very helpful If I knew what line you are getting the error on.

Yes [tblSalesInvoice]![AccountCodeVAT] is numeric with the following format;
Double, Fixed, 0 Decimal places.

I don't know what the parentheses do for this line, but I doubt it is the
problem. In any case, you don't really need them:

If ([tblSalesInvoice]![AccountCodeVAT]) = 2205 Then

Is [tblSalesInvoice]![AccountCodeVAT] a numeric data type?

What line do you get the error on?

:

I have changed the code based on your suggestion and added Object recognition
as follows:
Private Sub cmdSLGLInput_Click()
Dim dbs As Database
Dim rst As Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblSalesInvoice", dbOpenDynaset)
DoCmd.OpenQuery "qappSLInvValueGross"
If ([tblSalesInvoice]![AccountCodeVAT]) = 2205 Then
DoCmd.OpenQuery "qappSLInvValueVAT"
ElseIf Not IsNull([tblSalesInvoice]![AccountCode1]) Then
DoCmd.OpenQuery "qappSLInvValue1"

The error I now get is No: 13 Type MisMatch

:

It is a syntax problem:
Incorrect:
ElseIf [tblSalesInvoice]![AccountCode1] Is Not Null Then
Correct:
ElseIf Not IsNull([tblSalesInvoice]![AccountCode1]) Then

I also note that if none of the conditions is true, nothing happens. Is
that what you want?

:

Below is the code I have written to load data into various parts of a
database, It falls over at the first "IF" statement, What am I doing wrong?
The error message is as follows "Error No: 2465, Description: Microsoft
Office Access can't find the field '|' referred to in your expression.

Private Sub cmdSLGLInput_Click()
On Error GoTo ErrorHandler

DoCmd.SetWarnings False
DoCmd.OpenQuery "qappSLInvValueGross"
If [tblSalesInvoice]![AccountCodeVAT] = 2205 Then
DoCmd.OpenQuery "qappSLInvValueVAT"
ElseIf [tblSalesInvoice]![AccountCode1] Is Not Null Then
DoCmd.OpenQuery "qappSLInvValue1"

 

[Guest]

Doh! I should have seen this earlier. There is a little coding error That I
did not catch. It is an object reference problem. Why you got a error 13, I
don't know. When I tested it, I get an error 424 "Object Required". Once
you open a recordset, you refer to it by the object name you gave it (rst)
not the name of the table.

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblSalesInvoice", dbOpenDynaset)
DoCmd.OpenQuery "qappSLInvValueGross"
If rst![AccountCodeVAT] = 2205 Then
DoCmd.OpenQuery "qappSLInvValueVAT"
ElseIf Not IsNull(rst![AccountCode1]) Then
DoCmd.OpenQuery "qappSLInvValue1"

The error occurs at the first "If" statement, The append query that follows
the statement works fine in isolation.

It would be very helpful If I knew what line you are getting the error on.

Yes [tblSalesInvoice]![AccountCodeVAT] is numeric with the following format;
Double, Fixed, 0 Decimal places.

:

I don't know what the parentheses do for this line, but I doubt it is the
problem. In any case, you don't really need them:

If ([tblSalesInvoice]![AccountCodeVAT]) = 2205 Then

Is [tblSalesInvoice]![AccountCodeVAT] a numeric data type?

What line do you get the error on?

:

I have changed the code based on your suggestion and added Object recognition
as follows:
Private Sub cmdSLGLInput_Click()
Dim dbs As Database
Dim rst As Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblSalesInvoice", dbOpenDynaset)
DoCmd.OpenQuery "qappSLInvValueGross"
If ([tblSalesInvoice]![AccountCodeVAT]) = 2205 Then
DoCmd.OpenQuery "qappSLInvValueVAT"
ElseIf Not IsNull([tblSalesInvoice]![AccountCode1]) Then
DoCmd.OpenQuery "qappSLInvValue1"

The error I now get is No: 13 Type MisMatch

:

It is a syntax problem:
Incorrect:
ElseIf [tblSalesInvoice]![AccountCode1] Is Not Null Then
Correct:
ElseIf Not IsNull([tblSalesInvoice]![AccountCode1]) Then

I also note that if none of the conditions is true, nothing happens. Is
that what you want?

:

Below is the code I have written to load data into various parts of a
database, It falls over at the first "IF" statement, What am I doing wrong?
The error message is as follows "Error No: 2465, Description: Microsoft
Office Access can't find the field '|' referred to in your expression.

Private Sub cmdSLGLInput_Click()
On Error GoTo ErrorHandler

DoCmd.SetWarnings False
DoCmd.OpenQuery "qappSLInvValueGross"
If [tblSalesInvoice]![AccountCodeVAT] = 2205 Then
DoCmd.OpenQuery "qappSLInvValueVAT"
ElseIf [tblSalesInvoice]![AccountCode1] Is Not Null Then
DoCmd.OpenQuery "qappSLInvValue1"

 

[Guest]

Klatuu

Thank you for your help, between the 2 of us we sorted the problem, extract
below:

Private Sub cmdSLGLInput_Click()
On Error GoTo ErrorHandler
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblSalesInvoice", dbOpenDynaset)
DoCmd.SetWarnings False
DoCmd.OpenQuery "qappSLInvValueGross"
If (Me![AccountCodeVAT]) = 2205 Then
DoCmd.OpenQuery "qappSLInvValueVAT"
End If
If Not IsNull(Me![AccountCode1]) Then
DoCmd.OpenQuery "qappSLInvValue1"
End If
If Not IsNull(Me![AccountCode2]) Then
DoCmd.OpenQuery "qappSLInvValue2"

 

[peregenem]

Yes [tblSalesInvoice]![AccountCodeVAT] is numeric with the following format;
Double, Fixed, 0 Decimal places.

You may want to check that:

http://www.govtalk.gov.uk/gdsc/sche...ierTypes-v1-0-xsd-ValueAddedTaxNumberType.htm