DBLookup problem in a query

[Guest]

Hello,

I have a problem with a dblookup in my query. Here is the code I have:

Month: DLookUp("[Month]","Months","[FiscalMonth]=" &
Forms!frmPNInputTime![Fiscal Month])

Now, it works for the initial part of the code when not using the criteria
portion of the code. What is going wrong?

Thank you,
MN

 

[Guest]

If FiscalMonth is text (i.e January) and not numeric (i.e 1 for January) then
you need
a single apostrophe before and after to signify text.

Month: DLookUp("[Month]","Months","[FiscalMonth]= '" &
Forms!frmPNInputTime![Fiscal Month] & "'")

 

[Guest]

Dennis,

Thanks. I tried that, didn't work. I keep getting a dialog box that comes
up prompting me to enter a fiscal month value (Forms!frmPNInputTime![Fiscal
Month] comes up in that box). I don't want that to happen. A date is
selected before that, which should bring the correct fiscal month for that
week onto the form and not prompt me like that.

Thanks,
MN

If FiscalMonth is text (i.e January) and not numeric (i.e 1 for January) then
you need
a single apostrophe before and after to signify text.

Month: DLookUp("[Month]","Months","[FiscalMonth]= '" &
Forms!frmPNInputTime![Fiscal Month] & "'")

Hello,

I have a problem with a dblookup in my query. Here is the code I have:

Month: DLookUp("[Month]","Months","[FiscalMonth]=" &
Forms!frmPNInputTime![Fiscal Month])

Now, it works for the initial part of the code when not using the criteria
portion of the code. What is going wrong?

Thank you,
MN

 

[Guest]

Hi.

If FiscalMonth is not a numerical data type, then the value assigned to it
must be delineated as a string. This can be single quotes or a pair of
double quotes. For example (all one line):

Month: DLookUp("[Month]","Months","[FiscalMonth]= '" &
Forms!frmPNInputTime![Fiscal Month]) & "'"

Ensure that the frmPNInputTime form is open when this query is run and that
it has a value. Also, Month is a reserved word, so I would suggest using a
different name for the field and alias, so as to prevent unexpected results
in expressions (i.e., bugs).

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.

 

[Guest]

Hi.

I keep getting a dialog box that comes
up prompting me to enter a fiscal month value (Forms!frmPNInputTime![Fiscal
Month] comes up in that box).

Ensure that the form is open, the form's name is spelled correctly, and the
control's name is spelled correctly, as well. If the "Fiscal Month" field is
a numeric data type (you didn't say), then the single quotes delineating the
value aren't needed in the syntax.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.

Dennis,

Thanks. I tried that, didn't work. I keep getting a dialog box that comes
up prompting me to enter a fiscal month value (Forms!frmPNInputTime![Fiscal
Month] comes up in that box). I don't want that to happen. A date is
selected before that, which should bring the correct fiscal month for that
week onto the form and not prompt me like that.

Thanks,
MN

If FiscalMonth is text (i.e January) and not numeric (i.e 1 for January) then
you need
a single apostrophe before and after to signify text.

Month: DLookUp("[Month]","Months","[FiscalMonth]= '" &
Forms!frmPNInputTime![Fiscal Month] & "'")

Hello,

I have a problem with a dblookup in my query. Here is the code I have:

Month: DLookUp("[Month]","Months","[FiscalMonth]=" &
Forms!frmPNInputTime![Fiscal Month])

Now, it works for the initial part of the code when not using the criteria
portion of the code. What is going wrong?

Thank you,
MN

 

[Guest]

Thank you !!
Both FiscalMonth and "Fiscal Month" are numeric fields....no text. As it
turns out, I DID spell the form name wrong and completely missed that error.
However, i still get an error saying "Syntax Error (missing operator) in
query expression "[FiscalMonth]= "

See anything I'm doing wrong?

Thank you again!!

Hi.

I keep getting a dialog box that comes
up prompting me to enter a fiscal month value (Forms!frmPNInputTime![Fiscal
Month] comes up in that box).

Ensure that the form is open, the form's name is spelled correctly, and the
control's name is spelled correctly, as well. If the "Fiscal Month" field is
a numeric data type (you didn't say), then the single quotes delineating the
value aren't needed in the syntax.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.

Dennis,

Thanks. I tried that, didn't work. I keep getting a dialog box that comes
up prompting me to enter a fiscal month value (Forms!frmPNInputTime![Fiscal
Month] comes up in that box). I don't want that to happen. A date is
selected before that, which should bring the correct fiscal month for that
week onto the form and not prompt me like that.

Thanks,
MN

If FiscalMonth is text (i.e January) and not numeric (i.e 1 for January) then
you need
a single apostrophe before and after to signify text.

Month: DLookUp("[Month]","Months","[FiscalMonth]= '" &
Forms!frmPNInputTime![Fiscal Month] & "'")

:

Hello,

I have a problem with a dblookup in my query. Here is the code I have:

Month: DLookUp("[Month]","Months","[FiscalMonth]=" &
Forms!frmPNInputTime![Fiscal Month])

Now, it works for the initial part of the code when not using the criteria
portion of the code. What is going wrong?

Thank you,
MN

 

[Guest]

Hi.

You're welcome.

However, i still get an error saying "Syntax Error (missing operator) in
query expression "[FiscalMonth]= "

See anything I'm doing wrong?

It appears that the info from the form isn't being read, but it's hard to
tell with only that snippet of the code showing. Please copy and paste the
entire new expression from your query into your next post so that we can take
a look at it.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.

Thank you !!
Both FiscalMonth and "Fiscal Month" are numeric fields....no text. As it
turns out, I DID spell the form name wrong and completely missed that error.
However, i still get an error saying "Syntax Error (missing operator) in
query expression "[FiscalMonth]= "

See anything I'm doing wrong?

Thank you again!!

Hi.

I keep getting a dialog box that comes
up prompting me to enter a fiscal month value (Forms!frmPNInputTime![Fiscal
Month] comes up in that box).

Ensure that the form is open, the form's name is spelled correctly, and the
control's name is spelled correctly, as well. If the "Fiscal Month" field is
a numeric data type (you didn't say), then the single quotes delineating the
value aren't needed in the syntax.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.

Dennis,

Thanks. I tried that, didn't work. I keep getting a dialog box that comes
up prompting me to enter a fiscal month value (Forms!frmPNInputTime![Fiscal
Month] comes up in that box). I don't want that to happen. A date is
selected before that, which should bring the correct fiscal month for that
week onto the form and not prompt me like that.

Thanks,
MN

:

If FiscalMonth is text (i.e January) and not numeric (i.e 1 for January) then
you need
a single apostrophe before and after to signify text.

Month: DLookUp("[Month]","Months","[FiscalMonth]= '" &
Forms!frmPNInputTime![Fiscal Month] & "'")

:

Hello,

I have a problem with a dblookup in my query. Here is the code I have:

Month: DLookUp("[Month]","Months","[FiscalMonth]=" &
Forms!frmPNInputTime![Fiscal Month])

Now, it works for the initial part of the code when not using the criteria
portion of the code. What is going wrong?

Thank you,
MN

 

[Guest]

Here is the new expression in the query....

FisMonth: DLookUp("[Month]","Months","[FiscalMonth]= " &
Forms!frmPMInputTime![Fiscal Month])

See anything wrong?

Hi.

You're welcome.

However, i still get an error saying "Syntax Error (missing operator) in
query expression "[FiscalMonth]= "

See anything I'm doing wrong?

It appears that the info from the form isn't being read, but it's hard to
tell with only that snippet of the code showing. Please copy and paste the
entire new expression from your query into your next post so that we can take
a look at it.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.

Thank you !!
Both FiscalMonth and "Fiscal Month" are numeric fields....no text. As it
turns out, I DID spell the form name wrong and completely missed that error.
However, i still get an error saying "Syntax Error (missing operator) in
query expression "[FiscalMonth]= "

See anything I'm doing wrong?

Thank you again!!

Hi.

I keep getting a dialog box that comes
up prompting me to enter a fiscal month value (Forms!frmPNInputTime![Fiscal
Month] comes up in that box).

Ensure that the form is open, the form's name is spelled correctly, and the
control's name is spelled correctly, as well. If the "Fiscal Month" field is
a numeric data type (you didn't say), then the single quotes delineating the
value aren't needed in the syntax.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.


:

Dennis,

Thanks. I tried that, didn't work. I keep getting a dialog box that comes
up prompting me to enter a fiscal month value (Forms!frmPNInputTime![Fiscal
Month] comes up in that box). I don't want that to happen. A date is
selected before that, which should bring the correct fiscal month for that
week onto the form and not prompt me like that.

Thanks,
MN

:

If FiscalMonth is text (i.e January) and not numeric (i.e 1 for January) then
you need
a single apostrophe before and after to signify text.

Month: DLookUp("[Month]","Months","[FiscalMonth]= '" &
Forms!frmPNInputTime![Fiscal Month] & "'")

:

Hello,

I have a problem with a dblookup in my query. Here is the code I have:

Month: DLookUp("[Month]","Months","[FiscalMonth]=" &
Forms!frmPNInputTime![Fiscal Month])

Now, it works for the initial part of the code when not using the criteria
portion of the code. What is going wrong?

Thank you,
MN

 

[Guest]

Hi.

I was able to duplicate this error message in two instances:

1.) When the value in the control on the form was a NULL value.

2.) When I ran the query, but left the form in Design View. The form needs
to be in Form View when the query runs in order for the query to pick up the
value in the form.

If the problem is a NULL value, then ensure that the expression compensates
for this. For example, try the following syntax (all one line):

FisMonth: DLookUp("[Month]","Months","[FiscalMonth]= " &
Nz(Forms!frmPMInputTime![Fiscal Month], 0))

Also, I notice that the control on the form has the control source with a
space between Fiscal and Month, while the Months table has no such space in
this field name. Does this accurately reflect your control's control source
name and your field's name? It's often better to use the same field name to
reflect the same attribute, even if the field name exists in multiple tables,
so as to avoid confusion and inadvertent bugs when someone mistypes the field
name because it's spelled correctly in a different table (a really
hard-to-find bug).

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.

Thank you !!
Both FiscalMonth and "Fiscal Month" are numeric fields....no text. As it
turns out, I DID spell the form name wrong and completely missed that error.
However, i still get an error saying "Syntax Error (missing operator) in
query expression "[FiscalMonth]= "

See anything I'm doing wrong?

Thank you again!!

Hi.

I keep getting a dialog box that comes
up prompting me to enter a fiscal month value (Forms!frmPNInputTime![Fiscal
Month] comes up in that box).

Ensure that the form is open, the form's name is spelled correctly, and the
control's name is spelled correctly, as well. If the "Fiscal Month" field is
a numeric data type (you didn't say), then the single quotes delineating the
value aren't needed in the syntax.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.

Dennis,

Thanks. I tried that, didn't work. I keep getting a dialog box that comes
up prompting me to enter a fiscal month value (Forms!frmPNInputTime![Fiscal
Month] comes up in that box). I don't want that to happen. A date is
selected before that, which should bring the correct fiscal month for that
week onto the form and not prompt me like that.

Thanks,
MN

:

If FiscalMonth is text (i.e January) and not numeric (i.e 1 for January) then
you need
a single apostrophe before and after to signify text.

Month: DLookUp("[Month]","Months","[FiscalMonth]= '" &
Forms!frmPNInputTime![Fiscal Month] & "'")

:

Hello,

I have a problem with a dblookup in my query. Here is the code I have:

Month: DLookUp("[Month]","Months","[FiscalMonth]=" &
Forms!frmPNInputTime![Fiscal Month])

Now, it works for the initial part of the code when not using the criteria
portion of the code. What is going wrong?

Thank you,
MN

 

[Guest]

I see what you are saying...but the form can't be open because it runs off
that query with the new field expression. The opening of that form (form2
let's call it) depends on input from the user from another form (form1). See
what i mean? How can i get around THIS problem then? Can i get the value
from form1 instead of from form2?

Thank you!!
MN

Hi.

I was able to duplicate this error message in two instances:

1.) When the value in the control on the form was a NULL value.

2.) When I ran the query, but left the form in Design View. The form needs
to be in Form View when the query runs in order for the query to pick up the
value in the form.

If the problem is a NULL value, then ensure that the expression compensates
for this. For example, try the following syntax (all one line):

FisMonth: DLookUp("[Month]","Months","[FiscalMonth]= " &
Nz(Forms!frmPMInputTime![Fiscal Month], 0))

Also, I notice that the control on the form has the control source with a
space between Fiscal and Month, while the Months table has no such space in
this field name. Does this accurately reflect your control's control source
name and your field's name? It's often better to use the same field name to
reflect the same attribute, even if the field name exists in multiple tables,
so as to avoid confusion and inadvertent bugs when someone mistypes the field
name because it's spelled correctly in a different table (a really
hard-to-find bug).

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.

Thank you !!
Both FiscalMonth and "Fiscal Month" are numeric fields....no text. As it
turns out, I DID spell the form name wrong and completely missed that error.
However, i still get an error saying "Syntax Error (missing operator) in
query expression "[FiscalMonth]= "

See anything I'm doing wrong?

Thank you again!!

Hi.

I keep getting a dialog box that comes
up prompting me to enter a fiscal month value (Forms!frmPNInputTime![Fiscal
Month] comes up in that box).

Ensure that the form is open, the form's name is spelled correctly, and the
control's name is spelled correctly, as well. If the "Fiscal Month" field is
a numeric data type (you didn't say), then the single quotes delineating the
value aren't needed in the syntax.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.


:

Dennis,

Thanks. I tried that, didn't work. I keep getting a dialog box that comes
up prompting me to enter a fiscal month value (Forms!frmPNInputTime![Fiscal
Month] comes up in that box). I don't want that to happen. A date is
selected before that, which should bring the correct fiscal month for that
week onto the form and not prompt me like that.

Thanks,
MN

:

If FiscalMonth is text (i.e January) and not numeric (i.e 1 for January) then
you need
a single apostrophe before and after to signify text.

Month: DLookUp("[Month]","Months","[FiscalMonth]= '" &
Forms!frmPNInputTime![Fiscal Month] & "'")

:

Hello,

I have a problem with a dblookup in my query. Here is the code I have:

Month: DLookUp("[Month]","Months","[FiscalMonth]=" &
Forms!frmPNInputTime![Fiscal Month])

Now, it works for the initial part of the code when not using the criteria
portion of the code. What is going wrong?

Thank you,
MN

 

[Guest]

I actually now have the query running off of form1 now instead of form2
(frmPMInputTime) so here is the new code.

FisMonth: DLookUp("[Month]","Months","[FiscalMonth]= " &
Forms!selSelectPMDate![Fiscal Month])

It actually runs now - no errors....however, "FisMonth" is incorrect and
only puts in "January" even though the fiscal month selected was "May." So
not sure of the bug there now...

I see what you are saying...but the form can't be open because it runs off
that query with the new field expression. The opening of that form (form2
let's call it) depends on input from the user from another form (form1). See
what i mean? How can i get around THIS problem then? Can i get the value
from form1 instead of from form2?

Thank you!!
MN

Hi.

I was able to duplicate this error message in two instances:

1.) When the value in the control on the form was a NULL value.

2.) When I ran the query, but left the form in Design View. The form needs
to be in Form View when the query runs in order for the query to pick up the
value in the form.

If the problem is a NULL value, then ensure that the expression compensates
for this. For example, try the following syntax (all one line):

FisMonth: DLookUp("[Month]","Months","[FiscalMonth]= " &
Nz(Forms!frmPMInputTime![Fiscal Month], 0))

Also, I notice that the control on the form has the control source with a
space between Fiscal and Month, while the Months table has no such space in
this field name. Does this accurately reflect your control's control source
name and your field's name? It's often better to use the same field name to
reflect the same attribute, even if the field name exists in multiple tables,
so as to avoid confusion and inadvertent bugs when someone mistypes the field
name because it's spelled correctly in a different table (a really
hard-to-find bug).

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.

Thank you !!
Both FiscalMonth and "Fiscal Month" are numeric fields....no text. As it
turns out, I DID spell the form name wrong and completely missed that error.
However, i still get an error saying "Syntax Error (missing operator) in
query expression "[FiscalMonth]= "

See anything I'm doing wrong?

Thank you again!!

:

Hi.

I keep getting a dialog box that comes
up prompting me to enter a fiscal month value (Forms!frmPNInputTime![Fiscal
Month] comes up in that box).

Ensure that the form is open, the form's name is spelled correctly, and the
control's name is spelled correctly, as well. If the "Fiscal Month" field is
a numeric data type (you didn't say), then the single quotes delineating the
value aren't needed in the syntax.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.


:

Dennis,

Thanks. I tried that, didn't work. I keep getting a dialog box that comes
up prompting me to enter a fiscal month value (Forms!frmPNInputTime![Fiscal
Month] comes up in that box). I don't want that to happen. A date is
selected before that, which should bring the correct fiscal month for that
week onto the form and not prompt me like that.

Thanks,
MN

:

If FiscalMonth is text (i.e January) and not numeric (i.e 1 for January) then
you need
a single apostrophe before and after to signify text.

Month: DLookUp("[Month]","Months","[FiscalMonth]= '" &
Forms!frmPNInputTime![Fiscal Month] & "'")

:

Hello,

I have a problem with a dblookup in my query. Here is the code I have:

Month: DLookUp("[Month]","Months","[FiscalMonth]=" &
Forms!frmPNInputTime![Fiscal Month])

Now, it works for the initial part of the code when not using the criteria
portion of the code. What is going wrong?

Thank you,
MN

 

[Guest]

Hi.

however, "FisMonth" is incorrect and
only puts in "January" even though the fiscal month selected was "May." So
not sure of the bug there now...

Instead of "May," you mean "5" don't you? FiscalMonth is supposed to be a
numerical field, isn't it? The January you see in the query may be
legitimate if the fiscal year actually starts in September.

If this isn't correct, then the only things I can think of that would cause
this error are having 5 in the FiscalMonth field in the Months table
corresponding to January, not May, or else the value being passed from the
form isn't what you expect it to be.

To test the former, open the Months table and ensure that for the row where
May is in the Month field, 5 is in the FiscalMonth field. If January is in
the Month field and 5 is in the FiscalMonth field, then this is where the
value is coming from, and the fiscal year starts in September, according to
this table's values.

To test the latter, try entering the following line into a new field in your
query and run it:

MoNum: CInt(Forms!selSelectPMDate![Fiscal Month].Value)

Does MoNum show 5? If it shows 1 (or the number in the FiscalMonth field in
the Months table corresponding to January in the Month field) then this is
where the value is coming from.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.

I actually now have the query running off of form1 now instead of form2
(frmPMInputTime) so here is the new code.

FisMonth: DLookUp("[Month]","Months","[FiscalMonth]= " &
Forms!selSelectPMDate![Fiscal Month])

It actually runs now - no errors....however, "FisMonth" is incorrect and
only puts in "January" even though the fiscal month selected was "May." So
not sure of the bug there now...

I see what you are saying...but the form can't be open because it runs off
that query with the new field expression. The opening of that form (form2
let's call it) depends on input from the user from another form (form1). See
what i mean? How can i get around THIS problem then? Can i get the value
from form1 instead of from form2?

Thank you!!
MN

Hi.

I was able to duplicate this error message in two instances:

1.) When the value in the control on the form was a NULL value.

2.) When I ran the query, but left the form in Design View. The form needs
to be in Form View when the query runs in order for the query to pick up the
value in the form.

If the problem is a NULL value, then ensure that the expression compensates
for this. For example, try the following syntax (all one line):

FisMonth: DLookUp("[Month]","Months","[FiscalMonth]= " &
Nz(Forms!frmPMInputTime![Fiscal Month], 0))

Also, I notice that the control on the form has the control source with a
space between Fiscal and Month, while the Months table has no such space in
this field name. Does this accurately reflect your control's control source
name and your field's name? It's often better to use the same field name to
reflect the same attribute, even if the field name exists in multiple tables,
so as to avoid confusion and inadvertent bugs when someone mistypes the field
name because it's spelled correctly in a different table (a really
hard-to-find bug).

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.


:

Thank you !!
Both FiscalMonth and "Fiscal Month" are numeric fields....no text. As it
turns out, I DID spell the form name wrong and completely missed that error.
However, i still get an error saying "Syntax Error (missing operator) in
query expression "[FiscalMonth]= "

See anything I'm doing wrong?

Thank you again!!

:

Hi.

I keep getting a dialog box that comes
up prompting me to enter a fiscal month value (Forms!frmPNInputTime![Fiscal
Month] comes up in that box).

Ensure that the form is open, the form's name is spelled correctly, and the
control's name is spelled correctly, as well. If the "Fiscal Month" field is
a numeric data type (you didn't say), then the single quotes delineating the
value aren't needed in the syntax.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.


:

Dennis,

Thanks. I tried that, didn't work. I keep getting a dialog box that comes
up prompting me to enter a fiscal month value (Forms!frmPNInputTime![Fiscal
Month] comes up in that box). I don't want that to happen. A date is
selected before that, which should bring the correct fiscal month for that
week onto the form and not prompt me like that.

Thanks,
MN

:

If FiscalMonth is text (i.e January) and not numeric (i.e 1 for January) then
you need
a single apostrophe before and after to signify text.

Month: DLookUp("[Month]","Months","[FiscalMonth]= '" &
Forms!frmPNInputTime![Fiscal Month] & "'")

:

Hello,

I have a problem with a dblookup in my query. Here is the code I have:

Month: DLookUp("[Month]","Months","[FiscalMonth]=" &
Forms!frmPNInputTime![Fiscal Month])

Now, it works for the initial part of the code when not using the criteria
portion of the code. What is going wrong?

Thank you,
MN

 

[Guest]

Sorry! I did mean that the fiscal month selected was "5" and not "May." The
value in "fiscal month" is 1 in fact when it is run....so that is where the
problem lies. The user actually selects a particular WEEK during the year -
and depending what week it is determines the fiscal month. When a week is
selected, the fiscal month field is NOT changing to that week's corresponding
month...so I have to investigate that problem further. As far as the lookup
is concerned, thank you very much for the help! I will just have to look
into the fact that the field value is not changing when a different week is
selected.

Thanks again!

Hi.

however, "FisMonth" is incorrect and
only puts in "January" even though the fiscal month selected was "May." So
not sure of the bug there now...

Instead of "May," you mean "5" don't you? FiscalMonth is supposed to be a
numerical field, isn't it? The January you see in the query may be
legitimate if the fiscal year actually starts in September.

If this isn't correct, then the only things I can think of that would cause
this error are having 5 in the FiscalMonth field in the Months table
corresponding to January, not May, or else the value being passed from the
form isn't what you expect it to be.

To test the former, open the Months table and ensure that for the row where
May is in the Month field, 5 is in the FiscalMonth field. If January is in
the Month field and 5 is in the FiscalMonth field, then this is where the
value is coming from, and the fiscal year starts in September, according to
this table's values.

To test the latter, try entering the following line into a new field in your
query and run it:

MoNum: CInt(Forms!selSelectPMDate![Fiscal Month].Value)

Does MoNum show 5? If it shows 1 (or the number in the FiscalMonth field in
the Months table corresponding to January in the Month field) then this is
where the value is coming from.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.

I actually now have the query running off of form1 now instead of form2
(frmPMInputTime) so here is the new code.

FisMonth: DLookUp("[Month]","Months","[FiscalMonth]= " &
Forms!selSelectPMDate![Fiscal Month])

It actually runs now - no errors....however, "FisMonth" is incorrect and
only puts in "January" even though the fiscal month selected was "May." So
not sure of the bug there now...

I see what you are saying...but the form can't be open because it runs off
that query with the new field expression. The opening of that form (form2
let's call it) depends on input from the user from another form (form1). See
what i mean? How can i get around THIS problem then? Can i get the value
from form1 instead of from form2?

Thank you!!
MN

:

Hi.

I was able to duplicate this error message in two instances:

1.) When the value in the control on the form was a NULL value.

2.) When I ran the query, but left the form in Design View. The form needs
to be in Form View when the query runs in order for the query to pick up the
value in the form.

If the problem is a NULL value, then ensure that the expression compensates
for this. For example, try the following syntax (all one line):

FisMonth: DLookUp("[Month]","Months","[FiscalMonth]= " &
Nz(Forms!frmPMInputTime![Fiscal Month], 0))

Also, I notice that the control on the form has the control source with a
space between Fiscal and Month, while the Months table has no such space in
this field name. Does this accurately reflect your control's control source
name and your field's name? It's often better to use the same field name to
reflect the same attribute, even if the field name exists in multiple tables,
so as to avoid confusion and inadvertent bugs when someone mistypes the field
name because it's spelled correctly in a different table (a really
hard-to-find bug).

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.


:

Thank you !!
Both FiscalMonth and "Fiscal Month" are numeric fields....no text. As it
turns out, I DID spell the form name wrong and completely missed that error.
However, i still get an error saying "Syntax Error (missing operator) in
query expression "[FiscalMonth]= "

See anything I'm doing wrong?

Thank you again!!

:

Hi.

I keep getting a dialog box that comes
up prompting me to enter a fiscal month value (Forms!frmPNInputTime![Fiscal
Month] comes up in that box).

Ensure that the form is open, the form's name is spelled correctly, and the
control's name is spelled correctly, as well. If the "Fiscal Month" field is
a numeric data type (you didn't say), then the single quotes delineating the
value aren't needed in the syntax.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.


:

Dennis,

Thanks. I tried that, didn't work. I keep getting a dialog box that comes
up prompting me to enter a fiscal month value (Forms!frmPNInputTime![Fiscal
Month] comes up in that box). I don't want that to happen. A date is
selected before that, which should bring the correct fiscal month for that
week onto the form and not prompt me like that.

Thanks,
MN

:

If FiscalMonth is text (i.e January) and not numeric (i.e 1 for January) then
you need
a single apostrophe before and after to signify text.

Month: DLookUp("[Month]","Months","[FiscalMonth]= '" &
Forms!frmPNInputTime![Fiscal Month] & "'")

:

Hello,

I have a problem with a dblookup in my query. Here is the code I have:

Month: DLookUp("[Month]","Months","[FiscalMonth]=" &
Forms!frmPNInputTime![Fiscal Month])

Now, it works for the initial part of the code when not using the criteria
portion of the code. What is going wrong?

Thank you,
MN

 

[Guest]

Hi.

As far as the lookup
is concerned, thank you very much for the help!

You're welcome! Glad you've got that part working. Since I've helped you,
please consider signing into the Online Community and marking the replies
that gave you the information you needed to fix the problem as "Answers," so
that anyone who has the same problem in the future can use the search feature
to filter on answered questions and quickly find the same solution that
helped you.

I will just have to look
into the fact that the field value is not changing when a different week is
selected.

In the AfterUpdate( ) event of the control that makes the change, the
following code would make the change in the [Fiscal Month] field:

Me!selSelectPMDate![Fiscal Month].Value =
calculateFiscalMonth(Me!txtWeekNum.Value)

.... where Me!txtWeekNum.Value is the name of the text box that displays the
week number and calculateFiscalMonth( ) is the name of the function which
calculates the fiscal month (you'd have to write the code for this function
if you haven't already).

Good luck!

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.

Sorry! I did mean that the fiscal month selected was "5" and not "May." The
value in "fiscal month" is 1 in fact when it is run....so that is where the
problem lies. The user actually selects a particular WEEK during the year -
and depending what week it is determines the fiscal month. When a week is
selected, the fiscal month field is NOT changing to that week's corresponding
month...so I have to investigate that problem further. As far as the lookup
is concerned, thank you very much for the help! I will just have to look
into the fact that the field value is not changing when a different week is
selected.

Thanks again!

Hi.

however, "FisMonth" is incorrect and
only puts in "January" even though the fiscal month selected was "May." So
not sure of the bug there now...

Instead of "May," you mean "5" don't you? FiscalMonth is supposed to be a
numerical field, isn't it? The January you see in the query may be
legitimate if the fiscal year actually starts in September.

If this isn't correct, then the only things I can think of that would cause
this error are having 5 in the FiscalMonth field in the Months table
corresponding to January, not May, or else the value being passed from the
form isn't what you expect it to be.

To test the former, open the Months table and ensure that for the row where
May is in the Month field, 5 is in the FiscalMonth field. If January is in
the Month field and 5 is in the FiscalMonth field, then this is where the
value is coming from, and the fiscal year starts in September, according to
this table's values.

To test the latter, try entering the following line into a new field in your
query and run it:

MoNum: CInt(Forms!selSelectPMDate![Fiscal Month].Value)

Does MoNum show 5? If it shows 1 (or the number in the FiscalMonth field in
the Months table corresponding to January in the Month field) then this is
where the value is coming from.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.

I actually now have the query running off of form1 now instead of form2
(frmPMInputTime) so here is the new code.

FisMonth: DLookUp("[Month]","Months","[FiscalMonth]= " &
Forms!selSelectPMDate![Fiscal Month])

It actually runs now - no errors....however, "FisMonth" is incorrect and
only puts in "January" even though the fiscal month selected was "May." So
not sure of the bug there now...

:

I see what you are saying...but the form can't be open because it runs off
that query with the new field expression. The opening of that form (form2
let's call it) depends on input from the user from another form (form1). See
what i mean? How can i get around THIS problem then? Can i get the value
from form1 instead of from form2?

Thank you!!
MN

:

Hi.

I was able to duplicate this error message in two instances:

1.) When the value in the control on the form was a NULL value.

2.) When I ran the query, but left the form in Design View. The form needs
to be in Form View when the query runs in order for the query to pick up the
value in the form.

If the problem is a NULL value, then ensure that the expression compensates
for this. For example, try the following syntax (all one line):

FisMonth: DLookUp("[Month]","Months","[FiscalMonth]= " &
Nz(Forms!frmPMInputTime![Fiscal Month], 0))

Also, I notice that the control on the form has the control source with a
space between Fiscal and Month, while the Months table has no such space in
this field name. Does this accurately reflect your control's control source
name and your field's name? It's often better to use the same field name to
reflect the same attribute, even if the field name exists in multiple tables,
so as to avoid confusion and inadvertent bugs when someone mistypes the field
name because it's spelled correctly in a different table (a really
hard-to-find bug).

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.


:

Thank you !!
Both FiscalMonth and "Fiscal Month" are numeric fields....no text. As it
turns out, I DID spell the form name wrong and completely missed that error.
However, i still get an error saying "Syntax Error (missing operator) in
query expression "[FiscalMonth]= "

See anything I'm doing wrong?

Thank you again!!

:

Hi.

I keep getting a dialog box that comes
up prompting me to enter a fiscal month value (Forms!frmPNInputTime![Fiscal
Month] comes up in that box).

Ensure that the form is open, the form's name is spelled correctly, and the
control's name is spelled correctly, as well. If the "Fiscal Month" field is
a numeric data type (you didn't say), then the single quotes delineating the
value aren't needed in the syntax.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.


:

Dennis,

Thanks. I tried that, didn't work. I keep getting a dialog box that comes
up prompting me to enter a fiscal month value (Forms!frmPNInputTime![Fiscal
Month] comes up in that box). I don't want that to happen. A date is
selected before that, which should bring the correct fiscal month for that
week onto the form and not prompt me like that.

Thanks,
MN

:

If FiscalMonth is text (i.e January) and not numeric (i.e 1 for January) then
you need
a single apostrophe before and after to signify text.

Month: DLookUp("[Month]","Months","[FiscalMonth]= '" &
Forms!frmPNInputTime![Fiscal Month] & "'")

:

Hello,

I have a problem with a dblookup in my query. Here is the code I have:

Month: DLookUp("[Month]","Months","[FiscalMonth]=" &
Forms!frmPNInputTime![Fiscal Month])

Now, it works for the initial part of the code when not using the criteria
portion of the code. What is going wrong?

Thank you,
MN

 

[Guest]

Yep, I'm already registered and I said "Yes" to it answering my question. I
appreciate the help!!

I am going to keep trying to get this code to work, taking your advice with
the afterupdate event of that combobox with the code you specified.

Thanks again!

Hi.

As far as the lookup
is concerned, thank you very much for the help!

You're welcome! Glad you've got that part working. Since I've helped you,
please consider signing into the Online Community and marking the replies
that gave you the information you needed to fix the problem as "Answers," so
that anyone who has the same problem in the future can use the search feature
to filter on answered questions and quickly find the same solution that
helped you.

I will just have to look
into the fact that the field value is not changing when a different week is
selected.

In the AfterUpdate( ) event of the control that makes the change, the
following code would make the change in the [Fiscal Month] field:

Me!selSelectPMDate![Fiscal Month].Value =
calculateFiscalMonth(Me!txtWeekNum.Value)

... where Me!txtWeekNum.Value is the name of the text box that displays the
week number and calculateFiscalMonth( ) is the name of the function which
calculates the fiscal month (you'd have to write the code for this function
if you haven't already).

Good luck!

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.

Sorry! I did mean that the fiscal month selected was "5" and not "May." The
value in "fiscal month" is 1 in fact when it is run....so that is where the
problem lies. The user actually selects a particular WEEK during the year -
and depending what week it is determines the fiscal month. When a week is
selected, the fiscal month field is NOT changing to that week's corresponding
month...so I have to investigate that problem further. As far as the lookup
is concerned, thank you very much for the help! I will just have to look
into the fact that the field value is not changing when a different week is
selected.

Thanks again!

Hi.

however, "FisMonth" is incorrect and
only puts in "January" even though the fiscal month selected was "May." So
not sure of the bug there now...

Instead of "May," you mean "5" don't you? FiscalMonth is supposed to be a
numerical field, isn't it? The January you see in the query may be
legitimate if the fiscal year actually starts in September.

If this isn't correct, then the only things I can think of that would cause
this error are having 5 in the FiscalMonth field in the Months table
corresponding to January, not May, or else the value being passed from the
form isn't what you expect it to be.

To test the former, open the Months table and ensure that for the row where
May is in the Month field, 5 is in the FiscalMonth field. If January is in
the Month field and 5 is in the FiscalMonth field, then this is where the
value is coming from, and the fiscal year starts in September, according to
this table's values.

To test the latter, try entering the following line into a new field in your
query and run it:

MoNum: CInt(Forms!selSelectPMDate![Fiscal Month].Value)

Does MoNum show 5? If it shows 1 (or the number in the FiscalMonth field in
the Months table corresponding to January in the Month field) then this is
where the value is coming from.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.


:

I actually now have the query running off of form1 now instead of form2
(frmPMInputTime) so here is the new code.

FisMonth: DLookUp("[Month]","Months","[FiscalMonth]= " &
Forms!selSelectPMDate![Fiscal Month])

It actually runs now - no errors....however, "FisMonth" is incorrect and
only puts in "January" even though the fiscal month selected was "May." So
not sure of the bug there now...

:

I see what you are saying...but the form can't be open because it runs off
that query with the new field expression. The opening of that form (form2
let's call it) depends on input from the user from another form (form1). See
what i mean? How can i get around THIS problem then? Can i get the value
from form1 instead of from form2?

Thank you!!
MN

:

Hi.

I was able to duplicate this error message in two instances:

1.) When the value in the control on the form was a NULL value.

2.) When I ran the query, but left the form in Design View. The form needs
to be in Form View when the query runs in order for the query to pick up the
value in the form.

If the problem is a NULL value, then ensure that the expression compensates
for this. For example, try the following syntax (all one line):

FisMonth: DLookUp("[Month]","Months","[FiscalMonth]= " &
Nz(Forms!frmPMInputTime![Fiscal Month], 0))

Also, I notice that the control on the form has the control source with a
space between Fiscal and Month, while the Months table has no such space in
this field name. Does this accurately reflect your control's control source
name and your field's name? It's often better to use the same field name to
reflect the same attribute, even if the field name exists in multiple tables,
so as to avoid confusion and inadvertent bugs when someone mistypes the field
name because it's spelled correctly in a different table (a really
hard-to-find bug).

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.


:

Thank you !!
Both FiscalMonth and "Fiscal Month" are numeric fields....no text. As it
turns out, I DID spell the form name wrong and completely missed that error.
However, i still get an error saying "Syntax Error (missing operator) in
query expression "[FiscalMonth]= "

See anything I'm doing wrong?

Thank you again!!

:

Hi.

I keep getting a dialog box that comes
up prompting me to enter a fiscal month value (Forms!frmPNInputTime![Fiscal
Month] comes up in that box).

Ensure that the form is open, the form's name is spelled correctly, and the
control's name is spelled correctly, as well. If the "Fiscal Month" field is
a numeric data type (you didn't say), then the single quotes delineating the
value aren't needed in the syntax.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.


:

Dennis,

Thanks. I tried that, didn't work. I keep getting a dialog box that comes
up prompting me to enter a fiscal month value (Forms!frmPNInputTime![Fiscal
Month] comes up in that box). I don't want that to happen. A date is
selected before that, which should bring the correct fiscal month for that
week onto the form and not prompt me like that.

Thanks,
MN

:

If FiscalMonth is text (i.e January) and not numeric (i.e 1 for January) then
you need
a single apostrophe before and after to signify text.

Month: DLookUp("[Month]","Months","[FiscalMonth]= '" &
Forms!frmPNInputTime![Fiscal Month] & "'")

:

Hello,

I have a problem with a dblookup in my query. Here is the code I have:

Month: DLookUp("[Month]","Months","[FiscalMonth]=" &
Forms!frmPNInputTime![Fiscal Month])

Now, it works for the initial part of the code when not using the criteria
portion of the code. What is going wrong?

Thank you,
MN

 

[Guest]

You're very welcome and thank you very much, too, for marking my post!
Unfortunately, the Web interface is still a bit buggy and your mark was
turned into a "helpful" reply instead of an "answer" to your question. Would
you please try marking it again? The questions that aren't marked as answers
are eventually purged from the database of questions to make room for new
questions, so it will eventually disapear and not provide the intended help
for future searchers if it doesn't get the green check mark of approval.

Thanks and good luck! If you have any further problems getting it to work,
post a new question (this one is getting long in the tooth!) and we'll try to
help.

Gunny

Yep, I'm already registered and I said "Yes" to it answering my question. I
appreciate the help!!

I am going to keep trying to get this code to work, taking your advice with
the afterupdate event of that combobox with the code you specified.

Thanks again!

Hi.

As far as the lookup
is concerned, thank you very much for the help!

You're welcome! Glad you've got that part working. Since I've helped you,
please consider signing into the Online Community and marking the replies
that gave you the information you needed to fix the problem as "Answers," so
that anyone who has the same problem in the future can use the search feature
to filter on answered questions and quickly find the same solution that
helped you.

I will just have to look
into the fact that the field value is not changing when a different week is
selected.

In the AfterUpdate( ) event of the control that makes the change, the
following code would make the change in the [Fiscal Month] field:

Me!selSelectPMDate![Fiscal Month].Value =
calculateFiscalMonth(Me!txtWeekNum.Value)

... where Me!txtWeekNum.Value is the name of the text box that displays the
week number and calculateFiscalMonth( ) is the name of the function which
calculates the fiscal month (you'd have to write the code for this function
if you haven't already).

Good luck!

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.

Sorry! I did mean that the fiscal month selected was "5" and not "May." The
value in "fiscal month" is 1 in fact when it is run....so that is where the
problem lies. The user actually selects a particular WEEK during the year -
and depending what week it is determines the fiscal month. When a week is
selected, the fiscal month field is NOT changing to that week's corresponding
month...so I have to investigate that problem further. As far as the lookup
is concerned, thank you very much for the help! I will just have to look
into the fact that the field value is not changing when a different week is
selected.

Thanks again!


:

Hi.

however, "FisMonth" is incorrect and
only puts in "January" even though the fiscal month selected was "May." So
not sure of the bug there now...

Instead of "May," you mean "5" don't you? FiscalMonth is supposed to be a
numerical field, isn't it? The January you see in the query may be
legitimate if the fiscal year actually starts in September.

If this isn't correct, then the only things I can think of that would cause
this error are having 5 in the FiscalMonth field in the Months table
corresponding to January, not May, or else the value being passed from the
form isn't what you expect it to be.

To test the former, open the Months table and ensure that for the row where
May is in the Month field, 5 is in the FiscalMonth field. If January is in
the Month field and 5 is in the FiscalMonth field, then this is where the
value is coming from, and the fiscal year starts in September, according to
this table's values.

To test the latter, try entering the following line into a new field in your
query and run it:

MoNum: CInt(Forms!selSelectPMDate![Fiscal Month].Value)

Does MoNum show 5? If it shows 1 (or the number in the FiscalMonth field in
the Months table corresponding to January in the Month field) then this is
where the value is coming from.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.


:

I actually now have the query running off of form1 now instead of form2
(frmPMInputTime) so here is the new code.

FisMonth: DLookUp("[Month]","Months","[FiscalMonth]= " &
Forms!selSelectPMDate![Fiscal Month])

It actually runs now - no errors....however, "FisMonth" is incorrect and
only puts in "January" even though the fiscal month selected was "May." So
not sure of the bug there now...

:

I see what you are saying...but the form can't be open because it runs off
that query with the new field expression. The opening of that form (form2
let's call it) depends on input from the user from another form (form1). See
what i mean? How can i get around THIS problem then? Can i get the value
from form1 instead of from form2?

Thank you!!
MN

:

Hi.

I was able to duplicate this error message in two instances:

1.) When the value in the control on the form was a NULL value.

2.) When I ran the query, but left the form in Design View. The form needs
to be in Form View when the query runs in order for the query to pick up the
value in the form.

If the problem is a NULL value, then ensure that the expression compensates
for this. For example, try the following syntax (all one line):

FisMonth: DLookUp("[Month]","Months","[FiscalMonth]= " &
Nz(Forms!frmPMInputTime![Fiscal Month], 0))

Also, I notice that the control on the form has the control source with a
space between Fiscal and Month, while the Months table has no such space in
this field name. Does this accurately reflect your control's control source
name and your field's name? It's often better to use the same field name to
reflect the same attribute, even if the field name exists in multiple tables,
so as to avoid confusion and inadvertent bugs when someone mistypes the field
name because it's spelled correctly in a different table (a really
hard-to-find bug).

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.


:

Thank you !!
Both FiscalMonth and "Fiscal Month" are numeric fields....no text. As it
turns out, I DID spell the form name wrong and completely missed that error.
However, i still get an error saying "Syntax Error (missing operator) in
query expression "[FiscalMonth]= "

See anything I'm doing wrong?

Thank you again!!

:

Hi.

I keep getting a dialog box that comes
up prompting me to enter a fiscal month value (Forms!frmPNInputTime![Fiscal
Month] comes up in that box).

Ensure that the form is open, the form's name is spelled correctly, and the
control's name is spelled correctly, as well. If the "Fiscal Month" field is
a numeric data type (you didn't say), then the single quotes delineating the
value aren't needed in the syntax.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that the first and
best answers are often given to those who have a history of rewarding the
contributors who have taken the time to answer questions correctly.


:

Dennis,

Thanks. I tried that, didn't work. I keep getting a dialog box that comes
up prompting me to enter a fiscal month value (Forms!frmPNInputTime![Fiscal
Month] comes up in that box). I don't want that to happen. A date is
selected before that, which should bring the correct fiscal month for that
week onto the form and not prompt me like that.

Thanks,
MN

:

If FiscalMonth is text (i.e January) and not numeric (i.e 1 for January) then
you need
a single apostrophe before and after to signify text.

Month: DLookUp("[Month]","Months","[FiscalMonth]= '" &
Forms!frmPNInputTime![Fiscal Month] & "'")

:

Hello,

I have a problem with a dblookup in my query. Here is the code I have:

Month: DLookUp("[Month]","Months","[FiscalMonth]=" &
Forms!frmPNInputTime![Fiscal Month])

Now, it works for the initial part of the code when not using the criteria
portion of the code. What is going wrong?

Thank you,
MN

 

['69 Camaro]

It worked! Thanks for the thumbs up!

Gunny

You're very welcome and thank you very much, too, for marking my post!
Unfortunately, the Web interface is still a bit buggy and your mark was
turned into a "helpful" reply instead of an "answer" to your question.
Would
you please try marking it again? The questions that aren't marked as
answers
are eventually purged from the database of questions to make room for new
questions, so it will eventually disapear and not provide the intended
help
for future searchers if it doesn't get the green check mark of approval.

Thanks and good luck! If you have any further problems getting it to
work,
post a new question (this one is getting long in the tooth!) and we'll try
to
help.

Gunny