Problems with DLookUp

[slenish]

Hello,

Im trying to use the DLookup function to fill in a few text boxes based on the criteria of 3 text boxes. I think I have the vba code right but for some reason its not working.

 
Me.Text1 = DLookup("[PlaceName]","MainTable","[Date]=" _ & Forms![EditForm]!Text3, _ 
"[Name]=" & Forms![EditForm]![Text4, 
"[Number]=" & Forms![EditForm]!Text5)
 
 

What im trying to do is have the text1 box display a value from the MainTable based on what is in text3, 4, and 5.

Appreciate any help with this

Thanks :)

[MikeTheBike]

Hi

On the basis that Text1, Text3, Text4 and Text5 are all in the same form and Text3 is a date, Text4 is Text and Text5 is numeric, the I suggest this should do it

Me.Text1 = DLookup("[PlaceName]", "MainTable", _
    "[Date]=#" & Format(Text3, "mm/dd/yy") & "# " & _
    "And [Name]= '" & Text4 & "' " & _
    "And [Number]= " & Text5)

The date should be delimited with # symbols and formatted in US format, and the Text arguments should be delimited with apostrophies.

HTH

MTB

[slenish]

Hi MTB,

Thanks so much for the reply. I think what you wrote is what I needed I adjusted it a little bit playing around with it because at first I was getting some errors. Now im not getting any errors but im not getting any results?? Any ideas? Also to let you know both Text4 and Text 5 are numbers but they are being entered and saved as a string.

Thanks again

Here is what the readjustment looks like

 
Me.Text1 = DLookup("[Name]", "MainTable", _
    "[Date]=#" & Format(Text3, "mm/dd/yy") & "# " & _
    "And [Number]=" & Format(Text4, "'") & _
    "And [Number2]=" & Format(Text5, "'"))
 
 

[slenish]

Haha I just got it to work. I had to readjust the format for the text 4 and 5 and it works great!

thanks again MTB could not have done it with out you :D

here is what I did

 
Me.Text1 = DLookup("[Name]", "MainTable", _
    "[ProcessDate]=#" & Format(Text3, "mm/dd/yy") & "# " & _
    "And [Number]=" & Chr(34) & Text4 & Chr(34) & _
    "And [Number2]=" & Chr(34) & Text5 & Chr(34))