Dlookup doesn't work

[SHIPP]

I am having problems with Dlookup. I am using Access 97. A
copy of the code follows:

Option Compare Database
Option Explicit

Private Sub btnRun_Click()
On Error GoTo Err_btnRun_Click

Dim dtBeg As Date

' This one works

' dtBeg = DLookup("gl_perod_stdt", "ingres_gl_Perod_tbl",
' "gl_perod_year = " & Forms!rmSafewayScorecard.intYear)

' This one works

' dtBeg = DLookup("gl_perod_stdt", "ingres_gl_Perod_tbl",
' "gl_perod_id = " & Forms!rmSafewayScorecard.intPeriod)

' This one does not work.

dtBeg = DLookup("gl_perod_stdt", "ingres_gl_Perod_tbl", _
"gl_perod_year = " & Forms!frmSafewayScorecard.intYear
And "gl_perod_id = " & Forms!frmSafewayScorecard.intPeriod)
Me.dtBegDate = dtBeg

Exit_btnRun_Click:
Exit Sub

Err_btnRun_Click:
MsgBox Err.DESCRIPTION
Resume Exit_btnRun_Click

End Sub

The minute I try and put an AND in the criteria it tells
me I have a type mismatch. Individually the gl_perod_year
and gl_perod_id work. Any help would be greatly
appreciated. Thanks in advance.

 

[SHIPP]

Bruce:

I tried it based on your input. I get:
Compile Error:
Expected: end of statement

Any additional help would be greatly appreciated.

-----Original Message-----

Does this work better?:

dtBeg = DLookup("gl_perod_stdt", "ingres_gl_Perod_tbl", _
"gl_perod_year = " & Forms!

frmSafewayScorecard.intYear & _

 

[Timothy C. Doherty]

Bruce caught the "And" error in the concatenated criteria string... since
this didn't fix it, did you notice that your form names are different in the
separate DLookup's than in the combined one...

In your individual DLookup statements, your form is named:
rmSafewayScorecard

In your combined, it's named: frmSafewayScorecard

If the first two statements work, your form is named: rmSafewayScorecard, so
try Bruce's corrected concatenation along with consistent form names:

dtBeg = DLookup("gl_perod_stdt", "ingres_gl_Perod_tbl", _
"gl_perod_year = " & Forms!rmSafewayScorecard.intYear & _
" And gl_perod_id = " & Forms!rmSafewayScorecard.intPeriod)