Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2014
    Posts
    3

    Unanswered: Unbound Date Variable in Query

    My data set records are for project which have a [StartDate] and an [EndDate]. How do I allow a user to enter a date to determine what projects were 'open' on a particular date?

    I tried the between command using an unbound textbox from another form without success.

    I also attempted a bound control to a table to store just that variable. This works once. After the second run, the records returned include results for each date (even if the same date).

    Thanks for any help.
    Steven
    Last edited by SCPO; 07-18-14 at 14:02. Reason: Poor grammer

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    as ever its all down to the where clause
    you want all rows that were open on a specific day
    that means all rows whose start date was on or before the specified day AND those whose enddate was on or after the specified date AND thise whose start or enddate is null

    use a datepicker control to force the specified date to be a date.. lets call that control tbSpecifiedDate

    Code:
    where (isnull(startdate) or startdate<= tbspecifieddate) and (isnull(enddate) or enddate >= tbspecifieddate)
    you may also be able to use
    Code:
    where (tbspecifieddate >= nz(startdate,cdate("1999/01/01")) and ( tbspecifieddate <= nz(enddate,cdate("2999/01/01") )
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2014
    Posts
    3

    Unbound Date Variable in Query

    So I must write the SQL macro? I can write VBA well in Excel, I imagine it's not much different in Access with SQL. I'll use your code, but I really feel I'm out of my league here.

    Is there no other way to grab the floating variable?

    Again thank you for such a rapid response.

    Steven

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    sorry I don't use macros, unless forced to....
    there may well be another way of doing it, but thats the way Im used to interacting with a SQL database.

    where are you using your current approach.. let me gues you are probably using the query designer...
    if so switch to SQL view and paste the code in place of the current where clause
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jul 2014
    Posts
    3

    Thanks

    healdem

    Thanks again. You've been very helpful.

    Best regards,
    Steven

Tags for this Thread

Posting Permissions

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