Dlookup help

[purchaandr]

Hey there, having a bit of trouble with the Dlookup function and was wondering if any of the guru's out there could help?? The code which I am using is:

Private Sub Opportunity_Id_AfterUpdate()
Dim OpportunityID As Integer

OpportunityID = [Forms]![frmActivity Add New]![Opportunity ID].Text
txtOpportunity = DLookup("Opportunity Description", "Opportunity", "Id =" & OpportunityID)
End Sub

And the error that this is giving me is:

Runtime Error '3075'

Syntax Error (missing operator) in query expression 'Opportunity Description'.


This seems rather odd as I have used this exact method of Dlookup before in my project and it has worked fine! What am I doing wrong? Is anyone able to help me out???


Cheers, Andrew

 

[allan57]

Try enclosing the field name with square brackets:

txtOpportunity = DLookup("[Opportunity Description]", "Opportunity", "Id =" & OpportunityID)

Try

 

[namliam]

Dont use spaces or any special characters, _)(*&^ etc, in table names...

If you do you have to ALWAYS fix this by putting [] around the names containing these characters.

edit
Alan beat me to this
/edit

Also, tables should adhere to a naming convention that differentiates tables from queries. 'good practice' is tblName for tables, qryName, frmName,rptName.

 

[purchaandr]

Ok thanks alot guys it works! I had not included the square brackets as I thought the use of speech marks would do the same job!

With regards to the table names, I did not create the database! All of the forms I have made however do follow appropriate naming conventions

It still amazes me how there can be something that trips me up for a while can be answered by you guys in a matter of minutes! I really appreciate the help, thanks a lot all of you!

Andrew

 

[namliam]

It still amazes me how there can be something that trips me up for a while can be answered by you guys in a matter of minutes! I really appreciate the help, thanks a lot all of you!

Just dont use spaces

Actually seconds.... but who is counting Just a matter of tripping on the same trap a couple of times...
This is something you will not have happen again.... and can help someone with someday yourself, also in seconds/minutes.

 

[Wet_Blanket]

Good Morning,

I am receiving the same error, and added the brackets to it (like above), however I still receive the same error. It seems like it will give me the error when it can't populate a value. Could this be it, and how could I fix it so the error does not show?

Here is my code:

Full_Name3.Value = DLookup("[Full_Name]", "[AMS_Accounts]", "[acct_num]='" & Account1 & "'" & "And [Full_Name] <> '" & [Full_Name1] & "And [Full_Name] <> '" & [Full_Name2] & "'")

If there isn't data for a third name that isn't the same as the first two, then it gives me the error.

 

[dkinley]

DLookup returns a null when there is no data. If would assume if there is no 3rd name, then the null returned makes the entire statement null.

You can either use the Nz(DLookup()) wrapper to overcome this or perhaps Nz([ThirdName], "") so it is an empty string might work.

-dK

 

[Wet_Blanket]

DLookup returns a null when there is no data. If would assume if there is no 3rd name, then the null returned makes the entire statement null.

You can either use the Nz(DLookup()) wrapper to overcome this or perhaps Nz([ThirdName], "") so it is an empty string might work.

-dK

So if I did the wrapper, would it look like this:

Full_Name3.Value = Nz(DLookup("[Full_Name]", "[AMS_Accounts]", "[acct_num]='" & Account1 & "'" & "And [Full_Name] <> '" & [Full_Name1] & "And [Full_Name] <> '" & [Full_Name2] & "'"))

?

 

[dkinley]

The usage is ...

Nz(statement, statement if null). For example Nz(Something, "No Way"). If something evaluated to a null, then "No Way" would be displayed. So you would lose the entire name in this instance. You can try it around the third name to see if that will help.

If not, you could do a check prior to running your DLookup. For instance ...

If IsNull(DLookup(ThirdName)) Then
'dlookup on only the first two names
Else
'dlookup on all of the names
End If

-dK

 

[Wet_Blanket]

Hmmm...I believe I am over my head.

As an alternative, I set up combo boxes for each name - so in theory, the user can use each combo box to pick the name. A query is executed for each box that populates the name choices. However, when the user moves to a new entry, the names from the old entry are still populated. Can you tell me how to get the combo boxes to start 'fresh' with each new entry?

 

[dkinley]

Maybe not ... let's check your premise.

Three conditions must be met in order for the DLookup to not return a null.
(1) Account [acct_num]='" & Account1 & "'" & "
(2) [Full_Name] <> '" & [Full_Name1] & "
(3) [Full_Name] <> '" & [Full_Name2]

The three conditions must be true or a null is returned. Is this what the effect is supposed to be?

I am wondering the quotation usage around the first premise. A number doesn't require quotations - only strings. If the account number is alpha-numeric then ignore this paragraph.

Now, as far as referencing - I am assuming that it is on a form. So we could use (just as further swags) ...

Me.Full_Name3 = DLookup("[Full_Name]", "[AMS_Accounts]", "[acct_num]='" & Me.Account1 & "'" & "And [Full_Name] <> '" & [Full_Name1] & "And [Full_Name] <> '" & [Full_Name2] & "'")

Also, I am assuming the [..Name] fields are all of those in a table and comparing to each other and not on any controls on a form so leaving them as is. If not, then they should be Me.Full_Name1 or 2.

Last, what is the outcome of these premises? Are you just checking for something to let the user know they should assign another thing if that something doesn't exist (e.g., checking for duplicates?) If checking the existence of something, you could use the Nz() ...

Me.Full_Name3 = Nz(DLookup("[Full_Name]", "[AMS_Accounts]", "[acct_num]='" & Me.Account1 & "'" & "And [Full_Name] <> '" & [Full_Name1] & "And [Full_Name] <> '" & [Full_Name2] & "'"), "Not found")

-dK

 

[Wet_Blanket]

Maybe not ... let's check your premise.

Three conditions must be met in order for the DLookup to not return a null.
(1) Account [acct_num]='" & Account1 & "'" & "
(2) [Full_Name] <> '" & [Full_Name1] & "
(3) [Full_Name] <> '" & [Full_Name2]

The three conditions must be true or a null is returned. Is this what the effect is supposed to be?

I am wondering the quotation usage around the first premise. A number doesn't require quotations - only strings. If the account number is alpha-numeric then ignore this paragraph.

Now, as far as referencing - I am assuming that it is on a form. So we could use (just as further swags) ...

Me.Full_Name3 = DLookup("[Full_Name]", "[AMS_Accounts]", "[acct_num]='" & Me.Account1 & "'" & "And [Full_Name] <> '" & [Full_Name1] & "And [Full_Name] <> '" & [Full_Name2] & "'")

Also, I am assuming the [..Name] fields are all of those in a table and comparing to each other and not on any controls on a form so leaving them as is. If not, then they should be Me.Full_Name1 or 2.

Last, what is the outcome of these premises? Are you just checking for something to let the user know they should assign another thing if that something doesn't exist (e.g., checking for duplicates?) If checking the existence of something, you could use the Nz() ...

Me.Full_Name3 = Nz(DLookup("[Full_Name]", "[AMS_Accounts]", "[acct_num]='" & Me.Account1 & "'" & "And [Full_Name] <> '" & [Full_Name1] & "And [Full_Name] <> '" & [Full_Name2] & "'"), "Not found")

-dK

Thanks for your help. Basically, the output from these DLookup populate fields for a table. Sometimes an account has one owner, other times, it may have four. So instead of the user having to manually look up and input the owners, I want the dlookups to put their names in the field (for verification by user). I tried the last code you provided, but it resulted in the same error.

 

[dkinley]

Just as an aside because I haven't seen your db. I am supposing that the account is selected and then verified by the user (hence the looking up of names).

To go back to one of your other points, if the account is on a combo box then the row source of the account could include these names. Then the text boxes could simply reference a column of the combo box to populate it. E.g, Me.txtControlName = cboComboBoxName.Column(2).

-dK