Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2005
    Posts
    12

    Unanswered: Problem with Text field

    I have been working on this but can't seem to get it right, so any help on this is greatly appreciated. I want to create a text field on a form which which displays a count of certain records. The field should utilize a control source which should retrieve the number I want by using the following query:
    SELECT count(dbo_Submission.ProjectID) AS Countofprojectid
    FROM dbo_Submission
    WHERE (((dbo_Submission.Submission_Status)<>"Closed" Or (dbo_Submission.Submission_Status)<>"Invoiced") AND ((dbo_Submission.Solution) like "%WISDOM PM%"))

    However, no matter how I "tweak" it, I am getting either #Name? or an error indicating I may have entered an operand without an operator. Again, any and all help is greatly appreciated.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Have your query set which does the sum and has a field in that query which is the summed total (ie. CTotal). Then in your form utilize the Dlookup function =dlookup("[CTotal]","[MyQueryName]") for value (ControlSource) of that field or set it via code (you can also make your query a function which returns a value and then set the control source to =functionname.) It looks like you may also need to tweak your SELECT query on the syntax.
    Last edited by pkstormy; 09-08-06 at 10:51.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Apr 2005
    Posts
    12
    Quote Originally Posted by pkstormy
    Have your query set which does the sum and has a field in that query which is the summed total (ie. CTotal). Then in your form utilize the Dlookup function =dlookup("[CTotal]","[MyQueryName]") for value (ControlSource) of that field or set it via code (you can also make your query a function which returns a value and then set the control source to =functionname.) It looks like you may also need to tweak your SELECT query on the syntax.
    Ouch. That's almost over my head I think. I am confused as to why I would use sum instead of count? Projectid is a number.

  4. #4
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    I'll offer a slightly different way to PKStormy which might seem more logical to you....

    Create a select query using the statement you have already given and save it.

    In your text box on your form, enter the following:
    =DCount("ProjectID","myQuery")

    where myQuery is the name of the query you created.

    By the way, I think there is something wrong with your boolean logic.

    <>"Closed" or <>"Invoice" will always return all records and therefore is rather redundant.
    Do you really mean:
    <>"Closed" And <>"Invoice"

    hth
    Chris

Posting Permissions

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