Dlookup problem

[waq963]

Hi, Can someone help me format that this Dlookup so it works. I jus cant get the formatting right. Thanks.

txtScore.Value = DLookup("[Score]", "tblAQ", "ID = '" & Forms![frmQ]!cboP.Column(0) & "'" And "[Q No] = """ & Forms![frmQ]!txtQ & """")

 

[ajetrumpet]

first thing here: what data type is "ID"? <----- this determines how and where to use the quote marks

second thing...you don't need .column(0) in the "cboP" portion of the criteria part of the statement. that is default anyway for multi-column controls.

 

[waq963]

Thanks for the reply. ID is a string.

 

[ajetrumpet]

these are always a pain in the butt to figure out. try one of these:

1) txtScore.Value = DLookup("[Score]", "tblAQ", "ID = '" & Forms![frmQ]!cboP & "' And [Q No] = '" & Forms![frmQ]!txtQ & "'")

2) txtScore.Value = DLookup("[Score]", "tblAQ", "ID = '" & Forms![frmQ]!cboP & "'" And "[Q No] = '" & Forms![frmQ]!txtQ & "'")

 

[waq963]

The 2nd is correct but it comes up with the 'Type Mismatch' Error. When i place my cursor over the code it is showing the correct ID and Q No but just the mismatch error comes up. Dont know why though?

 

[ajetrumpet]

The 2nd is correct but it comes up with the 'Type Mismatch' Error. When i place my cursor over the code it is showing the correct ID and Q No but just the mismatch error comes up. Dont know why though?

I personally believe that the 1st one is correct...i am in hospital right now, but if you upload the DB, i'm sure someone can get it fixed for you..

 

[unclejoe]

My guess would be the missing "&" at
2) txtScore.Value = DLookup("[Score]", "tblAQ", "ID = '" & Forms![frmQ]!cboP & "'" & " And [Q No] = '" & Forms![frmQ]!txtQ & "'")

 

[waq963]

Thanks for the reply. I get an error message saying 'Reserved Error'. I have never come accross this before?

 

[ajetrumpet]

Waq,

i don't think what has been posted is right anyway. upload the database. syntax for these kinds of functions in VBA vary WIDELY from version to version. sorry to say. uploading will get your problem answered quicker than what you've already gone through...