Results 1 to 6 of 6

Thread: Query problem

  1. #1
    Join Date
    Apr 2004
    Posts
    35

    Unhappy Unanswered: Query problem

    Hi,

    I have a query and I want to select some rows based on the date. I am using "Between BegDate And EndDate " but it doesn't work, it doen't give all the rows. If I use "CallDate>BegDate and CallDate<EndDate", it doesn't work neither.

    I dont know what is the problem.

    Helppppp!!!!
    MARGA

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What are BegDate and EndDate? Parameters?

    Try BETWEEN #BegDate# AND #EndDate#
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    I'm not sure if you have your Between statement in a criteria field for the date field of the query which then requires that you furnish the beginning and ending dates each time the query is run (ie: parameters), but you may want to try changing it to "Between cd([BegDate]) And cd([EndDate]) and see it that solves the problem. Otherwise, if you could export a small part of the table you are querying on and this problem query into a new DB and zip it and post it, I'm sure one of us here could come up with a solution.

    TD

  4. #4
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by margaquinon
    Hi,

    I have a query and I want to select some rows based on the date. I am using "Between BegDate And EndDate " but it doesn't work, it doen't give all the rows. If I use "CallDate>BegDate and CallDate<EndDate", it doesn't work neither.

    I dont know what is the problem.

    Helppppp!!!!
    In SQL view of your query do something like this:
    Code:
    WHERE (((tblDateGrabber.PurchaseDate) Between [BegDate] And [EndDate]));
    OR in the QBE design view of your Query, for the field that holds the date place this in the Criteria section:
    Between [BegDate] And [EndDate]

    Whatever you place in the SquareBrackets is what the user will be promted for in a message box. So make sure it is easily expressed what they are to enter. Oh, both examples above do exactly the same thing.

    hope this helps you out,
    have a nice one,
    BUD

  5. #5
    Join Date
    Apr 2004
    Posts
    35
    Thank you everybody for your help,

    The problem was that the field wasn't a Date type. I don't understand why, since I had the field CallDate = Format( CallDate, "Short Date"), I thought that was enough. What I did was: CDate(Format(BegDate, "Short Date"), and everything is working

    Doesn't the Format function return a Date?
    MARGA

  6. #6
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    No, the Format function returns a text string. If you had a table with a field called Sales and it contained Sales Dollars and you queried on that table and wrote Format([Sales],"$#,###.00") the field would return a nicely formatted result but you couldn't add up that field because it is just text now. Same with the date. The Format() function in Access is like the Text() function in Excel.

    If the date resides in a Table, you should consider copying the table ( as a backup in case of a problem changing the table structure) and then go into Table Design and select the field that contains your date and change the Data Type to Date/Time.

    TD

Posting Permissions

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