Can you use DLookup on 2 fields from 1 query (that pulls both fiel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can you do a Dlookup to look up 2 fields (contract_no & doc_type) from 1
query? I keep getting a "compile error: syntax error."

If IsNull(DLookup("[contract_no] & [doc_type]",
"qryBill_EffContract_forDelete", "[contract_no] " & [doc_type]= " &
Me!Contract_no & Me!Doc_type)) Then
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.CancelEvent
Me.Requery
MsgBox ("deleting")
Else
Cancel = True
MsgBox ("You cannot delete this contract. Billing/Revenue records
exist.")
End If

Thanks!!

Geri
 
gg said:
Can you do a Dlookup to look up 2 fields (contract_no & doc_type) from 1
query? I keep getting a "compile error: syntax error."

If IsNull(DLookup("[contract_no] & [doc_type]",
"qryBill_EffContract_forDelete", "[contract_no] " & [doc_type]= " &
Me!Contract_no & Me!Doc_type)) Then
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.CancelEvent
Me.Requery
MsgBox ("deleting")
Else
Cancel = True
MsgBox ("You cannot delete this contract. Billing/Revenue records
exist.")
End If
Click to expand...


Should work if you get rid of the extra quote in the
condition:
. . . "[contract_no] & [doc_type]= " & Me!Contract_no &
Me!Doc_type)

but, assuming both fields are numeric type, a more common
way to write that would be:
If DCount("*", "qryBill_EffContract_forDelete",
"[contract_no] = " & Me!Contract_no & " And & [doc_type] = "
& Me!Doc_type) Then
 
I've never seen that done. I would check the syntax in Access Help, it looks
like your first parameter is invalid, you only need one column name in there.
DLookup tends to be slow. Its better to open a recordset on a query

-Dorian.
 
Hi Marsh,

Much simpler and better idea to use "DCount". Works out great.

THANK YOU!



Marshall Barton said:
gg said:
Can you do a Dlookup to look up 2 fields (contract_no & doc_type) from 1
query? I keep getting a "compile error: syntax error."

If IsNull(DLookup("[contract_no] & [doc_type]",
"qryBill_EffContract_forDelete", "[contract_no] " & [doc_type]= " &
Me!Contract_no & Me!Doc_type)) Then
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.CancelEvent
Me.Requery
MsgBox ("deleting")
Else
Cancel = True
MsgBox ("You cannot delete this contract. Billing/Revenue records
exist.")
End If
Click to expand...


Should work if you get rid of the extra quote in the
condition:
. . . "[contract_no] & [doc_type]= " & Me!Contract_no &
Me!Doc_type)

but, assuming both fields are numeric type, a more common
way to write that would be:
If DCount("*", "qryBill_EffContract_forDelete",
"[contract_no] = " & Me!Contract_no & " And & [doc_type] = "
& Me!Doc_type) Then
Click to expand...
 
What would be the syntax to open the recordset instead? Thanks,



mscertified said:
I've never seen that done. I would check the syntax in Access Help, it looks
like your first parameter is invalid, you only need one column name in there.
DLookup tends to be slow. Its better to open a recordset on a query

-Dorian.

gg said:
Can you do a Dlookup to look up 2 fields (contract_no & doc_type) from 1
query? I keep getting a "compile error: syntax error."

If IsNull(DLookup("[contract_no] & [doc_type]",
"qryBill_EffContract_forDelete", "[contract_no] " & [doc_type]= " &
Me!Contract_no & Me!Doc_type)) Then
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.CancelEvent
Me.Requery
MsgBox ("deleting")
Else
Cancel = True
MsgBox ("You cannot delete this contract. Billing/Revenue records
exist.")
End If

Thanks!!

Geri
Click to expand...
Click to expand...
 
Can you do a Dlookup to look up 2 fields (contract_no & doc_type) from 1
query? I keep getting a "compile error: syntax error."
Click to expand...

Not with a single DLookUp, no. You can look up a NEW concatenated
field though - which would seem to be what you're trying to do. The
problem is in your criteria. If you want the criteria to search
(separately) for a Contract_No and a Doc_Type, you need to construct a
valid SQL query WHERE clause. Try

If IsNull(DLookup("[contract_no] & [doc_type]", _
"qryBill_EffContract_forDelete", _
"[contract_no] " & Me!Contract_no _
& " AND [doc_type]= " & Me!Doc_type)) Then

This assumes that Contract_No and Doc_Type are number type fields. If
they are Text, you need to delimit them with quotemarks:


If IsNull(DLookup("[contract_no] & [doc_type]", _
"qryBill_EffContract_forDelete", _
"[contract_no] '" & Me!Contract_no & "'" _
& " AND [doc_type]= '" & Me!Doc_type) & "'") Then

This will create a search string like

[Contract_No] = 'A-2149' AND [Doc_Type] = 'Amendment'


or whatever the values in the Contract_No and Doc_Type fields might
be.

John W. Vinson[MVP]
 
John Vinson said:
Can you do a Dlookup to look up 2 fields (contract_no & doc_type) from 1
query? I keep getting a "compile error: syntax error."
Click to expand...

Not with a single DLookUp, no. You can look up a NEW concatenated
field though - which would seem to be what you're trying to do. The
problem is in your criteria. If you want the criteria to search
(separately) for a Contract_No and a Doc_Type, you need to construct a
valid SQL query WHERE clause. Try

If IsNull(DLookup("[contract_no] & [doc_type]", _
"qryBill_EffContract_forDelete", _
"[contract_no] " & Me!Contract_no _
& " AND [doc_type]= " & Me!Doc_type)) Then

This assumes that Contract_No and Doc_Type are number type fields. If
they are Text, you need to delimit them with quotemarks:


If IsNull(DLookup("[contract_no] & [doc_type]", _
"qryBill_EffContract_forDelete", _
"[contract_no] '" & Me!Contract_no & "'" _
& " AND [doc_type]= '" & Me!Doc_type) & "'") Then

This will create a search string like

[Contract_No] = 'A-2149' AND [Doc_Type] = 'Amendment'


or whatever the values in the Contract_No and Doc_Type fields might
be.

John W. Vinson[MVP]
Click to expand...
John,

Your solutions are always clear and concise. I appreciate your
explanations. Thank you and happy New Year!

geri
 
Back
Top