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

[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

 

[Marshall Barton]

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

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

 

[Guest]

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.

 

[Guest]

Hi Marsh,

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

THANK YOU!

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

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

 

[Guest]

What would be the syntax to open the recordset instead? Thanks,

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.

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

 

[John Vinson]

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."

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]

 

[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."

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,

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

geri