Why does Access ask for parameter value when I enter criteria for a calculated field?

Hi All,

In Access 2007, I have a column that calculates an eligibility date from a previous field and it works like a dream.  Here is the syntax for that code:

ELIG: DateAdd("m",12,[HIRE])

When I enter criteria in this column,  <[DATE]  

I get asked for a Parameter Value for the the HIRE parameter.  The Hire parameter is it's own calculation and works when I don't put in the criteria.

Here is a succession of the columns:

HIRE: CDate(IIf(Len([MBHIRE])=7,Format(Mid([MBHIRE],4,2) & "-" & Mid([MBHIRE],6,2) & "-" & Mid([MBHIRE],2,2),"mm\/dd\/yyyy"),Format(Mid([MBHIRE],3,2) & "-" & Mid([MBHIRE],5,2) & "-" & Left([MBHIRE],2),"mm\/dd\/yyyy")))   *** Represents one column

ELIG: DateAdd("m",12,[HIRE])   *** Represents the column next to it.

Any help would be greatly appreciated.

-Anthony
LVL 1
Anthony6890Asked:
Who is Participating?
 
als315Connect With a Mentor Commented:
You are trying ro compare field, which is calculated in another field (and not ready yet).
Try to change it to:
ELIG: DateAdd("m",12,CDate(IIf(Len([MBHIRE])=7,Format(Mid([MBHIRE],4,2) & "-" & Mid([MBHIRE],6,2) & "-" & Mid([MBHIRE],2,2),"mm\/dd\/yyyy"),Format(Mid([MBHIRE],3,2) & "-" & Mid([MBHIRE],5,2) & "-" & Left([MBHIRE],2),"mm\/dd\/yyyy"))))
And add criteria to this field
0
 
Anthony6890Author Commented:
That's what I was figuring, but I couldn't figure out how to logically get it correct.

The formula works, except when I put:  <[DATE] in the criteria I get no results.  When I put in >[DATE] I get mixed results.  The parameter [DATE] I put in when I'm prompted to do so and the date I'm currently using in 04/30/2011.   I need to show everyone that has a date before 04/30/2011.

0
 
Dale FyeCommented:
I assume that [MBHire] is a string, and is not in standard date format.  Do you have valid entries in [MBHire] in your table for every record.  If not, then LEN() function will fail.  You might want to write a function and pass it [MBHire].  This way you could do some error checking and return a default value if [MBHIRE] is NULL.

So, which field are you using the criteria < [Date] in?  Is [Date] a field (if so it shouldn't be) in your table, or did you really mean to use < Date()  

Date is a reserved word, so you should not use it as a field name in any of your tables.

Generally, I've found that Access will allow referencing other computed columns, as long as the definition for that column precedes the reference to it.
0
 
Anthony6890Author Commented:
Thanks for the response back, but I was able to figure out the issue.  I didn't set the parameter in the Parameters area of Access.  After explicitly stating it and that it was a date/time field it worked like a dream.

To answer you question about MBHIRE, it isn't a date field field.  It's actually a numeric field that is pulled in from an AS400 so it's in format,  "cyymmdd".  I then do a conversion which lists that column as HIRE.  "HIRE" reflects the converted AS400 date into a recognized date format in Access "mm/dd/yyyy"

And thanks for the tip on using the term DATE.  I will change it to something more specific.

-Anthony
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.