error 3075

  • Thread starter Thread starter Jon
  • Start date Start date
J

Jon

Greeting,
I have the following code which apply in before update event in form, but i
have this error syntax error missing operator in query expression
‘Sum(Quantity * iif(transaction type = “additionâ€,1,-1))’. My code is as
follows:
Dim strMessage As String
Dim strCriteria As String
Dim intStockInHand As Integer

If Me.[Transaction Type] <> "addition" Then
strCriteria = "Item = """ & Me.Item & """"

intStockInHand = _
DSum("Quantity * IIf(Transaction Type = ""addition"",1,-1)",
"Inventory Transactions Extended", strCriteria)

If intStockInHand - Me.Quantity < 0 Then
strMessage = "Insufficient " & Item & " stock in hand."
MsgBox strMessage, vbExclamation, "Invalid Operation"
Cancel = True
ElseIf intStockInHand - Me.Quantity < 3 Then
strMessage = "This transaction will leave " & _
intStockInHand - Me.Quantity & " of " & Me.Item & _
" in stock." & vbNewLine & vbNewLine & _
"Do you wish to continue?"
If MsgBox(strMessage, vbQuestion + vbOKCancel, _
"Warning") = vbCancel Then

End If
End If
End If

My orginal post can be found here:
http://www.microsoft.com/office/com...&p=1&tid=46d77a20-1c41-4a87-9aaa-15c7ab80cee9
 
Try using apostrophes inside the quotes:

strCriteria = "Item = '" & me.Item & "'"

intStockInHand = DSum("Quantity * IIf(Transaction Type = 'addition'",1,-1)",
"Inventory Transactions Extended", strCriteria)
 
Edit my previous, think left extraneous quote:
Try using apostrophes inside the quotes:

strCriteria = "Item = '" & me.Item & "'"

intStockInHand = DSum("Quantity * IIf(Transaction Type = 'addition',1,-1)",
"Inventory Transactions Extended", strCriteria)


June7 said:
Try using apostrophes inside the quotes:

strCriteria = "Item = '" & me.Item & "'"

intStockInHand = DSum("Quantity * IIf(Transaction Type = 'addition'",1,-1)",
"Inventory Transactions Extended", strCriteria)


Jon said:
Greeting,
I have the following code which apply in before update event in form, but i
have this error syntax error missing operator in query expression
‘Sum(Quantity * iif(transaction type = “additionâ€,1,-1))’. My code is as
follows:
Dim strMessage As String
Dim strCriteria As String
Dim intStockInHand As Integer

If Me.[Transaction Type] <> "addition" Then
strCriteria = "Item = """ & Me.Item & """"

intStockInHand = _
DSum("Quantity * IIf(Transaction Type = ""addition"",1,-1)",
"Inventory Transactions Extended", strCriteria)

If intStockInHand - Me.Quantity < 0 Then
strMessage = "Insufficient " & Item & " stock in hand."
MsgBox strMessage, vbExclamation, "Invalid Operation"
Cancel = True
ElseIf intStockInHand - Me.Quantity < 3 Then
strMessage = "This transaction will leave " & _
intStockInHand - Me.Quantity & " of " & Me.Item & _
" in stock." & vbNewLine & vbNewLine & _
"Do you wish to continue?"
If MsgBox(strMessage, vbQuestion + vbOKCancel, _
"Warning") = vbCancel Then

End If
End If
End If

My orginal post can be found here:
http://www.microsoft.com/office/com...&p=1&tid=46d77a20-1c41-4a87-9aaa-15c7ab80cee9
Click to expand...
Click to expand...
 
Edit my previous, think left extraneous quote:
Try using apostrophes inside the quotes:

strCriteria = "Item = '" & me.Item & "'"

intStockInHand = DSum("Quantity * IIf(Transaction Type = 'addition',1,-1)",
"Inventory Transactions Extended", strCriteria)
Click to expand...

one way to do this that makes it more obvious is to declare a constant
for the single quote and use that...

Const cQUOTE As String = " ' " 'remove the extra spaces... that's
so you can see the single quote

strCriteria = "Item =" & cQUOTE & me.Item & cQuote
 
Jon said:
I have the following code which apply in before update event in form, but i
have this error syntax error missing operator in query expression
‘Sum(Quantity * iif(transaction type = “additionâ€,1,-1))’.
Click to expand...

What is transaction type? If it's a field name which contains a space,
surround that name with square brackets to avoid confusing Access.

.... iif([transaction type] = ...
 
Back
Top