Dlookup in qry based on 2 numeric fields

[penwood]

i have tblDates that has two number long integer fields named FiscalYear and FiscalMonth. then there is my field PerSeq is number long integer.

in my Query1, i have 2 fields that use a question in the field part of the qry like:
Desired FiscalYear: [Enter Fiscal Year] EG 2007
Desired FiscalMonth: [Enter Fiscal Month] EG 2

And now the Qry question. The field in the qry "Desired BaseSeq". i want this dlookup to get PerSeq out of tblDates based on my response to the Desired FiscalYear and Desired FiscalMonth.

I start it out here but it is wrong i know.

Desired BaseSeq: DLookUp("PerSeq","tblDates","[Desired FiscalYear] And [Desired FiscalMonth]=" & [tblDates].[FiscalYear] And [tblDates].[FiscalMonth])

Thanks you.

 

[pbaldy]

Your structure for multiple criteria is off. Perhaps this will help:

http://www.mvps.org/access/general/gen0018.htm

 

[penwood]

i am getting closer. the answer now comes out fine. but i am prompted 2 times to enter the year and month.

here are the fields in the Qry involved.

Desired FiscalYear: [Enter Fiscal Year]

Desired FiscalMonth: [Enter Fiscal Month]

Desired BaseSeq: DLookUp("PerSeq","tblDates","[FiscalYear]=" & [Desired FiscalYear] & " And [FiscalMonth]=" & [Desired FiscalMonth])


so for some reason, i am asked to enter the fiscal year and fiscal month, twice.

maybe the 3rd field needs a tweak when referencing those 2 fields?

Penwood

 

[pbaldy]

No, the 2 requests are slightly different (spaces in the first, not the second).