Converting Text to date format and filter

[Lonnie via AccessMonster.com]

Using a form to lookup data from a table and post it on a report--- multiple
selections available for search criteria. Am trying to format so that the
user can select a month (i.e. January) from a combo box on the form, and it
will pull up all records for 1/1/09 to 1/29/09 in the report.

Using an IIF statement on the text box on the report (where the date would
show):

=IIf(([text14]="march"),(dlookup"([overall production]","[dates]", >=
#3/01/09# <=#3/31/09#),"failed")

overall production is table
dates is field

keep getting error: "you may have entered an operand without an operator"

I've been staring so long at the screen my eyes are burning and Im just not
seeing the problem. Any suggestions would be helpful.

TY
Lonnie

 

[PJFry]

Try:

=IIf(([text14]="march"),(dlookup"([overall production]","[dates]", ">=
#3/01/09# AND <=#3/31/09#"),"failed")

I also added "" for the Where clause of your lookup. I usually don't do
dates in my lookups, so it possible the syntax of the "" is not correct. Try
and post back if it does not work.

 

[Lonnie via AccessMonster.com]

PJ,
Still getting the invalid syntax error as before.

=IIf(([text14]="march"),(dlookup"([overall production]","[dates]", ">=
#3/01/09# AND <=#3/31/09#"),"failed")

I also added "" for the Where clause of your lookup. I usually don't do
dates in my lookups, so it possible the syntax of the "" is not correct. Try
and post back if it does not work.

 

[PJFry]

I fiddled with it some more and duplicated the scenario. This should work:

=IIf(([text14]="march"),(dlookup"([overall production]","[dates]", "[dates]

=

#3/01/09# AND [dates] <=#3/31/09#"),"failed")


--
Regards,

PJ
Please rate this post using the vote buttons if it was helpful.

Using a form to lookup data from a table and post it on a report--- multiple
selections available for search criteria. Am trying to format so that the
user can select a month (i.e. January) from a combo box on the form, and it
will pull up all records for 1/1/09 to 1/29/09 in the report.

Using an IIF statement on the text box on the report (where the date would
show):

=IIf(([text14]="march"),(dlookup"([overall production]","[dates]", >=
#3/01/09# <=#3/31/09#),"failed")

overall production is table
dates is field

keep getting error: "you may have entered an operand without an operator"

I've been staring so long at the screen my eyes are burning and Im just not
seeing the problem. Any suggestions would be helpful.

TY
Lonnie

 

[Chegu Tom]

Just to clarify.
is [Dates] the name of a field in your table
is [Overall Production] the name of your table?

If [Dates] is a field in [Overall production] then you have the first two
references REVERSED. Try this instead

(dlookup("[dates]","[overall production]", "[dates] = #3/01/09# AND [dates]
<=#3/31/09#"))

Note also that the firs tquotation marks have been moved inside the
parentheses and a parentheses is added to the end

I fiddled with it some more and duplicated the scenario. This should work:

=IIf(([text14]="march"),(dlookup"([overall production]","[dates]",
"[dates]

=

#3/01/09# AND [dates] <=#3/31/09#"),"failed")


--
Regards,

PJ
Please rate this post using the vote buttons if it was helpful.

Using a form to lookup data from a table and post it on a report---
multiple
selections available for search criteria. Am trying to format so that the
user can select a month (i.e. January) from a combo box on the form, and
it
will pull up all records for 1/1/09 to 1/29/09 in the report.

Using an IIF statement on the text box on the report (where the date
would
show):

=IIf(([text14]="march"),(dlookup"([overall production]","[dates]", >=
#3/01/09# <=#3/31/09#),"failed")

overall production is table
dates is field

keep getting error: "you may have entered an operand without an
operator"

I've been staring so long at the screen my eyes are burning and Im just
not
seeing the problem. Any suggestions would be helpful.

TY
Lonnie

 

[Lonnie via AccessMonster.com]

Alright Chegu, we are on the right track now--- using your code, the correct
data for the month displays, but each entry has "3/01/09" for a date..... I
really dont understand that--> any ideas?

(dlookup("[dates]","[overall production]", "[dates] = #3/01/09# AND [dates]
<=#3/31/09#"))

 

[Chegu Tom]

i don't really know what your form is doing or where it is looking

The Dlookup command will always take the first value it finds even if there
are other values available. I suspect that the first date it finds is
3/01/09 over and over.

I suspect there is a better way to go about what you want, but I don't know
what that is I would need more information about your form and what it is
trying to show. Are you trying to filter for the month of March? What
happens in April?

Alright Chegu, we are on the right track now--- using your code, the
correct
data for the month displays, but each entry has "3/01/09" for a date.....
I
really dont understand that--> any ideas?

(dlookup("[dates]","[overall production]", "[dates] = #3/01/09# AND
[dates]
<=#3/31/09#"))

 

[Lonnie via AccessMonster.com]

Im using one form to search multiple tables. In the form, I was requested to
use a general month (jan, feb, mar) instead of having to type in to/from
dates. My month is an unbound combo box. On clicking the enter button on the
form, you are taken directly to the report. The report is filtered several
ways, depending on what options you choose on the form (month, shift,
initials, machine). Assuming i can find a way to filter the date correctly, I
intend to modify and use a similar method for filtering each of the other
options.
Right now, on the report header, I have the filter choices from the form
listed in attempt to easily tie the report, form, and tables together. As of
right now, the report lists the choices I make, but shows every record, each
listed as the same (i.e. the dates are all the same, the machines will be the
same, ect) even though I do recognize the seperate records.