Results 1 to 10 of 10

Thread: null query

  1. #1
    Join Date
    Nov 2007
    Posts
    93

    Unanswered: null query

    i wrote a query which throws me out the result for 3 months sale
    but if there is no sale then its not showing anything.. the condition i gave in the query is

    Between Date() And DateAdd('d',-90,Date())

    bt if nothing is sold in between these 90 days then its throwing blank ... is there any way to say as 0

    any suggestions would be helpfull

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Presuming you're using the query as the basis for a report, you could use the no data event of the report to alert the user that no records exist and stop the report from opening with

    Cancel = True

    You would also have to trap for error 2501 in the procedure that opens the report.
    Paul

  3. #3
    Join Date
    Nov 2007
    Posts
    93
    thanks for the reply

    but the query isnt showing any results ... its jst empty... so there is no point of running the report

    have to mention something in the query i believe ....

    they are 3 fields such as id, quantity, salesdate

    so it shuld show up id and quantity for last 90 days
    for example 1,20 else
    for example 2,0


    bt how to do .. i am wondering
    Last edited by sam_01; 03-12-09 at 17:28.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Yes, I realize the query is empty. Have you tried what I suggested, which is the standard way of handling this situation?
    Paul

  5. #5
    Join Date
    Nov 2007
    Posts
    93
    the values of the query1 isnt directly placed on the report

    query1 in turns acts as joins in the other querys

    so i dont think so i can make use of no data event

    or may be i am wrong ...
    any clues

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Do a DCount on the query before opening the report.
    Paul

  7. #7
    Join Date
    Nov 2007
    Posts
    93
    gues u havnt understood the point .. .

    let me be clear again

    query1 acts as a join in query2 and query3

    so if query1 is empty then its obvious query2 to be empty too

    hope i am bit close to wht i want

    thanks

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    One of us isn't understanding. Does this not work?

    Code:
    If DCount("*", "Query1") > 0 Then
      'Code here to open the report or whatever you want to do
    Else
      Msgbox "No records in query 1"
    End If
    Paul

  9. #9
    Join Date
    Nov 2007
    Posts
    93
    sorry again let me clear ..

    were there is a point of report ???

    i jst need to run a query wit the condition as 0 if found nothing

    please refer to my first post

    sorry for not being clear

  10. #10
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    what I do here is

    run the Query showing the sales Between Date() And DateAdd('d',-90,Date())

    now create a other query use the above one and then join them to the Product table (right on the Join and select one of the options 2 or 3)
    include ALL records form '???' and only those records form the '??' where the joined feilds are equal

    if you select the right one you will now see all records form the between query and the blank ones which are the one not solds

    them you can add a iif(isnull(feildname),0,feildname) which put the 0 in the coloum
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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