By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,550 Members | 1,733 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,550 IT Pros & Developers. It's quick & easy.

syntax for a dlookup

P: n/a
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?

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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?

Nov 13 '05 #2

P: n/a
"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.
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.