Results 1 to 6 of 6
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Comparison2 (2003)

    I found the cause of my problem <post#=587,520>post 587,520</post#>. Someone had completed a task, and put August's date in, so with the date range I was using it wasn't picking it up. I've one, hopefully, minor problem now. I entered the criteria Between #2/28/2005# And [Forms]![frmStats]![txtDate]-7) into my query, and when I run it, I put a date in and get the error message, 'The expression is typed incorrectly or is too complex' etc.
    Here's the SQL

    SELECT IIf(InStr([Actionee],"/"),Left([Actionee],InStr([Actionee],"/")-1),[Actionee]) AS Name, Count(*) AS [No Completed]
    FROM [tblDaily Production Main] INNER JOIN [tblDaily Production] ON [tblDaily Production Main].[Log No] = [tblDaily Production].[Log No]
    WHERE ((([tblDaily Production].Date) Between #2/28/2005# And [Forms]![frmStats]![txtDate]-7) AND (([tblDaily Production].Completed)="yes"))
    GROUP BY IIf(InStr([Actionee],"/"),Left([Actionee],InStr([Actionee],"/")-1),[Actionee]);
    It works if I put two dates in the criteria, but not if I use the form to input a date.

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

    Re: Comparison2 (2003)

    Does it help if you select Query | Parameters..., enter [Forms]![frmStats]![txtDate] as parameter and Date/Time as data type?

  3. #3
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Comparison2 (2003)

    That's done it thanks Hans. Just two more things you can probably help me with. How can I use the Date I input on the form, as a label on my report, sort of to show the results are for that date. Plus, how do I call the module you created for me to update the Actionee table <post#=583,540>post 583,540</post#>

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

    Re: Comparison2 (2003)

    You can place a text box on the report with control source
    <code>
    =[Forms]![frmParameters]![txtDate]
    </code>
    substituting the correct names for the form and the date text box.

    If you want to make the ListActionees procedure available to users, put a command button on a form and insert the following line on the On Click event procedure of the command button:

    Call ListActionees

  5. #5
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Comparison2 (2003)

    Come across another problem. Because I've used a Union query, where the fields with data in match up to the name where there is no data, these are left blank. Is there a way of making them 0, as they are affecting my calculations. Previously we did all this in a spreadsheet, but tha's what I want to get away from.

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

    Re: Comparison2 (2003)

    You can use expressions such as Nz([fieldname],0) to replace null (blank) values with 0.

Posting Permissions

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