Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2009
    Posts
    5

    Unanswered: Problem with query in report (Access 2003)

    Hello,

    I've a query [qry_sales], showing the Turn Over [TO] value for different [month] and per different [product_category].

    I've then created a report with the purpose to show month by month the TO for each Product Category.

    I was trying to use this query applying it in the textbox control source :

    =(SELECT [TO] FROM [qry_sales] WHERE [month]="January09" AND [product_category]="Category1")

    but unsuccesfully.

    Where I'm wrong and how to solve this problem?

    Thanks a lot for the help
    Last edited by blues66; 09-25-09 at 19:23.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    First, by having a field name called TO and a field name called month.

    These kind of field names will cause problems with select statements.

    Typically, you want to store date type values in a date/time field type (ie. called SalesDate or something like that but NEVER use "reserved" words such as TO and MONTH.) You then design your query utilizing the power of that date type field with a Between #SomeDate# and #SomeDate#. You can format the "looks" of the date field any way you want (ie. you can format it to look like "January09".)


    Design your report query using the query designer and then change it from design view to SQL view and look at what they syntax might look like.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Sep 2009
    Posts
    5
    Quote Originally Posted by pkstormy
    First, by having a field name called TO and a field name called month.

    These kind of field names will cause problems with select statements.

    Typically, you want to store date type values in a date/time field type (ie. called SalesDate or something like that but NEVER use "reserved" words such as TO and MONTH.) You then design your query utilizing the power of that date type field with a Between #SomeDate# and #SomeDate#. You can format the "looks" of the date field any way you want (ie. you can format it to look like "January09".)


    Design your report query using the query designer and then change it from design view to SQL view and look at what they syntax might look like.

    Thanks for the answer.

    the name I put for the field are just an example to make easier then explanation, so sorry if I cretaed some misunderstanding.

    I'm not using [TO] but [Sum of Expr4]
    I'm not using [month] but [Expr3]
    I'm not using "January09" but [2009_01]

    If I use the query as mentioned before I've in the text box "Name?"
    If I use the following formula :
    =Dlookup("Sum Of Expr4" ,"[qry_sales]","[Expr3]='2010_01' And [product_category]='Category1')

    then I will have syntax error.

    I could not use "date between" because in this case I've previously converted the date value in string "2010_01" "2010_02" and so on.

    Thanks for your help

    Andrea

  4. #4
    Join Date
    Sep 2009
    Posts
    5
    I solved the problem by DLookup replacement of the "," with ";" .

Posting Permissions

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