Dlookup in qry using 2 number fields in criteria 1

[molly]

i have tblDates that has Number long integer fields named FiscalYear and FiscalMonth and PerSeq.

in my Query1, i have 2 fields that use a question in the field part of the qry:

Desired FiscalYear: [Enter Fiscal Year]

Desired FiscalMonth: [Enter Fiscal Month]

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

I start the qry field 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.

 

[dhookom]

I'm not sure about your setup but your expression might be:

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


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[molly]

Duane - i think you are getting closer.

i get a new error message:

the expression you entered as a query parameter produced this error: "microsoft Access can't find the name 'Desired FiscalYear' you entered in the expression.


the spelling seem okay to me. so i don't know.

 

[dhookom]

Sorry, I should have looked deeper. I never use parameter queries when references to controls on forms are better.

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


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[molly]

Duane - This is almost perfect. I get the correct answer. However, i must answer the 2 Qry questions 2 times. But it works. Maybe you can see how to make it just 1 time?
i have a hunch that the fields in Desired BaseSeq think i want to answer them. but i thought the 2 "desired" fields would look to the other fields for their answer. Here are the fields in the qry.

Desired FiscalYear: [Enter Fiscal Year]

Desired FiscalMonth: [Enter Fiscal Month]

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


So somehow i am asked to enter the fiscal year and then the month. and then i am asked again. and it works.

do you know why that is?
thanks

 

[dhookom]

As I stated "I never use parameter queries when references to controls on forms are better".

Consider creating a form with two controls on it and using
Desired BaseSeq: DLookUp("PerSeq","tblDates","[FiscalYear]=" & Forms!frmCrit!txtFYr & " And [FiscalMonth]=" & Forms!frmCrit!txtFMth )


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]