syntax for a dlookup

[jwa6]

syntax for a dlookup
ok I give up!

here is the error...
' Run-time error 3075 Syntax error in query expression '...'

this is the code before that a contractor wrote.
(I dont know if it ever worked....the table was wrong however)
If Nz(DLookup("FedBudget", "tblBudget", "([SubgNo] = " & Me.SubgNo & ")
And ([BudgetID] = " & Me.BudgetID & ")"), 0) - ([FedFSRToDate]) < -500
Then

' MsgBox "Overspending " & vbCrLf & vbCrLf & Me.Category &
vbCrLf & vbCrLf & "by more than $500", vbExclamation

this is the code I put in causing and error.

If Nz(DLookup("[BUDGET_FEDERAL]", "[VOCA_BUDGET]",
"([VOCA_BUDGET].[SUBGRANT_NO] = " & Me.SubgNo & ") And
([VOCA_BUDGET].[Budget_ID] = " & Me.BudgetID & ")"), 0) -
([FedFSRToDate]) < -500 Then
MsgBox "Overspending " & vbCrLf & vbCrLf & Me.Category & vbCrLf
& vbCrLf & "by more than $500", vbExclamation
any help?

[Douglas J. Steele]

Try removing the [VOCA_BUDGET]. from the criteria section of the DLookup.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"jwa6" <jw*****@yahoo.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

syntax for a dlookup
ok I give up!

here is the error...
' Run-time error 3075 Syntax error in query expression '...'

this is the code before that a contractor wrote.
(I dont know if it ever worked....the table was wrong however)
If Nz(DLookup("FedBudget", "tblBudget", "([SubgNo] = " & Me.SubgNo & ")
And ([BudgetID] = " & Me.BudgetID & ")"), 0) - ([FedFSRToDate]) < -500
Then

' MsgBox "Overspending " & vbCrLf & vbCrLf & Me.Category &
vbCrLf & vbCrLf & "by more than $500", vbExclamation

this is the code I put in causing and error.

If Nz(DLookup("[BUDGET_FEDERAL]", "[VOCA_BUDGET]",
"([VOCA_BUDGET].[SUBGRANT_NO] = " & Me.SubgNo & ") And
([VOCA_BUDGET].[Budget_ID] = " & Me.BudgetID & ")"), 0) -
([FedFSRToDate]) < -500 Then
MsgBox "Overspending " & vbCrLf & vbCrLf & Me.Category & vbCrLf
& vbCrLf & "by more than $500", vbExclamation
any help?

[Eric Schittlipz]

"jwa6" <jw*****@yahoo.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

syntax for a dlookup
ok I give up!

here is the error...
' Run-time error 3075 Syntax error in query expression '...'

this is the code before that a contractor wrote.
(I dont know if it ever worked....the table was wrong however)
If Nz(DLookup("FedBudget", "tblBudget", "([SubgNo] = " & Me.SubgNo & ")
And ([BudgetID] = " & Me.BudgetID & ")"), 0) - ([FedFSRToDate]) < -500
Then

' MsgBox "Overspending " & vbCrLf & vbCrLf & Me.Category &
vbCrLf & vbCrLf & "by more than $500", vbExclamation

this is the code I put in causing and error.

If Nz(DLookup("[BUDGET_FEDERAL]", "[VOCA_BUDGET]",
"([VOCA_BUDGET].[SUBGRANT_NO] = " & Me.SubgNo & ") And
([VOCA_BUDGET].[Budget_ID] = " & Me.BudgetID & ")"), 0) -
([FedFSRToDate]) < -500 Then
MsgBox "Overspending " & vbCrLf & vbCrLf & Me.Category & vbCrLf
& vbCrLf & "by more than $500", vbExclamation
any help?

Why not break it up into manageable bits and use variables to hold certain
bits of data. For example, you could dimension strWhere as a string to hold
the WHERE clause for the DLookup you could build this up in bits and do a
Debug.Print of it to check it is as expected. Also dimension something to
hold FedFSRToDate - whatever this is - a field from a bound form?
To put the whole lot in effectively one line of code is, as you have found
out, asking for trouble.