Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    New Jersey
    Posts
    103
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Access Audit Help (Access 2002)

    I have an issue that I need your help

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access Audit Help (Access 2002)

    Create a query based on YearToDateEarnings.

    Add the 3 fields to the query grid, and also the following monster calculated column:

    PreviousEarnings: CCur(Nz(DLookup("[02 - Regular Earnings]", "YearToDateEarnings", "[Payroll Date] = #" & Format(Nz(DMax("[Payroll Date]", "YearToDateEarnings", "EmployeeID = " & [EmployeeID] & " AND [Payroll Date] < #" & Format([Payroll Date], "mm/dd/yyyy") & "#"), #1/1/1900#), "mm/dd/yyyy") & "#), 0))

    Set the criteria for this column to

    >[02 - Regular Earnings]

  3. #3
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    New Jersey
    Posts
    103
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Access Audit Help (Access 2002)

    Hello Hans,

    I created the query using this: The "payroll Accumulator" is the 02 and payroll date is LastUpdateDate in my table:


    PreviousEarnings: CCur(Nz(DLookup("[PayrollAccumulatorCode]","YearToDateEarnings", "[LastUpdateDate]= #"& Format(Nz(DMax(" [LastUpdateDate]","YearToDateEarnings","EmployeeID="& [EmployeeID] &"And [LastUpdateDate] < #" & Format( [LastUpdateDate] ,"mm/dd/yyyy")& "#"),#1/1/1900#),"mm/dd/yyyy") & "#),0))

    However, I am receiving an error message that reads "Expression you entered has an invalid string. A string can be up to 2048 characters long, including opening and closing quoation marks.

    What am I missing? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access Audit Help (Access 2002)

    Oops, sorry, I goofed up in a last-minute edit. There should be a double quote after the last # character:
    <code>
    ... #),"mm/dd/yyyy") & "#"),0))
    </code>
    Without that closing quote, Access thinks the rest of the SQL is part of the string beginning with # <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15>

  5. #5
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    New Jersey
    Posts
    103
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Access Audit Help (Access 2002)

    Sorry to keep bothering you but I added the extra quote after the last # sign but I am still receiving the same error message. Is there another quote that I am missing somewhere?

    PreviousEarnings: CCur(Nz(DLookup("[PayrollAccumulatorCode]", "YearToDateAmount", "[LastUpdateDate] = #" & Format(Nz(DMax("[LastUpdateDate]", "YearToDateAmount", "EmployeeID = " & [EmployeeID] & " AND [LastUpdateDate] < #" & Format([LastUpdateDate], "mm/dd/yyyy") & "#"), #1/1/1900#), "mm/dd/yyyy") & "#

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access Audit Help (Access 2002)

    It's hardly visible on screen, but you have added a "curly quote" <code><big><big>

  7. #7
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    New Jersey
    Posts
    103
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Access Audit Help (Access 2002)

    Okay...Thank you, I am not getting that error message anymore but I am getting this one now:

    Syntax Error in query expression 'EmployeeID = 60038 AND [LastUpdateDate] < #12/01/2006#'

    Any idea what this means? Why would this employee id cause a problem with my query?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access Audit Help (Access 2002)

    I think we'll need to see the database to solve this. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  9. #9
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    New Jersey
    Posts
    103
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Access Audit Help (Access 2002)

    I think I know what may be causing it? The PayrollAccumulatorCode consists of many codes such as 02, 04, 13 etc. Some codes will also only have one date. Is there a way that the expression looks at the records By grouping them by Employee ID, By Payroll Accumulator Code. Also, there are instances where there is only one record for a particular payroll accumulator code and payroll date. Can this be considered as ok?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access Audit Help (Access 2002)

    Please see my previous reply.

Posting Permissions

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