Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2006
    Posts
    10

    Question Unanswered: Newbie Needs an Expression

    First of all let me say that this is my first post and I think all of you guys and gals are amazing. I have learned ten times as much from reading your posts over the past few months as I had in my Microsoft Access level 1 and 2 course combined.
    I have been searching some posts for a few hours now and can't find the info I require so I will just ask you all straight up:

    The nutshell background: I have created a database for my employer to help forecast salaries and vacancies within the organization. The database is a based on our fiscal year Apr 1 - Mar 31 and I does not look past that point.

    The problem: I am creating some reports to list off the employees (field name: Name) in a certain position (Position Number). Now in any particular position there can be multiple employees if say one quit half way through the fiscal year and another started a month later. I want my report to only list the person in that position as of today. The reports I have created keep listing all the employees. Along with those previous listed fields are the Start Date and End Date fields

    My attempted solutions have involved building queries and attempting to use the expression builder using the Iif function with an expression similar to this:

    IIf(Date()>[Start Date] And Date()<[End Date],[Name])

    but like I said previously...am still quite novice to most access functions and am really a newbie when it come to expressions.

    Any help in this matter is much appreciated. And once again you people amaze me constantly.

    David

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I would move the logic to your where claus:

    WHERE date() BETWEEN [Start Date] AND [End Date]


    SideNote: It's generally considered best practice not to use spaces in your field names, it's not a show stopper or anything, just future reference...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Aug 2006
    Posts
    10
    Wow, thank you for the timely response.

    I tried using "WHERE date() BETWEEN [Start Date] AND [End Date]"

    But am getting the "The Expression you Entered Contains invalid Syntax, you may have entered an operand without an operator."

    Now remember I'm a newbie before you throw the book at me, but I thought the WHERE, BETWEEN and AND were the operators?

    Once again, I aplologize for my peasant's knowledge of Access. I want to take the upcoming courses offered through the local community college but they are coming to my building until the winter

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I suppose I forgot to ask what version of access you're using, this will work regardless:

    WHERE date() >= [Start date] AND date() < [End Date]
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Aug 2006
    Posts
    10
    I'm currently using 2000 (9.0.7616 SP3) if that helps any.

    I tried "WHERE date() >= [Start date] AND date() < [End Date]" and is still giving me the same error.

    Now, I don't know if this helps, but the Name, Start Date, End Datefields are being brought in from the Employee Table, while the rest of the fields are from the Position Table

    (basically any info related to the employee is stored in the employee table, while anything detailed to the position itself is likewise in the position table. The two are related through the field Position Number which appears in both tables.)

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Typo? Is [Start date] supposed to be [Start Date]? using the BETWEEN should not be a prob in 2K ... WHERE (DATE BETWEEN [Start Date] AND [End Date]) ... Yeah, I cap all reserved words - makes it easy to distinguish ...
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Aug 2006
    Posts
    10
    Good News: Old error is gone
    Bad News: "Undefined function "WHERE" in expression"

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by brodie_182
    Good News: Old error is gone
    Bad News: "Undefined function "WHERE" in expression"
    Let's see your SQL then ...
    Back to Access ... ADO is not the way to go for speed ...

  9. #9
    Join Date
    Aug 2006
    Posts
    10
    SELECT DISTINCTROW [Position Table].[Position #], [Position Table].[Position Count], [Position Table].[Position Status], [Position Table].[Position Category], [Employee Table].Name, [Employee Table].[Start Date], [Employee Table].[End Date]
    FROM [Employee Table] INNER JOIN [Position Table] ON [Employee Table].[Position #] = [Position Table].[Position #]
    GROUP BY [Position Table].[Position #], [Position Table].[Position Count], [Position Table].[Position Status], [Position Table].[Position Category], [Employee Table].Name, [Employee Table].[Start Date], [Employee Table].[End Date]
    HAVING ((([Position Table].[Position Status])<>"LTIP"));


    Sorry about the delay, was whisked away into one of those useless Friday afternoon meetings. I hope this what you wanted.
    Thanks

  10. #10
    Join Date
    Aug 2006
    Posts
    10
    SELECT DISTINCTROW [Position Table].[Position #], [Position Table].[Position Count], [Position Table].[Position Status], [Position Table].[Position Category], [Employee Table].Name, [Employee Table].[Start Date], [Employee Table].[End Date]
    FROM [Employee Table] INNER JOIN [Position Table] ON [Employee Table].[Position #] = [Position Table].[Position #]
    GROUP BY [Position Table].[Position #], [Position Table].[Position Count], [Position Table].[Position Status], [Position Table].[Position Category], [Employee Table].Name, [Employee Table].[Start Date], [Employee Table].[End Date]
    HAVING ((([Position Table].[Position Status])<>"LTIP") AND (([Employee Table].Name)=WHERE("DATE" Between [Start Date] And [End Date])));

    And there it is when I try and implement the expression in the criteria of my Name field, thus giving me the error message regarding the undefined function "WHERE"

  11. #11
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    I can see why ... You're use of the Where clause is invalid ... Look at the structure of an SQL statement ...

    SELECT
    FROM
    WHERE
    GROUP BY
    HAVING
    Back to Access ... ADO is not the way to go for speed ...

  12. #12
    Join Date
    Aug 2006
    Posts
    10
    WOW! fixed! You guys are awesome!

    I have been trying to do this for about 3 days now, on and off.
    Thank you both so much for all your help. You have no idea how much respect I have for you folks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •