Dlookup requiring 2 criteria

[Guest]

I am using a query to append data to a table. One of the fields to append
gets it value from another table. (2 of the fields are getting their values
from a FORM).

I want to retrieve the FiscalPeriodNumber from tblFiscalCalendar based on
the month from the form AND the year from the form.

I have tried a couple of different "syntax" approaches, but to no avail.
Can anyone suggest the correct syntax for a dlookup within a query?

Here is what I have tried:

MyPeriodNumber=DLookup("[fiscalperiodnumber]", "tblfiscalcalendar",
"[tblfiscalcalendar]![fiscalperiod] = " &
[Forms]![A_fmnuInvCntrl_R]![txtMyMonth] And
"[tblfiscalcalendar]![fiscalyear]= " & [Forms]![A_fmnuInvCntrl_R]![txtMyYear])

I have also tried this:
PerX:
DLookUp("[fiscalperiodnumber]","tblfiscalcalendar","[tblfiscalcalendar]![fiscalperiod]
= " & [TheMonth] And "[tblfiscalcalendar]![fiscalyear]= " & [TheYear])

In this example, the "themonth" and "theyear" are in the query by
referencing the form controls through query parameters.

Both attempts return a 1. It is as if the AND portion is being ignored and
it simply returns the first information from the first record in the fiscal
calendar where the years match. Syntax . . . punctuation is everything, I
guess!

Help! Thanks!

 

[Ken Snell [MVP]]

The AND word must be inside the quotes, not outside of them.

DLookUp("[fiscalperiodnumber]","tblfiscalcalendar","[tblfiscalcalendar]![fiscalperiod]
= " & [TheMonth] & " And [tblfiscalcalendar]![fiscalyear]= " & [TheYear])

 

[Guest]

Ken, I changed it to this:

PerX:
DLookUp("[fiscalperiodnumber]","tblfiscalcalendar","[tblfiscalcalendar]![fiscalperiod]
= " & [TheMonth] & " And [tblfiscalcalendar]![fiscalyear]= " & [TheYear])

but now get an error that says: Microsoft Office Access can't find the name
'February' you entered in the expression.

The control form displays the month in a text control and it is February.
The control form also displays the year in a text control = 2005. After
getting the error message, the query successfully returns the data with
"TheMonth" and "TheYear" columns populated to February & 2005, the column
"PerX" is blank. The query reads the control form fields fine for these
columns.

Any ideas? And thanks for the speedy reply!

The AND word must be inside the quotes, not outside of them.

DLookUp("[fiscalperiodnumber]","tblfiscalcalendar","[tblfiscalcalendar]![fiscalperiod]
= " & [TheMonth] & " And [tblfiscalcalendar]![fiscalyear]= " & [TheYear])
--

Ken Snell
<MS ACCESS MVP>

I am using a query to append data to a table. One of the fields to append
gets it value from another table. (2 of the fields are getting their
values
from a FORM).

I want to retrieve the FiscalPeriodNumber from tblFiscalCalendar based on
the month from the form AND the year from the form.

I have tried a couple of different "syntax" approaches, but to no avail.
Can anyone suggest the correct syntax for a dlookup within a query?

Here is what I have tried:

MyPeriodNumber=DLookup("[fiscalperiodnumber]", "tblfiscalcalendar",
"[tblfiscalcalendar]![fiscalperiod] = " &
[Forms]![A_fmnuInvCntrl_R]![txtMyMonth] And
"[tblfiscalcalendar]![fiscalyear]= " &
[Forms]![A_fmnuInvCntrl_R]![txtMyYear])

I have also tried this:
PerX:
DLookUp("[fiscalperiodnumber]","tblfiscalcalendar","[tblfiscalcalendar]![fiscalperiod]
= " & [TheMonth] And "[tblfiscalcalendar]![fiscalyear]= " & [TheYear])

In this example, the "themonth" and "theyear" are in the query by
referencing the form controls through query parameters.

Both attempts return a 1. It is as if the AND portion is being ignored
and
it simply returns the first information from the first record in the
fiscal
calendar where the years match. Syntax . . . punctuation is everything, I
guess!

Help! Thanks!

 

[Ken Snell [MVP]]

Is [tblfiscalcalendar]![fiscalperiod] a text field that holds the names of
months? Or is it a number field that holds the number for a month (e.g.,
February would be the number 2)?

Assuming that it's a text field, delimit the string from TheMonth with '
characters to show that it's a text string:

DLookUp("[fiscalperiodnumber]","tblfiscalcalendar","[fiscalperiod] = '" &
[TheMonth] & "' And [fiscalyear]= " & [TheYear])


Note that I also removed the redundant (and not needed) [tblfiscalcalendar]!
references in the expression.
--

Ken Snell
<MS ACCESS MVP>

Ken, I changed it to this:

PerX:
DLookUp("[fiscalperiodnumber]","tblfiscalcalendar","[tblfiscalcalendar]![fiscalperiod]
= " & [TheMonth] & " And [tblfiscalcalendar]![fiscalyear]= " & [TheYear])

but now get an error that says: Microsoft Office Access can't find the
name
'February' you entered in the expression.

The control form displays the month in a text control and it is February.
The control form also displays the year in a text control = 2005. After
getting the error message, the query successfully returns the data with
"TheMonth" and "TheYear" columns populated to February & 2005, the column
"PerX" is blank. The query reads the control form fields fine for these
columns.

Any ideas? And thanks for the speedy reply!

The AND word must be inside the quotes, not outside of them.

DLookUp("[fiscalperiodnumber]","tblfiscalcalendar","[tblfiscalcalendar]![fiscalperiod]
= " & [TheMonth] & " And [tblfiscalcalendar]![fiscalyear]= " & [TheYear])
--

Ken Snell
<MS ACCESS MVP>

I am using a query to append data to a table. One of the fields to
append
gets it value from another table. (2 of the fields are getting their
values
from a FORM).

I want to retrieve the FiscalPeriodNumber from tblFiscalCalendar based
on
the month from the form AND the year from the form.

I have tried a couple of different "syntax" approaches, but to no
avail.
Can anyone suggest the correct syntax for a dlookup within a query?

Here is what I have tried:

MyPeriodNumber=DLookup("[fiscalperiodnumber]", "tblfiscalcalendar",
"[tblfiscalcalendar]![fiscalperiod] = " &
[Forms]![A_fmnuInvCntrl_R]![txtMyMonth] And
"[tblfiscalcalendar]![fiscalyear]= " &
[Forms]![A_fmnuInvCntrl_R]![txtMyYear])

I have also tried this:
PerX:
DLookUp("[fiscalperiodnumber]","tblfiscalcalendar","[tblfiscalcalendar]![fiscalperiod]
= " & [TheMonth] And "[tblfiscalcalendar]![fiscalyear]= " & [TheYear])

In this example, the "themonth" and "theyear" are in the query by
referencing the form controls through query parameters.

Both attempts return a 1. It is as if the AND portion is being ignored
and
it simply returns the first information from the first record in the
fiscal
calendar where the years match. Syntax . . . punctuation is
everything, I
guess!

Help! Thanks!

 

[Guest]

Yes, it is a text field and so is the "TheYear" data field. I will try the
additional punctuation tomorrow! Thanks!

Is [tblfiscalcalendar]![fiscalperiod] a text field that holds the names of
months? Or is it a number field that holds the number for a month (e.g.,
February would be the number 2)?

Assuming that it's a text field, delimit the string from TheMonth with '
characters to show that it's a text string:

DLookUp("[fiscalperiodnumber]","tblfiscalcalendar","[fiscalperiod] = '" &
[TheMonth] & "' And [fiscalyear]= " & [TheYear])


Note that I also removed the redundant (and not needed) [tblfiscalcalendar]!
references in the expression.
--

Ken Snell
<MS ACCESS MVP>

Ken, I changed it to this:

PerX:
DLookUp("[fiscalperiodnumber]","tblfiscalcalendar","[tblfiscalcalendar]![fiscalperiod]
= " & [TheMonth] & " And [tblfiscalcalendar]![fiscalyear]= " & [TheYear])

but now get an error that says: Microsoft Office Access can't find the
name
'February' you entered in the expression.

The control form displays the month in a text control and it is February.
The control form also displays the year in a text control = 2005. After
getting the error message, the query successfully returns the data with
"TheMonth" and "TheYear" columns populated to February & 2005, the column
"PerX" is blank. The query reads the control form fields fine for these
columns.

Any ideas? And thanks for the speedy reply!

The AND word must be inside the quotes, not outside of them.

DLookUp("[fiscalperiodnumber]","tblfiscalcalendar","[tblfiscalcalendar]![fiscalperiod]
= " & [TheMonth] & " And [tblfiscalcalendar]![fiscalyear]= " & [TheYear])
--

Ken Snell
<MS ACCESS MVP>

I am using a query to append data to a table. One of the fields to
append
gets it value from another table. (2 of the fields are getting their
values
from a FORM).

I want to retrieve the FiscalPeriodNumber from tblFiscalCalendar based
on
the month from the form AND the year from the form.

I have tried a couple of different "syntax" approaches, but to no
avail.
Can anyone suggest the correct syntax for a dlookup within a query?

Here is what I have tried:

MyPeriodNumber=DLookup("[fiscalperiodnumber]", "tblfiscalcalendar",
"[tblfiscalcalendar]![fiscalperiod] = " &
[Forms]![A_fmnuInvCntrl_R]![txtMyMonth] And
"[tblfiscalcalendar]![fiscalyear]= " &
[Forms]![A_fmnuInvCntrl_R]![txtMyYear])

I have also tried this:
PerX:
DLookUp("[fiscalperiodnumber]","tblfiscalcalendar","[tblfiscalcalendar]![fiscalperiod]
= " & [TheMonth] And "[tblfiscalcalendar]![fiscalyear]= " & [TheYear])

In this example, the "themonth" and "theyear" are in the query by
referencing the form controls through query parameters.

Both attempts return a 1. It is as if the AND portion is being ignored
and
it simply returns the first information from the first record in the
fiscal
calendar where the years match. Syntax . . . punctuation is
everything, I
guess!

Help! Thanks!